9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL

[Guillaume Cottenceau]

Hello,

I am toying around with 9.2.1, trying to measure/determine how
index-only scans can improve our performance.

A small script which is attached to this mail, shows that as long
as the table has been VACUUM FULL'd, there is a unusual high
amount of heap fetches. It is strange that the visibilitymap_test
predicate fails in these situations, is the visibility map
somehow trashed in this situation? It should not, or at least the
documentation[1] should state it (my understanding is that vacuum
full does *more* than vacuum, but nothing less) (note to usual
anti vacuum full trollers: I know you hate vacuum full).

Using pg 9.2.1 compiled from sources, almost standard
configuration except shared_buffers at 512M, effective_cache_size
at 1536M, random_page_cost at 2, and vacuum delays increased.

Please find complete logs attached, and selected logs below:

After table creation + analyze:

Index Only Scan using i on ta (cost=0.00..156991.10 rows=2018667 width=4) (actual time=0.034..336.443 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000

After vacuum:

Index Only Scan using i on ta (cost=0.00..50882.62 rows=2018667 width=4) (actual time=0.014..193.120 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0

After vacuum analyze:

Index Only Scan using i on ta (cost=0.00..50167.13 rows=1990353 width=4) (actual time=0.015..193.035 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0

After vacuum full:

Index Only Scan using i on ta (cost=0.00..155991.44 rows=1990333 width=4) (actual time=0.042..364.412 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
^^^^^^^ uh uh, looking bad

After vacuum full analyze:

Index Only Scan using i on ta (cost=0.00..157011.85 rows=2030984 width=4) (actual time=0.025..365.657 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000

After vacuum:

Index Only Scan using i on ta (cost=0.00..51192.45 rows=2031000 width=4) (actual time=0.015..192.520 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0

Thanks for any comments/hints,

Ref:
[1] http://www.postgresql.org/docs/9.1/static/sql-vacuum.html

[Pavan Deolasee]

Post by Guillaume Cottenceau
Hello,
I am toying around with 9.2.1, trying to measure/determine how
index-only scans can improve our performance.
A small script which is attached to this mail, shows that as long
as the table has been VACUUM FULL'd, there is a unusual high
amount of heap fetches. It is strange that the visibilitymap_test
predicate fails in these situations, is the visibility map
somehow trashed in this situation? It should not, or at least the
documentation[1] should state it (my understanding is that vacuum
full does *more* than vacuum, but nothing less) (note to usual
anti vacuum full trollers: I know you hate vacuum full).

I don't find it very surprising given that VACUUM FULL is now implemented
as a CLUSTER command which rewrites the entire heap, thus invalidating all
the visibility map info whatsoever. The code paths that VACUUM FULL and
LAZY VACUUM takes are now completely different.

Even with the old VACUUM FULL we would have seen some impact on heap
fetches because it used to move tuples around and thus potentially
resetting visibility map bits. But its definitely going to be worse with
the new implementation.

Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits
set to visible, thats an entirely different question. I don't think it can,
but then I haven't thought through this completely.

Thanks,
Pavan

[Andres Freund]

Post by Pavan Deolasee

Post by Guillaume Cottenceau
Hello,
I am toying around with 9.2.1, trying to measure/determine how
index-only scans can improve our performance.
A small script which is attached to this mail, shows that as long
as the table has been VACUUM FULL'd, there is a unusual high
amount of heap fetches. It is strange that the visibilitymap_test
predicate fails in these situations, is the visibility map
somehow trashed in this situation? It should not, or at least the
documentation[1] should state it (my understanding is that vacuum
full does *more* than vacuum, but nothing less) (note to usual
anti vacuum full trollers: I know you hate vacuum full).

I don't find it very surprising given that VACUUM FULL is now implemented
as a CLUSTER command which rewrites the entire heap, thus invalidating all
the visibility map info whatsoever.

Me neither.

Post by Pavan Deolasee
Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits
set to visible, thats an entirely different question. I don't think it can,
but then I haven't thought through this completely.

It can't set everything to visible as it also copies RECENTLY_DEAD
tuples and tuples which are not yet visible to other transactions, but
it should be relatively easy to keep enough information about whether it
can set the current page to all visible. At least for the data in the
main relation, the toast tables are a different matter.
Just tracking whether the page in rewriteheap.c's state->rs_buffer
contains only tuples that are clearly visible according to the xmin
horizon seems to be enough.

The current effect of resetting the VM has the disadvantage of making
the next autovacuum basically a full table vacuum without any
benefits...

Greetings,

Andres

[Pavan Deolasee]

Post by Andres Freund

Post by Pavan Deolasee
Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits
set to visible, thats an entirely different question. I don't think it

can,

Post by Pavan Deolasee
but then I haven't thought through this completely.

It can't set everything to visible as it also copies RECENTLY_DEAD
tuples and tuples which are not yet visible to other transactions, but
it should be relatively easy to keep enough information about whether it
can set the current page to all visible.

Yeah, that looks fairly easy to have. Thinking about it more, now that we
have ability to skip WAL for the case when a table is created and populated
in the same transaction, we could also set the visibility map bits for such
a table (if we are not doing that already). That should be fairly safe too.

Thanks,
Pavan

[Andres Freund]

Post by Pavan Deolasee

Post by Andres Freund

Post by Pavan Deolasee
Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits
set to visible, thats an entirely different question. I don't think it

can,

Post by Pavan Deolasee
but then I haven't thought through this completely.

It can't set everything to visible as it also copies RECENTLY_DEAD
tuples and tuples which are not yet visible to other transactions, but
it should be relatively easy to keep enough information about whether it
can set the current page to all visible.

Yeah, that looks fairly easy to have. Thinking about it more, now that we
have ability to skip WAL for the case when a table is created and populated
in the same transaction, we could also set the visibility map bits for such
a table (if we are not doing that already). That should be fairly safe too.

I don't think the latter would be safe at all. Every repeatable read
transaction that started before the table creation would see that tables
content based on the visibilitymap instead of seeing it as empty.

Greetings,

Andres Freund

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

[Pavan Deolasee]

Post by Pavan Deolasee

Post by Pavan Deolasee
Yeah, that looks fairly easy to have. Thinking about it more, now that we
have ability to skip WAL for the case when a table is created and

populated

Post by Pavan Deolasee
in the same transaction, we could also set the visibility map bits for

such

Post by Pavan Deolasee
a table (if we are not doing that already). That should be fairly safe

too.
I don't think the latter would be safe at all. Every repeatable read
transaction that started before the table creation would see that tables
content based on the visibilitymap instead of seeing it as empty.

Yeah, but that should be easy to fix, no ? We know the transaction that
created the table and we can check if that transaction is visible to our
snapshot or not. If the creating transaction itself is not visible, the
data in the table is not visible either. OTOH if the creating transaction
is visible and is committed, we can trust the visibility map as well. Thats
probably better than scanning the entire table just to find that we
can/can't see all/any rows.

Its getting slightly off-topic, so my apologies anyways.

Thanks,
Pavan