From: Paul McKay <paul_mckay@clearwater-it.co.uk>
To: 'Robert Treat' <xzilla@users.sourceforge.net>
Cc: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query performance on large table
Date: Tue, 4 Mar 2003 17:19:03 -0000
Message-ID: <000601c2e272$27985900$0c64a8c0@paulspc> (raw)
In-Reply-To: <1046797349.16957.20.camel@camel>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: paul_mckay@clearwater-it.co.uk, xzilla@users.sourceforge.net, tgl@sss.pgh.pa.us
Subject: Re: Slow query performance on large table
In-Reply-To: <000601c2e272$27985900$0c64a8c0@paulspc>
References: <1046797349.16957.20.camel@camel> <000601c2e272$27985900$0c64a8c0@paulspc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox