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>
In-Reply-To: 
 <AM7P189MB1028DE31357B6B278FDEC3589D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 10 Dec 2024 10:31:37 -0500
Message-ID: 
 <CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio+wB3oAj6yOZPw@mail.gmail.com>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: 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="0000000000007ff32f0628ec2f18"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio%2BwB3oAj6yOZPw%40mail.gmail.com>
Precedence: bulk

--0000000000007ff32f0628ec2f18
Content-Type: text/plain; charset="UTF-8"

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

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

<div dir=3D"ltr">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 n=
eed 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 scan:<div=
><div><br></div><div>create index gregtest on node(geom) include(node_id) w=
here containing_face=3D0;</div><div><br></div>For those not following that =
link, the query is:<br><br>SELECT node_id, geom FROM node WHERE containing_=
face IN (0)<br>AND geom &amp;&amp; &#39;0102000020A21000000200000025DDA6B95=
DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40&#39;::geometry<div>=
<br></div></div></div><div>Or if containing_face is not always 0, a more ge=
neric variant:</div><div><br></div><div>create index gregtest on node(geom,=
 containing_face) include (node_id);</div><div><br></div><div>What is the n=
ature of the updates that are causing that many dead rows in the first plac=
e?</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></div></di=
v>

--0000000000007ff32f0628ec2f18--