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: Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>
Cc: Leonardo Francalanci <m_lists(at)yahoo(dot)it>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: I: About "Our CLUSTER implementation is pessimal" patch
Date: 2010-09-29 03:53:33
Message-ID: AANLkTimQxxis81hh1weqVtwx_HU7exE_Mr8Ki0zPp3Bf@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Sep 27, 2010 at 10:05 PM, Itagaki Takahiro
<itagaki(dot)takahiro(at)gmail(dot)com> wrote:
> I re-ordered some description in the doc. Does it look better?
> Comments and suggestions welcome.

I thought this paragraph was a little confusing:

!     In the second case, a full table scan is followed by a sort operation.
!     The method is faster than the first one when the table is highly
fragmented.
!     You need twice disk space of the sum in the case. In addition to the free
!     space needed by the previous case, this approach may also need a temporary
!     disk sort file which can be as big as the original table.

I think the worst-case disk space could be made a little more clear
here, and maybe some general wordsmithing as well. I wasn't sure what
"twice disk space of the sum" was in this description -- sum of what
(table and all indexes?). And does "twice disk space" include the
temporary disk sort file? Here's an idea of how I think this paragraph
could be cleaned up a bit, if my understanding of the disk space
required is about right:

!     In the second case, a full table scan is followed by a sort operation.
!     This method is faster than when the table is highly fragmented.
!     However, <command>CLUSTER</command> may require available disk space of
!     up to twice the sum of the size of the table and its indexes, if
it uses a temporary
!     disk sort file, which can be as big as the original table.

Also, AIUI, this second clustering method is similar to the older
idiom of CREATE TABLE new AS SELECT * FROM old ORDER BY col; Since the
paragraph describing this older idiom is being removed, perhaps a
brief mention in the documentation could be made of this similarity.

Some more wordsmithing: change
!      The planner tries to choose a faster method in them base on the
information
to:
!      The planner tries to choose the fastest method based on the information

I started looking at the performance impact of this patch based on
Leonardo's SQL file. On the 2 million row table, I see a consistent
~10% advantage for the sequential scan clusters. I'm going to try to
run the bigger tests a few times and post results from there when I
get a chance.

Josh

In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2010-09-29 04:27:33
Subject: Re: Proposal: plpgsql - "for in array" statement
Previous:From: Fujii MasaoDate: 2010-09-29 03:40:51
Subject: Re: Using streaming replication as log archiving

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