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?
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;
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.
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
