Re: PostgreSQL and a Catch-22 Issue related to dead rows
| От | Greg Sabino Mullane |
|---|---|
| Тема | Re: PostgreSQL and a Catch-22 Issue related to dead rows |
| Дата | |
| Msg-id | CAKAnmmJMgVLaDAb9jedsFhWCCKc8sRtpzPsio+wB3oAj6yOZPw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: PostgreSQL and a Catch-22 Issue related to dead rows (Lars Aksel Opsahl <Lars.Opsahl@nibio.no>) |
| Ответы |
Re: PostgreSQL and a Catch-22 Issue related to dead rows
Re: PostgreSQL and a Catch-22 Issue related to dead rows |
| Список | 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:
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
В списке pgsql-performance по дате отправления: