From: ow <oneway_111@yahoo.com>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg 7.4.rc1, Range query performance
Date: Mon, 10 Nov 2003 16:30:39 -0800 (PST)
Message-ID: <20031111003039.88131.qmail@web21405.mail.yahoo.com> (raw)
In-Reply-To: <200311090217.hA92HAL00523@candle.pha.pa.us>
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
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: oneway_111@yahoo.com, pgman@candle.pha.pa.us
Subject: Re: pg 7.4.rc1, Range query performance
In-Reply-To: <20031111003039.88131.qmail@web21405.mail.yahoo.com>
* 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