EXPLAIN with anonymous DO block?

It sounds like you are wanting to run 'explain analyze [query]' inside a
loop inside a DO block. That isn't possible as far as I know, but
auto_explain and log_nested_statements should be able to let you profile
the whole thing and perhaps you can pick out the part you want from the
logs.

Ron <ronljohnsonjr@gmail.com> writes:

There's a query inside a DO block which -- because it's parameterized -- I'd
rather analyze while it's in the FOR loop of a DO block, instead of pulling
it out and hard-coding the parameters.

Is this possible?

No.

The thing to do to duplicate the behavior of a plpgsql query is
to set it up as a PREPAREd statement (with parameters for any
plpgsql variables it references) and use EXPLAIN EXECUTE.
This also works if you're curious about the behavior of a
query issued via PQexecParams or the like.

It's recommendable to repeat the EXPLAIN half a dozen times
to see if the plancache switches from a custom to a generic
plan. (In recent PG releases, changing plan_cache_mode
is another way to check what happens.)

regards, tom lane

On Thu, Jul 1, 2021 at 9:22 AM Michael Lewis <mlewis@entrata.com> wrote:

It sounds like you are wanting to run 'explain analyze [query]' inside a
loop inside a DO block. That isn't possible as far as I know, but
auto_explain and log_nested_statements should be able to let you profile
the whole thing and perhaps you can pick out the part you want from the
logs.

I believe it can be done technically, though basically the function will
need to be re-written for the purpose. It isn't as simple as adding an
explain somewhere since the output of explain is a result set. But as you
are already using pl/pgsql then your parameters can just be done up as
variables instead and that query should be able to be explained.

David J.

Good day!

There's a query inside a DO block which -- because it's parameterized -- I'd
rather analyze while it's in the FOR loop of a DO block, instead of pulling
it out and hard-coding the parameters.
Is this possible?

No.

Why not to use auto_explain module?

postgres=# LOAD 'auto_explain';
LOAD
postgres=# SET auto_explain.log_min_duration = 0;
SET
postgres=# SET auto_explain.log_nested_statements = on;
SET
postgres=# SET auto_explain.log_analyze = on;
SET
postgres=# SET auto_explain.log_level = 'NOTICE';
SET
postgres=# DO $$BEGIN FOR i IN 112 .. 113 LOOP PERFORM * FROM pg_class
WHERE oid = i::oid; END LOOP; END;$$;
NOTICE:  duration: 0.013 ms  plan:
Query Text: SELECT * FROM pg_class WHERE oid = i::oid
Index Scan using pg_class_oid_index on pg_class  (cost=0.27..8.29 rows=1
width=265) (actual time=0.009..0.011 rows=1 loops=1)
  Index Cond: (oid = '112'::oid)
NOTICE:  duration: 0.016 ms  plan:
Query Text: SELECT * FROM pg_class WHERE oid = i::oid
Index Scan using pg_class_oid_index on pg_class  (cost=0.27..8.29 rows=1
width=265) (actual time=0.008..0.009 rows=1 loops=1)
  Index Cond: (oid = '113'::oid)
DO

Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company