Re: 9.3: load path to mitigate load penalty for checksums

On Tue, Jun 12, 2012 at 6:26 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Tue, 2012-06-12 at 18:02 -0400, Tom Lane wrote:
>> Or (d) it's not a problem, since the inserting XID is still busy
>> according to the readers' snapshots.
>
> How much of a savings did we get from PD_ALL_VISIBLE when it was added
> into the page-at-a-time visibility check?
>
> >From 608195a3a3656145a7eec7a47d903bc684011d73:
>
> "In addition to the visibility map, there's a new PD_ALL_VISIBLE flag on
> each heap page, also indicating that all tuples on the page are visible
> to all transactions. It's important that this flag is kept up-to-date.
> It is also used to skip visibility tests in sequential scans, which
> gives a small performance gain on seqscans."
>
> If "small" means that it's something we can give up, then focusing on
> HEAP_XMIN_COMMITTED makes sense. But if we can't give it up, then we
> need to take it into account in the proposal.

It's significant.

rhaas=# create table foo (a int, b text);
ERROR: relation "foo" already exists
rhaas=# create table bar (a int, b text);
CREATE TABLE
rhaas=# insert into bar select g,
random()::text||random()::text||random()::text||random()::text from
generate_series(1,1000000) g;
INSERT 0 1000000
rhaas=# \timing
Timing is on.
rhaas=# select sum(1) from bar; sum
---------
1000000
(1 row)

Time: 257.500 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 140.763 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 142.760 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 140.603 ms
rhaas=# vacuum bar;
VACUUM
Time: 133.084 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 123.591 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 123.096 ms
rhaas=# select sum(1) from bar;
sum
---------
1000000
(1 row)

Time: 122.653 ms

So vacuuming to set the PD_ALL_VISIBLE bits is buying us more than 10% here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company