Vertica数据库的DIRECTED QUERY

原创 simonchiang 2024-08-29

200

总结

DIRECTED QUERY可以固化SQL的执行计划，命中DIRECTED QUERY时，其优先级最高，其他所有的优化措施均会失效。
1.在语句中添加空格、换行不会影响命中DIRECTED QUERY；
2.调整定值谓词条件不影响命中DIRECTED QUERY；
3.调整字段、谓词条件顺序，增加、减少字段、谓词条件都会影响命中DIRECTED QUERY；
4.非定值过滤条件不能使DIRECTED QUERY生效；

1. 原始SQL及其执行计划


create table public.directquerytest1 as select * from nodes;
create table public.directquerytest2 as select * from nodes;

explain select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'STANDBY' and b.node_name is not null;

 EnableForceOuter is on
 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 50, Rows: 136] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
 |  Join Cond: (a.node_name = b.node_name)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for a [Cost: 35, Rows: 136] (PATH ID: 2)
 | |      Projection: public.directquerytest1_b0
 | |      Materialize: a.node_state, a.node_name
 | |      Filter: (a.node_name IS NOT NULL)
 | |      Filter: (a.node_state = 'STANDBY')
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for b [Cost: 14, Rows: 137] (PATH ID: 3)
 | |      Projection: public.directquerytest2_b0
 | |      Materialize: b.node_name
 | |      Filter: (b.node_name IS NOT NULL)
 | |      Execute on: All Nodes

2. 创建DIRECTED QUERY

CREATE DIRECTED QUERY OPTIMIZER 'directquerytest1'
select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'STANDBY' and b.node_name is not null;

ACTIVATE DIRECTED QUERY directquerytest1;

SELECT input_query, annotated_query FROM V_CATALOG.DIRECTED_QUERIES WHERE query_name = 'directquerytest1';
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
input_query     | SELECT a.node_name, a.node_state FROM (public.directquerytest1 a LEFT  JOIN public.directquerytest2 b ON ((a.node_name = b.node_name))) WHERE ((a.node_state = 'STANDBY'::varchar(7) /*+:v(1)*/) AND (b.node_name IS NOT NULL))
annotated_query | SELECT /*+syntactic_join,verbatim*/ a.node_name AS node_name, a.node_state AS node_state FROM (public.directquerytest1 AS a/*+projs('public.directquerytest1')*/ JOIN /*+Distrib(R,R),JType(M)*/ public.directquerytest2 AS b/*+projs('public.directquerytest2')*/  ON (a.node_name = b.node_name)) WHERE (a.node_name IS NOT NULL) AND (a.node_state = 'STANDBY'::varchar(7) /*+:v(1)*/) AND (b.node_name IS NOT NULL)

3. 验证DIRECTED QUERY有效性

3.1 修改谓词条件的值

GET DIRECTED QUERY select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'UP' and b.node_name is not null;
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query_name      | directquerytest1
is_active       | t
vertica_version | Vertica Analytic Database v11.1.1-20
comment         | Optimizer-generated directed query
creation_date   | 2024-01-11 14:55:16.988907
annotated_query | SELECT /*+syntactic_join,verbatim*/ a.node_name AS node_name, a.node_state AS node_state FROM (public.directquerytest1 AS a/*+projs('public.directquerytest1')*/ JOIN /*+Distrib(R,R),JType(M)*/ public.directquerytest2 AS b/*+projs('public.directquerytest2')*/  ON (a.node_name = b.node_name)) WHERE (a.node_name IS NOT NULL) AND (a.node_state = 'STANDBY'::varchar(7) /*+:v(1)*/) AND (b.node_name IS NOT NULL)

 Optimizer Directives
 ----------------------
 NoRewrite = true
 SyntacticOptimizer = true
 
 
 explain select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'UP' and b.node_name is not null;
 
 The following active directed query(query name: directquerytest1) is being executed:
 SELECT /*+syntactic_join,verbatim*/  a.node_name, a.node_state FROM (public.directquerytest1 a/*+projs('public.directquerytest1')*/ JOIN /*+Distrib('R', 'R'), JType('M')*/public.directquerytest2 b/*+projs('public.directquerytest2')*/ ON ((a.node_name = b.
node_name))) WHERE ((a.node_name IS NOT NULL) AND (a.node_state = 'UP'::varchar(2)) AND (b.node_name IS NOT NULL))
 
 
 EnableForceOuter is on
 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 50, Rows: 134] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
 |  Join Cond: (a.node_name = b.node_name)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for a [Cost: 35, Rows: 134] (PATH ID: 2)
 | |      Projection: public.directquerytest1_b0
 | |      Materialize: a.node_state, a.node_name
 | |      Filter: (a.node_name IS NOT NULL)
 | |      Filter: (a.node_state = 'UP')
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for b [Cost: 14, Rows: 137] (PATH ID: 3)
 | |      Projection: public.directquerytest2_b0
 | |      Materialize: b.node_name
 | |      Filter: (b.node_name IS NOT NULL)
 | |      Execute on: All Nodes

