Re: cluster index on a table - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: cluster index on a table
Date
Msg-id 4A6040DD02000025000288FB@gw.wicourts.gov
Whole thread Raw
In response to Re: cluster index on a table  (Scara Maccai <m_lists@yahoo.it>)
List pgsql-performance
Scara Maccai <m_lists@yahoo.it> wrote:

> - create table mytable as select * from <parent_table> where time
> <in last month> (this gets all the data of last month ordered in the
> "almost" correct order, because all the single tables were
> clustered)

Be sure to include an ORDER BY clause.  Without that, there is no
guarantee that even two successive SELECTs from the same table, with
no modifications between, will return rows in the same order.  For
example, if someone else starts a query which the planner determines
is best handled with a table scan, and that is still running when you
issue your INSERT/SELECT, your query will join the current scan at
it's point of progress, and "wrap around" when it hits the end.  Also,
there would be no guarantee of what order the child tables were read.

-Kevin

pgsql-performance by date:

Previous
From: Matthew Wakeling
Date:
Subject: Calling conventions
Next
From: Craig Ringer
Date:
Subject: Re: Concurrency issue under very heay loads