Comments (5)
hsyuan
commented on August 17, 2024
If I do select disable_xform('CXformJoinAssociativity');, it can generate same plan with planner.
from gporca.
d
commented on August 17, 2024
Consider the following:
create table t1(a int, b int) distributed by (a);
create table t2(a int, b int) distributed by (a);
create table t3(a int, b int) distributed by (a);
insert into t1 select i,i from generate_series(1,1000) i;
insert into t2 select i,i from generate_series(1,1000) i;
insert into t3 select i,i from generate_series(1100,2300) i;
select disable_xform('CXformJoinAssociativity');
explain select * from t1, t2,t3 where t1.a = t2.a and t1.b < t3.b;
pivotal=# explain select * from t1, t2,t3 where t1.a = t2.a and t1.b < t3.b;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..1355692.50 rows=400334 width=24)
-> Hash Join (cost=0.00..1355656.69 rows=133445 width=24)
Hash Cond: t2.a = t1.a
Join Filter: t1.b < t3.b
-> Nested Loop (cost=0.00..1355070.89 rows=400334 width=16)
Join Filter: true
-> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.16 rows=1000 width=8)
-> Table Scan on t2 (cost=0.00..431.01 rows=334 width=8)
-> Table Scan on t3 (cost=0.00..431.01 rows=401 width=8)
-> Hash (cost=431.16..431.16 rows=1000 width=8)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.16 rows=1000 width=8)
-> Table Scan on t1 (cost=0.00..431.01 rows=334 width=8)
Optimizer status: PQO version 2.51.2
(13 rows)
with optimizer=off, planner generates your favorite plan:
set optimizer to off;
explain select * from t1, t2,t3 where t1.a = t2.a and t1.b < t3.b;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice2; segments: 3) (cost=53.25..81183.80 rows=400334 width=24)
-> Nested Loop (cost=53.25..81183.80 rows=133445 width=24)
Join Filter: t1.b < t3.b
-> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..63.05 rows=1201 width=8)
-> Seq Scan on t3 (cost=0.00..15.01 rows=401 width=8)
-> Materialize (cost=53.25..63.25 rows=334 width=16)
-> Hash Join (cost=25.50..52.25 rows=334 width=16)
Hash Cond: t1.a = t2.a
-> Seq Scan on t1 (cost=0.00..13.00 rows=334 width=8)
-> Hash (cost=13.00..13.00 rows=334 width=8)
-> Seq Scan on t2 (cost=0.00..13.00 rows=334 width=8)
Settings: optimizer=off
Optimizer status: legacy query optimizer
(13 rows)
Now here's the kicker:
reset all;
set optimizer_nestloop_factor=1;
explain select * from t1, t2,t3 where t1.a = t2.a and t1.b < t3.b;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1801.56 rows=400334 width=24)
-> Nested Loop (cost=0.00..1765.75 rows=133445 width=24)
Join Filter: t1.b < t3.b
-> Hash Join (cost=0.00..862.20 rows=334 width=16)
Hash Cond: t1.a = t2.a
-> Table Scan on t1 (cost=0.00..431.01 rows=334 width=8)
-> Hash (cost=431.01..431.01 rows=334 width=8)
-> Table Scan on t2 (cost=0.00..431.01 rows=334 width=8)
-> Materialize (cost=0.00..431.20 rows=1201 width=8)
-> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.19 rows=1201 width=8)
-> Table Scan on t3 (cost=0.00..431.01 rows=401 width=8)
Settings: optimizer_nestloop_factor=1
Optimizer status: PQO version 2.51.2
(13 rows)
The query here essentially can only (well reasonably only) be implemented as one hash join plus one nested loop join. The choice the optimizer faces here is in which order we perform the two joins: do we run a nested loop for the cross join (t3 t2) then a hash join on top? Or do we run the hash join (t1 t2) before running a nest loop to join with t3? Intuitively, we should prefer performing the smaller join first, which is what planner plan and the second orca plan do.
IMHO, the default value of 1024 for optimizer_nestloop_factor creates a perverse incentive for the optimizer to defer the hash join in favor of doing a nested loop join first, even if that means we are doing a cartesian product when we could have avoided that
from gporca.
hsyuan
commented on August 17, 2024
what do you want to say?
from gporca.
d
commented on August 17, 2024
Ohhhhhhhhhhhh I found the bug now
from gporca.
vraghavan78
commented on August 17, 2024
Similar (not identical) plan now generated by Orca
vraghavan=# explain select * from t1, t2,t3 where t1.a = t2.a;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1808226.75 rows=1201000 width=24)
-> Nested Loop (cost=0.00..1808119.33 rows=400334 width=24)
Join Filter: true
-> Hash Join (cost=0.00..862.20 rows=334 width=16)
Hash Cond: t1.a = t2.a
-> Table Scan on t1 (cost=0.00..431.01 rows=334 width=8)
-> Hash (cost=431.01..431.01 rows=334 width=8)
-> Table Scan on t2 (cost=0.00..431.01 rows=334 width=8)
-> Materialize (cost=0.00..431.20 rows=1201 width=8)
-> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.19 rows=1201 width=8)
-> Table Scan on t3 (cost=0.00..431.01 rows=401 width=8)
Settings: optimizer=on
Optimizer status: PQO version 3.21.0
(13 rows)
vraghavan=# set optimizer = off;
SET
vraghavan=# explain select * from t1, t2,t3 where t1.a = t2.a;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice2; segments: 3) (cost=54.50..72177.55 rows=1201000 width=24)
-> Nested Loop (cost=54.50..72177.55 rows=400334 width=24)
-> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..63.05 rows=1201 width=8)
-> Seq Scan on t3 (cost=0.00..15.01 rows=401 width=8)
-> Materialize (cost=54.50..64.50 rows=334 width=16)
-> Hash Join (cost=25.50..53.50 rows=334 width=16)
Hash Cond: t1.a = t2.a
-> Seq Scan on t1 (cost=0.00..13.00 rows=334 width=8)
-> Hash (cost=13.00..13.00 rows=334 width=8)
-> Seq Scan on t2 (cost=0.00..13.00 rows=334 width=8)
Settings: optimizer=off
Optimizer status: legacy query optimizer
(12 rows)
from gporca.
Related Issues (20)
- make error on aarch64 with gcc 8.3.0 on archlinuxarm 64 HOT 8
- Memory leak when running JoinPlanWithRedistribute.mdp in debug mode
- Incorrect join partition elimination HOT 3
- Long plan generation utilising all available memory HOT 1
- make error on solaris HOT 3
- GPORCA doesn't support aarch64 platform.
- can orca apply to innodb?
- gporca occurs exception when using statistic from numeric(30, 8) column to optimize plan
- clang-format in Allman style always indents the contents of extern "C" blocks in C headers
- no <dxl:DistrOpfamilies> tag in gporca/server/dxl.xsd
- CHistogram should keep track of row count (cardinality)
- Orca optimizer single-threaded vs. multi-threaded HOT 3
- why COptCtxt to tls
- [question] why use `virtual` instead of `override`
- Delegating query optimization task to Orca HOT 1
- Orca - how to use
- Is there any formal grammar for the definition of query/plan?
- Should consider startup cost of inner table in NLJ? HOT 1
- Is a bug of CExpression.cpp:505?
- Incorrect result about group by SQL HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from gporca.