可以命中DIRECTED QUERY。

explain select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'UP' and b.node_name ilike '%node%';

 EnableForceOuter is on
 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 50, Rows: 134] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
 |  Join Cond: (a.node_name = b.node_name)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for a [Cost: 35, Rows: 134] (PATH ID: 2)
 | |      Projection: public.directquerytest1_b0
 | |      Materialize: a.node_state, a.node_name
 | |      Filter: (a.node_name ~~* '%node%')
 | |      Filter: (a.node_state = 'UP')
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for b [Cost: 14, Rows: 137] (PATH ID: 3)
 | |      Projection: public.directquerytest2_b0
 | |      Materialize: b.node_name
 | |      Filter: (b.node_name ~~* '%node%')
 | |      Execute on: All Nodes

未能命中DIRECTED QUERY。

3.2 调整表的force outer

alter table public.directquerytest1 force outer 1;

 explain select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'UP' and b.node_name is not null;

 The following active directed query(query name: directquerytest1) is being executed:
 SELECT /*+syntactic_join,verbatim*/  a.node_name, a.node_state FROM (public.directquerytest1 a/*+projs('public.directquerytest1')*/ JOIN /*+Distrib('R', 'R'), JType('M')*/public.directquerytest2 b/*+projs('public.directquerytest2')*/ ON ((a.node_name = b.
node_name))) WHERE ((a.node_name IS NOT NULL) AND (a.node_state = 'UP'::varchar(2)) AND (b.node_name IS NOT NULL))
 
 
 EnableForceOuter is on
 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 50, Rows: 134] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
 |  Join Cond: (a.node_name = b.node_name)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for a [Cost: 35, Rows: 134] (PATH ID: 2)
 | |      Projection: public.directquerytest1_b0
 | |      Force outer level: 1
 | |      Materialize: a.node_state, a.node_name
 | |      Filter: (a.node_name IS NOT NULL)
 | |      Filter: (a.node_state = 'UP')
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for b [Cost: 14, Rows: 137] (PATH ID: 3)
 | |      Projection: public.directquerytest2_b0
 | |      Materialize: b.node_name
 | |      Filter: (b.node_name IS NOT NULL)
 | |      Execute on: All Nodes

force outer设置成功，但因为存在DIRECTED QUERY，force outer设置未起作用，关联关系仍然按照DIRECTED QUERY执行。

3.3 减少谓词条件

 explain select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'UP' /*and b.node_name is not null*/;
 
 
 EnableForceOuter is on
 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [RightOuter] [Cost: 34, Rows: 135] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
 |  Join Cond: (a.node_name = b.node_name)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for b [Cost: 11, Rows: 137] (PATH ID: 2)
 | |      Projection: public.directquerytest2_b0
 | |      Materialize: b.node_name
 | |      Filter: (b.node_name IS NOT NULL)
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for a [Cost: 22, Rows: 135] (PATH ID: 3)
 | |      Projection: public.directquerytest1_b0
 | |      Force outer level: 1
 | |      Materialize: a.node_state, a.node_name
 | |      Filter: (a.node_state = 'UP')
 | |      Execute on: All Nodes

未能命中DIRECTED QUERY，且设置的Force outer对执行计划产生了影响。

3.4 调整字段、谓词条件顺序

