pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Glaesemann <grzm@seespotcode.net>
To: Kurt Overberg <kurt@hotdogrecords.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Weird 8.2.4 performance
Date: Wed, 6 Jun 2007 19:14:17 -0500
Message-ID: <7229E474-9550-4419-A665-429D3881A1CB@seespotcode.net> (raw)
In-Reply-To: <371D1936-E96B-4865-AEEF-C82AC41C2982@hotdogrecords.com>
References: <371D1936-E96B-4865-AEEF-C82AC41C2982@hotdogrecords.com>

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





view thread (12+ messages)  latest in thread

Message-ID: <7229E474-9550-4419-A665-429D3881A1CB@seespotcode.net>
Permalink:  ../7229E474-9550-4419-A665-429D3881A1CB@seespotcode.net/
Also on:    postgresql.org/message-id/7229E474-9550-4419-A665-429D3881A1CB@seespotcode.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: grzm@seespotcode.net, kurt@hotdogrecords.com
  Subject: Re: Weird 8.2.4 performance
  In-Reply-To: <7229E474-9550-4419-A665-429D3881A1CB@seespotcode.net>
  References: <371D1936-E96B-4865-AEEF-C82AC41C2982@hotdogrecords.com> <7229E474-9550-4419-A665-429D3881A1CB@seespotcode.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox