Message-ID: <4FF58717.2000901@ringerc.id.au>
Date: Thu, 05 Jul 2012 20:22:47 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Albe Laurenz <laurenz.albe@wien.gv.at>
CC: Akash Kodibail *EXTERN* <akash.kodibail@onmobile.com>,
	pgsql-admin@postgresql.org
Subject: Re: Delay in completion of aggregation inserts when run in
	a single commit - PG 9.1.2
References: 
 <67A07D932F8DCC41989A050EDE427A210CF9B5@JETWINSRVRPS02.onmobile.com>
	<D960CB61B694CF459DCFB4B0128514C2081BEC77@exadv11.host.magwien.gv.at>
In-Reply-To: 
 <D960CB61B694CF459DCFB4B0128514C2081BEC77@exadv11.host.magwien.gv.at>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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