Re: Weird 8.2.4 performance

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