On 11st of December 2024, David Rowley committed patch:
Enable BUFFERS with EXPLAIN ANALYZE by default The topic of turning EXPLAIN's BUFFERS option on with the ANALYZE option has come up a few times over the past few years. In many ways, doing this seems like a good idea as it may be more obvious to users why a given query is running more slowly than they might expect. Also, from my own (David's) personal experience, I've seen users posting to the mailing lists with two identical plans, one slow and one fast asking why their query is sometimes slow. In many cases, this is due to additional reads. Having BUFFERS on by default may help reduce some of these questions, and if not, make it more obvious to the user before they post, or save a round-trip to the mailing list when additional I/O effort is the cause of the slowness. The general consensus is that we want BUFFERS on by default with ANALYZE. However, there were more than zero concerns raised with doing so. The primary reason against is the additional verbosity, making it harder to read large plans. Another concern was that buffer information isn't always useful so may not make sense to have it on by default. It's currently December, so let's commit this to see if anyone comes forward with a strong objection against making this change. We have over half a year remaining in the v18 cycle where we could still easily consider reverting this if someone were to come forward with a convincing enough reason as to why doing this is a bad idea. There were two patches independently submitted to achieve this goal, one by me and the other by Guillaume. This commit is a mix of both of these patches with some additional work done by me to adjust various additional places in the documentation which include EXPLAIN ANALYZE output. Author: Guillaume Lelarge, David Rowley Reviewed-by: Robert Haas, Greg Sabino Mullane, Michael Christofides Discussion: https://postgr.es/m/CANNMO++W7MM8T0KyXN3ZheXXt-uLVM3aEtZd+WNfZ=obxffUiA@mail.gmail.com
This will be easy to show, but it's definitely worth a post, so here we go 🙂
The best thing that PostgreSQL gives you is EXPLAIN.
You run it to see what plan Pg would use if it ran the query, or you run it with ANALYZE option to see the plan and how long it actually takes.
This is how it can look like:
=$ EXPLAIN analyze SELECT SUM(timeworked) FROM tickets; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────────────────────────── Aggregate (cost=9.89..9.90 ROWS=1 width=8) (actual TIME=1.089..1.098 ROWS=1 loops=1) -> Seq Scan ON tickets (cost=0.00..9.31 ROWS=231 width=4) (actual TIME=0.037..0.579 ROWS=234 loops=1) Planning TIME: 2.185 ms Execution TIME: 1.164 ms (4 ROWS)
Now, with Pg 18, the same command produces more information:
=$ EXPLAIN analyze SELECT SUM(timeworked) FROM tickets; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────────────────────────── Aggregate (cost=8.93..8.94 ROWS=1 width=8) (actual TIME=0.104..0.104 ROWS=1 loops=1) Buffers: shared hit=6 -> Seq Scan ON tickets (cost=0.00..8.34 ROWS=234 width=4) (actual TIME=0.016..0.044 ROWS=234 loops=1) Buffers: shared hit=6 Planning: Buffers: shared hit=94 READ=4 I/O Timings: shared READ=0.032 Planning TIME: 0.527 ms Execution TIME: 0.772 ms (9 ROWS)
This information was available previously using separate explain option:
=$ EXPLAIN ( analyze, buffers ) SELECT SUM(timeworked) FROM tickets; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────────────────────────── Aggregate (cost=9.89..9.90 ROWS=1 width=8) (actual TIME=0.564..0.569 ROWS=1 loops=1) Buffers: shared hit=7 -> Seq Scan ON tickets (cost=0.00..9.31 ROWS=231 width=4) (actual TIME=0.011..0.280 ROWS=234 loops=1) Buffers: shared hit=7 Planning: Buffers: shared hit=99 Planning TIME: 0.515 ms Execution TIME: 0.665 ms (8 ROWS)
But now, it's included by default if you do ANALYZE. Which is great as it can easily show problems that might be otherwise unclear.
If you'd like, for some reason, to get previous output, you can get analyze with buffers disabled:
=$ EXPLAIN ( analyze, buffers off ) SELECT SUM(timeworked) FROM tickets; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────────────────────────── Aggregate (cost=8.93..8.94 ROWS=1 width=8) (actual TIME=0.096..0.097 ROWS=1 loops=1) -> Seq Scan ON tickets (cost=0.00..8.34 ROWS=234 width=4) (actual TIME=0.011..0.039 ROWS=234 loops=1) Planning TIME: 0.357 ms Execution TIME: 0.220 ms (4 ROWS)
Great stuff. Thanks to everyone involved 🙂