| -- Test Optimizer Row Hints Feature |
| -- |
| -- Purpose: Test that row hints may be used to edit cardinality estimates |
| -- Row hints have not implments in Postgres-based planner |
| LOAD 'pg_hint_plan'; |
| DROP SCHEMA IF EXISTS rowhints CASCADE; |
| CREATE SCHEMA rowhints; |
| SET search_path=rowhints; |
| SET optimizer_trace_fallback=on; |
| -- Setup tables |
| CREATE TABLE my_table(a int, b int); |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE INDEX my_awesome_index ON my_table(a); |
| CREATE TABLE your_table(a int, b int) WITH (appendonly=true); |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE INDEX your_awesome_index ON your_table(a); |
| CREATE TABLE our_table(a int, b int) PARTITION BY RANGE (a) (PARTITION p1 START(0) END(100) INCLUSIVE EVERY(20)); |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE INDEX our_awesome_index ON our_table(a); |
| INSERT INTO my_table SELECT i, i FROM generate_series(1, 100)i; |
| INSERT INTO your_table SELECT i, i FROM generate_series(1, 100)i; |
| INSERT INTO our_table SELECT i, i FROM generate_series(1, 100)i; |
| ANALYZE my_table, your_table, our_table; |
| -------------------------------------------------------------------- |
| -- Test the different row hint types: |
| -- |
| -- - Absolute |
| -- - Add |
| -- - Subtract |
| -- - Multiply |
| -------------------------------------------------------------------- |
| -- Baseline no hints |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=20000000000.00..20000017562.79 rows=1000000 width=8) |
| -> Nested Loop (cost=20000000000.00..20000004229.45 rows=333333 width=8) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..2.67 rows=100 width=4) |
| -> Seq Scan on your_table t2 (cost=0.00..1.33 rows=33 width=4) |
| -> Materialize (cost=10000000000.00..10000000068.45 rows=3333 width=4) |
| -> Nested Loop (cost=10000000000.00..10000000051.79 rows=3333 width=4) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..7.87 rows=102 width=0) |
| -> Append (cost=0.00..6.51 rows=34 width=0) |
| -> Seq Scan on our_table_1_prt_p1_1 t3_1 (cost=0.00..1.06 rows=6 width=0) |
| -> Seq Scan on our_table_1_prt_p1_2 t3_2 (cost=0.00..1.07 rows=7 width=0) |
| -> Seq Scan on our_table_1_prt_p1_3 t3_3 (cost=0.00..1.07 rows=7 width=0) |
| -> Seq Scan on our_table_1_prt_p1_4 t3_4 (cost=0.00..1.07 rows=7 width=0) |
| -> Seq Scan on our_table_1_prt_p1_5 t3_5 (cost=0.00..1.07 rows=7 width=0) |
| -> Seq Scan on our_table_1_prt_p1_6 t3_6 (cost=0.00..1.01 rows=1 width=0) |
| -> Materialize (cost=0.00..1.50 rows=33 width=4) |
| -> Seq Scan on my_table t1 (cost=0.00..1.33 rows=33 width=4) |
| Optimizer: Postgres query optimizer |
| (17 rows) |
| |
| 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 |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(t1 t2 t3 #123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: statement: /*+ |
| Rows(t1 t2 t3 #123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: pg_hint_plan: |
| used hint: |
| not used hint: |
| Rows(t1 t2 t3 #123) |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=1000000 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=333333 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=33 width=xxx) |
| -> Materialize (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Nested Loop (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=102 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t3_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t3_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t3_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t3_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t3_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t3_6 (cost=xxx..xxx rows=1 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=33 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(t1 t2 t3 +123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: statement: /*+ |
| Rows(t1 t2 t3 +123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: pg_hint_plan: |
| used hint: |
| Rows(t1 t2 t3 +123) |
| not used hint: |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=1000000 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=333333 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=33 width=xxx) |
| -> Materialize (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Nested Loop (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=102 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t3_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t3_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t3_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t3_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t3_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t3_6 (cost=xxx..xxx rows=1 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=33 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(t1 t2 t3 -123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: statement: /*+ |
| Rows(t1 t2 t3 -123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: pg_hint_plan: |
| used hint: |
| Rows(t1 t2 t3 -123) |
| not used hint: |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=1000000 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=333333 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=33 width=xxx) |
| -> Materialize (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Nested Loop (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=102 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t3_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t3_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t3_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t3_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t3_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t3_6 (cost=xxx..xxx rows=1 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=33 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(t1 t2 t3 *123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: statement: /*+ |
| Rows(t1 t2 t3 *123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: pg_hint_plan: |
| used hint: |
| Rows(t1 t2 t3 *123) |
| not used hint: |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=1000000 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=333333 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=33 width=xxx) |
| -> Materialize (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Nested Loop (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=102 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t3_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t3_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t3_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t3_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t3_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t3_6 (cost=xxx..xxx rows=1 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=33 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| -------------------------------------------------------------------- |
| -- |
| -- Subqueries |
| -- |
| -------------------------------------------------------------------- |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(my_table your_table #123) |
| */ |
| EXPLAIN SELECT * FROM my_table, (SELECT * FROM your_table) AS q; |
| LOG: statement: /*+ |
| Rows(my_table your_table #123) |
| */ |
| EXPLAIN SELECT * FROM my_table, (SELECT * FROM your_table) AS q; |
| LOG: pg_hint_plan: |
| used hint: |
| Rows(my_table your_table #123) |
| not used hint: |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost={inf}..{inf} rows=10000 width=xxx) |
| -> Nested Loop (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Seq Scan on my_table (cost=xxx..xxx rows=33 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table (cost=xxx..xxx rows=33 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| -------------------------------------------------------------------- |
| -- |
| -- CTE |
| -- |
| -------------------------------------------------------------------- |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(my_table your_table #123) |
| */ |
| EXPLAIN WITH cte AS (SELECT * FROM my_table, (SELECT * FROM your_table) as q) SELECT * FROM cte; |
| LOG: statement: /*+ |
| Rows(my_table your_table #123) |
| */ |
| EXPLAIN WITH cte AS (SELECT * FROM my_table, (SELECT * FROM your_table) as q) SELECT * FROM cte; |
| LOG: pg_hint_plan: |
| used hint: |
| Rows(my_table your_table #123) |
| not used hint: |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost={inf}..{inf} rows=10000 width=xxx) |
| -> Nested Loop (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Seq Scan on my_table (cost=xxx..xxx rows=33 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table (cost=xxx..xxx rows=33 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| -------------------------------------------------------------------- |
| -- Test updating lower join row hint |
| -------------------------------------------------------------------- |
| -- force join order to isolate lower join row hint |
| set optimizer_join_order=query; |
| LOG: statement: set optimizer_join_order=query; |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(t1 t2 #123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: statement: /*+ |
| Rows(t1 t2 #123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: pg_hint_plan: |
| used hint: |
| not used hint: |
| Rows(t1 t2 #123) |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=1000000 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=333333 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=33 width=xxx) |
| -> Materialize (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Nested Loop (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=102 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t3_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t3_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t3_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t3_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t3_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t3_6 (cost=xxx..xxx rows=1 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=33 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(t1 t2 *123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: statement: /*+ |
| Rows(t1 t2 *123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: pg_hint_plan: |
| used hint: |
| Rows(t1 t2 *123) |
| not used hint: |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=1000000 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=333333 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=33 width=xxx) |
| -> Materialize (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Nested Loop (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=102 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t3_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t3_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t3_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t3_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t3_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t3_6 (cost=xxx..xxx rows=1 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=33 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| -------------------------------------------------------------------- |
| -- Test Semi/AntiSemi Joins with RowHints |
| -------------------------------------------------------------------- |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(t1 t2 #123) |
| */ |
| EXPLAIN SELECT * FROM my_table AS t1 WHERE t1.a IN (SELECT t2.a FROM our_table t2); |
| LOG: statement: /*+ |
| Rows(t1 t2 #123) |
| */ |
| EXPLAIN SELECT * FROM my_table AS t1 WHERE t1.a IN (SELECT t2.a FROM our_table t2); |
| LOG: pg_hint_plan: |
| used hint: |
| not used hint: |
| Rows(t1 t2 #123) |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Hash Semi Join (cost=xxx..xxx rows=33 width=xxx) |
| Hash Cond: (t1.a = t2.a) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| -> Hash (cost=xxx..xxx rows=34 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t2_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t2_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t2_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t2_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t2_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t2_6 (cost=xxx..xxx rows=1 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(t1 t2 #123) |
| */ |
| EXPLAIN SELECT * FROM my_table AS t1 WHERE t1.a NOT IN (SELECT t2.a FROM our_table t2); |
| LOG: statement: /*+ |
| Rows(t1 t2 #123) |
| */ |
| EXPLAIN SELECT * FROM my_table AS t1 WHERE t1.a NOT IN (SELECT t2.a FROM our_table t2); |
| LOG: pg_hint_plan: |
| used hint: |
| not used hint: |
| Rows(t1 t2 #123) |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=7 width=xxx) |
| -> Hash Left Anti Semi (Not-In) Join (cost=xxx..xxx rows=2 width=xxx) |
| Hash Cond: (t1.a = t2.a) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| -> Hash (cost=xxx..xxx rows=102 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=102 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t2_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t2_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t2_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t2_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t2_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t2_6 (cost=xxx..xxx rows=1 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| --------------------------------------------------------------------------------------------- |
| -- Test case where we disable InnerJoin alternatives so that Stats for the join group are |
| -- derived from LeftSemi/LeftAntiSemiJoin operators instead of CLogicalJoin operator. |
| --------------------------------------------------------------------------------------------- |
| SELECT disable_xform('CXformLeftSemiJoin2InnerJoin'); |
| LOG: statement: SELECT disable_xform('CXformLeftSemiJoin2InnerJoin'); |
| disable_xform |
| ------------------------------------------ |
| CXformLeftSemiJoin2InnerJoin is disabled |
| (1 row) |
| |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(t1 t2 #123) |
| */ |
| EXPLAIN SELECT * FROM my_table AS t1 WHERE t1.a NOT IN (SELECT t2.a FROM our_table t2); |
| LOG: statement: /*+ |
| Rows(t1 t2 #123) |
| */ |
| EXPLAIN SELECT * FROM my_table AS t1 WHERE t1.a NOT IN (SELECT t2.a FROM our_table t2); |
| LOG: pg_hint_plan: |
| used hint: |
| not used hint: |
| Rows(t1 t2 #123) |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=7 width=xxx) |
| -> Hash Left Anti Semi (Not-In) Join (cost=xxx..xxx rows=2 width=xxx) |
| Hash Cond: (t1.a = t2.a) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| -> Hash (cost=xxx..xxx rows=102 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=102 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t2_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t2_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t2_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t2_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t2_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t2_6 (cost=xxx..xxx rows=1 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| SELECT enable_xform('CXformLeftSemiJoin2InnerJoin'); |
| LOG: statement: SELECT enable_xform('CXformLeftSemiJoin2InnerJoin'); |
| enable_xform |
| ----------------------------------------- |
| CXformLeftSemiJoin2InnerJoin is enabled |
| (1 row) |
| |
| -------------------------------------------------------------------- |
| -- Test Joins from project sub queries with RowHints |
| -------------------------------------------------------------------- |
| CREATE TABLE foo(a int, b int) DISTRIBUTED BY (a); |
| LOG: statement: CREATE TABLE foo(a int, b int) DISTRIBUTED BY (a); |
| CREATE TABLE bar(a int, b int) DISTRIBUTED BY (a); |
| LOG: statement: CREATE TABLE bar(a int, b int) DISTRIBUTED BY (a); |
| INSERT INTO bar SELECT i, i+3 FROM generate_series(1,5) i; |
| LOG: statement: INSERT INTO bar SELECT i, i+3 FROM generate_series(1,5) i; |
| -- Insert single row |
| INSERT INTO foo values (-2, 34); |
| LOG: statement: INSERT INTO foo values (-2, 34); |
| ANALYZE foo; |
| LOG: statement: ANALYZE foo; |
| ANALYZE bar; |
| LOG: statement: ANALYZE bar; |
| -- Nested Loop Left Join operator estimates 41 rows(per segment for 3 segment cluster) |
| -- honoring the specified RowHint. However, Gather Motion estimates total number of |
| -- rows as 5 because the outer table bar only has 5 rows and ComputeScalar is being smart |
| -- about it and estimates 5 rows. |
| -- flaky test |
| -- \o results/pg_hint_plan.tmpout |
| -- /*+ |
| -- Rows(f b #123) |
| -- */ |
| -- EXPLAIN SELECT (SELECT a FROM foo AS f) FROM bar AS b; |
| -- \o |
| -- \! sql/maskout.sh results/pg_hint_plan.tmpout |
| -- Missing alias in query to test Un-used Hint logging |
| \o results/pg_hint_plan.tmpout |
| /*+ |
| Rows(y z #123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: statement: /*+ |
| Rows(y z #123) |
| */ |
| EXPLAIN SELECT t1.a, t2.a FROM my_table AS t1, your_table AS t2, our_table AS t3; |
| LOG: pg_hint_plan: |
| used hint: |
| not used hint: |
| Rows(y z #123) |
| duplication hint: |
| error hint: |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=1000000 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=333333 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=33 width=xxx) |
| -> Materialize (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Nested Loop (cost={inf}..{inf} rows=3333 width=xxx) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=102 width=xxx) |
| -> Append (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_1 t3_1 (cost=xxx..xxx rows=6 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_2 t3_2 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_3 t3_3 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_4 t3_4 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_5 t3_5 (cost=xxx..xxx rows=7 width=xxx) |
| -> Seq Scan on our_table_1_prt_p1_6 t3_6 (cost=xxx..xxx rows=1 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=33 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=33 width=xxx) |
| Optimizer: Postgres query optimizer |
| |
| RESET client_min_messages; |
| LOG: statement: RESET client_min_messages; |
| RESET pg_hint_plan.debug_print; |
| -- Clean Up |
| DROP TABLE foo; |
| DROP TABLE bar; |
