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.
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.