Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / joinhints.out
blob: 876be468fa7bc8afe361657973febabf5b679fed [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: pg_hint_plan:
used hint:
Leading((t1 (t2 t3)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading((t1 (t3 t2)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading(((t2 t3) t1))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading(((t3 t2) t1))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading(((t1 t3) t2))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading(((t3 t1) t2))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading((t2 (t1 t3)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading((t2 (t3 t1)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading(((t2 t1) t3))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading((t3 (t1 t2)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading((t3 (t2 t1)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
not used hint:
Leading((t2 (t1 t3)))
duplication hint:
error hint:
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
-> Limit
-> Seq Scan on t3
-> Materialize
-> Seq Scan on t1
Optimizer: Postgres-based planner
(14 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: pg_hint_plan:
used hint:
not used hint:
Leading(((t5 (t4 t3)) (t1 t2)))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice4; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice5; segments: 3)
-> Limit
-> Nested Loop
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Seq Scan on t1
-> Materialize
-> Seq Scan on t3
Optimizer: Postgres-based planner
(22 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: pg_hint_plan:
used hint:
Leading((t1 (t2 t5)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
-> Limit
-> Nested Loop
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Seq Scan on t4
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
not used hint:
Leading((((t1 t2) t3) t4))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t4
-> Materialize
-> HashAggregate
Group Key: t1.a, t2.a
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Seq Scan on t1
Optimizer: Postgres-based planner
(17 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: pg_hint_plan:
used hint:
not used hint:
Leading(((((g2_t1 g2_t2) g2_t3) t4) (g1_t1 (g1_t3 g1_t2))))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> HashAggregate
Group Key: g2_t1.a, g2_t2.a
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2 g2_t2
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t3 g2_t3
-> Materialize
-> Seq Scan on t1 g2_t1
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> HashAggregate
Group Key: g1_t1.a, g1_t2.a
-> Nested Loop
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t2 g1_t2
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice7; segments: 3)
-> Seq Scan on t3 g1_t3
-> Materialize
-> Seq Scan on t1 g1_t1
-> Materialize
-> Nested Loop
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice8; segments: 3)
-> Seq Scan on t5
Optimizer: Postgres-based planner
(35 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: pg_hint_plan:
used hint:
Leading(((t3 t2) t1))
Leading((t5 t4))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(19 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: pg_hint_plan:
used hint:
not used hint:
Leading((t1 t3))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice4; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice5; segments: 3)
-> Limit
-> Nested Loop
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Seq Scan on t1
-> Materialize
-> Seq Scan on t3
Optimizer: Postgres-based planner
(22 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: pg_hint_plan:
used hint:
not used hint:
Leading((t2 t3))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> HashAggregate
Group Key: t1.a, t2.a
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Leading(((t1 t2)))
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Leading((t1 (t2 t3 t4)))
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(15 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: pg_hint_plan:
used hint:
not used hint:
Leading(((t1 t5) (t2 t4)))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
-> Limit
-> Nested Loop
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Nested Loop
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Seq Scan on t1
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
not used hint:
Leading(((t1 t3) (t2 t4)))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
-> Limit
-> Nested Loop
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Nested Loop
-> Seq Scan on t2
-> Materialize
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Seq Scan on t1
Optimizer: Postgres-based planner
(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;
INFO: pg_hint_plan: hint syntax error at or near "Leading((t1 t2))
Leading((t2 t1))
"
DETAIL: Conflict leading hint.
LOG: pg_hint_plan:
used hint:
Leading((t2 t1))
not used hint:
duplication hint:
Leading((t1 t2))
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((((t5 t4) (t1 t3)) t2))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Nested Loop
-> Nested Loop
-> Seq Scan on t3
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Seq Scan on t1
Optimizer: Postgres-based planner
(19 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: pg_hint_plan:
used hint:
Leading(((t5 t4) (t1 t3)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Nested Loop
-> Nested Loop
-> Seq Scan on t3
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Seq Scan on t1
Optimizer: Postgres-based planner
(19 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: pg_hint_plan:
used hint:
Leading((t2 t1))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Sort
Sort Key: t1.a
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Seq Scan on t1
-> Materialize
-> Merge Join
Merge Cond: (t3.a = t2.a)
-> Sort
Sort Key: t3.a
-> Seq Scan on t3
-> Materialize
-> Sort
Sort Key: t2.a
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Seq Scan on t2
Optimizer: Postgres-based planner
(25 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: pg_hint_plan:
used hint:
Leading((t5 (((t4 t3) t2) t1)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Sort
Sort Key: t1.a
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Seq Scan on t1
-> Materialize
-> Merge Join
Merge Cond: (t3.a = t2.a)
-> Sort
Sort Key: t3.a
-> Seq Scan on t3
-> Materialize
-> Sort
Sort Key: t2.a
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Seq Scan on t2
Optimizer: Postgres-based planner
(25 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: pg_hint_plan:
used hint:
Leading((t5 (((t4 t3) t2) t1)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Sort
Sort Key: t1.a
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t1.a
-> Merge Join
Merge Cond: (((t1.a + 1)) = t3.a)
-> Sort
Sort Key: ((t1.a + 1))
-> Result
-> Redistribute Motion 3:3 (slice3; segments: 3)
Hash Key: (t1.a + 1)
-> Seq Scan on t1
Filter: ((a + 1) > 42)
-> Sort
Sort Key: t3.a
-> Seq Scan on t3
Filter: (a > 42)
-> Materialize
-> Sort
Sort Key: t2.a
-> Nested Loop
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Nested Loop
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Seq Scan on t2
Optimizer: Postgres-based planner
(33 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: pg_hint_plan:
used hint:
Leading((t3 t4))
Leading((t1 (t2 t5)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
-> Limit
-> Nested Loop
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Seq Scan on t4
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((t3 t4))
Leading(t1 t2 t5)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
-> Limit
-> Nested Loop
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Seq Scan on t4
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((t3 t4))
Leading((t1 (t2 t5)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Nested Loop
-> Limit
-> Gather Motion 3:1 (slice1; segments: 3)
-> Limit
-> Nested Loop
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Limit
-> Nested Loop
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(t1 t2 t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading(t3 t2 t1)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t3
-> Materialize
-> Nested Loop
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
HashJoin(t1 t2)
not used hint:
duplication hint:
error 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: Postgres-based planner
(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: pg_hint_plan:
used hint:
NoHashJoin(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
-> Materialize
-> Index Scan using i2 on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
SeqScan(t1)
SeqScan(t2)
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: (t1.a = t2.a)
-> Seq Scan on t1
-> Materialize
-> Seq Scan on t2
Optimizer: Postgres-based planner
(7 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: pg_hint_plan:
used hint:
IndexScan(t1)
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
Optimizer: Postgres-based planner
(6 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: pg_hint_plan:
used hint:
IndexScan(t2)
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: Postgres-based planner
(6 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: pg_hint_plan:
used hint:
NoNestLoop(t1 t2)
not used hint:
duplication hint:
error 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: Postgres-based planner
(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: pg_hint_plan:
used hint:
NoHashJoin(t1 t2 t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Join
Merge Cond: (t3.a = t1.a)
-> Index Scan using i3 on t3
-> Materialize
-> Sort
Sort Key: t1.a
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
NoHashJoin(t1 t2)
NoHashJoin(t1 t3)
NoHashJoin(t2 t3)
NoHashJoin(t1 t2 t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Join
Merge Cond: (t3.a = t1.a)
-> Index Scan using i3 on t3
-> Materialize
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i2 on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
NoNestLoop(t1 t2 t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t3.a)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
NoNestLoop(t1 t2)
NoNestLoop(t1 t3)
NoNestLoop(t2 t3)
NoNestLoop(t1 t2 t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t3.a)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
-> Seq Scan on t1
-> Index Scan using i2 on t2
Index Cond: (a = t1.a)
Optimizer: Postgres-based planner
(6 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: pg_hint_plan:
used hint:
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
Optimizer: Postgres-based planner
(6 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: 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: pg_hint_plan:
used hint:
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> HashAggregate
Group Key: t2.a
-> Seq Scan on t2
-> Index Scan using i1 on t1
Index Cond: (a = t2.a)
Optimizer: Postgres-based planner
(8 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: pg_hint_plan:
used hint:
NestLoop(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 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: 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: 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: 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 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)
-- 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: 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: 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: pg_hint_plan:
used hint:
HashJoin(t1 t2)
not used hint:
duplication hint:
error 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: Postgres-based planner
(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: pg_hint_plan:
used hint:
HashJoin(t1 t2)
not used hint:
duplication hint:
error 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: Postgres-based planner
(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: pg_hint_plan:
used hint:
HashJoin(t1 t2)
not used hint:
duplication hint:
error 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: Postgres-based planner
(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: pg_hint_plan:
used hint:
HashJoin(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> HashAggregate
Group Key: t2.a
-> Seq Scan on t2
Optimizer: Postgres-based planner
(9 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: pg_hint_plan:
used hint:
HashJoin(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Anti Semi (Not-In) Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres query optimizer
(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: pg_hint_plan:
used hint:
not used hint:
HashJoin(y z)
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Anti Semi (Not-In) Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Optimizer: Postgres query optimizer
(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)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> HashAggregate
Group Key: t2.a
-> Seq Scan on t2
Optimizer: Postgres-based planner
(9 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: pg_hint_plan:
used hint:
Leading((t1 (t2 t3)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Hash Left Join
Hash Cond: (t2.a = t3.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
Filter: (a > 40)
Optimizer: Postgres-based planner
(12 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: pg_hint_plan:
used hint:
Leading(((t1 t4) (t2 t3)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Hash Left Join
Hash Cond: (t1.a = t4.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t4
-> Hash
-> Hash Left Join
Hash Cond: (t2.a = t3.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(((t3 t2) t1))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash 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: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(((t3 t2) t1))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
-> Materialize
-> Hash Right Join
Hash Cond: (t3.a = t2.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading(((t3 t2) t1))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t1
-> Materialize
-> Hash Left Join
Hash Cond: (t3.a = t2.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading((t2 t1))
not used hint:
duplication hint:
error 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: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(((t1 t4) (t2 t3)))
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
-> Merge Left Join
Merge Cond: (t2.a = t4.a)
-> Sort
Sort Key: t2.a
-> Hash Left Join
Hash Cond: (t2.a = t3.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
-> Sort
Sort Key: t4.a
-> Seq Scan on t4
Optimizer: Postgres-based planner
(18 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: pg_hint_plan:
used hint:
Leading((t1 (t2 t3)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Right Join
Merge Cond: (t3.a = t1.a)
-> Index Scan using i3 on t3
-> Materialize
-> 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
(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: pg_hint_plan:
used hint:
Leading((t1 (t2 t3)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Left Join
Merge Cond: (t3.a = t1.a)
-> Index Scan using i3 on t3
-> Materialize
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i2 on t2
Optimizer: Postgres-based planner
(11 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
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t1.a = t3.a)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading((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
-> Merge Join
Merge Cond: (t2.a = t3.a)
-> Index Scan using i2 on t2
-> Materialize
-> Index Scan using i3 on t3
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((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
-> Merge Join
Merge Cond: (t2.a = t3.a)
-> Index Scan using i2 on t2
-> Materialize
-> Index Scan using i3 on t3
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((t1 (t2 t3)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Right Join
Merge Cond: (t3.a = t1.a)
-> Index Scan using i3 on t3
-> Materialize
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i2 on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((t1 (t2 t3)))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t1 (t3 t2)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Right Join
Merge Cond: (t3.a = t1.a)
-> Index Scan using i3 on t3
-> Materialize
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i2 on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((t1 (t3 t2)))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading(((t2 t3) t1))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Right Join
Merge Cond: (t3.a = t1.a)
-> Index Scan using i3 on t3
-> Materialize
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i2 on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(((t2 t3) t1))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading(((t3 t2) t1))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Right Join
Merge Cond: (t3.a = t1.a)
-> Index Scan using i3 on t3
-> Materialize
-> Merge Join
Merge Cond: (t1.a = t2.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i2 on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(((t3 t2) t1))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading(((t1 t3) t2))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Left Join
Hash Cond: (t1.a = t3.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(((t1 t3) t2))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading(((t3 t1) t2))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Right Join
Hash Cond: (t3.a = t1.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(((t3 t1) t2))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t2 (t1 t3)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Hash Left Join
Hash Cond: (t1.a = t3.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t3
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((t2 (t1 t3)))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t2 (t3 t1)))
not used hint:
duplication hint:
error 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: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((t2 (t3 t1)))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
Hash Cond: (t1.a = t3.a)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading(((t2 t1) t3))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Left Join
Hash Cond: (t1.a = t3.a)
-> Hash Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
-> Hash
-> Seq Scan on t3
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading(((t2 t1) t3))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t3 (t1 t2)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t3.a = t1.a)
-> Seq Scan on t3
-> Hash
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((t3 (t1 t2)))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t3 (t2 t1)))
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (t3.a = t1.a)
-> Seq Scan on t3
-> Hash
-> Hash Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
Leading((t3 (t2 t1)))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t1 t2))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Right Join
Merge Cond: (t3.a = t2.a)
-> Index Scan using i3 on t3
-> Materialize
-> Sort
Sort Key: t2.a
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t2.a
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
Optimizer: Postgres-based planner
(15 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: pg_hint_plan:
used hint:
Leading((t1 t2))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t2 t1))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Merge Right Join
Merge Cond: (t3.a = t2.a)
-> Index Scan using i3 on t3
-> Materialize
-> Sort
Sort Key: t2.a
-> 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
Optimizer: Postgres-based planner
(15 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: pg_hint_plan:
used hint:
Leading((t2 t1))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t2 t3))
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
-> Sort
Sort Key: t2.a
-> Hash Left Join
Hash Cond: (t2.a = t3.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
Leading((t2 t3))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t3 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
-> Sort
Sort Key: t2.a
-> Hash Right Join
Hash Cond: (t3.a = t2.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
Leading((t3 t2))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t1 t3))
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
-> Merge Left Join
Merge Cond: (t2.a = t3.a)
-> Index Scan using i2 on t2
-> Materialize
-> Index Scan using i3 on t3
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading((t1 t3))
not used hint:
duplication hint:
error hint:
a | b | a | b | a | b
----+----+----+----+----+----
51 | 51 | | | |
| | | | |
50 | 50 | 50 | 50 | 50 | 50
(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: pg_hint_plan:
used hint:
Leading((t3 t1))
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
-> Merge Left Join
Merge Cond: (t2.a = t3.a)
-> Index Scan using i2 on t2
-> Materialize
-> Index Scan using i3 on t3
Optimizer: Postgres-based planner
(11 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: pg_hint_plan:
used hint:
Leading((t3 t1))
not used hint:
duplication hint:
error hint:
a | b | a | b | a | b
----+----+----+----+----+----
51 | 51 | | | |
| | | | |
50 | 50 | 50 | 50 | 50 | 50
(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: pg_hint_plan:
used hint:
Leading((t2 t3))
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)
-> Merge Left Join
Merge Cond: (t1.a = t5.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i5 on t5
-> Materialize
-> Sort
Sort Key: t2.a
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t2.a
-> Merge Right Join
Merge Cond: (t4.a = t3.a)
-> Index Scan using i4 on t4
-> Materialize
-> Sort
Sort Key: t3.a
-> Redistribute Motion 3:3 (slice3; segments: 3)
Hash Key: t3.a
-> Hash Left Join
Hash Cond: (t2.a = t3.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
Optimizer: Postgres-based planner
(27 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: pg_hint_plan:
used hint:
Leading((t2 t3))
not used hint:
duplication hint:
error 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)
/*+
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: pg_hint_plan:
used hint:
Leading((t3 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)
-> Merge Left Join
Merge Cond: (t1.a = t5.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i5 on t5
-> Materialize
-> Sort
Sort Key: t2.a
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t2.a
-> Merge Right Join
Merge Cond: (t4.a = t3.a)
-> Index Scan using i4 on t4
-> Materialize
-> Sort
Sort Key: t3.a
-> 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: Postgres-based planner
(27 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: pg_hint_plan:
used hint:
Leading((t3 t2))
not used hint:
duplication hint:
error 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)
/*+
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: pg_hint_plan:
used hint:
Leading((t4 (t3 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)
-> Merge Left Join
Merge Cond: (t1.a = t5.a)
-> Index Scan using i1 on t1
-> Materialize
-> Index Scan using i5 on t5
-> Materialize
-> Sort
Sort Key: t2.a
-> Hash Right Join
Hash Cond: (t4.a = t3.a)
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t4
-> Hash
-> Hash Right Join
Hash Cond: (t3.a = t2.a)
-> Seq Scan on t3
-> Hash
-> Seq Scan on t2
Optimizer: Postgres-based planner
(22 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: pg_hint_plan:
used hint:
Leading((t4 (t3 t2)))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t2 t3))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Filter: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
Leading((t2 t3))
not used hint:
duplication hint:
error 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 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: pg_hint_plan:
used hint:
Leading((t1 t3))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Filter: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
Leading((t1 t3))
not used hint:
duplication hint:
error 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)
/*+
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: pg_hint_plan:
used hint:
Leading((t1 t2))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Filter: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
Leading((t1 t2))
not used hint:
duplication hint:
error 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)
/*+
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: pg_hint_plan:
used hint:
Leading((t2 t3))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Filter: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
Leading((t2 t3))
not used hint:
duplication hint:
error 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: pg_hint_plan:
used hint:
Leading((t1 t3))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Filter: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
Leading((t1 t3))
not used hint:
duplication hint:
error 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 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: pg_hint_plan:
used hint:
Leading((t1 t2))
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
-> Seq Scan on t1
-> Materialize
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t2
Filter: (a > 53)
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t3
Optimizer: Postgres-based planner
(13 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: pg_hint_plan:
used hint:
Leading((t1 t2))
not used hint:
duplication hint:
error 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)
-- 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: pg_hint_plan:
used hint:
not used hint:
Leading(((t5 t4) (t3 (t1 t2))))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t1.a
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
Merge Key: t1.a
-> Limit
-> Nested Loop
-> Index Only Scan using i1 on t1
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Nested Loop
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t5
-> Memoize
Cache Key: t1.a
Cache Mode: logical
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: Postgres query optimizer
(27 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: pg_hint_plan:
used hint:
not used hint:
Leading((((t1 t2) t3) (t4 t5)))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t1.a
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
Merge Key: t1.a
-> Limit
-> Nested Loop
-> Index Only Scan using i1 on t1
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t2
-> Materialize
-> Nested Loop
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice6; segments: 3)
-> Seq Scan on t5
-> Memoize
Cache Key: t1.a
Cache Mode: logical
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: Postgres query optimizer
(27 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: pg_hint_plan:
used hint:
not used hint:
Leading(((t5 t4) (t3 (t1 t2))))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice4; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice5; segments: 3)
Merge Key: t1.a
-> Limit
-> Sort
Sort Key: t1.a
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
-> Materialize
-> Seq Scan on t3
Optimizer: Postgres-based planner
(25 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: pg_hint_plan:
used hint:
not used hint:
Leading((((t2 t1) t3) (t4 t5)))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on t5
-> Materialize
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on t4
-> Materialize
-> Nested Loop
-> Broadcast Motion 1:3 (slice4; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice5; segments: 3)
Merge Key: t1.a
-> Limit
-> Sort
Sort Key: t1.a
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
-> Materialize
-> Seq Scan on t3
Optimizer: Postgres-based planner
(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: pg_hint_plan:
used hint:
not used hint:
Leading(((t5 t4) (t3 (t1 t2))))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t1.a
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
Merge Key: t1.a
-> Limit
-> Sort
Sort Key: t1.a
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
-> Materialize
-> Nested Loop
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Memoize
Cache Key: t1.a
Cache Mode: logical
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: Postgres query optimizer
(29 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: pg_hint_plan:
used hint:
not used hint:
Leading((((t2 t1) t3) (t4 t5)))
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop Left Join
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: t1.a
-> Nested Loop
-> Broadcast Motion 1:3 (slice3; segments: 1)
-> Limit
-> Gather Motion 3:1 (slice4; segments: 3)
Merge Key: t1.a
-> Limit
-> Sort
Sort Key: t1.a
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
-> Materialize
-> Nested Loop
-> Seq Scan on t4
-> Materialize
-> Broadcast Motion 3:3 (slice5; segments: 3)
-> Seq Scan on t5
-> Memoize
Cache Key: t1.a
Cache Mode: logical
-> Index Scan using i3 on t3
Index Cond: (a = t1.a)
Optimizer: Postgres query optimizer
(29 rows)
RESET client_min_messages;
LOG: statement: RESET client_min_messages;
RESET pg_hint_plan.debug_print;