On Nov 28, 2007 3:15 PM, cluster <skrald@xxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
