From: | Dario Beraldi <dario(dot)beraldi(at)ed(dot)ac(dot)uk> |
---|---|
To: | Andy Colson <andy(at)squeakycode(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Cluster table and order information |
Date: | 2011-02-01 16:17:00 |
Message-ID: | 20110201161700.15651ccyz7g3kmjo@www.staffmail.ed.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Quoting Andy Colson <andy(at)squeakycode(dot)net>:
> 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
>
Thanks very much Andy, this clarifies my doubts.
I was misled by the docs saying "When a table is clustered, PostgreSQL
remembers which index it was clustered by" which made me think that
the order information is stored somewhere.
All the best
Dario
--
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2011-02-01 16:22:27 | Re: Cluster table and order information |
Previous Message | bricklen | 2011-02-01 16:16:07 | Re: PD_ALL_VISIBLE flag was incorrectly set in relation "pg_statistic" |