Message-ID: <47D54A47.3030608@postnewspapers.com.au>
Date: Mon, 10 Mar 2008 23:48:39 +0900
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>,
	pgsql-performance@postgresql.org
Subject: Re: Very slow (2 tuples/second) sequential scan after bulk
	insert; speed returns to ~500 tuples/second after commit
References: <47D505AF.4040101@postnewspapers.com.au>
	<47D5150C.8090802@enterprisedb.com>
	<23588.1205159638@sss.pgh.pa.us>
In-Reply-To: <23588.1205159638@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> "Heikki Linnakangas" <heikki@enterprisedb.com> writes:
>> For 8.4, it would be nice to improve that. I tested that on my laptop 
>> with a similarly-sized table, inserting each row in a pl/pgsql function 
>> with an exception handler, and I got very similar run times. According 
>> to oprofile, all the time is spent in TransactionIdIsInProgress. I think 
>> it would be pretty straightforward to store the committed subtransaction 
>> ids in a sorted array, instead of a linked list, and binary search.
> 
> I think the OP is not complaining about the time to run the transaction
> that has all the subtransactions; he's complaining about the time to
> scan the table that it emitted.

Yes, but only in succeeding statements in the same transaction as the 
procedure that creates all the subtransactions. Table scan times return 
to normal after that transaction commits.

> Presumably, each row in the table has a
> different (sub)transaction ID and so we are thrashing the clog lookup
> mechanism.  It only happens once because after that the XMIN_COMMITTED
> hint bits are set.

It seems to happen with every statement run in the same transaction as, 
and after, the procedure with all the subtransactions. As soon as a 
COMMIT is executed, operations return to normal speed. There's no 
significant delay on the first statement after COMMIT as compared to 
subsequent statements, nor do successive statements before the COMMIT 
get faster.

In other words, if I repeatedly run one of the statements I used in 
testing for my initial post, like:

EXPLAIN ANALYZE SELECT * FROM booking;

... after running the problem stored procedure, it takes just as long 
for the second and third and so on runs as for the first.

As soon as I commit the transaction, the exact same statement returns to 
running in less than a second, and doesn't significantly change in 
runtime for subsequent executions.

I'll bang out a couple of examples at work tomorrow to see what I land 
up with, since this is clearly something that can benefit from a neat 
test case.

In any case, avoding the use of an exception block per record generated 
worked around the performance issues, so it's clearly something to do 
with the vast numbers of subtransactions - as Heikki Linnakangas 
suggested and tested.

--
Craig Ringer