Re: Cluster table and order information

On 2/1/2011 9:08 AM, Dario Beraldi wrote:
> Hello,
>
>  From the documentation of CLUSTER table
> (http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I
> understand that clustering can be achieved by re-creating the table like
> this:
>
> CREATE TABLE newtable AS
> SELECT * FROM table ORDER BY columnlist;
>
> My question is: If I upload with COPY a datafile which is already
> correctly sorted, can I inform postgres of such order, so that no
> clustering is necessary after the import? In other words, how can I tell
> postgres that my file is order by this and that column?
>
> Many thanks!
>
> Dario
>

The planner has no knowledge of cluster.  Meaning PG will query a
clustered and unclustered table exactly the same way.  A table is not
marked or anything as clustered.  And in fact, during usage of a table
it'll become unclustered.

Clustering is only useful when you are going to read multiple records in
the same order as an index.   It turns "more random seeks" into "more
sequential reads".

If your COPY loads data in indexed order, then just dont run the cluster.

-Andy