pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Proposal: relaxing link between explicit JOINs and execution order
6+ messages / 3 participants
[nested] [flat]

* Proposal: relaxing link between explicit JOINs and execution order
@ 2003-01-22 23:01 Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-22 23:59 ` Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order Sean Chittenden <sean@chittenden.org>
  2003-01-23 00:17 ` Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Tom Lane @ 2003-01-22 23:01 UTC (permalink / raw)
  To: pgsql-hackers@postgreSQL.org; pgsql-performance

There's been some recent discussion about the fact that Postgres treats
explicit JOIN syntax as constraining the actual join plan, cf
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html

This behavior was originally in there simply because of lack of time to
consider alternatives.  I now realize that it wouldn't be hard to get
the planner to do better --- basically, preprocess_jointree just has to
be willing to fold JoinExpr-under-JoinExpr into a FromExpr when the
joins are inner joins.

But in the meantime, some folks have found the present behavior to be
a feature rather than a bug, since it lets them control planning time
on many-table queries.  If we are going to change it, I think we need
some way to accommodate both camps.

What I've been toying with is inventing a GUC variable or two.  I am
thinking of defining a variable that sets the maximum size of a FromExpr
that preprocess_jointree is allowed to create by folding JoinExprs.
If this were set to 2, the behavior would be the same as before: no
collapsing of JoinExprs can occur.  If it were set to a large number,
inner JOIN syntax would never affect the planner at all.  In practice
it'd be smart to leave it at some value less than GEQO_THRESHOLD, so
that folding a large number of JOINs wouldn't leave you with a query
that takes a long time to plan or produces unpredictable plans.

There is already a need for a GUC variable to control the existing
behavior of preprocess_jointree: right now, it arbitrarily uses
GEQO_THRESHOLD/2 as the limit for the size of a FromExpr that can be
made by collapsing FromExprs together.  This ought to be a separately
settable parameter, I think.

Comments?  In particular, can anyone think of pithy names for these
variables?  The best I'd been able to come up with is MAX_JOIN_COLLAPSE
and MAX_FROM_COLLAPSE, but neither of these exactly sing...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order
  2003-01-22 23:01 Proposal: relaxing link between explicit JOINs and execution order Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-22 23:59 ` Sean Chittenden <sean@chittenden.org>
  2003-01-23 00:06   ` Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 6+ messages in thread

From: Sean Chittenden @ 2003-01-22 23:59 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-performance

> There's been some recent discussion about the fact that Postgres
> treats explicit JOIN syntax as constraining the actual join plan, cf
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html
> 
> This behavior was originally in there simply because of lack of time
> to consider alternatives.  I now realize that it wouldn't be hard to
> get the planner to do better --- basically, preprocess_jointree just
> has to be willing to fold JoinExpr-under-JoinExpr into a FromExpr
> when the joins are inner joins.
> 
> But in the meantime, some folks have found the present behavior to be
> a feature rather than a bug, since it lets them control planning time
> on many-table queries.  If we are going to change it, I think we need
> some way to accommodate both camps.
[snip]
> Comments?  In particular, can anyone think of pithy names for these
> variables?  The best I'd been able to come up with is
> MAX_JOIN_COLLAPSE and MAX_FROM_COLLAPSE, but neither of these
> exactly sing...

How about something that's runtime tunable via a SET/SHOW config var?
There are some queries that I have that I haven't spent any time
tuning and would love to have the planner spend its CPU thinking about
it instead of mine.  Setting it to 2 by default, then on my tuned
queries, setting to something obscenely high so the planner won't muck
with what I know is fastest (or so I think at least).

I know this is a can of worms, but what about piggy backing on an
Oracle notation and having an inline way of setting this inside of a
comment?

SELECT /* +planner:collapse_tables=12  */ ....  ?
           ^^^^^^^ ^^^^^^^^^^^^^^^ ^^^
	   system  variable        value

::shrug::  In brainstorm mode.  Anyway, a few names:

auto_order_join
auto_order_join_max
auto_reorder_table_limit
auto_collapse_join
auto_collapse_num_join
auto_join_threshold

