Performance improvement hints

From: devik(at)cdi(dot)cz
To: pgsql-hackers(at)hub(dot)org
Subject: Performance improvement hints
Date: 2000-09-12 12:30:09
Message-ID: 39BE21D1.FB8BD89E@cdi.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,
I have encountered problems with particular query so that
a started to dug into sources. I've two questions/ideas:

1) when optimizer computes size of join it does it as
card(R1)*card(R2)*selectivity. Suppose two relations
(R1 & R2) each 10000 rows. If you (inner) join them
using equality operator, the result is at most 10000
rows (min(card(R1),card(R2)). But pg estimates
1 000 000 (uses selectivity 0.01 here).
Then when computing cost it will result in very high
cost in case of hash and loop join BUT low (right)
cost for merge join. It is because for hash and loop
joins the cost is estimated from row count but merge
join uses another estimation (as it always know that
merge join can be done only on equality op).
It then leads to use of mergejoin for majority of joins.
Unfortunately I found that in majority of such cases
the hash join is two times faster.
I tested it using SET ENABLE_MERGEJOIN=OFF ...
What about to change cost estimator to use min(card(R1),
card(R2)) instead of card(R1)*card(R2)*selectivity in
case where R1 and R2 are connected using equality ?
It should lead to much faster plans for majority of SQLs.

2) suppose we have relation R1(id,name) and index ix(id,name)
on it. In query like: select id,name from R1 order by id
planner will prefer to do seqscan+sort (althought the R1
is rather big). And yes it is really faster than using
indexscan.
But indexscan always lookups actual record in heap even if
all needed attributes are contained in the index.
Oracle and even MSSQL reads attributes directly from index
without looking for actual tuple at heap.
Is there any need to do it in such ineffecient way ?

regards, devik

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jules Bean 2000-09-12 12:45:45 Re: Performance improvement hints
Previous Message Kovacs Zoltan 2000-09-12 12:28:34 strange behaviour (bug)