Re: Tuning massive UPDATES and GROUP BY's?

From: Marti Raudsepp <marti(at)juffo(dot)org>
To: fork <forkandwait(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Tuning massive UPDATES and GROUP BY's?
Date: 2011-03-12 17:07:29
Message-ID: AANLkTi=dGuvB_EuU43CbFUeuaG57-WXvypw+A1WKHhZ8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Fri, Mar 11, 2011 at 21:06, fork <forkandwait(at)gmail(dot)com> wrote:
> Like the following?  Will it rebuild the indexes in a sensical way?

Don't insert data into an indexed table. A very important point with
bulk-loading is that you should load all the data first, then create
the indexes. Running multiple (different) CREATE INDEX queries in
parallel can additionally save a lot of time. Also don't move data
back and forth between the tables, just drop the original when you're
done.

Doing this should give a significant performance win. Partitioning
them to fit in cache should improve it further, but I'm not sure
anymore that it's worthwhile considering the costs and extra
maintenance.

> Is there a rule of thumb on tradeoffs in a partitioned table?

The only certain thing is that you'll lose "group" aggregate and
"merge join" query plans. If you only see "HashAggregate" plans when
you EXPLAIN your GROUP BY queries then it probably won't make much of
a difference.

> I would use the partition column whatever I am most likely
> to cluster by in a single big table, right?

Yes.

Regards,
Marti

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeremy 2011-03-13 12:21:47 Re: Planner wrongly shuns multi-column index for select .. order by col1, col2 limit 1
Previous Message John Surcombe 2011-03-12 10:07:41 Planner wrongly shuns multi-column index for select .. order by col1, col2 limit 1