Re: [HACKERS] Proposal: new GUC paramter

[Bruce Momjian]

 Tatsuo Ishii [EMAIL PROTECTED] writes:
  This is really annoying since:
  o these code fragments actually controls the optimization efforts for
subqueries and views, not related to GEQO at all. So using GEQO
parameters for this kind of purpose seems abuse for me.
 
 But GEQO_RELS is directly related to the maximum number of FROM-clause
 entries that we want to try to handle by exhaustive search.  So I think
 it's not completely unreasonable to use it for this additional purpose.
 
 Still, if you want to do the work to create another GUC parameter,
 I won't object.

This is a tough call.  The GEQO value is used here to indicate a table
list that is very long and needs GEQO processing, so there is some
relationship.  If we get to a point where the number of tables is too
large, we do have problems.

However, the GEQO setting is set to the point where we want GEQO to take
over from the standard optimizer.  If GEQO was to be improved, this
value would be decreased but the point at which you would want to stop
increasing the target list probably would be the same.

The GEQO/2 is clearly just a ballpark estimate.  I can see the value as
a separate config parameter, but I can also see it as something that may
be confusing to users and 1% of people will want to change it.  In
fact, interestingly, even if GEQO is off, GEQO_THRESHHOLD can be changed
by users wishing to pull more of their subqueries into their target
list.

I started thinking of some more complex comparison we could do, such as
determining if:

2 * (factorial(rels_in_upper_query) + factorial(rels_in_subquery)) 
factorial(rels_in_upper_query + factorial(rels_in_subquery)

but this doesn't seem to generate good decisions.

I have applied the following documentation patch to at least document
the current behavior.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026


Index: doc/src/sgml/runtime.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.89
diff -c -r1.89 runtime.sgml
*** doc/src/sgml/runtime.sgml   2001/10/09 18:46:00 1.89
--- doc/src/sgml/runtime.sgml   2001/10/11 21:08:59
***
*** 719,725 
  this many FROM items involved.  (Note that a JOIN construct
counts as only one FROM item.) The default is 11. For simpler
queries it is usually best to use the
! deterministic, exhaustive planner.
 /para
/listitem
   /varlistentry
--- 719,727 
  this many FROM items involved.  (Note that a JOIN construct
counts as only one FROM item.) The default is 11. For simpler
queries it is usually best to use the
! deterministic, exhaustive planner.  This parameter also controls
! how hard the optimizer will try to merge subquery
! literalFROM/literal clauses into the upper query.
 /para
/listitem
   /varlistentry



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] Proposal: new GUC paramter

[Tom Lane]

Tatsuo Ishii [EMAIL PROTECTED] writes:
 This is really annoying since:
 o these code fragments actually controls the optimization efforts for
   subqueries and views, not related to GEQO at all. So using GEQO
   parameters for this kind of purpose seems abuse for me.

But GEQO_RELS is directly related to the maximum number of FROM-clause
entries that we want to try to handle by exhaustive search.  So I think
it's not completely unreasonable to use it for this additional purpose.

Still, if you want to do the work to create another GUC parameter,
I won't object.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[HACKERS] Proposal: new GUC paramter

[Tatsuo Ishii]

I see following in src/backend/optimizer/plan/planner.c

if (child  IsA(child, FromExpr))
{

/*
 * Yes, so do we want to merge it into parent?  Always do
 * so if child has just one element (since that doesn't
 * make the parent's list any longer).  Otherwise we have
 * to be careful about the increase in planning time
 * caused by combining the two join search spaces into
 * one.  Our heuristic is to merge if the merge will
 * produce a join list no longer than GEQO_RELS/2.
 * (Perhaps need an additional user parameter?)
 */

This is really annoying since:

o these code fragments actually controls the optimization efforts for
  subqueries and views, not related to GEQO at all. So using GEQO
  parameters for this kind of purpose seems abuse for me.

o Even if geqo = false in postgresql.con, the code looks into the GEQO
  value. This is really confusing for users.

So I propose a new GUC parameter called subquery_merge_threshold
solely for this purpose.

Comments?
--
Tatsuo Ishii

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly