If there are unremovable rows it usually also means that index-only scan degrades to index-scan-with-visibility-checks-in-tables.
I think the ask is to be able to remove the recently dead rows that are not visible in any current snapshot and can never become visible to any future snapshot,
Something that Tom described as currently not easily doable above.
Maybe we could figure out a way for long-running transactions to upload their snapshot set to some meta-vacuum process which can use it to determine which rows fall into that category. In this way the change would affect only the long-running transactions and if we do it onl maybe once a minute it should not be too heavy overhead even for these transactions.
Thanks for that link; seeing actual queries is a big help. One thing to try is to get some index-only scans to run. Regular indexes need to consult the heap (main table) for visibility information, and a bloated table can make that consultation expensive.
For example, an index like this should work to trigger an index-only scan:
create index gregtest on node(geom) include(node_id) where containing_face=0;
For those not following that link, the query is:
SELECT node_id, geom FROM node WHERE containing_face IN (0)
AND geom && '0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40'::geometry
Or if containing_face is not always 0, a more generic variant:
create index gregtest on node(geom, containing_face) include (node_id);
What is the nature of the updates that are causing that many dead rows in the first place?
Cheers,
Greg
