On Jan 27, 2008 12:29 PM, Dean Rasheed <dean_rasheed@xxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
