pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedProposal: 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>
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:59 Sean Chittenden <sean@chittenden.org>
parent: 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-23 00:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sean Chittenden <sean@chittenden.org>
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-23 00:17 Josh Berkus <josh@agliodbs.com>
parent: 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-23 00:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
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-26 05:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
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