"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes:
> You could make an index on (question_id,status) (or a partial index on
> question id, with status=1 as the filter), but I'm not sure how much it would
> help you unless the questions table is extremely big. It doesn't appear to
> be; in fact, it appears to be all in RAM, so that's not your bottleneck.
Wouldn't help, because the accesses to "questions" are not the problem.
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 ... what exactly is that
"random_number" column, and why are you desirous of ordering by it?
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 ...
regards, tom lane