Skip site navigation (1) Skip section navigation (2)

Re: postgres clustering interactions with pg_dump

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: "Fitch, Britt" <brittf(at)telenav(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: postgres clustering interactions with pg_dump
Date: 2012-07-23 19:07:10
Message-ID: CA+TgmobrRyoLoWMCs=5CVvi5nyy+8R76nmf5ovCZHb5OX9MCxQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Jun 11, 2012 at 9:55 AM, Fitch, Britt <brittf(at)telenav(dot)com> wrote:
> Hi, I have a table that I am clustering on an index.
>
> I am then dumping that table via pg_dump –Fc and loading it into another
> database via pg_restore.
>
> It is unclear to me though if the clustering I did in the original database
> is preserved during the dump & restore or if I would still need to perform a
> CLUSTER again once the data was loaded into the new database.
>
> Can anyone confirm this?

The rows will end up in the new table in the same physical order that
they were stored in the dump file.

You might want to look at pg_stats.correlation for the clustered
column - that's often a good way to know whether things are ordered
the way you expect, and it's updated every time the table is analyzed.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2012-07-23 19:38:35
Subject: Re: High CPU Usage
Previous:From: Jim VannsDate: 2012-07-23 15:49:25
Subject: Re: Odd blocking (or massively latent) issue - even with EXPLAIN

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