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