From: | Greg Sabino Mullane <htamfids(at)gmail(dot)com> |
---|---|
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 |
Date: | 2024-12-10 15:31:37 |
Message-ID: | CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio+wB3oAj6yOZPw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2024-12-10 20:26:11 | Re: PostgreSQL and a Catch-22 Issue related to dead rows |
Previous Message | Lars Aksel Opsahl | 2024-12-10 14:03:41 | Re: PostgreSQL and a Catch-22 Issue related to dead rows |