Search code examples
sqlpostgresqlvalidation

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.

Solution

  • 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)