flâneur

EXPLAIN (ANALYZE) needs BUFFERS to improve the Postgres query optimization process | Postgres.AI

postgres.ai · 4,268 words · saved by 1 readers

Jupiter's moon IO. Credit: ALMA (ESO/NAOJ/NRAO), I. de Pater et al.; NRAO/AUI NSF, S. Dagnello; NASA/JPL/Space Science Institute SQL query optimization is challenging for those who have just started working with PostgreSQL. There are many objective reasons for this, such as: All these barriers are reasonable. They limit the number of engineers possessing well-developed Postgres query optimization skills. However, there is a specific artificial barrier that is rather influential and which is relatively easy to eliminate. Here it is: the EXPLAIN command has the BUFFERS option disabled by default. I am sure it has to be enabled and used by everyone who needs to do some SQL optimization work. The BUFFERS option helps us see how much IO work Postgres did when executing each node in the query execution plan. For database systems, which mostly perform IO-intensive operations, dealing with too many data pages (or "buffers", "blocks" – depending on the context) is the most popular reason for po

Jupiter's moon IO . Credit: ALMA (ESO/NAOJ/NRAO), I. de Pater et al.; NRAO/AUI NSF, S. Dagnello; NASA/JPL/Space Science Institute SQL query optimization is challenging for those who have just started working with PostgreSQL. There are many objective reasons for this, such as: the difficulty of the field of system performance in general, lack of good "playground" environments where people can experience how databases work at a larger scale, lack of certain capabilities in Postgres observability tools that are still developing (though, at a good pace), insufficiency of good educational materials

related reading