Skip site navigation (1) Skip section navigation (2)

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

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Akash Kodibail *EXTERN*" <akash(dot)kodibail(at)onmobile(dot)com>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2
Date: 2012-07-05 08:00:08
Message-ID: D960CB61B694CF459DCFB4B0128514C2081BEC77@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-admin
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.
The auto_explain contrib module might come handy for that.

Yours,
Laurenz Albe

In response to

Responses

pgsql-admin by date

Next:From: Craig RingerDate: 2012-07-05 12:22:47
Subject: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2
Previous:From: Craig RingerDate: 2012-07-05 06:33:50
Subject: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group