Search code examples
postgresqlpsqlexplain

Postgres: (how) can I output EXPLAIN <query> to a file?

This works:

psql -XqAt "<DB_URI>" \
  -c "\copy select 1) to out.sql"

Can EXPLAIN be output to a file as well? If the above SELECT 1 is prepended with explain(analyze,buffers,costs,timing,verbose,format json)

psql -XqAt "<DB_URI>" \
  -c "\copy (explain(analyze,buffers,costs,timing,verbose,format json) select 1) to out.sql"

this then errors.

Solution

  • Your command is incorrect. Youre using \copy but thats only used to copy data from a table to a file not for running sql queries. If im not mistaken this should work:

    psql -XqAt "<DB_URI>" \
  -o out.sql \
  -c "explain(analyze,buffers,costs,timing,verbose,format json) select 1"