Hi hackers,
Sometime ago I investigated slow query performance case of one customer and noticed that index-only scan has made a lot of heap fetches.
-> Index Only Scan using ix_client_objects_vendor_object_id on client_objects client_objects_1 (cost=0.56..2.78 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=208081) Index Cond: (vendor_object_id = vendor_objects.id) Heap Fetches: 208081 Buffers: shared hit=1092452 read=156034
So almost any index entry requires visibility check and index-only scan is actually normal index-scan.
It certainly have bad impact on performance.
I do not know what happen in this particular case, why pages are not marked as all-visible and why index-only scan plan was chosen by optimizer.
But the problem can be quite easily reproduced. We can just populate table with some data with some other transaction with assigned XID active.
Then explicitly vacuum this tables or wait until autovacuum does it.
At this moment table has no more dead or inserted tuples so autovacuum will not be called for it. But heap pages of this table are still not marked as all-visible.
And will never be marked as all-visible unless table is updated or is explicitly vacuumed.
This is why I think that it may be useful to add more columns to pg_stat_all_tables and pg_stat_all_indexes views providing information about heap visibility checks performed by index-only scan. And in addition to number of dead/inserted tuples add number of such visibility checks as criteria for performing autovacuum for the particular table.
Proposed patch is attached.
I am not quit happy with the test - it is intended to check if autovacuum is really triggered by this new criteria. But it depends on autovacuum activation frequency and may take several seconds.
Will be glad to receive any feedback.
