Re: Tuning massive UPDATES and GROUP BY's?

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