CODEWITHSUNDEEP
sqlpostgresqlvalidation
Thomas S
Thomas S

Reputation: 23

postgresql - Get query rows and plan from Postgres EXPLAIN ANALYZE query

I am currently trying to get the rows returned by the query and along with some metrics like query complexity for validating query correctness and efficiency. Is there a way that I can get both the rows and the query plan without having to the run the query twice?

I have seen this post on dba exchnage but I'm unsure if it is possible to get the rows.

Upvotes: 2

Views: 208

Answers (1)

richyen
richyen

Reputation: 9978

The way to do this is by loading auto_explain with

  • LOAD 'auto_explain';
  • SET auto_explain.log_analyze=true;
  • SET auto_explain.log_min_duration=0;

This way, the EXPLAIN ANALYZE plan gets printed into the log, and you get your query results in the client. Note these steps will only activate auto_explain for your session. If you want a global auto_explain, you will need to update your postgresql.conf

More info in the documentation: https://www.postgresql.org/docs/current/auto-explain.html

Disclosure: I work for EnterpriseDB (EDB)

Upvotes: 4

Related Questions