Skip site navigation (1) Skip section navigation (2)

Re: Weird 8.2.4 performance

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Kurt Overberg <kurt(at)hotdogrecords(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Weird 8.2.4 performance
Date: 2007-06-07 00:14:17
Message-ID: 7229E474-9550-4419-A665-429D3881A1CB@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-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



In response to

pgsql-performance by date

Next:From: markDate: 2007-06-07 03:33:52
Subject: Re: LIKE search and performance
Previous:From: Mark KirkwoodDate: 2007-06-07 00:01:51
Subject: Re: Weird 8.2.4 performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group