Re: Slow query performance on large table

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(at)clearwater-it(dot)co(dot)uk
t:0161 877 6090
m: 07713 510946
======================================

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 04 March 2003 15:13
To: Paul McKay
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Slow query performance on large table

"Paul McKay" <paul_mckay(at)clearwater-it(dot)co(dot)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