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

Reply via email to