X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D84DC476130 for ; Tue, 4 Mar 2003 11:10:45 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 20395-06 for ; Tue, 4 Mar 2003 11:10:41 -0500 (EST) Received: from relay.firstnet.net.uk (inbound.firstnet.net.uk [212.103.224.21]) by postgresql.org (Postfix) with SMTP id 0A415475ED1 for ; Tue, 4 Mar 2003 11:10:41 -0500 (EST) Received: (qmail 23261 invoked from network); 4 Mar 2003 16:10:37 -0000 Received: from unknown (HELO paulspc) (62.105.89.252) by meld.first with SMTP; 4 Mar 2003 16:10:37 -0000 From: "Paul McKay" To: "'Tom Lane'" Cc: Subject: Re: Slow query performance on large table Date: Tue, 4 Mar 2003 16:11:20 -0000 Message-ID: <000001c2e268$b1e47210$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: <23081.1046790781@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/9 X-Sequence-Number: 1307 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. Thanks a lot for your help. Paul Mckay. ====================================== 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: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: 04 March 2003 15:13 To: Paul McKay Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query performance on large table "Paul McKay" writes: > The query I am executing is > Select time,value > From measurement > Where assessment = ? > And time between ? and ? EXPLAIN ANALYZE would help you investigate this. Is it using an indexscan? On which index? Does forcing use of the other index (by temporarily dropping the preferred one) improve matters? Possibly a two-column index on both assessment and time would be an improvement, but it's hard to guess without knowing anything about the selectivity of the two WHERE clauses. regards, tom lane