Re: Removing PD_ALL_VISIBLE

On 21.01.2013 11:10, Jeff Davis wrote:
> That confuses me. The testing was to show it didn't hurt other workloads
> (like scans or inserts/updates/deletes); so the best possible result is
> that they don't show signs either way.

I went back to look at the initial test results that demonstrated that
keeping the pin on the VM buffer mitigated the overhead of pinning the
vm page. The obvious next question is, what is the impact when that's
inefficient, ie. when you update pages across different 512 MB sections,
so that the vm pin has to be dropped and reacquired repeatedly.

I tried to construct a worst case scenario for that:

create unlogged table testtable (i int4);
insert into testtable select generate_series(1, 15000000);
insert into testtable select generate_series(1, 15000000);
create index testtable_index on testtable (i);

When you traverse tuples using that index, the tuples will come
alternating from low-numbered pages and high-numbered pages, which
defeats keeping the last vm page pinned. To test, I ran this:

set enable_bitmapscan=off; set enable_seqscan=off;
begin;
delete from testtable where i >= 0;
rollback;

I repeated a few times with and without the patch
(rm-pd-all-visible-0118.patch). According to \timing, the delete takes
about 12.6 seconds without the patch, and 15.3 seconds with it.

This is a worst-case scenario, and the slowdown isn't huge, so maybe
it's a worthwhile tradeoff. But it shows that removing PD_ALL_VISIBLE is
not completely free.

- Heikki