Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id CD8AE2E0309 for ; Mon, 10 Mar 2008 12:03:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 03339-01-6 for ; Mon, 10 Mar 2008 12:03:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail01.enterprisedb.com (webmail.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id 2183A2E2E89 for ; Mon, 10 Mar 2008 12:03:17 -0300 (ADT) thread-index: AciCv/QohWqp6cKrRseUNl1LRazolQ== Received: from [172.24.32.184] ([62.232.55.118]) by mail01.enterprisedb.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.3959); Mon, 10 Mar 2008 11:03:52 -0400 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4133 Message-ID: <47D54DB2.4020405@enterprisedb.com> Date: Mon, 10 Mar 2008 15:03:14 +0000 From: "Heikki Linnakangas" Organization: EnterpriseDB User-Agent: Mozilla-Thunderbird 2.0.0.9 (X11/20080110) MIME-Version: 1.0 To: "Craig Ringer" Cc: "Tom Lane" , 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> <47D54A47.3030608@postnewspapers.com.au> In-Reply-To: <47D54A47.3030608@postnewspapers.com.au> Content-Type: text/plain; format=flowed; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 10 Mar 2008 15:03:52.0546 (UTC) FILETIME=[F4154C20:01C882BF] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/200 X-Sequence-Number: 29154 Craig Ringer wrote: > 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. Here's what I used to reproduce this: postgres=# BEGIN; BEGIN postgres=# CREATE TABLE foo (id int4,t text);CREATE TABLE postgres=# CREATE OR REPLACE FUNCTION insertfunc() RETURNS void LANGUAGE plpgsql AS $$ begin INSERT INTO foo VALUES ( 1, repeat('a',110)); exception when unique_violation THEN end; $$; CREATE FUNCTION postgres=# SELECT COUNT(insertfunc()) FROM generate_series(1,300000); count -------- 300000 (1 row) postgres=# EXPLAIN ANALYZE SELECT COUNT(*) FROM foo; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Aggregate (cost=13595.93..13595.94 rows=1 width=0) (actual time=239535.904..239535.906 rows=1 loops=1) -> Seq Scan on foo (cost=0.00..11948.34 rows=659034 width=0) (actual time=0.022..239133.898 rows=300000 loops=1) Total runtime: 239535.974 ms (3 rows) The oprofile output is pretty damning: samples % symbol name 42148 99.7468 TransactionIdIsCurrentTransactionId If you put a COMMIT right before "EXPLAIN ANALYZE..." it runs in < 1s. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com