MIME-Version: 1.0
References: 
 <AM7P189MB10281DCA1FD196A5610484B29D3C2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
 <5EE4CC0C-E624-4E82-A2E8-B2EBA44D3521@thebuild.com>
 <3908773.1733760455@sss.pgh.pa.us>
 <AM7P189MB102815FBBAC696A3FE173CEB9D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
 <CAKAnmmJLzTQAEzyBoLhG40WEN3siWFSBT68GSu2b_mF=WJL-JQ@mail.gmail.com>
 <AM7P189MB1028DE31357B6B278FDEC3589D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
 <CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio+wB3oAj6yOZPw@mail.gmail.com>
In-Reply-To: 
 <CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio+wB3oAj6yOZPw@mail.gmail.com>
From: Hannu Krosing <hannuk@google.com>
Date: Tue, 10 Dec 2024 21:26:11 +0100
Message-ID: 
 <CAMT0RQQY4cuJGRWJ5C9zzaXZEfqyb+WfxOai6hVJ-R9wDSaM1w@mail.gmail.com>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>, Tom Lane <tgl@sss.pgh.pa.us>,
	Christophe Pettus <xof@thebuild.com>,
	"pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000675c810628f04b22"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMT0RQQY4cuJGRWJ5C9zzaXZEfqyb%2BWfxOai6hVJ-R9wDSaM1w%40mail.gmail.com>
Precedence: bulk

--000000000000675c810628f04b22
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail=
.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 consu=
lt
> 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=3D0;
>
> For those not following that link, the query is:
>
> SELECT node_id, geom FROM node WHERE containing_face IN (0)
> AND geom &&
> '0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625=
404E468D0D23E84D40'::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
>
>

--000000000000675c810628f04b22
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">If there are unremovable rows it usually also means that i=
ndex-only scan degrades to index-scan-with-visibility-checks-in-tables.<br>=
<br>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 fu=
ture snapshot,=C2=A0<div>Something that Tom described as currently not easi=
ly doable above.<br><br>Maybe we could figure out a way for long-running tr=
ansactions to upload their snapshot set to some meta-vacuum process which c=
an 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 tran=
sactions.</div></div><br><div class=3D"gmail_quote gmail_quote_container"><=
div dir=3D"ltr" class=3D"gmail_attr">On Tue, Dec 10, 2024 at 4:32=E2=80=AFP=
M Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gm=
ail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex"><div dir=3D"ltr">Thanks for that link; seeing actual queries is a bi=
g help. One thing to try is to get some index-only scans to run. Regular in=
dexes need to consult the heap (main table) for visibility=C2=A0information=
, and a bloated table can make that consultation expensive.<div><br></div><=
div>For example, an index like this should work to trigger an index-only sc=
an:<div><div><br></div><div>create index gregtest on node(geom) include(nod=
e_id) where containing_face=3D0;</div><div><br></div>For those not followin=
g that link, the query is:<br><br>SELECT node_id, geom FROM node WHERE cont=
aining_face IN (0)<br>AND geom &amp;&amp; &#39;0102000020A21000000200000025=
DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40&#39;::geomet=
ry<div><br></div></div></div><div>Or if containing_face is not always 0, a =
more generic variant:</div><div><br></div><div>create index gregtest on nod=
e(geom, containing_face) include (node_id);</div><div><br></div><div>What i=
s the nature of the updates that are causing that many dead rows in the fir=
st place?</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></d=
iv></div>
</blockquote></div>

--000000000000675c810628f04b22--