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
