X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7CFF947619B for ; Tue, 4 Mar 2003 12:02:40 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 36044-05 for ; Tue, 4 Mar 2003 12:02:39 -0500 (EST) Received: from localhost.localdomain (unknown [65.217.53.66]) by postgresql.org (Postfix) with ESMTP id 69F8F476154 for ; Tue, 4 Mar 2003 12:02:37 -0500 (EST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h24HWV6P023850; Tue, 4 Mar 2003 12:32:33 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h24H2Up03967; Tue, 4 Mar 2003 12:02:31 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id F3HTH0NK; Tue, 4 Mar 2003 12:02:29 -0500 Subject: Re: Slow query performance on large table From: Robert Treat To: Paul McKay Cc: "'Tom Lane'" , pgsql-performance@postgresql.org In-Reply-To: <000001c2e268$b1e47210$0c64a8c0@paulspc> References: <000001c2e268$b1e47210$0c64a8c0@paulspc> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 04 Mar 2003 12:02:29 -0500 Message-Id: <1046797349.16957.20.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/11 X-Sequence-Number: 1309 On Tue, 2003-03-04 at 11:11, Paul McKay wrote: > The results were > > clearview=# explain analyse > clearview-# select assessment,time > clearview-# from measurement > clearview-# where assessment = 53661 > clearview-# and time between 1046184261 and 1046335461; > > NOTICE: QUERY PLAN: > > Index Scan using idx_measurement_assessment on measurement > (cost=0.00..34668.61 rows=261 width=8) (actual time=26128.07..220584.69 > rows=503 loops=1) > Total runtime: 220587.06 msec > > EXPLAIN > > After adding the index kindly suggested by yourself and Tomasz I get, > > clearview=# explain analyse > clearview-# select assessment,time > clearview-# from measurement > clearview-# where assessment = 53661 > clearview-# and time between 1046184261 and 1046335461; > NOTICE: QUERY PLAN: > > Index Scan using ind_measurement_ass_time on measurement > (cost=0.00..1026.92 rows=261 width=8) (actual time=15.37..350.46 > rows=503 loops=1) > Total runtime: 350.82 msec > > EXPLAIN > > > I vaguely recall doing a bit of a reorganize on this database a bit back > and it looks like I lost the primary Key index. No wonder it was going > slow. > Maybe it's just me, but I get the feeling you need to work some regular reindexing into your maintenance schedule. Given your query is using between, I don't think it would use the index on the time field anyway (and explain analyze seems to be supporting this). Rewrite it so that you have a and time > foo and time < bar and I think you'll see a difference. With that in mind, I think your speedier query results are due more to having a non-bloated index freshly created than the fact that it being a dual column index. Robert Treat