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 7CDA0D1B4E6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  9 Nov 2003 02:17:37 +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 82360-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  8 Nov 2003 22:17:10 -0400 (AST)
Received: from candle.pha.pa.us (unknown [207.106.42.251])
	by svr1.postgresql.org (Postfix) with ESMTP id CE730D1B4FD
	for <pgsql-sql@postgresql.org>; Sat,  8 Nov 2003 22:17:05 -0400 (AST)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.11.6) id hA92HAL00523;
	Sat, 8 Nov 2003 21:17:10 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200311090217.hA92HAL00523@candle.pha.pa.us>
Subject: Re: pg 7.4.rc1, Range query performance
In-Reply-To: <20031109021012.65828.qmail@web21401.mail.yahoo.com>
To: ow <oneway_111@yahoo.com>
Date: Sat, 8 Nov 2003 21:17:10 -0500 (EST)
Cc: pgsql-sql@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL108 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/65
X-Sequence-Number: 15880


Try CLUSTER --- that usually helps with index scans on ranges.

---------------------------------------------------------------------------

ow wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073