Re: Eliminating PD_ALL_VISIBLE, take 2

On 2013-07-01 19:52:57 -0700, Jeff Davis wrote:
> 2. Can you be more specific about the scenarios that you *are* concerned
> about? Preferably in a form that could be tested on a 64-core box; but
> at least some kind of analysis involving numbers. "More page accesses"
> is scary, but completely meaningless without saying how *many* more and
> in which situations.

Ok, so you want some benchmark results. I spent 20 minutes concocting some
quick tests. Here you go:

master (384f933046dc9e9a2b416f5f7b3be30b93587c63):
tps = 155075.448341 (including connections establishing)
tps = 155259.752267 (excluding connections establishing)

dev (384f933046dc9e9a2b416f5f7b3be30b93587c63 + patch):
tps = 151450.387021 (including connections establishing)
tps = 152512.741161 (excluding connections establishing)

That's about a 3% regression.

Testsetup:
~/build/postgres/{master,dev}-optimize/vpath/src/backend/postgres \   -D /srv/dev/pdav-{master,dev}/ \   -c
shared_buffers=1GB  -c max_connections=150
 

Data loaded: load.sql.
Test run: SELECT key, data FROM kv WHERE key = 'key-17';
Test: pgbench postgres -n -S -M prepared -f /tmp/query.sql -T 100 -c 100 -j 100

So basically we're just scanning a smalling relation that's all visible
rather frequently. With lookup tables - that might even be accessed in a
correlated subquery - that's not a unrealistic scenario.

I am pretty sure it's not all that hard to create a test where the loss
is bigger due to the loss of all_visible on small relations (the
SCAN_VM_THRESHOLD thing).

I am not sure whether that's big enough to say the idea of
SCAN_VM_THRESHOLD is dead, but it's not small enough to dismiss either.

So, running the same test with 'kv' having 36 pages/5500 tuples instead
of just 1 page/100 tuples:
master:
tps = 171260.444722 (including connections establishing)
tps = 173335.031802 (excluding connections establishing)

dev:
tps = 170809.702066 (including connections establishing)
tps = 171730.291712 (excluding connections establishing)

~1%

With SELECT count(*) FROM kv;
master:
tps = 13740.652186 (including connections establishing)
tps = 13779.507250 (excluding connections establishing)

dev:
tps = 13409.639239 (including connections establishing)
tps = 13466.905051 (excluding connections establishing)

~2%.

All that isn't a too big regression, but it shows that this isn't free
lunch either. Would be interesting to see whether that shows up worse on
bigger hardware than mine (2 x E5520).

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services