Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 2677B2E002C for ; Wed, 4 Jun 2008 19:52:25 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 15376-08 for ; Wed, 4 Jun 2008 19:52:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by developer.postgresql.org (Postfix) with ESMTP id 4A4742E002E for ; Wed, 4 Jun 2008 19:52:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m54MqJcv006657; Wed, 4 Jun 2008 18:52:19 -0400 (EDT) To: andrew klassen cc: James Mansion , pgsql-performance@postgresql.org Subject: Re: insert/update tps slow with indices on table > 1M rows In-reply-to: <354284.6724.qm@web37304.mail.mud.yahoo.com> References: <354284.6724.qm@web37304.mail.mud.yahoo.com> Comments: In-reply-to andrew klassen message dated "Wed, 04 Jun 2008 14:30:13 -0700" Date: Wed, 04 Jun 2008 18:52:19 -0400 Message-ID: <6656.1212619939@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/31 X-Sequence-Number: 30381 andrew klassen writes: > I am using the c-library interface and for these particular transactions > I preload PREPARE statements. Then as I get requests, I issue a BEGIN, > followed by at most 300 EXECUTES and then a COMMIT. That is the > general scenario. What value beyond 300 should I try? Well, you could try numbers in the low thousands, but you'll probably get only incremental improvement. > Also, how might COPY (which involves file I/O) improve the > above scenario? COPY needn't involve file I/O. If you are using libpq you can push anything you want into PQputCopyData. This would involve formatting the data according to COPY's escaping rules, which are rather different from straight SQL, but I doubt it'd be a huge amount of code. Seems worth trying. regards, tom lane