Thread: EXPLAIN with anonymous DO block?
Postgresql 12.5 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? If so, where do I put the EXPLAIN statement? -- Angular momentum makes the world go 'round.
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