Re: An Idea for planner hints

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:

> On Tue, Aug 15, 2006 at 07:55:28PM +0200, Peter Eisentraut wrote:
>
> > It would not be hard to create an "auto explain analyze" mode that
> > implicitly runs EXPLAIN ANALYZE along with every query and logs the
> > result. On its face, it sounds like an obviously great idea. I just
> > don't see how you would put that to actual use, unless you want to read
> > server logs all day long. Grepping for query duration and using the
> > statistics views are much more manageable tuning methods. In my view
> > anyway.
>
> Well, the output would really need to go into some machine-readable
> format, since you certainly aren't going to read it. That would also
> make it trivial to identify plans that diverged greatly from reality.

Oracle's EXPLAIN had a peculiar design feature that always seemed bizarre from
a user's point of view. But here's where it begins to become clear what they
were thinking.

It stuffs the EXPLAIN output into a table. It means you can then use SQL to
format the data for display, to generate aggregate reports of plans, or to
search for plans or plan nodes that meet certain criteria. They don't even
have to be plans generated by your session. You can have an application run
explain on its queries and then go and peek at the plans from a separate
session. And it doesn't interfere with the query outputting its normal output.

I'm not sure it's worth throwing out the more user-friendly interface we have
now but I think it's clear that a table is the obvious "machine-readable
format" if you're already sitting in an SQL database... :)

Also, incidentally you guys are still thinking of applications that don't use
prepared queries and parameters extensively. If they do they won't have reams
of plans since there'll only be one ream of plans with one plan for each query
on a session start not one for each execution.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com