From: ow <oneway_111@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: pg 7.4.rc1, Range query performance
Date: Sat, 8 Nov 2003 18:10:12 -0800 (PST)
Message-ID: <20031109021012.65828.qmail@web21401.mail.yahoo.com> (raw)
Postgresql 7.4.rc1 on i686 pc linux compiled by gcc 2.06.
All configuration settings are default.
Hi,
Trying to find a way to improve range query performance.
The table Test has about 30 million records.
-- DLong, Dtimestamp, Dint, etc are domains of the respective types.
create table Test (
id DLong not null,
a Dtimestamp null,
b Dint not null,
c Dint not null,
d Dstring null,
constraint PK_id primary key (id),
constraint AK_abc unique (a, b, c)
);
The following query retrieves a single record, it runs against AK index and is
extremely fast (30-150 ms) for the table of this size:
-- returns result in 30-150 ms
select * from Test
where a = '2002-09-01'
and b = 5
and c = 255
OTOH, the following range query that returns 30 records performs much slower,
about 33000 ms. The query is using AK index, as it should, but why does it take
so much longer to scan the index for the range of just 30 records? I see that
PG is hitting the disk very intensively for this query. Can the query be
rewritten, etc to improve performance? Thanks
select * from Test
where a >= '2002-09-01'
and a <= '2002-09-30'
and b = 5
and c = 255
QUERY PLAN
Index Scan using ak_abc on test (cost=0.00..106.27 rows=30 width=53) (actual
time=33.536..33200.998 rows=30 loops=1)
Index Cond: (((a)::timestamp without time zone >= '2002-09-01
00:00:00'::timestamp without time zone) AND ((a)::timestamp without time zone
<= '2002-09-30 00:00:00'::timestamp without time zone) AND ((b)::integer
= 5) AND ((c) (..)
Total runtime: 33201.219 ms
__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oneway_111@yahoo.com
Subject: Re: pg 7.4.rc1, Range query performance
In-Reply-To: <20031109021012.65828.qmail@web21401.mail.yahoo.com>
* 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