X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id A2610D1B50E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  9 Nov 2003 02:10:39 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 80173-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  8 Nov 2003 22:10:12 -0400 (AST)
Received: from web21401.mail.yahoo.com (web21401.mail.yahoo.com
	[216.136.232.71])
	by svr1.postgresql.org (Postfix) with SMTP id 83819D1B504
	for <pgsql-sql@postgresql.org>; Sat,  8 Nov 2003 22:10:08 -0400 (AST)
Message-ID: <20031109021012.65828.qmail@web21401.mail.yahoo.com>
Received: from [64.81.205.234] by web21401.mail.yahoo.com via HTTP;
	Sat, 08 Nov 2003 18:10:12 PST
Date: Sat, 8 Nov 2003 18:10:12 -0800 (PST)
From: ow <oneway_111@yahoo.com>
Subject: pg 7.4.rc1, Range query performance
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/64
X-Sequence-Number: 15879

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