From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: cluster <skrald@amossen.dk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query only slow on first run
Date: Wed, 28 Nov 2007 00:38:33 +0100
Message-ID: <20071127233833.GA24031@uio.no> (raw)
In-Reply-To: <fii71p$oc1$1@news.hub.org>
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>
<fii71p$oc1$1@news.hub.org>
On Tue, Nov 27, 2007 at 11:51:40PM +0100, cluster wrote:
> 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.
An index scan (as opposed to a bitmap index scan) can only use one index at a
time, so it will choose the most selective one. Here it quite correctly
recognizes that there will only be one matching record for the given
question_id, so it uses the primary key instead.
You could make an index on (question_id,status) (or a partial index on
question id, with status=1 as the filter), but I'm not sure how much it would
help you unless the questions table is extremely big. It doesn't appear to
be; in fact, it appears to be all in RAM, so that's not your bottleneck.
/* Steinar */
--
Homepage: http://www.sesse.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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgunderson@bigfoot.com, skrald@amossen.dk
Subject: Re: Query only slow on first run
In-Reply-To: <20071127233833.GA24031@uio.no>
* 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