On Jun 6, 2007, at 18:27 , Kurt Overberg wrote:
select id from taskinstance where taskid in (select id from task
where campaignid = 75);
Now, I know this could (and should) be rewritten to not use the
WHERE x IN () style, but this is actually a sub-query to a larger
query.
Granted, it won't explain why this particular query is slower in 8.2,
but it shouldn't be to hard to drop in something like
SELECT id
FROM taskinstance
NATURAL JOIN (
SELECT id AS taskid, campaignid
FROM tasks) t
WHERE campaignid = 75
AIUI, the planner can sometimes rewrite IN as a join, but I don't
know whether or not that's is happening in this case. I'm guessing
not as I see nested loops in the plans. (I'm a novice at reading
plans, so take this with at least a teaspoon of salt. :) )
if I run the query again, it gets successively faster (50,000ms-
>6000ms->27ms). Is this normal? If I change the campaignid from
75 to another number, it jumps back to 50,000ms, which leads me to
believe that postgresql is somehow caching the results of the query
and not figuring out a better way to run the query.
As the query is repeated, the associated rows are probably already in
memory, leading to the speedups you're seeing.
-- 8.2
Recheck Cond: (taskinstance.taskid = task.id)
-> Bitmap Index Scan on taskid_taskinstance_key
(cost=0.00..20.57 rows=556 width=0) (actual time=54.709..54.709
rows=196 loops=9)
Index Cond: (taskinstance.taskid = task.id)
-- 8.0
-> Index Scan using taskid_taskinstance_key on taskinstance
(cost=0.00..2152.28 rows=563 width=8) (actual time=0.012..0.832
rows=145 loops=11)
Index Cond: (taskinstance.taskid = "outer".id)
I see that the row estimates in both of the query plans are off a
little. Perhaps increasing the statistics would help? Also, you can
see that 8.2 is using bitmap scans, which aren't available in 8.0.
Perhaps try setting enable_bitmapscan off and running the query again
to see if there's a performance difference.
The weird thing is that on 8.2, I don't see any sequential scans
taking place, it seems to be properly using the indexes.
As an aside, whether the planner decides to use a sequential scan or
an index has more to do with the particular query: indexes are not a
guaranteed performance win.
Hope this helps a bit.
Michael Glaesemann
grzm seespotcode net
