Re: Query only slow on first run

[tmp <skrald(at)amossen(dot)dk>]

> 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