When I'm thinking about what this variable will do for me as a DBA, I
think it will make the plan more intelligent by reordering the joins.
My $0.02.  -sc

-- 
Sean Chittenden



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order
  2003-01-22 23:01 Proposal: relaxing link between explicit JOINs and execution order Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-22 23:59 ` Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order Sean Chittenden <sean@chittenden.org>
@ 2003-01-23 00:06   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2003-01-23 00:06 UTC (permalink / raw)
  To: Sean Chittenden <sean@chittenden.org>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-performance

Sean Chittenden <sean@chittenden.org> writes:
> How about something that's runtime tunable via a SET/SHOW config var?

Er, that's what I was talking about.

> I know this is a can of worms, but what about piggy backing on an
> Oracle notation and having an inline way of setting this inside of a
> comment?

I don't want to go there ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order
  2003-01-22 23:01 Proposal: relaxing link between explicit JOINs and execution order Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-23 00:17 ` Josh Berkus <josh@agliodbs.com>
  2003-01-23 00:21   ` Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-26 05:27   ` Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 2 replies; 6+ messages in thread

From: Josh Berkus @ 2003-01-23 00:17 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgreSQL.org; pgsql-performance

Tom,

I am very strongly in favor of this idea.   I would personally prefer it if 
the Join collapsing parmeter could be set at query time through a SET 
statement, but will of course defer to the difficulty level in doing so.

> Comments?  In particular, can anyone think of pithy names for these
> variables?  The best I'd been able to come up with is MAX_JOIN_COLLAPSE
> and MAX_FROM_COLLAPSE, but neither of these exactly sing...

How about:
EXPLICIT_JOIN_MINIMUM
and
FROM_COLLAPSE_LIMIT

Just to make the two params not sound so identical?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order
  2003-01-22 23:01 Proposal: relaxing link between explicit JOINs and execution order Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-23 00:17 ` Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order Josh Berkus <josh@agliodbs.com>
@ 2003-01-23 00:21   ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: Tom Lane @ 2003-01-23 00:21 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-hackers@postgreSQL.org; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> I am very strongly in favor of this idea.   I would personally prefer it if 
> the Join collapsing parmeter could be set at query time through a SET 
> statement, but will of course defer to the difficulty level in doing so.

I guess I failed to make it clear that that's what I meant.  GUC
variables are those things that you can set via SET, or in the
postgresql.conf file, etc.  These values would be just as manipulable
as, say, ENABLE_SEQSCAN.

> How about:
> EXPLICIT_JOIN_MINIMUM
> and
> FROM_COLLAPSE_LIMIT

> Just to make the two params not sound so identical?

Hmm.  The two parameters would have closely related functions, so I'd
sort of think that the names *should* be pretty similar.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order
  2003-01-22 23:01 Proposal: relaxing link between explicit JOINs and execution order Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-23 00:17 ` Re: [PERFORM] Proposal: relaxing link between explicit JOINs and execution order Josh Berkus <josh@agliodbs.com>
@ 2003-01-26 05:27   ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: Tom Lane @ 2003-01-26 05:27 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-hackers@postgreSQL.org; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> How about:
> EXPLICIT_JOIN_MINIMUM
> and
> FROM_COLLAPSE_LIMIT

I've implemented this using FROM_COLLAPSE_LIMIT and JOIN_COLLAPSE_LIMIT
as the variable names.  It'd be easy enough to change if someone comes
up with better names.  You can read updated documentation at
http://developer.postgresql.org/docs/postgres/explicit-joins.html

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-01-26 05:27 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-22 23:01 Proposal: relaxing link between explicit JOINs and execution order Tom Lane <tgl@sss.pgh.pa.us>
2003-01-22 23:59 ` Sean Chittenden <sean@chittenden.org>
2003-01-23 00:06   ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-23 00:17 ` Josh Berkus <josh@agliodbs.com>
2003-01-23 00:21   ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-26 05:27   ` Tom Lane <tgl@sss.pgh.pa.us>

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