From: Craig James <craig_james@emolecules.com>
To: tmp <skrald@amossen.dk>
To: pgsql-performance@postgresql.org
Subject: Re: Query only slow on first run
Date: Wed, 28 Nov 2007 06:56:33 -0800
Message-ID: <474D81A1.20301@emolecules.com> (raw)
In-Reply-To: <474CBF98.6070508@amossen.dk>
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>
<474CBF98.6070508@amossen.dk>
tmp wrote:
>> 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].
A trick that I used is to sample the random column once, and create a much smaller table of the first N rows, where N is the sample size you want, and use that.
If you need a different N samples each time, you can create a temporary table, put your random N rows into that, do an ANALYZE, and then join to this smaller table. The overall performance can be MUCH faster even though you're creating and populating a whole table, than the plan that Postgres comes up with. This seems wrong-headed (why shouldn't Postgres be able to be as efficient on its own?), but it works.
Craig
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: craig_james@emolecules.com, skrald@amossen.dk
Subject: Re: Query only slow on first run
In-Reply-To: <474D81A1.20301@emolecules.com>
* 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