Turning the PLANNER off

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@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Does PREPARE turn
select * from mytable
into
select mytable.field1,mytable.field2 from mynamespace.mytable
?

I was looking for this functionality for one of my projects so I'm curious.

David Walker wrote:

Does PREPARE turn
select * from mytable
into
select mytable.field1,mytable.field2 from mynamespace.mytable
?

I was looking for this functionality for one of my projects so I'm curious.

PREPARE turns the query into an internal format used by the executor.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

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.

What's the basis for your assertion that it's "planning things that
don't need it"? Given a JOIN-constrained query I do not believe the
planner will look at any cases other than the intended join order.

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?

He can do whatever he wants, as long as he has no illusions about
getting it accepted back into the sources ;-).

What would probably be more useful is to do some profiling to understand
why the planner is taking longer than he wants even with a
JOIN-constrained query. I should think this would be pretty quick.

regards, tom lane

On Wed, Oct 30, 2002 at 10:59:39PM -0500, Tom Lane wrote:

What's the basis for your assertion that it's "planning things that
don't need it"? Given a JOIN-constrained query I do not believe the
planner will look at any cases other than the intended join order.

Well, that was a loose choice of words - let's say the planner seems to
be taking awfully long to build an execution tree with only one choice
available.

He can do whatever he wants, as long as he has no illusions about
getting it accepted back into the sources ;-).

Understood - this would be a hacking tool only.

What would probably be more useful is to do some profiling to understand
why the planner is taking longer than he wants even with a
JOIN-constrained query. I should think this would be pretty quick.

Yup, that targets the same question as above - anything 'uneeded' actually
happening in the planner? I'll send him off with this suggestion.

Ross