Andrew Sullivan <ajs@crankycanuck.ca> writes: > On Tue, Nov 27, 2007 at 05:33:36PM +0100, cluster wrote: >> I have a query that takes about 7000 ms in average to complete the first >> time it runs. Subsequent runs complete in only 50 ms. That is more than >> a factor 100 faster! How can I make the query perform good in the first >> run too? > Probably by buying much faster disk hardware. Or buy more RAM, so that the data can stay cached. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

"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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend

"Dave Dutcher" <dave@tridecap.com> writes: > ... According to the explain analyze > there are only 646 rows in posts which match your criteria, so it does seem > like scanning posts first might be the right thing to do. No, that's not right. What the output actually shows is that only 646 posts rows were needed to produce the first 200 aggregate rows, which was enough to satisfy the LIMIT. The planner is evidently doing things this way in order to exploit the presence of the LIMIT --- if it had to compute all the aggregate results it would likely have picked a different plan. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

cluster <skrald@amossen.dk> writes: > 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. Buy a faster disk? You're essentially asking for a random sample of data that is not currently in memory. You're not going to get that without some I/O. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

cluster <skrald@amossen.dk> writes: >> You're essentially asking for a random sample of data that is not >> currently in memory. You're not going to get that without some I/O. > No, that sounds reasonable enough. But do you agree with the statement > that my query will just get slower and slower over time as the number of > posts increases while the part having status = 1 is constant? No, not as long as it sticks to that plan. The time's basically determined by the number of aggregate rows the LIMIT asks for, times the average number of "post" rows per aggregate group. And as far as you said the latter number is not going to increase. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend