GaussDB优化器在分布式框架下制定语句的执行策略时，有三种执行计划方式：生成下推语句计划、生成分布式执行计划、生成发送语句的分布式执行计划。

不支持下推的语法

以如下三个表定义说明不支持下推的SQL语法。

gaussdb=# CREATE TABLE CUSTOMER1 
( 
    C_CUSTKEY     BIGINT NOT NULL 
  , C_NAME        VARCHAR(25) NOT NULL 
  , C_ADDRESS     VARCHAR(40) NOT NULL 
  , C_NATIONKEY   INT NOT NULL 
  , C_PHONE       CHAR(15) NOT NULL 
  , C_ACCTBAL     DECIMAL(15,2)   NOT NULL 
  , C_MKTSEGMENT  CHAR(10) NOT NULL 
  , C_COMMENT     VARCHAR(117) NOT NULL 
) 
DISTRIBUTE BY hash(C_CUSTKEY); 
gaussdb=# CREATE TABLE test_stream(a int,b float); --float不支持重分布 
gaussdb=# CREATE TABLE sal_emp ( c1 integer[] ) DISTRIBUTE BY replication;

• 不支持returning语句下推

  gaussdb=# explain update customer1 set C_NAME = 'a' returning c_name; QUERY PLAN ------------------------------------------------------------------ Update on customer1 (cost=0.00..0.00 rows=30 width=187) Node/s: All datanodes Node expr: c_custkey -> Data Node Scan on customer1 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=187) Node/s: All datanodes (5 rows)

• 不支持聚集函数中使用order by语句的下推

  gaussdb=# explain verbose select count ( c_custkey order by c_custkey) from customer1; QUERY PLAN ------------------------------------------------------------------ Aggregate (cost=2.50..2.51 rows=1 width=8) Output: count(customer1.c_custkey ORDER BY customer1.c_custkey) -> Data Node Scan on customer1 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=8) Output: customer1.c_custkey Node/s: All datanodes Remote query: SELECT c_custkey FROM ONLY public.customer1 WHERE true (6 rows)

• count(distinct expr)中的字段不支持重分布，则不支持下推

  gaussdb=# explain verbose select count(distinct b) from test_stream; QUERY PLAN ------------------------------------------------------------------ Aggregate (cost=2.50..2.51 rows=1 width=8) Output: count(DISTINCT test_stream.b) -> Data Node Scan on test_stream "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=8) Output: test_stream.b Node/s: All datanodes Remote query: SELECT b FROM ONLY public.test_stream WHERE true (6 rows)

• 不支持distinct on用法下推

  gaussdb=# explain verbose select distinct on (c_custkey) c_custkey from customer1 order by c_custkey; QUERY PLAN ------------------------------------------------------------------ Unique (cost=49.83..54.83 rows=30 width=8) Output: customer1.c_custkey -> Sort (cost=49.83..52.33 rows=30 width=8) Output: customer1.c_custkey Sort Key: customer1.c_custkey -> Data Node Scan on customer1 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=8) Output: customer1.c_custkey Node/s: All datanodes Remote query: SELECT c_custkey FROM ONLY public.customer1 WHERE true (9 rows)

• 不支持数组表达式下推

  gaussdb=# explain verbose select array[c_custkey,1] from customer1 order by c_custkey; QUERY PLAN ------------------------------------------------------------------ Sort (cost=49.83..52.33 rows=30 width=8) Output: (ARRAY[customer1.c_custkey, 1::bigint]), customer1.c_custkey Sort Key: customer1.c_custkey -> Data Node Scan on "__REMOTE_SORT_QUERY__" (cost=0.00..0.00 rows=30 width=8) Output: (ARRAY[customer1.c_custkey, 1::bigint]), customer1.c_custkey Node/s: All datanodes Remote query: SELECT ARRAY[c_custkey, 1::bigint], c_custkey FROM ONLY public.customer1 WHERE true ORDER BY 2 (7 rows)