From: cluster <skrald@amossen.dk>
To: pgsql-performance@postgresql.org
Subject: Re: Query only slow on first run
Date: Tue, 27 Nov 2007 23:51:40 +0100
Message-ID: <fii71p$oc1$1@news.hub.org> (raw)
In-Reply-To: <056501c83128$55d58a90$8e00a8c0@tridecap.com>
References: <fihgt1$1mmh$1@news.hub.org>
<20071127165543.GY11978@crankycanuck.ca>
<23999.1196184368@sss.pgh.pa.us>
<fihn7h$2jrn$1@news.hub.org>
<056501c83128$55d58a90$8e00a8c0@tridecap.com>
> As for optimizing the query, I noticed that all three joins are done by
> nested loops. I wonder if another join method would be faster. Have you
> analyzed all the tables?
Yes. I did a VACUUM FULL ANALYZE before running the test queries. Also I
have just performed an ANALYZE just to be sure everything was really
analyzed.
> You aren't disabling hash joins or merge joins are
> you?
Nope.
> If you aren't, then as a test I would try disabling nested loops by
> doing "set enable_nestloop=false" and see if the query is any faster for
> you.
If I disable the nested loops, the query becomes *much* slower.
A thing that strikes me is the following. As you can see I have the
constraint: q.status = 1. Only a small subset of the data set has this
status. I have an index on q.status but for some reason this is not
used. Instead the constraint are ensured with a "Filter: (q.status = 1)"
in an index scan for the primary key in the "q" table. If the small
subset having q.status = 1 could be isolated quickly using an index, I
would expect the query to perform better. I just don't know why the
planner doesn't use the index on q.status.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: skrald@amossen.dk
Subject: Re: Query only slow on first run
In-Reply-To: <fii71p$oc1$1@news.hub.org>
* 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