Re: Turning the PLANNER off

Ah, so Ben finally got around to posting here. Ben's a CS Grad student
here at Rice. His (current) project involves taking some interesting
results from constraint satisfaction and implementing them on a database:
one of the CS faculty has demonstrated that one class of highly joined
DB queries maps to a solved problem in constraint satisfaction. The end
goal would be an optimizer module or setting that recognizes this class
of query, and spits out a mathematically optimized join order.

So, in the interim, Ben's trying to do it by hand: preorder the joins
and demonstrate that the 'best' order is in fact the best. Then move on
to looking into integrating this, if possible: part of the problem is
recognizing the structure of the query, of course. Right now, the planner
is getting in the way - although he can extract the needed timing info,
he's wasting CPU cycles planning things that don't need it, limiting
the number of cases he can try.

As mentioned before, there's currently no interface to feed in a Plan,
so he's out of luck. Is there, programmatically, a way to do it?
Serialize a plan tree to a file, and feed it in latter, purely for
development purposes. How painful would that be? Should I send him in to
see if he can implement one quickly, or are there dragons hiding in there?

Ross

On Mon, Oct 28, 2002 at 07:55:02PM -0500, Bruce Momjian wrote:
>
> That is a good question. The planner does more than just analyse the
> query. It generates the Plan used by the executor, so that can't be
> removed.
>
> It is always a pain when the optimizer/planner takes longer than the
> executor. We do have PREPARE/EXECUTE in 7.3beta for you to use.
>
>
> ---------------------------------------------------------------------------
>
> Ben McMahan wrote:
> > I'm looking at different ways of optimizing queries with a large number of
> > joins. I write the same query in a number of different ways and compare
> > the running times. Now the problem is I do not want the optimizer
> > changing the queries. So I explicit state the order of the joins in the
> > FROM clause. I also turn off everything I can except for one type of join
> > (say hash join), and I've turned off geqo. But I find that the PLANNER
> > still takes an enormous amount of time for some queries. It doesn't look
> > like the Planner is actually optimizing (changing) anything, but just in
> > case, I was wondering if there was a way to turn off the PLANNER.
> >
> > Note, when I say an enormous amount of time, I mean at least double the
> > time the EXECUTOR takes to actually answer the query.
> >
> > Thanks for your help,
> >
> > Ben McMahan
> >
> > ps. here is a small example of what my queries look like (so you can see
> > if there is something else it might be deciding on):
> >
> > SELECT DISTINCT c0.x1 , c1.x2 , c0.x3 , c0.x4 , c2.x5
> > FROM r1 c4 (x4,x2,x5) JOIN (r0 c3 (x2,x3,x5) JOIN (r2 c2 (x3,x1,x5) JOIN (r1 c1
> > (x4,x1,x2) JOIN r1 c0 (x1,x3,x4)
> > ON ( c0.x4 = c1.x4 AND c0.x1 = c1.x1 ))
> > ON ( c0.x3 = c2.x3 AND c0.x1 = c2.x1 ))
> > ON ( c1.x2 = c3.x2 AND c0.x3 = c3.x3 AND c2.x5 = c3.x5 ))
> > ON ( c0.x4 = c4.x4 AND c1.x2 = c4.x2 AND c2.x5 = c4.x5 );
> >
> > A quick reminder, FROM r1 c4 (x4,x2,x5) just renames a table r1 into c4
> > where it also renames the columns to x4, x2, and x5 respectively.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html