--调整select字段顺序
explain select a.node_state, a.node_name from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'UP' and b.node_name is not null;

 EnableForceOuter is on
 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 50, Rows: 134] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
 |  Join Cond: (a.node_name = b.node_name)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for b [Cost: 14, Rows: 137] (PATH ID: 2)
 | |      Projection: public.directquerytest2_b0
 | |      Materialize: b.node_name
 | |      Filter: (b.node_name IS NOT NULL)
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for a [Cost: 35, Rows: 134] (PATH ID: 3)
 | |      Projection: public.directquerytest1_b0
 | |      Force outer level: 1
 | |      Materialize: a.node_state, a.node_name
 | |      Filter: (a.node_name IS NOT NULL)
 | |      Filter: (a.node_state = 'UP')
 | |      Execute on: All Nodes
未能命中DIRECTED QUERY，且设置的Force outer对执行计划产生了影响。

--调整谓词条件顺序
 explain select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where b.node_name is not null and a.node_state = 'UP' ;

 EnableForceOuter is on
 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 50, Rows: 134] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
 |  Join Cond: (a.node_name = b.node_name)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for a [Cost: 35, Rows: 134] (PATH ID: 2)
 | |      Projection: public.directquerytest1_b0
 | |      Materialize: a.node_state, a.node_name
 | |      Filter: (a.node_name IS NOT NULL)
 | |      Filter: (a.node_state = 'UP')
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for b [Cost: 14, Rows: 137] (PATH ID: 3)
 | |      Projection: public.directquerytest2_b0
 | |      Materialize: b.node_name
 | |      Filter: (b.node_name IS NOT NULL)
 | |      Execute on: All Nodes

未能命中DIRECTED QUERY。

3.5 增加空格、换行等

explain select a.node_name, 
a.node_state from public.directquerytest1 a left join public.directquerytest2 b 
on a.node_name=b.node_name where a.node_state = 'UP' and    b.node_name is not null;
 The following active directed query(query name: directquerytest1) is being executed:
 SELECT /*+syntactic_join,verbatim*/  a.node_name, a.node_state FROM (public.directquerytest1 a/*+projs('public.directquerytest1')*/ JOIN /*+Distrib('R', 'R'), JType('M')*/public.directquerytest2 b/*+projs('public.directquerytest2')*/ ON ((a.node_name = b.
node_name))) WHERE ((a.node_name IS NOT NULL) AND (a.node_state = 'UP'::varchar(2)) AND (b.node_name IS NOT NULL))
 
 
 EnableForceOuter is on
 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 50, Rows: 134] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
 |  Join Cond: (a.node_name = b.node_name)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for a [Cost: 35, Rows: 134] (PATH ID: 2)
 | |      Projection: public.directquerytest1_b0
 | |      Materialize: a.node_state, a.node_name
 | |      Filter: (a.node_name IS NOT NULL)
 | |      Filter: (a.node_state = 'UP')
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for b [Cost: 14, Rows: 137] (PATH ID: 3)
 | |      Projection: public.directquerytest2_b0
 | |      Materialize: b.node_name
 | |      Filter: (b.node_name IS NOT NULL)
 | |      Execute on: All Nodes
可以命中DIRECTED QUERY。

3.6 非定值过滤条件测试

CREATE DIRECTED QUERY OPTIMIZER 'directquerytest2' 
select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'STANDBY' and b.node_name is not null and a.last_msg_from_node_at > sysdate - 1;

ACTIVATE DIRECTED QUERY directquerytest2;

GET DIRECTED QUERY select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'STANDBY' and b.node_name is not null and a.last_msg_from_node_at > sysdate - 1;
(No rows)

 explain select a.node_name, a.node_state from public.directquerytest1 a left join public.directquerytest2 b on a.node_name=b.node_name where a.node_state = 'STANDBY' and b.node_name is not null and a.last_msg_from_node_at > sysdate - 1;
 
 
 EnableForceOuter is on
 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 71, Rows: 136] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
 |  Join Cond: (a.node_name = b.node_name)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for a [Cost: 56, Rows: 136] (PATH ID: 2)
 | |      Projection: public.directquerytest1_b0
 | |      Materialize: a.node_name, a.node_state
 | |      Filter: (a.node_name IS NOT NULL)
 | |      Filter: (a.node_state = 'STANDBY')
 | |      Filter: (a.last_msg_from_node_at > '2024-01-10 15:20:45.46701+08'::timestamptz)
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for b [Cost: 14, Rows: 137] (PATH ID: 3)
 | |      Projection: public.directquerytest2_b0
 | |      Materialize: b.node_name
 | |      Filter: (b.node_name IS NOT NULL)
 | |      Execute on: All Nodes

未能命中DIRECTED QUERY。