Message-ID: <20031111003039.88131.qmail@web21405.mail.yahoo.com>
Date: Mon, 10 Nov 2003 16:30:39 -0800 (PST)
From: ow <oneway_111@yahoo.com>
Subject: Re: pg 7.4.rc1, Range query performance
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200311090217.hA92HAL00523@candle.pha.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi,

I tried CLUSTER and it did improve performance, somewhat. The query against
"clustered" table performs about five (5) times better than the same table but
"non-clustered". However, even after that table was clustered, the difference
in performance between single record query and range query is significant:

table Test (see below) has 10M records
  single record - 31 ms and remains mostly constant as table grows
  range query returning 30 records - about 10 secs and grows together with the
table

Also, CLUSTER is locking the table (in our case this also means locking the
database), so it may be impossible to use it in production on large tables
(impossible in our case).

It feels like I really have a problem here. Any ideas? Thanks

P.S. For the future I would consider implementing "CREATE [CLUSTERED] INDEX"


--- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> 
> 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


__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree