From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steinar H. Gunderson <sgunderson@bigfoot.com>
Cc: cluster <skrald@amossen.dk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query only slow on first run
Date: Tue, 27 Nov 2007 19:25:54 -0500
Message-ID: <15029.1196209554@sss.pgh.pa.us> (raw)
In-Reply-To: <20071127233833.GA24031@uio.no>
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>
<20071127233833.GA24031@uio.no>
"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes:
> 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.
Wouldn't help, because the accesses to "questions" are not the problem.
The query's spending nearly all its time in the scan of "posts", and
I'm wondering why --- doesn't seem like it should take 6400msec to fetch
646 rows, unless perhaps the data is just horribly misordered relative
to the index. Which may in fact be the case ... what exactly is that
"random_number" column, and why are you desirous of ordering by it?
For that matter, if it is what it sounds like, why is it sane to group
by it? You'll probably always get groups of one row ...
regards, tom lane
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: tgl@sss.pgh.pa.us, sgunderson@bigfoot.com, skrald@amossen.dk
Subject: Re: Query only slow on first run
In-Reply-To: <15029.1196209554@sss.pgh.pa.us>
* 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