Re: Query only slow on first run

On Nov 28, 2007 3:15 PM, cluster <skrald(at)amossen(dot)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