Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2

Hi All,

We have a reporting application to consume transactional log created by a billing system. The flow of data in ETL framework is from TRANSACTION_LOG->STAGING_TABLES->FACT_TABLES->AGGREGATE_TABLES.

There are around 15 aggregate tables to be populated from these staging tables. Number of records for a one time will be 1.2 million records on an average. We are expecting anywhere near to 10 such processes in a day.

Problem:

- Staging table population happens in expected time. Anywhere from 10-15 minutes for each process.

- 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?

----

The system is CentOS 5.5, Postgres installed is 9.1.2.

There are 3 postgres servers installed, each at 5432, 5433 and 5434 ports. It is a 16GB RAM with 3 NetAPP storage box mounted with 500GB each.

Postgresql.conf remains untouched, except for log writing which has been made to "all".

----

Any info greatly appreciated.

Thanks in advance,
Akash.

________________________________

DISCLAIMER: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Further, this e-mail may contain viruses and all reasonable precaution to minimize the risk arising there from is taken by OnMobile. OnMobile is not liable for any damage sustained by you as a result of any virus in this e-mail. All applicable virus checks should be carried out by you before opening this e-mail or any attachment thereto.
Thank you - OnMobile Global Limited.