How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres?

Question

I normally retrieve the row_count of a query by doing GET DIAGNOSTICS row_count (normally within a function).

But if I want to run EXPLAIN ANALYZE on a query AND get row_count, I can't see any easy way to do that other than parsing the string output, because then GET DIAGNOSTICS row_count would return the number of rows in the EXPLAIN output.

Is there any built in way to do this other than parsing the string?

Answer 1

Having come to this conclusion that there isn't an easy built-in way, here is my quick solution for this:

Create this function to capture the explain analyze plan result:

CREATE OR REPLACE FUNCTION get_explain_analyze(p_sql text)
RETURNS TABLE("QUERY PLAN" text)
AS
$BODY$
BEGIN
  RETURN QUERY EXECUTE 'EXPLAIN ANALYZE '||p_sql;
END;
$BODY$
LANGUAGE plpgsql;

Run this to get the row count (only tested for select/create temp table statements - regex to the 2nd rows= number):

SELECT substring("QUERY PLAN" from 'rows=.+rows=(\d+)') AS row_count
FROM get_explain_analyze('SELECT 1')  -- query here
LIMIT 1;

Answer 2

There is no way I would know of. You could wrap your query into a subquery and count, but that's a 2nd execution:

SELECT count(*) FROM(<query>) sub;

For big queries, parsing the EXPLAIN output will be faster.
Consider the Postgres Wiki on count estimates.

Answer 3

Why not use auto_explain?

The way to do this is by loading auto_explain with

• LOAD ‘auto_explain’;
• SET auto_explain.analyze_threshold=on;
• 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