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

________________________________
Sent: Tuesday, December 10, 2024 4:31 PM
To: Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; Christophe Pettus <xof(at)thebuild(dot)com>; pgsql-performance(at)lists(dot)postgresql(dot)org <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

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

Hi

Thank's I did not know about the include option, I will have this mind when working on this.

I have done some simple testing with it now, but I have not seen any big changes yet, but issue in case 97 is not the same as in issue 67. What problems cases we end up with, will vary depending on the simple feature layers we run overlay between and for instance how many surfaces we need remove from the topology layer before we produce the final result.

In this case based on pg_stat_statements in seems like a lot off the time is used on updates when removing edges.

Why we get all this dead rows are related Postgis Topology database structure and input data. In some cases we get input data with verry many almost parallel lines which are close and that will cause a lot off edges be removed.

Lars