Re: [PERFORM] In progress INSERT wrecks plans on table

At 2013-07-10 09:47:34 -0700, josh(at)agliodbs(dot)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