From: tmp <skrald@amossen.dk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query only slow on first run
Date: Wed, 28 Nov 2007 02:08:40 +0100
Message-ID: <474CBF98.6070508@amossen.dk> (raw)
In-Reply-To: <15029.1196209554@sss.pgh.pa.us>
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>
> 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
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: skrald@amossen.dk, tgl@sss.pgh.pa.us
Subject: Re: Query only slow on first run
In-Reply-To: <474CBF98.6070508@amossen.dk>
* 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