Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Ux98s-0000Yd-Cs for pgsql-hackers@arkaria.postgresql.org; Thu, 11 Jul 2013 05:10:10 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Ux98r-0004Fe-HV for pgsql-hackers@arkaria.postgresql.org; Thu, 11 Jul 2013 05:10:09 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Ux98q-0004FR-4R for pgsql-hackers@postgresql.org; Thu, 11 Jul 2013 05:10:08 +0000 Received: from raven.toroid.org ([178.63.84.200]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Ux98i-0008Co-QJ for pgsql-hackers@postgresql.org; Thu, 11 Jul 2013 05:10:07 +0000 Received: from pipit.toroid.org (unknown [10.9.8.10]) by raven.toroid.org (Postfix) with ESMTP id 6181FE500AE; Thu, 11 Jul 2013 07:09:59 +0200 (CEST) Received: by pipit.toroid.org (Postfix, from userid 1000) id 820B6460226; Thu, 11 Jul 2013 10:39:58 +0530 (IST) Date: Thu, 11 Jul 2013 10:39:58 +0530 From: Abhijit Menon-Sen To: Josh Berkus Cc: Simon Riggs , Mark Kirkwood , Tom Lane , Gavin Flower , pgsql-hackers@postgresql.org Subject: Re: [PERFORM] In progress INSERT wrecks plans on table Message-ID: <20130711050958.GA23755@toroid.org> References: <916bff05a5ab130c76d7835abbaf7762.squirrel@mail.catalyst.net.nz> <51870CB6.6090404@catalyst.net.nz> <20130624044350.GA32322@toroid.org> <51DAF2C8.6010705@agliodbs.com> <51DD9026.2030107@agliodbs.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <51DD9026.2030107@agliodbs.com> X-Pg-Spam-Score: -0.0 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org At 2013-07-10 09:47:34 -0700, josh@agliodbs.com wrote: > > Due to the apparent lack of performance testing, I'm setting this back > to "needs review". The original submission (i.e. the message linked from the CF page) includes test results that showed a clear performance improvement. Here's an excerpt: > OK, here's a easily reproducible test... > > Prep: > DROP TABLE IF EXISTS plan; > CREATE TABLE plan > ( > id INTEGER NOT NULL, > typ INTEGER NOT NULL, > dat TIMESTAMP, > val TEXT NOT NULL > ); > insert into plan select generate_series(1,100000), 0, > current_timestamp, 'some texts'; > CREATE UNIQUE INDEX plan_id ON plan(id); > CREATE INDEX plan_dat ON plan(dat); > > testcase.pgb > select count(*) from plan where dat is null and typ = 3; > > Session 1: > pgbench -n -f testcase.pgb -t 100 > > Session 2: > BEGIN; insert into plan select 1000000 + generate_series(1, 100000), > 3, NULL, 'b'; > > Transaction rate in Session 1: (in tps) > (a) before we run Session 2: > Current: 5600tps > Patched: 5600tps > > (b) after Session 2 has run, yet before transaction end > Current: 56tps > Patched: 65tps > > (c ) after Session 2 has aborted > Current/Patched: 836, 1028, 5400tps > VACUUM improves timing again > > New version of patch attached which fixes a few bugs. > > Patch works and improves things, but we're still swamped by the block > accesses via the index. -- Abhijit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers