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
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) |