public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hannu Krosing <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: Lars Aksel Opsahl <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Christophe Pettus <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
Date: Tue, 10 Dec 2024 21:26:11 +0100
Message-ID: <CAMT0RQQY4cuJGRWJ5C9zzaXZEfqyb+WfxOai6hVJ-R9wDSaM1w@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio+wB3oAj6yOZPw@mail.gmail.com>
References: <AM7P189MB10281DCA1FD196A5610484B29D3C2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
<[email protected]>
<[email protected]>
<AM7P189MB102815FBBAC696A3FE173CEB9D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
<CAKAnmmJLzTQAEzyBoLhG40WEN3siWFSBT68GSu2b_mF=WJL-JQ@mail.gmail.com>
<AM7P189MB1028DE31357B6B278FDEC3589D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
<CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio+wB3oAj6yOZPw@mail.gmail.com>
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 <[email protected]>
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
>
>
view thread (17+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
In-Reply-To: <CAMT0RQQY4cuJGRWJ5C9zzaXZEfqyb+WfxOai6hVJ-R9wDSaM1w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox