From: | Harold A(dot) Giménez Ch(dot) <harold(dot)gimenez(at)gmail(dot)com> |
---|---|
To: | Kenneth Marshall <ktm(at)rice(dot)edu> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Question about clustering indexes and restores |
Date: | 2009-01-22 20:02:19 |
Message-ID: | c807ef1a0901221202o7ec4d0e6r831f94c5a6d89d1e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Many thanks for your answer. I did see a comment about this in the
documentation on the link I posted below.
My main question remains though: Is it necessary to cluster after a restore?
Thanks again!
On Thu, Jan 22, 2009 at 2:58 PM, Kenneth Marshall <ktm(at)rice(dot)edu> wrote:
> On Thu, Jan 22, 2009 at 02:52:12PM -0500, Harold A. Gim?nez Ch. wrote:
> > Hi list,
> >
> > Clustering my indexes dramatically improves the query performance of many
> of
> > my queries. Also, the actual clustering takes a very long time for big
> > databases, roughly 20 hours. I have two questions about how to improve
> this:
> >
> > 1. I've tweaked maintenance_mem_max and effective_cache_size to a point
> > where the cluster operation uses a good chunk of my physical RAM, and the
> OS
> > does not start swapping. Is there any other parameter I should look at?
> >
> > 2. Reading the documentation for cluster at
> > http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html, I see
> that
> > all clustering does is reorder the data on disk to 'match' the order of
> the
> > clustered index. My question is, if I dump a clustered database using
> > pg_dump in custom format, is it necessary to cluster after restoring it?
> Or
> > does a dump/restore not guarantee that the order of the data restored is
> the
> > same as the original dumped database?
> >
> > 3. Somewhat related to #2, what is the best way to move data from a
> staging
> > database on one server, to the production environment on a different
> server?
> > I've been using pg_dump/pg_restore, but there must be a better way...
> >
> >
> > Thanks for any pointers,
> >
> > -Harold
>
> Harold,
>
> There have been discussions on the hackers list about the pessimal
> cluster performance. Here is a pointer to the discussion, it seems
> that a faster way is to build a new table with the desired orderwith
> "CREATE TABLE AS ... ORDER BY ...":
>
> http://www.mail-archive.com/pgsql-hackers(at)postgresql(dot)org/msg121205.html
>
> Cheers,
> Ken
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ibrahim Harrani | 2009-01-22 21:27:36 | Re: postgresql 8.3 tps rate |
Previous Message | Kenneth Marshall | 2009-01-22 19:58:06 | Re: Question about clustering indexes and restores |