Re: Slow query performance on large table - Mailing list pgsql-performance

From Paul McKay
Subject Re: Slow query performance on large table
Date
Msg-id 000001c2e268$b1e47210$0c64a8c0@paulspc
Whole thread Raw
In response to Re: Slow query performance on large table  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Slow query performance on large table  (Robert Treat <xzilla@users.sourceforge.net>)
Re: Slow query performance on large table  (Josh Berkus <josh@agliodbs.com>)
List pgsql-performance
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" <paul_mckay@clearwater-it.co.uk> 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


pgsql-performance by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: Slow query performance on large table
Next
From: Andreas Pflug
Date:
Subject: Re: Slow query performance on large table