4

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?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
DB140141
  • 181
  • 6

3 Answers3

3

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;
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
DB140141
  • 181
  • 6
  • I took the liberty to remove the (undocumented) temp schema qualification, which is probably a testing artefact. It should also be noted that the function is completely open to SQL injection, so it should not be exposed to untrusted user input. Apart from that it does the job. You might merge both into one function ... – Erwin Brandstetter Oct 12 '15 at 02:28
  • Regarding SQL injection, the purpose of using this kind of function is specifically to log performance of known SQL statements being developed for an application, not being exposed to a client UI. Also, it is not a danger the way Postgres permissions work in functions. If you try to inject "DROP TABLE foo" and you don't have permissions to do so, it won't let you even though you are doing it through a function (unless it was created by a high perms user with 'security definer'). Please correct me if I'm mistaken. – DB140141 Oct 13 '15 at 12:04
  • Not mistaken, and yes, there are also SECURITY DEFINER functions. Also, applications typically do not want to allow everything that is granted to a role, only a small subset of operations defined by the app. Privileges of a role are typically a superset. – Erwin Brandstetter Oct 13 '15 at 14:11
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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
1

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

richyen
  • 770
  • 4
  • 9