search

tjhelmuth / postgres-explain-visualizer

14 stars 0 forks source link

ERROR: EXPLAIN option BUFFERS requires ANALYZE #1

Closed seanf closed 1 year ago

seanf commented 2 years ago

The generated query for "Visualize Explain" doesn't work, because it executes a query like EXPLAIN (FORMAT JSON, VERBOSE, COSTS, BUFFERS) SELECT .... The database responds with this error: ERROR: EXPLAIN option BUFFERS requires ANALYZE.

I have only tested with Postgres 11, but I think this limitation applies to all versions: https://www.postgresql.org/docs/15/sql-explain.html#:~:text=values%20are%20printed.-,This%20parameter%20may%20only%20be%20used%20when%20ANALYZE%20is%20also%20enabled,-.%20It%20defaults%20to

seanf commented 2 years ago

Looks like something changed with BUFFERS in Postgres 13. https://postgres.ai/blog/20220106-explain-analyze-needs-buffers-to-improve-the-postgres-query-optimization-process#bonus-planners-io-work-postgres-13

seanf commented 2 years ago

Looks like something changed with BUFFERS in Postgres 13. postgres.ai/blog/20220106-explain-analyze-needs-buffers-to-improve-the-postgres-query-optimization-process#bonus-planners-io-work-postgres-13

I think the upshot is that "EXPLAIN option BUFFERS requires ANALYZE" if Postgres version < 13.

tjhelmuth commented 1 year ago

Pushed a fix for that in version 0.9. Sorry for the wait.