Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id D9D3F2E0282 for ; Tue, 27 Nov 2007 20:26:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 96312-09 for ; Tue, 27 Nov 2007 20:26:29 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 119B52E18EC for ; Tue, 27 Nov 2007 20:26:35 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lAS0Psrq015030; Tue, 27 Nov 2007 19:25:54 -0500 (EST) To: "Steinar H. Gunderson" cc: cluster , pgsql-performance@postgresql.org Subject: Re: Query only slow on first run In-reply-to: <20071127233833.GA24031@uio.no> References: <20071127165543.GY11978@crankycanuck.ca> <23999.1196184368@sss.pgh.pa.us> <056501c83128$55d58a90$8e00a8c0@tridecap.com> <20071127233833.GA24031@uio.no> Comments: In-reply-to "Steinar H. Gunderson" message dated "Wed, 28 Nov 2007 00:38:33 +0100" Date: Tue, 27 Nov 2007 19:25:54 -0500 Message-ID: <15029.1196209554@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/332 X-Sequence-Number: 27747 "Steinar H. Gunderson" 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