On 07/05/2012 04:00 PM, Albe Laurenz wrote:
Akash Kodibail wrote:
Problem:
[...]
- Aggregation process almost never completes which is a set of 15
insert queries. Entire
process happens in a single commit (population of staging tables and
aggregation).
- When I run these 15 queries individually, it happens in no time.
Could you be kind enough to explain how differently queries run when
in a "single commit" as opposed
to running each of them individually?
Apart from minor differences (current_timestamp, ...) the
main difference is that there is no COMMIT after each query.
COMMIT is expensive since it requires a write to hard disk.
Try to get the execution plans of the queries in both cases
and see if you can spot a difference.
Actually, that's a thought. Autovaccum can't see uncomitted work, and
thus can't analyze it. Maybe they need an explicit ANALYZE or two after
a bulk insert or update during their data load.
--
Craig Ringer
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
