Message-ID: <474CBF98.6070508@amossen.dk>
Date: Wed, 28 Nov 2007 02:08:40 +0100
From: tmp <skrald@amossen.dk>
User-Agent: Thunderbird 2.0.0.6 (X11/20071022)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query only slow on first run
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>
	<15029.1196209554@sss.pgh.pa.us>
In-Reply-To: <15029.1196209554@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

Yes, they probably are. I use the random_number column in order to 
receive a semi random sample subset from the large amount of rows. The 
technique is described in [1]. This subset is later used for some 
statistical investigation, but this is somewhat irrelevant here. In 
order to receive the sample fast, I have made an index on the 
random_number column.

> what exactly is that
> "random_number" column

A random float that is initialized when the row is created and never 
modified afterwards. The physical row ordering will clearly not match 
the random_number ordering. However, other queries uses a row ordering 
by the primary key so I don't think it would make much sense to make the 
index on random_number a clustering index just in order to speed up this 
single query.

>  and why are you desirous of ordering by it?

In order to simulate a random pick of K rows. See [1].

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

For each random_number, another table (question_tags) holds zero or more 
rows satisfying a number of constraints. I need to count(*) the number 
of corresponding question_tag rows for each random_number.

We have primarily two tables of interest here: questions (~100k rows) 
and posts (~400k rows). Each post refers to a question, but only the 
"posts" rows for which the corresponding "question.status = 1" are 
relevant. This reduces the number of relevant question rows to about 
10k. Within the post rows corresponding to these 10k questions I would 
like to pick a random sample of size K.

[1] http://archives.postgresql.org/pgsql-general/2007-10/msg01240.php