Обсуждение: [GENERAL] join_collapse_limit = 14

Hi all.

 

I have a query which takes forever and the only way I've been able to make it perform reasonably well is increasing join_collapse_limit to 14 (12 still produced lots of nest-loops).

This way lots of nest-loops (which I think caused the slowness) was made into hash-joins and performance was acceptable again.

 

I wonder; In general, is there any downside of having join_collapse_limit = 14 on modern hardware (32 cores, 64GB RAM), and geqo_threshold=16 ?

I'm aware of it increasing planning-time, but is this really an issue in practice?

 

Thanks.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Andreas Joseph Krogh <andreas@visena.com> writes:
> I wonder; In general, is there any downside of having join_collapse_limit = 14
> on modern hardware (32 cores, 64GB RAM), and geqo_threshold=16 ?
> I'm aware of it increasing planning-time, but is this really an issue in
> practice?

It can be.  The number of possible join orders is exponential in the join
size (at least 2^n, maybe worse, don't recall at the moment).  There are
heuristics in the planner that usually let it avoid investigating large
parts of that space; but the only thing that will certainly limit the
problem is preventing large join subproblems from being considered in the
first place --- which is what join_collapse_limit/from_collapse_limit are
for.

Depending on what your queries are like, you might not ever hit any of the
bad cases, so it might be fine.  But I'd be nervous about throwing a wide
mix of complex queries at a planner set with high thresholds.

If you've got just one problem query, it might be worth your time to take
note of the optimal join order (as seen in EXPLAIN when a good plan is
chosen) and rearrange the query so that the syntactic join order matches
that.  Then you should get the same plan even when join_collapse_limit is
small.

            regards, tom lane

Andreas Joseph Krogh <andreas@visena.com> writes:
> I wonder; In general, is there any downside of having join_collapse_limit = 14
> on modern hardware (32 cores, 64GB RAM), and geqo_threshold=16 ?
> I'm aware of it increasing planning-time, but is this really an issue in
> practice?

It can be.  The number of possible join orders is exponential in the join
size (at least 2^n, maybe worse, don't recall at the moment).  There are
heuristics in the planner that usually let it avoid investigating large
parts of that space; but the only thing that will certainly limit the
problem is preventing large join subproblems from being considered in the
first place --- which is what join_collapse_limit/from_collapse_limit are
for.

Depending on what your queries are like, you might not ever hit any of the
bad cases, so it might be fine.  But I'd be nervous about throwing a wide
mix of complex queries at a planner set with high thresholds.

If you've got just one problem query, it might be worth your time to take
note of the optimal join order (as seen in EXPLAIN when a good plan is
chosen) and rearrange the query so that the syntactic join order matches
that.  Then you should get the same plan even when join_collapse_limit is
small.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Andreas Joseph Krogh <andreas@visena.com> writes:
> På lørdag 07. januar 2017 kl. 17:48:49, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
>  If you've got just one problem query, it might be worth your time to take
>  note of the optimal join order (as seen in EXPLAIN when a good plan is
>  chosen) and rearrange the query so that the syntactic join order matches
>  that.  Then you should get the same plan even when join_collapse_limit is
>  small.

> Will geqo kick if if join_collapse_limit =4 and there are many more joins or
> will the planner just stop trying to rearrange JOINs after this limit?

geqo_threshold applies to the size of the join subproblems that exist
after flattening is done, or not done, according to join_collapse_limit
and from_collapse_limit: if a subproblem is >= geqo_threshold relations
then it's planned via geqo, else the normal search.  So if geqo_threshold
is more than join_collapse_limit then it's impossible for a JOIN nest to
result in use of the geqo code.

> In any case; I assume you're suggesting doing something like "SET LOCAL
> join_collapse_limit=<some-value>" in the same transaction so this doesn't have
> to be a system-wide setting?

Right, there's no reason not to set it locally in your session for
experimental purposes.  I'd just be nervous about turning it up globally
for production without having checked all your queries ...

            regards, tom lane

Andreas Joseph Krogh <andreas@visena.com> writes:
> På lørdag 07. januar 2017 kl. 17:48:49, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
>  If you've got just one problem query, it might be worth your time to take
>  note of the optimal join order (as seen in EXPLAIN when a good plan is
>  chosen) and rearrange the query so that the syntactic join order matches
>  that.  Then you should get the same plan even when join_collapse_limit is
>  small.

> Will geqo kick if if join_collapse_limit =4 and there are many more joins or
> will the planner just stop trying to rearrange JOINs after this limit?

geqo_threshold applies to the size of the join subproblems that exist
after flattening is done, or not done, according to join_collapse_limit
and from_collapse_limit: if a subproblem is >= geqo_threshold relations
then it's planned via geqo, else the normal search.  So if geqo_threshold
is more than join_collapse_limit then it's impossible for a JOIN nest to
result in use of the geqo code.

> In any case; I assume you're suggesting doing something like "SET LOCAL
> join_collapse_limit=<some-value>" in the same transaction so this doesn't have
> to be a system-wide setting?

Right, there's no reason not to set it locally in your session for
experimental purposes.  I'd just be nervous about turning it up globally
for production without having checked all your queries ...

regards, tom lane

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com