Re: Slow set-returning functions

On Jan 27, 2008 12:29 PM, Dean Rasheed <dean_rasheed(at)hotmail(dot)com> wrote:
> >> Is there any way that I can see what execution plan is being used
> >> internally by the functions?
> >>
> >
> > Not directly, but you can do this:
> >
> >
> > postgres=# PREPARE p (int4) AS SELECT id FROM foo WHERE lower(name) LIKE
> > 'foo' ORDER BY id OFFSET 0 LIMIT $1;
> > PREPARE
> >
> > postgres=# EXPLAIN EXECUTE p(100); QUERY PLAN
> > -----------------------------------------------------------------------------
> > Limit (cost=0.00..49.18 rows=2 width=4)
> >
> > -> Index Scan using foo_pkey on foo (cost=0.00..614.77 rows=25 width=4)
> > Filter: (lower(name) ~~ 'foo'::text)
> > (3 rows)
>
>
> I think that having the ability to see the execution plans being used
> by queries inside functions would be quite useful.
>
> More generally, I would like to be able to log the execution plans of
> all queries issued by an application (in my case I am working on a web
> application, where some of the queries are auto-generated by
> Hibernate). I've tried setting debug_print_plan, but the results are a
> little hard to interpret.
>
> As an experiment, I have tried hacking around a little with the code.
> This is my first foray into the source code, so I might well be
> missing something, but basically I added a new configuration parameter
> debug_explain_plan which causes all queries to be instrumented and
> ExecutorRun() to call explain_outNode() at the end, logging the
> results at level DEBUG1.

I read your email, blinked twice, and thought: where have you been all
my life! :-)

(IOW, +1)

merlin