________________________________
Sent: Tuesday, December 10, 2024 4:31 PM
To: Lars Aksel Opsahl <lars.ops...@nibio.no>
Cc: Tom Lane <t...@sss.pgh.pa.us>; Christophe Pettus <x...@thebuild.com>; 
pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.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

Reply via email to