AnalyticDB for PostgreSQL allows you to use hints to modify execution plans. For example, you can use hints to enable or disable the ORCA optimizer for a query statement or to modify the JOIN order and type.
Prerequisites
An AnalyticDB for PostgreSQL instance of V6.3.7.0 or later is created. For more information about how to view and update the minor version of an instance, see Update the minor engine version.
The pg_hint_plan extension is installed. For more information, see Use the hint feature.
Enable or disable the ORCA optimizer for a query statement
Enable hints to modify execution plans and enable the hint registration feature.
SET pg_hint_plan.enable_hint to on; SET pg_hint_plan.enable_hint_table to on;Create tables t1 and t2.
CREATE TABLE t1 (id int PRIMARY KEY, val int); CREATE TABLE t2 (id int PRIMARY KEY, val int);Insert data to tables t1 and t2.
INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t; INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;Create indexes for tables t1 and t2.
CREATE INDEX t1_val ON t1 (val); CREATE INDEX t2_val ON t2 (val);Disable the ORCA optimizer.
SELECT * FROM hint_plan.insert_hint_table($$/*+ SET(optimizer off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.id = 1;$$);The following information is returned:
-[ RECORD 1 ]---------+----------------------------------------------------- id | 1 norm_query_string | explain select * from t1 join t2 on t1.val = t2.val; application_name | hints | set(optimizer off) query_hash | -2169095602568752481 enable | t prepare_param_strings | {}Display an execution plan.
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.id = 3;The Postgres query optimizer is used in the execution plan. The following information is returned:
QUERY PLAN ------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice2; segments: 3) (cost=8.25..27.61 rows=11 width=16) -> Hash Join (cost=8.25..27.61 rows=4 width=16) Hash Cond: (t2.val = t1.val) -> Seq Scan on t2 (cost=0.00..13.00 rows=334 width=8) -> Hash (cost=8.22..8.22 rows=1 width=8) -> Broadcast Motion 1:3 (slice1; segments: 1) (cost=0.16..8.22 rows=3 width=8) -> Index Scan using t1_pkey on t1 (cost=0.16..8.18 rows=1 width=8) Index Cond: (id = 3) Optimizer: Postgres query optimizer (9 rows)Enable the ORCA optimizer.
SELECT * FROM hint_plan.insert_hint_table($$/*+ SET(optimizer on) SET(rds_optimizer_options 0) */SELECT * FROM t1 WHERE t1.id = 1;$$);The following information is returned:
-[ RECORD 1 ]---------+------------------------------------------------ id | 2 norm_query_string | select * from t1 where t1.id = $1; application_name | hints | set(optimizer on) set(rds_optimizer_options 0) query_hash | -8281826471521807124 enable | t prepare_param_strings | {}Display an execution plan.
EXPLAIN SELECT * FROM t1 WHERE t1.id = 2;The ORCA optimizer is used in the execution plan. The following information is returned:
QUERY PLAN ---------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=8) -> Index Scan using t1_pkey on t1 (cost=0.00..6.00 rows=1 width=8) Index Cond: (id = 1) Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0 (4 rows)
Modify the JOIN type and JOIN order
Enable hints to modify execution plans and enable the hint registration feature.
SET optimizer to off; SET pg_hint_plan.enable_hint to on; SET pg_hint_plan.enable_hint_table to on;Create tables t1 and t2.
CREATE TABLE t1 (id int PRIMARY KEY, val int); CREATE TABLE t2 (id int PRIMARY KEY, val int);Insert data to tables t1 and t2.
INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t; INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;Create indexes for tables t1 and t2.
CREATE INDEX t1_val ON t1 (val); CREATE INDEX t2_val ON t2 (val);Display an execution plan.
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;The default execution plan is displayed, with the JOIN type being hash join and the JOIN order being
(t1 t2). The following information is returned:QUERY PLAN ---------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=25.50..208.00 rows=800 width=16) -> Hash Join (cost=25.50..208.00 rows=267 width=16) Hash Cond: (t1.id = t2.id) -> Seq Scan on t1 (cost=0.00..137.00 rows=3334 width=8) Filter: (val < 100) -> Hash (cost=15.50..15.50 rows=267 width=8) -> Seq Scan on t2 (cost=0.00..15.50 rows=267 width=8) Filter: (val > 1) Optimizer: Postgres query optimizerUse one of the following methods to modify the JOIN type and JOIN order:
Add hints to the beginning of the execution plan statement.
/*+ MergeJoin(t1 t2) Leading((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;The JOIN type changes from hash join to merge join and the JOIN order changes from (t1 t2) to (t2 t1). The following information is returned:
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.31..93.54 rows=800 width=16) -> Merge Join (cost=0.31..93.54 rows=267 width=16) Merge Cond: (t2.id = t1.id) -> Index Scan using t2_pkey on t2 (cost=0.15..52.90 rows=267 width=8) Filter: (val > 1) -> Index Scan using t1_pkey on t1 (cost=0.16..279.91 rows=3334 width=8) Filter: (val < 100) Optimizer: Postgres query optimizer (8 rows)Register hints and display an execution plan.
Register hints with an SQL pattern.
SELECT * FROM hint_plan.insert_hint_table($$/*+ MergeJoin(t1 t2) Leading((t2 t1)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);The following information is returned:
-[ RECORD 1 ]---------+-------------------------------------------------------------------------- id | 1 norm_query_string | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; application_name | hints | MergeJoin(t1 t2) Leading((t2 t1)) query_hash | -4733464863014584191 enable | t prepare_param_strings | {}Display an execution plan.
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;The hints take effect. The JOIN type changes from hash join to merge join and the JOIN order changes from (t1 t2) to (t2 t1). The following information is returned:
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.31..93.54 rows=800 width=16) -> Merge Join (cost=0.31..93.54 rows=267 width=16) Merge Cond: (t2.id = t1.id) -> Index Scan using t2_pkey on t2 (cost=0.15..52.90 rows=267 width=8) Filter: (val > 1) -> Index Scan using t1_pkey on t1 (cost=0.16..279.91 rows=3334 width=8) Filter: (val < 100) Optimizer: Postgres query optimizer (8 rows)Display the execution plan of a query statement that uses the same SQL pattern.
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 80 and t2.val > 20;The following information is returned. The hints take effect and modify the execution plan of the query statement that uses the same SQL pattern.
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=8.25..38.24 rows=13 width=16) -> Merge Join (cost=8.25..38.24 rows=5 width=16) Merge Cond: (t1.id = t2.id) -> Index Scan using t1_pkey on t1 (cost=0.16..279.91 rows=2667 width=8) Filter: (val < 80) -> Sort (cost=8.09..8.09 rows=1 width=8) Sort Key: t2.id -> Index Scan using t2_val on t2 (cost=0.15..8.08 rows=1 width=8) Index Cond: (val > 20) Optimizer: Postgres query optimizer (10 rows)(Optional) Remove hints from the SQL pattern.
SELECT * FROM hint_plan.delete_all_hint_table($$SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);
Register hints and display an execution plan when a fixed constant exits in the query statement.
Use a PREPARE statement to register hints with an SQL pattern.
SELECT * FROM hint_plan.insert_hint_table($$/*+ MergeJoin(t1 t2) Leading((t2 t1)) */PREPARE a AS SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > $1;$$);The following information is returned:
-[ RECORD 1 ]---------+-------------------------------------------------------------------------- id | 1 norm_query_string | PREPARE a AS SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; application_name | hints | MergeJoin(t1 t2) Leading((t2 t1)) query_hash | -4733464863014584191 enable | t prepare_param_strings | {100,$1}Display the execution plan of a query statement in which a parameterized constant is changed.
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 2;After the parameterized constant is changed, the hints still take effect. The JOIN type changes from hash join to merge join and the JOIN order changes from (t1 t2) to (t2 t1). The following information is returned:
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.31..93.54 rows=800 width=16) -> Merge Join (cost=0.31..93.54 rows=267 width=16) Merge Cond: (t1.id = t2.id) -> Index Scan using t1_pkey on t1 (cost=0.16..279.91 rows=3334 width=8) Filter: (val < 100) -> Index Scan using t2_pkey on t2 (cost=0.15..52.90 rows=267 width=8) Filter: (val > 2) Optimizer: Postgres query optimizer (8 rows)Display the execution plan of a query statement in which a fixed constant is changed.
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 80 and t2.val > 20;After the fixed constant is changed, the hints do not take effect. The following information is returned:
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=8.09..175.22 rows=13 width=16) -> Hash Join (cost=8.09..175.22 rows=5 width=16) Hash Cond: (t1.id = t2.id) -> Seq Scan on t1 (cost=0.00..137.00 rows=2667 width=8) Filter: (val < 80) -> Hash (cost=8.08..8.08 rows=1 width=8) -> Index Scan using t2_val on t2 (cost=0.15..8.08 rows=1 width=8) Index Cond: (val > 20) Optimizer: Postgres query optimizer (9 rows)(Optional) Remove hints from the SQL pattern.
SELECT * FROM hint_plan.delete_all_hint_table($$SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);