X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7F4D74761D8 for ; Tue, 4 Mar 2003 12:18:24 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 38166-09 for ; Tue, 4 Mar 2003 12:18:19 -0500 (EST) Received: from relay.firstnet.net.uk (inbound.firstnet.net.uk [212.103.224.21]) by postgresql.org (Postfix) with SMTP id 1A66B4761C4 for ; Tue, 4 Mar 2003 12:18:19 -0500 (EST) Received: (qmail 2563 invoked from network); 4 Mar 2003 17:18:20 -0000 Received: from unknown (HELO paulspc) (62.105.89.252) by meld.first with SMTP; 4 Mar 2003 17:18:20 -0000 From: "Paul McKay" To: "'Robert Treat'" Cc: "'Tom Lane'" , Subject: Re: Slow query performance on large table Date: Tue, 4 Mar 2003 17:19:03 -0000 Message-ID: <000601c2e272$27985900$0c64a8c0@paulspc> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 Importance: Normal In-Reply-To: <1046797349.16957.20.camel@camel> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/13 X-Sequence-Number: 1311 I used the between .. and in a vain attempt to improve performance! Running with < and > improves the performance again by about 10 times. The explain's below were ran on a test server I was using (not the live server) where I had recreated the database in order to investigate matters, so all the indexes were newly created anyway. The dual column index was the key (literally). ====================================== Paul Mckay Consultant Partner Servicing Division Clearwater-IT e:paul_mckay@clearwater-it.co.uk t:0161 877 6090 m: 07713 510946 ====================================== -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Robert Treat Sent: 04 March 2003 17:02 To: Paul McKay Cc: 'Tom Lane'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query performance on large table 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)