From: Bruce Momjian <pgman@candle.pha.pa.us>
To: ow <oneway_111@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg 7.4.rc1, Range query performance
Date: Sat, 8 Nov 2003 21:17:10 -0500 (EST)
Message-ID: <200311090217.hA92HAL00523@candle.pha.pa.us> (raw)
In-Reply-To: <20031109021012.65828.qmail@web21401.mail.yahoo.com>
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
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: pgman@candle.pha.pa.us, oneway_111@yahoo.com
Subject: Re: pg 7.4.rc1, Range query performance
In-Reply-To: <200311090217.hA92HAL00523@candle.pha.pa.us>
* 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