From: Scott Marlowe <scott.marlowe@gmail.com>
To: cluster <skrald@amossen.dk>
Cc: Steinar H. Gunderson <sgunderson@bigfoot.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query only slow on first run
Date: Thu, 29 Nov 2007 00:49:41 -0600
Message-ID: <dcc563d10711282249k52b72257mdfdd015fb9b6448a@mail.gmail.com> (raw)
In-Reply-To: <474DDA8F.3070801@amossen.dk>
References: <fihgt1$1mmh$1@news.hub.org>
<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>
<fikia6$26ql$1@news.hub.org>
<20071128203442.GB30600@uio.no>
<474DDA8F.3070801@amossen.dk>
On Nov 28, 2007 3:15 PM, cluster <skrald@amossen.dk> wrote:
> > The indexes don't contain visibility information, so Postgres has to look up
> > the row on disk to verify it isn't dead.
>
> I guess this fact drastically decreases the performance. :-(
> The number of rows with a random_number will just grow over time while
> the number of questions with status = 1 will always be somewhat constant
> at about 10.000 or most likely much less.
Have you tried a partial index?
create index xyz on tablename (random) where status = 1
> I could really use any kind of suggestion on how to improve the query in
> order to make it scale better for large data sets The 6-7000 ms for a
> clean run is really a showstopper. Need to get it below 70 ms somehow.
Also, look into clustering the table on status or random every so often.
More importantly, you might need to research a faster way to get your
random results
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: scott.marlowe@gmail.com, skrald@amossen.dk, sgunderson@bigfoot.com
Subject: Re: Query only slow on first run
In-Reply-To: <dcc563d10711282249k52b72257mdfdd015fb9b6448a@mail.gmail.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