Skip site navigation (1) Skip section navigation (2)

Re: I: About "Our CLUSTER implementation is pessimal" patch

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Leonardo Francalanci <m_lists(at)yahoo(dot)it>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: I: About "Our CLUSTER implementation is pessimal" patch
Date: 2010-09-30 02:41:01
Message-ID: AANLkTinGJU+igVRE4q_mJhPUEgUzYr1RCXxQ9cFXd7hL@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, Sep 29, 2010 at 11:55 AM, Leonardo Francalanci <m_lists(at)yahoo(dot)it> wrote:
> Can someone else test the patch to see if what I found is still valid?
> I don't think it makes much sense if I'm the only one that says
> "this is faster" :)

I ran a few more performance tests on this patch. Here's what I got
for the tests Leonardo posted originally:

   * 2M rows:  22 seconds for seq. scan, 24 seconds for index scan
   * 5M rows:  139 seconds for seq. scan, 97 seconds for index scan
   * 10M rows: 256 seconds seq. scan, 611 seconds for index scan

(times are for the cluster operation only, not for the table
creations, etc. which took most of the time)

I tried a few more tests of creating a table with either 10M or 50M
rows, then deleting 90% of the rows and running a cluster. The patch
didn't fare so well here:

 * 10M rows: 84 seconds for seq. scan, 44 seconds for index scan

The seq. scan results here were obtained with the patch applied, and
without using planner hints (enable_seqscan or enable_indexscan). I
added in an ereport() call to check that use_sort was actually true.
The index scan results were obtained without the patch applied. The
SQL file I used is attached.

So I think there are definitely cases where this patch helps, but it
looks like a seq. scan is being chosen in some cases where it doesn't
help.

Test machine: MacBook Pro laptop, C2D 2.53 GHz, 4GB RAM.
Settings: shared_buffers = 16MB, work_mem and maintenance_work_mem set
from the SQL scripts.

Josh

Attachment: cluster_vs_bloat.sql
Description: text/x-sql (492 bytes)

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-09-30 02:54:19
Subject: Re: recovery.conf location
Previous:From: Itagaki TakahiroDate: 2010-09-30 02:40:10
Subject: Re: Fw: patch for pg_ctl.c to add windows service start-type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group