Re: Tuning massive UPDATES and GROUP BY's?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Mar 11, 2011 at 21:06, fork <forkandwait@xxxxxxxxx> 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux