From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200311110050.hAB0ouQ11856@candle.pha.pa.us>
Subject: Re: pg 7.4.rc1, Range query performance
In-Reply-To: <20031111003039.88131.qmail@web21405.mail.yahoo.com>
To: ow <oneway_111@yahoo.com>
Date: Mon, 10 Nov 2003 19:50:56 -0500 (EST)
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

ow wrote:
> 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"

Strange 30 records takes 30x the time than one record.  Can you run
ANALYZE and send us an EXPLAIN of the query to make sure it hasn't
changed?

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