X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4DD5247611E for ; Tue, 4 Mar 2003 09:44:49 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 01446-10 for ; Tue, 4 Mar 2003 09:44:47 -0500 (EST) Received: from relay.firstnet.net.uk (inbound.firstnet.net.uk [212.103.224.21]) by postgresql.org (Postfix) with SMTP id 99B0447611C for ; Tue, 4 Mar 2003 09:44:46 -0500 (EST) Received: (qmail 18724 invoked from network); 4 Mar 2003 14:44:35 -0000 Received: from unknown (HELO paulspc) (62.105.89.252) by tulyar.first with SMTP; 4 Mar 2003 14:44:35 -0000 From: "Paul McKay" To: Subject: Slow query performance on large table Date: Tue, 4 Mar 2003 14:45:18 -0000 Message-ID: <000001c2e25c$ad506170$0c64a8c0@paulspc> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C2E25C.AD51E810" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/5 X-Sequence-Number: 1303 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C2E25C.AD51E810 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, I am executing a query on a table: Table "public.measurement" Column | Type | Modifiers ------------+-----------------------+----------- assessment | integer | time | integer | value | character varying(50) | Indexes: idx_measurement_assessment btree (assessment), idx_measurement_time btree ("time") The primary key of the table is a combination of assessment and time, and there are indexes on both assessment and time. The query I am executing is Select time,value From measurement Where assessment = ? And time between ? and ? This used to run like a rocket before my database got a little larger. There are now around 15 million rows in the table and it is taking a long time to execute queries that get a fair number of rows back (c.300) The database is 'VACUUM ANALYZED' regularly, and I've upped the shared buffers to a significant amount. I've tried it on various machine configurations now. A dual processor Linux/Intel Machine with 1G of Memory, (0.5G shared buffers). A single processor Linux/Intel Machine (0.25G shared buffers) , and a Solaris machine (0.25G shared buffers). I'm getting similar performance on all of them. Anybody see anything I've obviously done wrong? Any ways of improving the performance of this query? Thanks in advance. Paul McKay. ====================================== Paul Mckay Consultant Partner Servicing Division Clearwater-IT e:paul_mckay@clearwater-it.co.uk t:0161 877 6090 m: 07713 510946 ====================================== ------=_NextPart_000_0001_01C2E25C.AD51E810 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

I am executing a query on a table:

 

         &n= bsp; Table "public.measurement"

   Column   |         Type          | Modifiers

------------+-----------------------+-----------<= /font>

 assessment | integer           &n= bsp;   |

 time       | integer           &n= bsp;   |

 value      | character varying(50) |

Indexes: idx_measurement_assessment btree (assessment),<= /span>

         idx_mea= surement_time btree ("time")

 

The primary key of the table is a combination of assessm= ent and time, and there are indexes on both assessment and time.<= /p>

 

The query I am executing is

 

Select time,value

From measurement

Where assessment =3D ?

And time between ? and ?

 

This used to run like a rocket before my database got a little larger.  There are now around 15 million rows in the table and = it is taking a long time to execute queries that get a fair number of rows back (c.300)

 

The database is  ‘VACUUM ANALYZED’ regu= larly, and I’ve upped the shared buffers to a significant amount.

 

I’ve tried it on various machine configurations no= w. A dual processor Linux/Intel Machine with 1G of Memory, (0.5G shared buffers).  A single processor Linux/Intel Machine (0.25G shared buffer= s) , and a Solaris machine (0.25G shared buffers).  I’m getting simil= ar performance on all of them.

 

Anybody see anything I’ve obviously done wrong?&nb= sp; Any ways of improving the performance of this query?

 

Thanks in advance.

 

Paul McKay.

 

 

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<= /p>

Paul Mckay

Consultant Partner

Servicing Division

Clearwater-IT

e:paul_mckay@clearwater-it.co.u= k

t:0161 877 6090

m: 07713 510946

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<= /p>

 

------=_NextPart_000_0001_01C2E25C.AD51E810--