AntDB 数据库分布式功能-辅助索引表2

用例

创建主表

CREATE TABLE tb(id int, name text, age int) DIESTRIBUTE BY hash(id);

在 name 上创建辅助索引

CREATE auxiliary TABLE ON tb(name);
\d+ tb

antdb=# \d+ tb
                                    Table "public.tb"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              |
 name   | text    |           |          |         | extended |              |
 age    | integer |           |          |         | plain    |              |
Auxiliary table:
    "tb_name_aux" on tb(name)
DISTRIBUTE BY HASH(id) TO NODE(dn1, dn2, dn3)
Access method: heap

在主表 tb 上插入一些数据

INSERT INTO tb SELECT n,'name'||n,random()*100 FROM generate_series(1,10) AS n;
SELECT *,adb_node_oid() AS node FROM tb;

  id |  name  | age | node
----+--------+-----+-------
  2 | name2  |  87 | 16388
  6 | name6  |  76 | 16388
  7 | name7  |  77 | 16388
  1 | name1  |  70 | 16389
  4 | name4  |  29 | 16389
  8 | name8  |  16 | 16389
  9 | name9  |  95 | 16389
 10 | name10 |  66 | 16389
  3 | name3  | 100 | 16387
  5 | name5  |  12 | 16387
(10 rows)

非分片字段等值执行计划

--查询结果
SELECT * FROM tb WHERE name='name2';
 id | name  | age
----+-------+-----
  2 | name2 |  87
(1 row)

--执行计划：name为非分片字段,最终执行计划只在16388这个节点上执行
EXPLAIN (verbose,analyze,costs off,timing off) SELECT * FROM tb WHERE name='name2';
                      QUERY PLAN
-------------------------------------------------------
 Cluster Gather (actual rows=1 loops=1)
   Remote node: 16388
   ->  Tid Scan on public.tb (actual rows=0 loops=1)
         Output: id, name, age
         TID Cond: (tb.ctid = '(0,1)'::tid)
         Filter: (tb.name = 'name2'::text)
         Remote node: 16388
         Node 16388: (actual rows=1 loops=1)
 Planning Time: 0.728 ms
 Execution Time: 1.664 ms
(10 rows)

--关闭使用辅助表功能后的执行计划
SET use_aux_type =off;
EXPLAIN (verbose,analyze,costs off,timing off) SELECT * FROM tb WHERE name='name2';
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__" (actual rows=1 loops=1)
   Output: tb.id, tb.name, tb.age
   Primary node/s: dn1
   Node/s: dn1, dn2, dn3
   Remote query: SELECT id, name, age FROM public.tb tb WHERE (name = 'name2'::text)
 Planning Time: 0.083 ms
 Execution Time: 9.138 ms
(7 rows)

--对in表达式同样支持,两条记录在同一个节点上
SET use_aux_type =on;
EXPLAIN (verbose,analyze,costs off,timing off) SELECT * FROM tb WHERE name IN('name2','name7');
                            QUERY PLAN
------------------------------------------------------------------
 Cluster Gather (actual rows=2 loops=1)
   Remote node: 16388
   ->  Tid Scan on public.tb (actual rows=0 loops=1)
         Output: id, name, age
         TID Cond: (tb.ctid = ANY ('{"(0,1)","(0,3)"}'::tid[]))
         Filter: (tb.name = ANY ('{name2,name7}'::text[]))
         Remote node: 16388
         Node 16388: (actual rows=2 loops=1)
 Planning Time: 11.996 ms
 Execution Time: 1.516 ms
(10 rows)

--两条记录在两个不同节点上
EXPLAIN (verbose,analyze,costs off,timing off) SELECT * FROM tb WHERE name IN('name2','name5');
                            QUERY PLAN
------------------------------------------------------------------
 Cluster Gather (actual rows=2 loops=1)
   Remote node: 16387,16388
   ->  Tid Scan on public.tb (actual rows=0 loops=1)
         Output: id, name, age
         TID Cond: (tb.ctid = ANY ('{"(0,1)","(0,2)"}'::tid[]))
         Filter: (tb.name = ANY ('{name2,name5}'::text[]))
         Remote node: 16387,16388
         Node 16387: (actual rows=1 loops=1)
         Node 16388: (actual rows=1 loops=1)
 Planning Time: 1.465 ms
 Execution Time: 2.651 ms
(11 rows)

语法

创建辅助索引表的语法为：

CREATE AUXILIARY TABLE [auxiliary_table_name]
ON master_table_name ( column_name [ index_options ])
[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY OptDistributeType ]
[ TO NODE (node_name [, ...] ) | TO GROUP pgxcgroup_name ]

index_options:
/* empty */
| INDEX [ CONCURRENTLY ] [ name ] [ USING method ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]

说明：
AUXILIARY 为辅助表关键字
auxiliary_table_name 为辅助表表名，可选，若不指定则按照：主表名+辅助字段名+tbl 命名。
master_table_name 为主表表名，必选。
column_name 为主表字段名（需要建辅助表的字段），必选。
index_options 辅助表索引信息，指定时按照指定语法创建索引。未指定时，则按照默认参数创建索引。（辅助表的column_name字段必然创建索引。默认时，索引方法默认btree，命名空间与辅助表保持一致。）
tablespace_name 辅助表命名空间，可选。未指定时，为当前默认tablespace。
DISTRIBUTE BY OptDistributeType 辅助表分片方式，可选。未指定时，以辅助字段HASH分片。
TO NODE (node_name [, ...] ) | TO GROUP pgxcgroup_name 辅助表分片节点，可选。未指定时，默认ALL DATANODES。