Re: PostgreSQL and a Catch-22 Issue related to dead rows

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.

On Tue, Dec 10, 2024 at 4:32 PM Greg Sabino Mullane <htamfids(at)gmail(dot)com>
wrote:

> 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
>
>