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:
For those not following that link, the query is:
SELECT node_id, geom FROM node WHERE containing_face IN (0)
AND geom && '0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40'::geometry
create index gregtest on node(geom) include(node_id) where containing_face=0;
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
