From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | ow <oneway_111(at)yahoo(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: pg 7.4.rc1, Range query performance |
Date: | 2003-11-11 00:50:56 |
Message-ID: | 200311110050.hAB0ouQ11856@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-11-11 00:57:51 | Re: pg 7.4.rc1, Range query performance |
Previous Message | ow | 2003-11-11 00:41:39 | Re: pg 7.4.rc1, Range query performance |