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

On Wed, Sep 29, 2010 at 11:55 AM, Leonardo Francalanci <m_lists@yahoo.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