public inbox for [email protected]  

help / color / mirror / Atom feed
From: Greg Sabino Mullane <[email protected]>
To: 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 10:31:37 -0500
Message-ID: <CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio+wB3oAj6yOZPw@mail.gmail.com> (raw)
In-Reply-To: <AM7P189MB1028DE31357B6B278FDEC3589D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.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>

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]
  Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
  In-Reply-To: <CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio+wB3oAj6yOZPw@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