Re: pg 7.4.rc1, Range query performance

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073