Re: pg 7.4.rc1, Range query performance - Mailing list pgsql-sql

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,
Pennsylvania19073