pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Abhijit Menon-Sen <ams@2ndQuadrant.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: Simon Riggs <simon@2ndQuadrant.com>
Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gavin Flower <gavinflower@archidevsys.co.nz>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] In progress INSERT wrecks plans on table
Date: Thu, 11 Jul 2013 10:39:58 +0530
Message-ID: <20130711050958.GA23755@toroid.org> (raw)
In-Reply-To: <51DD9026.2030107@agliodbs.com>
References: <916bff05a5ab130c76d7835abbaf7762.squirrel@mail.catalyst.net.nz>
	<CA+U5nMJHjQccVSB3SkAHdRn2AfuuO49wA8-ZGB366cddTynjdQ@mail.gmail.com>
	<CA+U5nMKMfZUEimZ9=oF2wJ-WkiGhMw3mQjUFT6PBGofUpmXL8A@mail.gmail.com>
	<51870CB6.6090404@catalyst.net.nz>
	<CA+U5nMJHgHczmWrKzst=PJeP+9BHJAsTgRNXyVZX5Oyys2zn_g@mail.gmail.com>
	<20130624044350.GA32322@toroid.org>
	<WM!ecf8a06e8ffb427fca7f6e0f4ff5638515a78282eced37d2e4f9981c1e769de67caec13f84aa60888557e9af7b43dca1!@asav-2.01.com>
	<51DAF2C8.6010705@agliodbs.com>
	<WM!26a3c5a0f97d399bf3d258d64ee1eee5843f0b45f9801c411e8f5e0770f3c25a892f527c93229c57bfa8f0f3eb6066dc!@asav-2.01.com>
	<51DD9026.2030107@agliodbs.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

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



view thread (42+ messages)  latest in thread

Message-ID: <20130711050958.GA23755@toroid.org>
Permalink:  ../20130711050958.GA23755@toroid.org/
Also on:    postgresql.org/message-id/20130711050958.GA23755@toroid.org

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: ams@2ndQuadrant.com, josh@agliodbs.com, simon@2ndQuadrant.com, mark.kirkwood@catalyst.net.nz, tgl@sss.pgh.pa.us, gavinflower@archidevsys.co.nz, pgsql-hackers@postgresql.org
  Subject: Re: [PERFORM] In progress INSERT wrecks plans on table
  In-Reply-To: <20130711050958.GA23755@toroid.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox