From: Merlin Moncure <mmoncure@gmail.com>
To: Dean Rasheed <dean_rasheed@hotmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow set-returning functions
Date: Sun, 27 Jan 2008 18:01:08 -0500
Message-ID: <b42b73150801271501p3e47ef8ela42c1ce5bcfac186@mail.gmail.com> (raw)
In-Reply-To: <BAY113-W336D429DFB9A2E2772E906F23B0@phx.gbl>
References: <BAY113-W283034364555E644CD1765F23C0@phx.gbl>
<47935C0C.4090908@enterprisedb.com>
<b42b73150801200725y45600f6end8d234cea4f8ff22@mail.gmail.com>
<10405.1200853835@sss.pgh.pa.us>
<BAY113-W336D429DFB9A2E2772E906F23B0@phx.gbl>
On Jan 27, 2008 12:29 PM, Dean Rasheed <dean_rasheed@hotmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: mmoncure@gmail.com, dean_rasheed@hotmail.com, tgl@sss.pgh.pa.us, heikki@enterprisedb.com
Subject: Re: Slow set-returning functions
In-Reply-To: <b42b73150801271501p3e47ef8ela42c1ce5bcfac186@mail.gmail.com>
References: <BAY113-W283034364555E644CD1765F23C0@phx.gbl> <47935C0C.4090908@enterprisedb.com> <b42b73150801200725y45600f6end8d234cea4f8ff22@mail.gmail.com> <10405.1200853835@sss.pgh.pa.us> <BAY113-W336D429DFB9A2E2772E906F23B0@phx.gbl> <b42b73150801271501p3e47ef8ela42c1ce5bcfac186@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox