From: "Dave Dutcher" <dave@tridecap.com>
To: "'cluster'" <skrald@amossen.dk>,
	<pgsql-performance@postgresql.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>
Subject: Re: Query only slow on first run
Date: Tue, 27 Nov 2007 17:47:31 -0600
Message-ID: <05ae01c8314f$e0a86990$8e00a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcgxS9vMaOyNCILOSbakAZMJx38FbAAAp/4A
In-Reply-To: <fii71p$oc1$1@news.hub.org>

> -----Original Message-----
> From: cluster
> 
> 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.
> 

What version of Postgres are you using?  Do you know what your
join_collapse_limit is set to?

You might be able to force it to scan for questions with a status of 1 first
to see if it helps by changing the FROM clause to: 

FROM posts p, question_tags qt, (SELECT * FROM questions WHERE status = 1
OFFSET 0) q

Dave