Re: Problem analyzing performance of query

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Is there any way to ask the server what plan it's using when it's actually
> executing the query in production, rather than trying to feed it the same
> query later in another context and hope it gets the same result?

From the planner's point of view, a plpgsql query involving plpgsql
variables is a parameterized query, which is the same as a PREPAREd
query with parameters. So for instance

create function foo(int) ...
...
select ... where keycol = $1;
...

looks the same as

PREPARE q(int) AS select ... where keycol = $1;

and you can investigate the plan for this with

EXPLAIN [ANALYZE] EXECUTE q(42);

Clear? It'd be nice to have more infrastructure for debugging plpgsql
code, but so far no one's got round to building any :-(

regards, tom lane