Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / joinhints_optimizer.out
blob: 6eeadd7e1a8b20a40d7a4d3702e3b37d089eec80 [file] [log] [blame]
-- Test Optimizer Join Hints Feature
--
-- Purpose: Test that join hints may be used to coerce certain plan shapes
LOAD 'pg_hint_plan';
DROP SCHEMA IF EXISTS joinhints CASCADE;
CREATE SCHEMA joinhints;
SET search_path=joinhints;
SET optimizer_trace_fallback=on;
-- Setup tables
CREATE TABLE t1(a int, b int);
CREATE TABLE t2(a int, b int);
CREATE TABLE t3(a int, b int);
CREATE TABLE t4(a int, b int);
CREATE TABLE t5(a int, b int);
SET client_min_messages TO log;
SET pg_hint_plan.debug_print TO ON;
LOG: statement: SET pg_hint_plan.debug_print TO ON;
-- Replace timestamp while logging with static string
-- start_matchsubs
-- m/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}:[0-9]{6} [A-Z]{3}/
-- s/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}:[0-9]{6} [A-Z]{3}/YYYY-MM-DD HH:MM:SS:MSMSMS TMZ/
-- end_matchsubs
-- Test that join order hint for every tree shape is applied.
--
-- These check that every possible order on 3 relations. There are 12 possible
-- orders:
--
-- T1 T2 T3 => (T1 T2) T3, T1 (T2 T3)
-- T1 T3 T2 => (T1 T3) T2, T1 (T3 T2)
-- T2 T1 T3 => (T2 T1) T3, T2 (T1 T3)
-- T2 T3 T1 => (T2 T3) T1, T2 (T3 T1)
-- T3 T1 T2 => (T3 T1) T2, T3 (T1 T2)
-- T3 T2 T1 => (T3 T2) T1, T3 (T2 T1)
/*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:562633 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 (t2 t3))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
-> Seq Scan on t3
Optimizer: GPORCA
(12 rows)
/*+
Leading((t1 (t3 t2)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading((t1 (t3 t2)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:569103 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 (t3 t2))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t2
Optimizer: GPORCA
(12 rows)
/*+
Leading(((t2 t3) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading(((t2 t3) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:573925 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t2 t3) t1)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
-> Seq Scan on t3
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
Optimizer: GPORCA
(12 rows)
/*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:578503 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t2) t1)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
Optimizer: GPORCA
(12 rows)
/*+
Leading(((t1 t3) t2))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading(((t1 t3) t2))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:582665 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t1 t3) t2)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t3
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: GPORCA
(12 rows)
/*+
Leading(((t3 t1) t2))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading(((t3 t1) t2))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:587113 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t1) t2)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: GPORCA
(12 rows)
/*+
Leading((t2 (t1 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading((t2 (t1 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:591667 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 (t1 t3))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t3
Optimizer: GPORCA
(12 rows)
/*+
Leading((t2 (t3 t1)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading((t2 (t3 t1)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:596315 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 (t3 t1))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t1
Optimizer: GPORCA
(12 rows)
/*+
Leading(((t1 t2) t3))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading(((t1 t2) t3))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:600797 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t1 t2) t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(12 rows)
/*+
Leading(((t2 t1) t3))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading(((t2 t1) t3))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:604699 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t2 t1) t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(12 rows)
/*+
Leading((t3 (t1 t2)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading((t3 (t1 t2)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:609033 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 (t1 t2))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t3
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t2
Optimizer: GPORCA
(12 rows)
/*+
Leading((t3 (t2 t1)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading((t3 (t2 t1)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:615511 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 (t2 t1))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t3
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
-> Seq Scan on t1
Optimizer: GPORCA
(12 rows)
-- Test that join order hint may be applied over non-leaf nodes
--
-- These check that LIMIT, GROUP BY, or CTE queries may be coerced by a plan
-- hint to produce a specific join order.
/*+
Leading((t2 (t1 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, (SELECT * FROM t3 LIMIT 42) AS q;
LOG: statement: /*+
Leading((t2 (t1 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, (SELECT * FROM t3 LIMIT 42) AS q;
LOG: 2024-05-07 18:38:22:621632 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 (t1 t3))
not used hint:
]",
QUERY PLAN
------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t1
-> Materialize
-> Redistribute Motion 1:3 (slice3)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(16 rows)
/*+
Leading(((t5 (t4 t3)) (t1 t2)))
*/
EXPLAIN (costs off) SELECT * FROM (SELECT t1.a FROM t1, t2 LIMIT 42) AS q, t3, t4, t5;
LOG: statement: /*+
Leading(((t5 (t4 t3)) (t1 t2)))
*/
EXPLAIN (costs off) SELECT * FROM (SELECT t1.a FROM t1, t2 LIMIT 42) AS q, t3, t4, t5;
LOG: 2024-05-07 18:38:22:630341 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t5 (t4 t3)) (t1 t2))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Seq Scan on t5
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t4
-> Seq Scan on t3
-> Materialize
-> Broadcast Motion 1:3 (slice2)
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t2
Optimizer: GPORCA
(24 rows)
/*+
Leading((t1 (t2 t5)))
*/
EXPLAIN (costs off) SELECT * FROM (SELECT * FROM t1, t2, t5 LIMIT 42) q, t4, t3;
LOG: statement: /*+
Leading((t1 (t2 t5)))
*/
EXPLAIN (costs off) SELECT * FROM (SELECT * FROM t1, t2, t5 LIMIT 42) q, t4, t3;
LOG: 2024-05-07 18:38:22:645375 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 (t2 t5))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------
Nested Loop
Join Filter: true
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
-> Seq Scan on t5
-> Materialize
-> Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t4
-> Seq Scan on t3
Optimizer: GPORCA
(22 rows)
/*+
Leading((((t1 t2) t3) t4))
*/
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT t1.a, t2.a FROM t1, t2, t3 GROUP BY t1.a, t2.a) AS q, t4;
LOG: statement: /*+
Leading((((t1 t2) t3) t4))
*/
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT t1.a, t2.a FROM t1, t2, t3 GROUP BY t1.a, t2.a) AS q, t4;
LOG: 2024-05-07 18:38:22:655490 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(((t1 t2) t3) t4)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> GroupAggregate
Group Key: t1.a, t2.a
-> Sort
Sort Key: t1.a, t2.a
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t4
Optimizer: GPORCA
(20 rows)
/*+
Leading(((((g2_t1 g2_t2) g2_t3) t4) (g1_t1 (g1_t3 g1_t2))))
*/
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT g1_t1.a, g1_t2.a FROM t1 AS g1_t1, t2 AS g1_t2, t3 AS g1_t3 GROUP BY g1_t1.a, g1_t2.a) AS q1,
(SELECT g2_t1.a, g2_t2.a FROM t1 AS g2_t1, t2 AS g2_t2, t3 AS g2_t3 GROUP BY g2_t1.a, g2_t2.a) AS q2, t4, t5;
LOG: statement: /*+
Leading(((((g2_t1 g2_t2) g2_t3) t4) (g1_t1 (g1_t3 g1_t2))))
*/
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT g1_t1.a, g1_t2.a FROM t1 AS g1_t1, t2 AS g1_t2, t3 AS g1_t3 GROUP BY g1_t1.a, g1_t2.a) AS q1,
(SELECT g2_t1.a, g2_t2.a FROM t1 AS g2_t1, t2 AS g2_t2, t3 AS g2_t3 GROUP BY g2_t1.a, g2_t2.a) AS q2, t4, t5;
LOG: 2024-05-07 18:38:22:675973 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((((g2_t1 g2_t2) g2_t3) t4) (g1_t1 (g1_t3 g1_t2)))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> GroupAggregate
Group Key: g2_t1.a, g2_t2.a
-> Sort
Sort Key: g2_t1.a, g2_t2.a
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice8; segments: 3)
-> Seq Scan on t1 g2_t1
-> Seq Scan on t2 g2_t2
-> Materialize
-> Broadcast Motion 3:3 (slice7; segments: 3)
-> Seq Scan on t3 g2_t3
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> GroupAggregate
Group Key: g1_t1.a, g1_t2.a
-> Sort
Sort Key: g1_t1.a, g1_t2.a
-> Nested Loop
Join Filter: true
-> Seq Scan on t1 g1_t1
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t3 g1_t3
-> Seq Scan on t2 g1_t2
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t5
Optimizer: GPORCA
(43 rows)
/*+
Leading(((t3 t2) t1))
Leading((t5 t4))
*/
EXPLAIN (COSTS OFF)
WITH cte AS
(
SELECT * FROM t1, t2, t3
)
SELECT * FROM cte, t4, t5;
LOG: statement: /*+
Leading(((t3 t2) t1))
Leading((t5 t4))
*/
EXPLAIN (COSTS OFF)
WITH cte AS
(
SELECT * FROM t1, t2, t3
)
SELECT * FROM cte, t4, t5;
LOG: 2024-05-07 18:38:22:685870 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t2) t1)
JoinHint:(t5 t4)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t5
-> Seq Scan on t4
Optimizer: GPORCA
(21 rows)
-- Test that bad join order hint is *not* applied
--
-- These check that invalid plans are not produced.
-- No plan joins (t2 t3) becase the query has LIMIT on (t1 t2) which must be
-- applied after joining (t1 t2)
/*+
Leading((t1 t3))
*/
EXPLAIN (costs off) SELECT * FROM (SELECT t1.a FROM t1, t2 LIMIT 42) AS q, t3, t4, t5;
LOG: statement: /*+
Leading((t1 t3))
*/
EXPLAIN (costs off) SELECT * FROM (SELECT t1.a FROM t1, t2 LIMIT 42) AS q, t3, t4, t5;
LOG: 2024-05-07 18:38:22:708144 CDT,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:(t1 t3)
]",
QUERY PLAN
---------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Broadcast Motion 1:3 (slice2)
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t2
Optimizer: GPORCA
(24 rows)
-- No plan joins (t2 t3) because the GROUP BY is on (t1 t2)
/*+
Leading((t2 t3))
*/
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT t1.a, t2.a FROM t1, t2 GROUP BY t1.a, t2.a) AS q, t3;
LOG: statement: /*+
Leading((t2 t3))
*/
EXPLAIN (COSTS OFF) SELECT * FROM (SELECT t1.a, t2.a FROM t1, t2 GROUP BY t1.a, t2.a) AS q, t3;
LOG: 2024-05-07 18:38:22:721274 CDT,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:(t2 t3)
]",
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> GroupAggregate
Group Key: t1.a, t2.a
-> Sort
Sort Key: t1.a, t2.a
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t2
-> Seq Scan on t3
Optimizer: GPORCA
(15 rows)
-- syntax error: extra parens
/*+
Leading(((t1 t2)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2;
LOG: statement: /*+
Leading(((t1 t2)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2;
INFO: pg_hint_plan: hint syntax error at or near "Leading(((t1 t2)))
"
DETAIL: Leading hint requires two sets of relations when parentheses nests.
LOG: 2024-05-07 18:38:22:725788 CDT,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
]",
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t2
Optimizer: GPORCA
(7 rows)
-- syntax error: cannot mix directed and non-directed hint
/*+
Leading((t1 (t2 t3 t4)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3, t4;
LOG: statement: /*+
Leading((t1 (t2 t3 t4)))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3, t4;
INFO: pg_hint_plan: hint syntax error at or near "Leading((t1 (t2 t3 t4)))
"
DETAIL: Leading hint requires two sets of relations when parentheses nests.
LOG: 2024-05-07 18:38:22:746565 CDT,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t3
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: GPORCA
(17 rows)
-- Cannot partially specify lower join hint if upper join hint specified.
--
-- Lower join on t2, t3, t4 is not fully specified (hint on t3 missing), and
-- upper join on t1, (t2, t3 t4), t5 is partially specified (hint on t1
-- exists).
/*+
Leading(((t1 t5) (t2 t4)))
*/
EXPLAIN (costs off) SELECT * FROM t1, (SELECT * FROM t2, t3, t4 LIMIT 42) AS q, t5;
LOG: statement: /*+
Leading(((t1 t5) (t2 t4)))
*/
EXPLAIN (costs off) SELECT * FROM t1, (SELECT * FROM t2, t3, t4 LIMIT 42) AS q, t5;
LOG: 2024-05-07 18:38:22:769904 CDT,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:((t1 t5) (t2 t4))
]",
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop
Join Filter: true
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t5
Optimizer: GPORCA
(22 rows)
-- Cannot specify hint between leaf nodes of different joins
--
-- t1 and t3 are leaf nodes of different joins. Hint cannot join them together
-- without first completing join of all relations in one join.
/*+
Leading(((t1 t3) (t2 t4)))
*/
EXPLAIN (costs off) SELECT * FROM t1, (SELECT * FROM t2, t3, t4 LIMIT 42) AS q, t5;
LOG: statement: /*+
Leading(((t1 t3) (t2 t4)))
*/
EXPLAIN (costs off) SELECT * FROM t1, (SELECT * FROM t2, t3, t4 LIMIT 42) AS q, t5;
LOG: 2024-05-07 18:38:22:789581 CDT,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:((t1 t3) (t2 t4))
]",
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop
Join Filter: true
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t5
Optimizer: GPORCA
(22 rows)
-- Cannot apply two conflicting hints
--
-- One hint specifiese t1 on outer side, but the other hint joins t1 inner
-- side. Both hints cannot be satisfied.
/*+
Leading((t1 t2))
Leading((t2 t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2;
LOG: statement: /*+
Leading((t1 t2))
Leading((t2 t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2;
LOG: 2024-05-07 18:38:22:793904 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t2)
not used hint:
JoinHint:(t2 t1)
]",
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t2
Optimizer: GPORCA
(7 rows)
/*+
Leading((((t5 t4) (t1 t3)) t2))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1, t2, t3, t4, t5;
LOG: statement: /*+
Leading((((t5 t4) (t1 t3)) t2))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1, t2, t3, t4, t5;
LOG: 2024-05-07 18:38:22:799394 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(((t5 t4) (t1 t3)) t2)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Seq Scan on t4
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t2
Optimizer: GPORCA
(20 rows)
/*+
Leading(((t5 t4) (t1 t3)))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1, t2, t3, t4, t5;
LOG: statement: /*+
Leading(((t5 t4) (t1 t3)))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1, t2, t3, t4, t5;
LOG: 2024-05-07 18:38:22:809330 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t5 t4) (t1 t3))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Seq Scan on t4
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t2
Optimizer: GPORCA
(20 rows)
/*+
Leading((t2 t1))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t3.a=t1.a, t4, t5;
LOG: statement: /*+
Leading((t2 t1))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t3.a=t1.a, t4, t5;
LOG: 2024-05-07 18:38:22:830917 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t1)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t3.a)
-> Nested Loop
Join Filter: true
-> Hash Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t5
-> Hash
-> Nested Loop
Join Filter: true
-> Seq Scan on t3
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
Optimizer: GPORCA
(21 rows)
/*+
Leading((t5 (((t4 t3) t2) t1)))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t3.a=t1.a, t4, t5;
LOG: statement: /*+
Leading((t5 (((t4 t3) t2) t1)))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t3.a=t1.a, t4, t5;
LOG: 2024-05-07 18:38:22:842164 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t5 (((t4 t3) t2) t1))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Hash Join
Hash Cond: ((t2.a = t1.a) AND (t3.a = t1.a))
-> Hash Join
Hash Cond: (t3.a = t2.a)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t4
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
Optimizer: GPORCA
(20 rows)
/*+
Leading((t5 (((t4 t3) t2) t1)))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t3.a=t1.a+1 AND t3.a>42, t4, t5;
LOG: statement: /*+
Leading((t5 (((t4 t3) t2) t1)))
*/
EXPLAIN (costs off) SELECT t1.a, t2.a FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t3.a=t1.a+1 AND t3.a>42, t4, t5;
LOG: 2024-05-07 18:38:22:856438 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t5 (((t4 t3) t2) t1))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Hash Join
Hash Cond: ((t2.a = t1.a) AND (t3.a = (t1.a + 1)))
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Seq Scan on t3
Filter: (a > 42)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
Optimizer: GPORCA
(22 rows)
-- Test that multiple join order hint can be applied
--
-- Following queries produce multiple NAry join operators where different join
-- order hints may be applied.
/*+
Leading((t3 t4))
Leading((t1 (t2 t5)))
*/
EXPLAIN (costs off) SELECT * FROM t4, t3, (SELECT * FROM t1, t2, t5 LIMIT 42) q;
LOG: statement: /*+
Leading((t3 t4))
Leading((t1 (t2 t5)))
*/
EXPLAIN (costs off) SELECT * FROM t4, t3, (SELECT * FROM t1, t2, t5 LIMIT 42) q;
LOG: 2024-05-07 18:38:22:866486 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 t4)
JoinHint:(t1 (t2 t5))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------
Nested Loop
Join Filter: true
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
-> Seq Scan on t5
-> Materialize
-> Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t4
Optimizer: GPORCA
(22 rows)
-- mixes directioned and directioned-less hint syntax
/*+
Leading((t3 t4))
Leading(t1 t2 t5)
*/
EXPLAIN (costs off) SELECT * FROM t4, t3, (SELECT * FROM t1, t2, t5 LIMIT 42) q;
LOG: statement: /*+
Leading((t3 t4))
Leading(t1 t2 t5)
*/
EXPLAIN (costs off) SELECT * FROM t4, t3, (SELECT * FROM t1, t2, t5 LIMIT 42) q;
LOG: 2024-05-07 18:38:22:878402 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 t4)
JoinHint:t1 t2 t5
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop
Join Filter: true
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t4
Optimizer: GPORCA
(22 rows)
/*+
Leading((t3 t4))
Leading((t1 (t2 t5)))
*/
EXPLAIN (costs off) SELECT * FROM (SELECT * FROM t4, t3 LIMIT 1) p, (SELECT * FROM t1, t2, t5 LIMIT 42) q;
LOG: statement: /*+
Leading((t3 t4))
Leading((t1 (t2 t5)))
*/
EXPLAIN (costs off) SELECT * FROM (SELECT * FROM t4, t3 LIMIT 1) p, (SELECT * FROM t1, t2, t5 LIMIT 42) q;
LOG: 2024-05-07 18:38:22:890434 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 t4)
JoinHint:(t1 (t2 t5))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------
Nested Loop
Join Filter: true
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
-> Seq Scan on t5
-> Materialize
-> Limit
-> Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t4
Optimizer: GPORCA
(23 rows)
-- Test that directioned-less join order hints can be applied
--
-- Following queries use directioned-less syntax. Example:
--
-- "Leading(t1 t2 ... tn)"
--
-- Above example specifies that t1 JOIN t2 happens *before* JOIN tn. But t1 can
-- be on the inner or outer side of the join. In contrast "Leading((t1 t2))
-- requires t1 to be on the outer side of the join.
/*+
Leading(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:897127 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:t1 t2 t3
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t1
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(12 rows)
/*+
Leading(t3 t2 t1)
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: statement: /*+
Leading(t3 t2 t1)
*/
EXPLAIN (costs off) SELECT * FROM t1, t2, t3;
LOG: 2024-05-07 18:38:22:902750 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:t3 t2 t1
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
Optimizer: GPORCA
(12 rows)
--------------------------------------------------------------------
-- Test join type hints can be applied
--
-- Join types can be HashJoin, NestLoop, or MergeJoin
--
-- NOTE: An index-nestloop join is a combination of NestLoop and IndexScan
--------------------------------------------------------------------
CREATE INDEX i1 ON t1(a);
LOG: statement: CREATE INDEX i1 ON t1(a);
CREATE INDEX i2 ON t2(a);
LOG: statement: CREATE INDEX i2 ON t2(a);
CREATE INDEX i3 ON t3(a);
LOG: statement: CREATE INDEX i3 ON t3(a);
CREATE INDEX i4 ON t4(a);
LOG: statement: CREATE INDEX i4 ON t4(a);
CREATE INDEX i5 ON t5(a);
LOG: statement: CREATE INDEX i5 ON t5(a);
/*+
HashJoin(t1 t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: statement: /*+
HashJoin(t1 t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: 2024-05-13 21:02:08:459587 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:HashJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
Optimizer: GPORCA
(7 rows)
/*+
NoHashJoin(t1 t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: statement: /*+
NoHashJoin(t1 t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: 2024-05-13 21:02:08:465181 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:NoHashJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: GPORCA
(7 rows)
/*+
NestLoop(t1 t2)
SeqScan(t1)
SeqScan(t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: statement: /*+
NestLoop(t1 t2)
SeqScan(t1)
SeqScan(t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: 2024-05-13 21:02:08:468568 CDT,THD000,TRACE,"PlanHint: [
used hint:
ScanHint: t1[indexes: types:SeqScan]
ScanHint: t2[indexes: types:SeqScan]
JoinTypeHint: aliases:t1,t2 type:NestedLoopJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: (t1.a = t2.a)
-> Seq Scan on t1
-> Seq Scan on t2
Optimizer: GPORCA
(6 rows)
/*+
NestLoop(t1 t2)
IndexScan(t1)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: statement: /*+
NestLoop(t1 t2)
IndexScan(t1)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: 2024-05-13 21:02:08:472654 CDT,THD000,TRACE,"PlanHint: [
used hint:
ScanHint: t1[indexes: types:IndexScan]
JoinTypeHint: aliases:t1,t2 type:NestedLoopJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
Optimizer: GPORCA
(7 rows)
/*+
NestLoop(t1 t2)
IndexScan(t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: statement: /*+
NestLoop(t1 t2)
IndexScan(t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: 2024-05-13 21:02:08:476605 CDT,THD000,TRACE,"PlanHint: [
used hint:
ScanHint: t2[indexes: types:IndexScan]
JoinTypeHint: aliases:t1,t2 type:NestedLoopJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: GPORCA
(7 rows)
/*+
NoNestLoop(t1 t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: statement: /*+
NoNestLoop(t1 t2)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
LOG: 2024-05-13 21:02:08:480493 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:NoNestedLoopJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
Optimizer: GPORCA
(7 rows)
/*+
NoHashJoin(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
NoHashJoin(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-13 21:02:08:502313 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2,t3 type:NoHashJoin
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
NoHashJoin(t2 t3)
NoHashJoin(t1 t3)
NoHashJoin(t1 t2)
NoHashJoin(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
NoHashJoin(t2 t3)
NoHashJoin(t1 t3)
NoHashJoin(t1 t2)
NoHashJoin(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-13 21:02:08:520553 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:NoHashJoin
JoinTypeHint: aliases:t1,t3 type:NoHashJoin
JoinTypeHint: aliases:t2,t3 type:NoHashJoin
JoinTypeHint: aliases:t1,t2,t3 type:NoHashJoin
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
NoNestLoop(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
NoNestLoop(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-13 21:02:08:541870 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2,t3 type:NoNestedLoopJoin
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
-> Hash
-> Seq Scan on t2
Optimizer: GPORCA
(11 rows)
/*+
NoNestLoop(t2 t3)
NoNestLoop(t1 t3)
NoNestLoop(t1 t2)
NoNestLoop(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
NoNestLoop(t2 t3)
NoNestLoop(t1 t3)
NoNestLoop(t1 t2)
NoNestLoop(t1 t2 t3)
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-13 21:02:08:557158 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:NoNestedLoopJoin
JoinTypeHint: aliases:t1,t3 type:NoNestedLoopJoin
JoinTypeHint: aliases:t2,t3 type:NoNestedLoopJoin
JoinTypeHint: aliases:t1,t2,t3 type:NoNestedLoopJoin
not used hint:
]",
QUERY PLAN
----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Hash Join
Hash Cond: (t1.a = t3.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t3
Optimizer: GPORCA
(11 rows)
--
-- Test nest loop join type hints
--
/*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
LOG: statement: /*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
LOG: 2024-05-13 21:02:08:560832 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:NestedLoopJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: GPORCA
(7 rows)
/*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.a;
LOG: statement: /*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.a;
LOG: 2024-05-13 21:02:08:564077 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:NestedLoopJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
Optimizer: GPORCA
(7 rows)
-- XXX: ORCA doesn't support nest join on full join
/*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 FULL JOIN t2 ON t1.a = t2.a;
LOG: statement: /*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 FULL JOIN t2 ON t1.a = t2.a;
LOG: 2024-05-13 21:02:08:568336 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
2024-05-13 21:02:08:568805 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA
LOG: pg_hint_plan:
used hint:
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Full Join
Merge Cond: (t1.a = t2.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i2 on t2
Optimizer: Postgres-based planner
(7 rows)
/*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
LOG: statement: /*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
LOG: 2024-05-13 21:02:08:579148 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:NestedLoopJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> GroupAggregate
Group Key: t2.a
-> Sort
Sort Key: t2.a
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
Optimizer: GPORCA
(11 rows)
/*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.a FROM t2);
LOG: statement: /*+
NestLoop(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.a FROM t2);
LOG: 2024-05-13 21:02:08:584528 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:NestedLoopJoin
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result
Filter: (SubPlan 1)
-> Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on t1
SubPlan 1
-> Result
Filter: ((CASE WHEN (sum((CASE WHEN (t1.a = t2.a) THEN 1 ELSE 0 END)) IS NULL) THEN true WHEN (sum((CASE WHEN (t2.a IS NULL) THEN 1 ELSE 0 END)) > '0'::bigint) THEN NULL::boolean WHEN (t1.a IS NULL) THEN NULL::boolean WHEN (sum((CASE WHEN (t1.a = t2.a) THEN 1 ELSE 0 END)) = '0'::bigint) THEN true ELSE false END) = true)
-> Aggregate
-> Result
-> Materialize
-> Gather Motion 3:1 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: GPORCA
(13 rows)
--
-- Test merge join type hints
--
-- XXX: ORCA doesn't support merge join on left join
/*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
LOG: statement: /*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
LOG: 2024-05-13 21:02:08:587990 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
2024-05-13 21:02:08:588478 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Left Join
Merge Cond: (t1.a = t2.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i2 on t2
Optimizer: Postgres-based planner
(7 rows)
-- XXX: ORCA doesn't support merge join on right join
/*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.a;
LOG: statement: /*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.a;
LOG: 2024-05-13 21:02:08:592166 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
2024-05-13 21:02:08:592658 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Left Join
Merge Cond: (t2.a = t1.a)
-> Index Scan using i2 on t2
-> Materialize
-> Index Scan using i1 on t1
Optimizer: Postgres-based planner
(7 rows)
/*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 FULL JOIN t2 ON t1.a = t2.a;
LOG: statement: /*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 FULL JOIN t2 ON t1.a = t2.a;
LOG: 2024-05-13 21:02:08:598574 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:MergeJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Full Join
Merge Cond: (t2.a = t1.a)
-> Sort
Sort Key: t2.a
-> Seq Scan on t2
-> Sort
Sort Key: t1.a
-> Seq Scan on t1
Optimizer: GPORCA
(10 rows)
-- XXX: ORCA doesn't support merge join on semi-join
/*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
LOG: statement: /*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
LOG: 2024-05-13 21:02:08:603533 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
2024-05-13 21:02:08:604007 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Index Scan using i1 on t1
-> Sort
Sort Key: t2.a
-> HashAggregate
Group Key: t2.a
-> Seq Scan on t2
Optimizer: Postgres-based planner
(10 rows)
-- XXX: ORCA doesn't support merge join on anti-semi-join
/*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.a FROM t2);
LOG: statement: /*+
MergeJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.a FROM t2);
LOG: 2024-05-13 21:02:08:608367 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
2024-05-13 21:02:08:608818 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA",
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Anti Semi (Not-In) Join
Join Filter: (t1.a = t2.a)
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres query optimizer
(8 rows)
--
-- Test hash join type hints
--
/*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
LOG: statement: /*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
LOG: 2024-05-13 21:02:08:612906 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:HashJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
Optimizer: GPORCA
(7 rows)
/*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.a;
LOG: statement: /*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.a;
LOG: 2024-05-13 21:02:08:616265 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:HashJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
Optimizer: GPORCA
(7 rows)
/*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 FULL JOIN t2 ON t1.a = t2.a;
LOG: statement: /*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 FULL JOIN t2 ON t1.a = t2.a;
LOG: 2024-05-13 21:02:08:621265 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:HashJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Full Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
Optimizer: GPORCA
(7 rows)
/*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
LOG: statement: /*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
LOG: 2024-05-13 21:02:08:628414 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:HashJoin
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Semi Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
Optimizer: GPORCA
(7 rows)
/*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.a FROM t2);
LOG: statement: /*+
HashJoin(t1 t2)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.a FROM t2);
LOG: 2024-05-13 21:02:08:632578 CDT,THD000,TRACE,"PlanHint: [
used hint:
JoinTypeHint: aliases:t1,t2 type:HashJoin
not used hint:
]",
QUERY PLAN
------------------------------------------------------
Hash Left Anti Semi (Not-In) Join
Hash Cond: (t1.a = t2.a)
-> Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on t1
-> Hash
-> Gather Motion 3:1 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: GPORCA
(8 rows)
-- Test planhints logging for JoinTypeHints
-- Missing alias in hint to test 'not used' hints logging
/*+
HashJoin(y z)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.a FROM t2);
LOG: statement: /*+
HashJoin(y z)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.a FROM t2);
LOG: 2024-05-13 21:02:08:636679 CDT,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinTypeHint: aliases:y,z type:HashJoin
]",
QUERY PLAN
------------------------------------------------------
Hash Left Anti Semi (Not-In) Join
Hash Cond: (t1.a = t2.a)
-> Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on t1
-> Hash
-> Gather Motion 3:1 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: GPORCA
(8 rows)
-- Invalid JoinHint type to test Hint logging behavior
/*+
InvalidJoinTypeHint(t1)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
LOG: statement: /*+
InvalidJoinTypeHint(t1)
*/
EXPLAIN (COSTS off) SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
INFO: pg_hint_plan: hint syntax error at or near "InvalidJoinTypeHint(t1)
"
DETAIL: Unrecognized hint keyword "InvalidJoinTypeHint".
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> GroupAggregate
Group Key: t2.a
-> Sort
Sort Key: t2.a
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
Optimizer: GPORCA
(11 rows)
-- Test join order hints on non-inner join queries
--
-- Following queries exercise LOJ and ROJ
/*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a AND t3.a>40;
LOG: statement: /*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a AND t3.a>40;
LOG: 2024-05-15 21:45:33:738814 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 (t2 t3))
not used hint:
]",
QUERY PLAN
-----------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i3 on t3
Index Cond: ((a = t2.a) AND (a > 40))
Optimizer: GPORCA
(11 rows)
/*+
Leading(((t1 t4) (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 on t1.a=t4.a;
LOG: statement: /*+
Leading(((t1 t4) (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 on t1.a=t4.a;
LOG: 2024-05-15 21:45:33:767312 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t1 t4) (t2 t3))
not used hint:
]",
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i4 on t4
Index Cond: (a = t1.a)
-> Hash
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
Optimizer: GPORCA
(15 rows)
/*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:33:787726 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t2) t1)
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Hash Right Join
Hash Cond: (t3.a = t2.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:33:802360 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t2) t1)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Hash Right Join
Hash Cond: (t3.a = t2.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
Optimizer: GPORCA
(12 rows)
/*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2 RIGHT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off) SELECT * FROM t1, t2 RIGHT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:33:819212 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t2) t1)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t3
-> Index Scan using i2 on t2
Index Cond: (a = t3.a)
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
Optimizer: GPORCA
(12 rows)
/*+
Leading((t2 t1))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
LOG: statement: /*+
Leading((t2 t1))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
LOG: 2024-05-15 21:45:33:831205 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t1)
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
Optimizer: GPORCA
(7 rows)
-- Bad LOJ/ROJ hints
--
-- Notice that the join condition does not allow the following hint to be
-- applied. This is because, unlike inner join, the join predicates of LOJ/ROJ
-- are not transitive.
/*+
Leading(((t1 t4) (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 on t2.a=t4.a;
LOG: statement: /*+
Leading(((t1 t4) (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 on t2.a=t4.a;
LOG: 2024-05-15 21:45:33:876225 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t1 t4) (t2 t3))
not used hint:
]",
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Nested Loop Left Join
Join Filter: true
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t2.a
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i4 on t4
Index Cond: (a = t2.a)
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
Optimizer: GPORCA
(17 rows)
-- Again, the join condition does not allow the following hint to be applied.
-- Here t3 join condition requires t1, it is illegal to join t2 and t3 first.
/*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:33:912790 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 (t2 t3))
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
-- Again, the join condition does not allow the following hint to be applied.
-- Here t3 join condition requires t1, it is illegal to join t2 and t3 first.
/*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a=t2.a RIGHT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a=t2.a RIGHT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 22:07:12:357398 UTC,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:(t1 (t2 t3))
]",
QUERY PLAN
------------------------------------------------------
Hash Right Join
Hash Cond: (t1.a = t3.a)
-> Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
-> Hash
-> Gather Motion 3:1 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(12 rows)
-- Make sure we generate the same plan when hints are not used.
EXPLAIN (costs off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a=t2.a RIGHT JOIN t3 ON t1.a=t3.a;
LOG: statement: EXPLAIN (costs off) SELECT * FROM t1 RIGHT JOIN t2 ON t1.a=t2.a RIGHT JOIN t3 ON t1.a=t3.a;
QUERY PLAN
------------------------------------------------------
Hash Right Join
Hash Cond: (t1.a = t3.a)
-> Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
-> Hash
-> Gather Motion 3:1 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(12 rows)
-- Tables (t2 and t3) of INNER JOIN on the inner side of a LEFT JOIN cannot be
-- reordered with the tables (t1) on the outer side.
/*+
Leading((t1 t2))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN (t2 INNER JOIN t3 ON t2.a = t3.a) ON t1.a = t2.a;
LOG: statement: /*+
Leading((t1 t2))
*/
EXPLAIN (costs off) SELECT * FROM t1 LEFT JOIN (t2 INNER JOIN t3 ON t2.a = t3.a) ON t1.a = t2.a;
LOG: 2024-05-16 03:21:39:252321 UTC,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:(t1 t2)
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Nested Loop
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
-> Hash
-> Seq Scan on t1
Optimizer: GPORCA
(11 rows)
-- Tables (t2 and t3) of INNER JOIN on the outer side of a RIGHT JOIN cannot be
-- reordered with the tables (t1) on the inner side.
/*+
Leading((t1 t2))
*/
EXPLAIN (costs off) SELECT * FROM t2 INNER JOIN t3 ON t2.a = t3.a RIGHT JOIN t1 ON t1.a=t2.a;
LOG: statement: /*+
Leading((t1 t2))
*/
EXPLAIN (costs off) SELECT * FROM t2 INNER JOIN t3 ON t2.a = t3.a RIGHT JOIN t1 ON t1.a=t2.a;
LOG: 2024-05-16 03:21:39:289029 UTC,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:(t1 t2)
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Nested Loop
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
-> Hash
-> Seq Scan on t1
Optimizer: GPORCA
(11 rows)
-- Test that only *valid* join order hint shapes are applied for LOJ.
--
-- These check that every possible order on 3 relations. There are 12 possible
-- orders:
--
-- T1 T2 T3 => (T1 T2) T3, T1 (T2 T3)
-- T1 T3 T2 => (T1 T3) T2, T1 (T3 T2)
-- T2 T1 T3 => (T2 T1) T3, T2 (T1 T3)
-- T2 T3 T1 => (T2 T3) T1, T2 (T3 T1)
-- T3 T1 T2 => (T3 T1) T2, T3 (T1 T2)
-- T3 T2 T1 => (T3 T2) T1, T3 (T2 T1)
--
-- Note that not every order is a valid plan! And that regardless of the hint,
-- the query should produce the same results.
INSERT INTO t1 VALUES (50, 50), (51, 51), (NULL, NULL);
LOG: statement: INSERT INTO t1 VALUES (50, 50), (51, 51), (NULL, NULL);
INSERT INTO t2 VALUES (50, 50), (52, 52), (NULL, NULL);
LOG: statement: INSERT INTO t2 VALUES (50, 50), (52, 52), (NULL, NULL);
INSERT INTO t3 VALUES (50, 50), (53, 53), (NULL, NULL);
LOG: statement: INSERT INTO t3 VALUES (50, 50), (53, 53), (NULL, NULL);
INSERT INTO t4 VALUES (50, 50), (54, 54), (NULL, NULL);
LOG: statement: INSERT INTO t4 VALUES (50, 50), (54, 54), (NULL, NULL);
INSERT INTO t5 VALUES (50, 50), (55, 55), (NULL, NULL);
LOG: statement: INSERT INTO t5 VALUES (50, 50), (55, 55), (NULL, NULL);
/*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t1 (t2 t3)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:061765 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 (t2 t3))
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading((t1 (t2 t3)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t1 (t2 t3)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:098155 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 (t2 t3))
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading((t1 (t3 t2)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t1 (t3 t2)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:135866 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 (t3 t2))
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading((t1 (t3 t2)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t1 (t3 t2)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:172535 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 (t3 t2))
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading(((t2 t3) t1))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t2 t3) t1))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:210088 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t2 t3) t1)
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading(((t2 t3) t1))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t2 t3) t1))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:246644 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t2 t3) t1)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t3 t2) t1))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:284287 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t2) t1)
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading(((t3 t2) t1))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t3 t2) t1))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:321173 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t2) t1)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading(((t1 t3) t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t1 t3) t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:345295 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t1 t3) t2)
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading(((t1 t3) t2))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t1 t3) t2))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:368628 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t1 t3) t2)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading(((t3 t1) t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t3 t1) t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:389086 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t1) t2)
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Hash Right Join
Hash Cond: (t3.a = t1.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading(((t3 t1) t2))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t3 t1) t2))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:408829 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t3 t1) t2)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading((t2 (t1 t3)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t2 (t1 t3)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:437353 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 (t1 t3))
not used hint:
]",
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading((t2 (t1 t3)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t2 (t1 t3)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:457683 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 (t1 t3))
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading((t2 (t3 t1)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t2 (t3 t1)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:478190 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 (t3 t1))
not used hint:
]",
QUERY PLAN
----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Hash Right Join
Hash Cond: (t3.a = t1.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t1
Optimizer: GPORCA
(11 rows)
/*+
Leading((t2 (t3 t1)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t2 (t3 t1)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:494968 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 (t3 t1))
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading(((t1 t2) t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t1 t2) t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:524205 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t1 t2) t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading(((t1 t2) t3))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t1 t2) t3))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:549541 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t1 t2) t3)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading(((t2 t1) t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t2 t1) t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:575774 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t2 t1) t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Nested Loop
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading(((t2 t1) t3))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading(((t2 t1) t3))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:600860 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t2 t1) t3)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading((t3 (t1 t2)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t3 (t1 t2)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:622475 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 (t1 t2))
not used hint:
]",
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t3.a = t1.a)
-> Seq Scan on t3
-> Hash
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading((t3 (t1 t2)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t3 (t1 t2)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:642252 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 (t1 t2))
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading((t3 (t2 t1)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t3 (t2 t1)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:668154 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 (t2 t1))
not used hint:
]",
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t3.a = t1.a)
-> Seq Scan on t3
-> Hash
-> Nested Loop
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
Optimizer: GPORCA
(11 rows)
/*+
Leading((t3 (t2 t1)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: statement: /*+
Leading((t3 (t2 t1)))
*/
SELECT * FROM t1 JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
LOG: 2024-05-15 21:45:34:687703 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 (t2 t1))
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
(1 row)
/*+
Leading((t1 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t1 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:718537 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t2)
not used hint:
]",
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t2.a
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
Optimizer: GPORCA
(13 rows)
/*+
Leading((t1 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t1 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:743620 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t2)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
51 | 51 | | | |
| | | | |
50 | 50 | 50 | 50 | 50 | 50
(3 rows)
/*+
Leading((t2 t1))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t2 t1))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:770551 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t1)
not used hint:
]",
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t2.a
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
Optimizer: GPORCA
(13 rows)
/*+
Leading((t2 t1))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t2 t1))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:791591 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t1)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
| | | | |
51 | 51 | | | |
50 | 50 | 50 | 50 | 50 | 50
(3 rows)
/*+
Leading((t2 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t2 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:831237 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
-> Hash
-> Seq Scan on t1
Optimizer: GPORCA
(11 rows)
/*+
Leading((t2 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t2 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:856704 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t3)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
| | | | |
51 | 51 | | | |
(3 rows)
/*+
Leading((t3 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t3 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:882599 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 t2)
not used hint:
]",
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Hash Right Join
Hash Cond: (t3.a = t2.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
Optimizer: GPORCA
(11 rows)
/*+
Leading((t3 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t3 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:902887 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 t2)
not used hint:
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
| | | | |
51 | 51 | | | |
(3 rows)
/*+
Leading((t1 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t1 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:933014 UTC,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:(t1 t3)
]",
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t2.a
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
Optimizer: GPORCA
(13 rows)
/*+
Leading((t1 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t1 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:957913 UTC,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:(t1 t3)
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
51 | 51 | | | |
| | | | |
(3 rows)
/*+
Leading((t3 t1))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t3 t1))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:34:985551 UTC,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:(t3 t1)
]",
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t2.a
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
Optimizer: GPORCA
(13 rows)
/*+
Leading((t3 t1))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: statement: /*+
Leading((t3 t1))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
LOG: 2024-05-15 21:45:35:010575 UTC,THD000,TRACE,"PlanHint: [
used hint:
not used hint:
JoinHint:(t3 t1)
]",
a | b | a | b | a | b
----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50
51 | 51 | | | |
| | | | |
(3 rows)
-- Test LOJ cases where middle joins are applied first
--
-- For example, if query is:
--
-- T1 LOJ T2 LOJ T3 LOJ T4 LOJ T5
--
-- Then if hint is Leading((T2 T3)), then query should generate correct results.
/*+
Leading((t2 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: statement: /*+
Leading((t2 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: 2024-05-15 21:45:35:074400 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t3.a
-> Nested Loop Left Join
Join Filter: true
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t2
-> Index Scan using i3 on t3
Index Cond: (a = t2.a)
-> Hash
-> Seq Scan on t1
-> Index Scan using i5 on t5
Index Cond: (a = t1.a)
-> Index Scan using i4 on t4
Index Cond: (a = t3.a)
Optimizer: GPORCA
(21 rows)
/*+
Leading((t2 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: statement: /*+
Leading((t2 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: 2024-05-15 21:45:35:135971 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t3)
not used hint:
]",
a | b | a | b | a | b | a | b | a | b
----+----+----+----+----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50
| | | | | | | | |
51 | 51 | | | | | | | |
(3 rows)
/*+
Leading((t3 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: statement: /*+
Leading((t3 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: 2024-05-15 21:45:35:201987 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 t2)
not used hint:
]",
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: true
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t3.a
-> Nested Loop Left Join
Join Filter: true
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Hash Right Join
Hash Cond: (t3.a = t2.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
-> Index Scan using i5 on t5
Index Cond: (a = t1.a)
-> Index Scan using i4 on t4
Index Cond: (a = t3.a)
Optimizer: GPORCA
(21 rows)
/*+
Leading((t3 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: statement: /*+
Leading((t3 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: 2024-05-15 21:45:35:260392 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t3 t2)
not used hint:
]",
a | b | a | b | a | b | a | b | a | b
----+----+----+----+----+----+----+----+----+----
50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50
| | | | | | | | |
51 | 51 | | | | | | | |
(3 rows)
/*+
Leading((t4 (t3 t2)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: statement: /*+
Leading((t4 (t3 t2)))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: 2024-05-15 21:45:35:308932 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t4 (t3 t2))
not used hint:
]",
QUERY PLAN
------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i5 on t5
Index Cond: (a = t1.a)
-> Hash
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t2.a
-> Hash Right Join
Hash Cond: (t4.a = t3.a)
-> Seq Scan on t4
-> Hash
-> Redistribute Motion 3:3 (slice3; segments: 3)
Hash Key: t3.a
-> Hash Right Join
Hash Cond: (t3.a = t2.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
Optimizer: GPORCA
(23 rows)
/*+
Leading((t4 (t3 t2)))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: statement: /*+
Leading((t4 (t3 t2)))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a LEFT JOIN t4 ON t3.a=t4.a LEFT JOIN t5 ON t1.a=t5.a;
LOG: 2024-05-15 21:45:35:351049 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t4 (t3 t2))
not used hint:
]",
a | b | a | b | a | b | a | b | a | b
----+----+----+----+----+----+----+----+----+----
51 | 51 | | | | | | | |
| | | | | | | | |
50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50
(3 rows)
-- Test LOJ cases with NULL aware predicates
--
-- Join order cannot be reordered if a join predicate is satisified when the
-- column of a table is NULL. This is because the number of NULLs can change
-- based on the order the join is executed.
--
-- For example,
--
-- Table Data:
-- T1 T2 T3
-- -- -- --
-- 1 NULL 1
--
-- Order #1:
-- (T1 LOJ T2 ON T1.a=T2.a) LOJ T3 ON t2.a IS NULL
-- T1 | T2 |T3
-- ---+----+---
-- 1 | |1
--
-- Order #2:
-- T1 LOJ (T2 LOJ T3 ON t2.a IS NULL) ON T1.a=T2.a
-- T1 | T2 |T3
-- ---+----+---
-- 1 | |
/*+
Leading((t2 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: statement: /*+
Leading((t2 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: 2024-05-15 21:45:35:379880 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Index Scan using i2 on t2
Index Cond: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(14 rows)
/*+
Leading((t2 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: statement: /*+
Leading((t2 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: 2024-05-15 21:45:35:396131 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t3)
not used hint:
]",
a | b | a | b | a | b
----+----+---+---+----+----
50 | 50 | | | 50 | 50
50 | 50 | | | 53 | 53
50 | 50 | | | |
51 | 51 | | | 50 | 50
51 | 51 | | | 53 | 53
51 | 51 | | | |
| | | | 50 | 50
| | | | 53 | 53
| | | | |
(9 rows)
/*+
Leading((t1 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: statement: /*+
Leading((t1 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: 2024-05-15 21:45:35:415071 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Index Scan using i2 on t2
Index Cond: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(14 rows)
/*+
Leading((t1 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: statement: /*+
Leading((t1 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: 2024-05-15 21:45:35:431535 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t3)
not used hint:
]",
a | b | a | b | a | b
----+----+---+---+----+----
50 | 50 | | | 53 | 53
50 | 50 | | | |
50 | 50 | | | 50 | 50
51 | 51 | | | 53 | 53
51 | 51 | | | |
51 | 51 | | | 50 | 50
| | | | 53 | 53
| | | | |
| | | | 50 | 50
(9 rows)
/*+
Leading((t1 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: statement: /*+
Leading((t1 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: 2024-05-15 21:45:35:449862 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t2)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Index Scan using i2 on t2
Index Cond: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(14 rows)
/*+
Leading((t1 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: statement: /*+
Leading((t1 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON t2.a IS NULL;
LOG: 2024-05-15 21:45:35:465381 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t2)
not used hint:
]",
a | b | a | b | a | b
----+----+---+---+----+----
51 | 51 | | | 53 | 53
51 | 51 | | | |
51 | 51 | | | 50 | 50
| | | | 53 | 53
| | | | |
| | | | 50 | 50
50 | 50 | | | 53 | 53
50 | 50 | | | |
50 | 50 | | | 50 | 50
(9 rows)
/*+
Leading((t2 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: statement: /*+
Leading((t2 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: 2024-05-15 21:45:35:483797 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Index Scan using i2 on t2
Index Cond: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(14 rows)
/*+
Leading((t2 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: statement: /*+
Leading((t2 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: 2024-05-15 21:45:35:500272 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t2 t3)
not used hint:
]",
a | b | a | b | a | b
----+----+---+---+----+----
51 | 51 | | | 53 | 53
51 | 51 | | | |
51 | 51 | | | 50 | 50
| | | | 53 | 53
| | | | |
| | | | 50 | 50
50 | 50 | | | 53 | 53
50 | 50 | | | |
50 | 50 | | | 50 | 50
(9 rows)
/*+
Leading((t1 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: statement: /*+
Leading((t1 t3))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: 2024-05-15 21:45:35:518671 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t3)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Index Scan using i2 on t2
Index Cond: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(14 rows)
/*+
Leading((t1 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: statement: /*+
Leading((t1 t3))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: 2024-05-15 21:45:35:535162 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t3)
not used hint:
]",
a | b | a | b | a | b
----+----+---+---+----+----
51 | 51 | | | 53 | 53
51 | 51 | | | |
51 | 51 | | | 50 | 50
| | | | 53 | 53
| | | | |
| | | | 50 | 50
50 | 50 | | | 53 | 53
50 | 50 | | | |
50 | 50 | | | 50 | 50
(9 rows)
/*+
Leading((t1 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: statement: /*+
Leading((t1 t2))
*/
EXPLAIN (costs off)
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: 2024-05-15 21:45:35:553204 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t2)
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Index Scan using i2 on t2
Index Cond: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
Optimizer: GPORCA
(14 rows)
/*+
Leading((t1 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: statement: /*+
Leading((t1 t2))
*/
SELECT * FROM t1 LEFT JOIN t2 ON t2.a>53 LEFT JOIN t3 ON NOT t2.a IS NOT NULL;
LOG: 2024-05-15 21:45:35:568917 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(t1 t2)
not used hint:
]",
a | b | a | b | a | b
----+----+---+---+----+----
51 | 51 | | | 50 | 50
51 | 51 | | | 53 | 53
51 | 51 | | | |
| | | | 50 | 50
| | | | 53 | 53
| | | | |
50 | 50 | | | 50 | 50
50 | 50 | | | 53 | 53
50 | 50 | | | |
(9 rows)
-- Test LOJ with subquery
--
-- Test various combinations
--
-- 1. LOJ outside subquery
--
/*+
Leading(((t5 t4) (t3 (t1 t2))))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1, t2 ORDER BY 1 LIMIT 42) AS q LEFT JOIN t3 ON t3.a=q.a, t4, t5;
LOG: statement: /*+
Leading(((t5 t4) (t3 (t1 t2))))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1, t2 ORDER BY 1 LIMIT 42) AS q LEFT JOIN t3 ON t3.a=q.a, t4, t5;
LOG: 2024-05-15 21:45:35:597976 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t5 t4) (t3 (t1 t2)))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice7; segments: 3)
-> Seq Scan on t5
-> Seq Scan on t4
-> Materialize
-> Redistribute Motion 1:3 (slice2)
-> Hash Right Join
Hash Cond: (t3.a = t1.a)
-> Gather Motion 3:1 (slice3; segments: 3)
-> Seq Scan on t3
-> Hash
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
Merge Key: t1.a
-> Sort
Sort Key: t1.a
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
Optimizer: GPORCA
(28 rows)
/*+
Leading((((t1 t2) t3) (t4 t5)))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1, t2 ORDER BY 1 LIMIT 42) AS q LEFT JOIN t3 ON t3.a=q.a, t4, t5;
LOG: statement: /*+
Leading((((t1 t2) t3) (t4 t5)))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1, t2 ORDER BY 1 LIMIT 42) AS q LEFT JOIN t3 ON t3.a=q.a, t4, t5;
LOG: 2024-05-15 21:45:35:627385 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(((t1 t2) t3) (t4 t5))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop Left Join
Join Filter: true
-> Redistribute Motion 1:3 (slice4)
Hash Key: t1.a
-> Limit
-> Gather Motion 3:1 (slice5; segments: 3)
Merge Key: t1.a
-> Sort
Sort Key: t1.a
-> Nested Loop
Join Filter: true
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t2
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
-> Seq Scan on t5
Optimizer: GPORCA
(28 rows)
--
-- 2. LOJ inside subquery
--
/*+
Leading(((t5 t4) (t3 (t1 t2))))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY 1 LIMIT 42) AS q, t3, t4, t5;
LOG: statement: /*+
Leading(((t5 t4) (t3 (t1 t2))))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY 1 LIMIT 42) AS q, t3, t4, t5;
LOG: 2024-05-15 21:45:35:668288 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t5 t4) (t3 (t1 t2)))
not used hint:
]",
QUERY PLAN
--------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t5
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Redistribute Motion 1:3 (slice3)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
Merge Key: t1.a
-> Sort
Sort Key: t1.a
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: GPORCA
(27 rows)
/*+
Leading((((t2 t1) t3) (t4 t5)))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY 1 LIMIT 42) AS q, t3, t4, t5;
LOG: statement: /*+
Leading((((t2 t1) t3) (t4 t5)))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY 1 LIMIT 42) AS q, t3, t4, t5;
LOG: 2024-05-15 21:45:35:693081 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(((t2 t1) t3) (t4 t5))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------
Nested Loop
Join Filter: true
-> Nested Loop
Join Filter: true
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
Merge Key: t1.a
-> Sort
Sort Key: t1.a
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
-> Materialize
-> Gather Motion 3:1 (slice3; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t4
-> Seq Scan on t5
Optimizer: GPORCA
(25 rows)
--
-- 2. LOJ inside/outside subquery
--
/*+
Leading(((t5 t4) (t3 (t1 t2))))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY 1 LIMIT 42) AS q LEFT JOIN t3 ON q.a=t3.a, t4, t5;
LOG: statement: /*+
Leading(((t5 t4) (t3 (t1 t2))))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY 1 LIMIT 42) AS q LEFT JOIN t3 ON q.a=t3.a, t4, t5;
LOG: 2024-05-15 21:45:35:726322 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:((t5 t4) (t3 (t1 t2)))
not used hint:
]",
QUERY PLAN
--------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t5
-> Seq Scan on t4
-> Materialize
-> Redistribute Motion 1:3 (slice2)
-> Hash Right Join
Hash Cond: (t3.a = t1.a)
-> Gather Motion 3:1 (slice3; segments: 3)
-> Seq Scan on t3
-> Hash
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
Merge Key: t1.a
-> Sort
Sort Key: t1.a
-> Nested Loop Left Join
Join Filter: true
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: GPORCA
(27 rows)
/*+
Leading((((t2 t1) t3) (t4 t5)))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY 1 LIMIT 42) AS q LEFT JOIN t3 ON q.a=t3.a, t4, t5;
LOG: statement: /*+
Leading((((t2 t1) t3) (t4 t5)))
*/
EXPLAIN (costs off)
SELECT * FROM (SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY 1 LIMIT 42) AS q LEFT JOIN t3 ON q.a=t3.a, t4, t5;
LOG: 2024-05-15 21:45:35:755206 UTC,THD000,TRACE,"PlanHint: [
used hint:
JoinHint:(((t2 t1) t3) (t4 t5))
not used hint:
]",
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: true
-> Nested Loop Left Join
Join Filter: true
-> Redistribute Motion 1:3 (slice4)
Hash Key: t1.a
-> Limit
-> Gather Motion 3:1 (slice5; segments: 3)
Merge Key: t1.a
-> Sort
Sort Key: t1.a
-> Hash Right Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Nested Loop
Join Filter: true
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
-> Seq Scan on t5
Optimizer: GPORCA
(27 rows)
RESET client_min_messages;
LOG: statement: RESET client_min_messages;
RESET pg_hint_plan.debug_print;