| -- 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=0.00..1356765552.28 rows=1000000 width=8) |
| -> Nested Loop (cost=0.00..1356765522.47 rows=333334 width=8) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..431.01 rows=100 width=4) |
| -> Seq Scan on your_table t2 (cost=0.00..431.00 rows=34 width=4) |
| -> Nested Loop (cost=0.00..1324091.78 rows=3334 width=4) |
| Join Filter: true |
| -> Seq Scan on my_table t1 (cost=0.00..431.00 rows=34 width=4) |
| -> Materialize (cost=0.00..431.00 rows=100 width=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=100 width=1) |
| -> Dynamic Seq Scan on our_table t3 (cost=0.00..431.00 rows=34 width=1) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| (13 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: 2024-05-07 18:31:55:686209 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: t1,t2,t3 #123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=123 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=41 width=xxx) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=3334 width=xxx) |
| Join Filter: true |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Dynamic Seq Scan on our_table t3 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| \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: 2024-05-07 18:31:55:717215 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: t1,t2,t3 +123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=1000123 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=333375 width=xxx) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=3334 width=xxx) |
| Join Filter: true |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Dynamic Seq Scan on our_table t3 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| \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: 2024-05-07 18:31:55:738492 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: t1,t2,t3 -123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=999877 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=333293 width=xxx) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=3334 width=xxx) |
| Join Filter: true |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Dynamic Seq Scan on our_table t3 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| \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: 2024-05-07 18:31:55:758885 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: t1,t2,t3 *123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=123000000 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=41000000 width=xxx) |
| Join Filter: true |
| -> Nested Loop (cost=xxx..xxx rows=3334 width=xxx) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Dynamic Seq Scan on our_table t3 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| -------------------------------------------------------------------- |
| -- |
| -- 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: 2024-05-07 18:31:55:774182 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: my_table,your_table #123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=123 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=41 width=xxx) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on my_table (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on your_table (cost=xxx..xxx rows=34 width=xxx) |
| Optimizer: GPORCA |
| |
| -------------------------------------------------------------------- |
| -- |
| -- 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: 2024-05-07 18:31:55:788695 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: my_table,your_table #123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=123 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=41 width=xxx) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on my_table (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on your_table (cost=xxx..xxx rows=34 width=xxx) |
| Optimizer: GPORCA |
| |
| -------------------------------------------------------------------- |
| -- 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: 2024-05-07 18:31:55:803571 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: t1,t2 #123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=12300 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=4100 width=xxx) |
| Join Filter: true |
| -> Nested Loop (cost=xxx..xxx rows=41 width=xxx) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Dynamic Seq Scan on our_table t3 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| \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: 2024-05-07 18:31:55:817837 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: t1,t2 *123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=123000000 width=xxx) |
| -> Nested Loop (cost=xxx..xxx rows=41000000 width=xxx) |
| Join Filter: true |
| -> Nested Loop (cost=xxx..xxx rows=410000 width=xxx) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Dynamic Seq Scan on our_table t3 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| -------------------------------------------------------------------- |
| -- 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: 2024-05-07 18:31:55:837804 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: t1,t2 #123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=123 width=xxx) |
| -> Hash Semi Join (cost=xxx..xxx rows=41 width=xxx) |
| Hash Cond: (t1.a = t2.a) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Hash (cost=xxx..xxx rows=34 width=xxx) |
| -> Dynamic Seq Scan on our_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| \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: 2024-05-07 18:31:55:854077 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: t1,t2 #123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=123 width=xxx) |
| -> Hash Left Anti Semi (Not-In) Join (cost=xxx..xxx rows=41 width=xxx) |
| Hash Cond: (t1.a = t2.a) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Hash (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Dynamic Seq Scan on our_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| --------------------------------------------------------------------------------------------- |
| -- 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: 2024-05-07 18:31:55:873694 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| RowHint: t1,t2 #123.000000 |
| not used hint: |
| ]", |
| \o |
| \! sql/maskout.sh results/pg_hint_plan.tmpout |
| QUERY PLAN |
| ---------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=xxx..xxx rows=123 width=xxx) |
| -> Hash Left Anti Semi (Not-In) Join (cost=xxx..xxx rows=41 width=xxx) |
| Hash Cond: (t1.a = t2.a) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Hash (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Dynamic Seq Scan on our_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| 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: 2024-05-07 18:31:55:959134 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| not used hint: |
| RowHint: y,z #123.000000 |
| ]", |
| \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=333334 width=xxx) |
| Join Filter: true |
| -> Nested Loop (cost=xxx..xxx rows=3334 width=xxx) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Seq Scan on my_table t1 (cost=xxx..xxx rows=34 width=xxx) |
| -> Seq Scan on your_table t2 (cost=xxx..xxx rows=34 width=xxx) |
| -> Materialize (cost=xxx..xxx rows=100 width=xxx) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=xxx..xxx rows=100 width=xxx) |
| -> Dynamic Seq Scan on our_table t3 (cost=xxx..xxx rows=34 width=xxx) |
| Number of partitions to scan: 6 (out of 6) |
| Optimizer: GPORCA |
| |
| RESET client_min_messages; |
| LOG: statement: RESET client_min_messages; |
| RESET pg_hint_plan.debug_print; |
| -- Clean Up |
| DROP TABLE foo; |
| DROP TABLE bar; |
