lightdb22.1新特性-分布式下支持使用优化器提示

最新推荐文章于 2023-08-18 16:21:53 发布

紫无之紫

最新推荐文章于 2023-08-18 16:21:53 发布

阅读量452

点赞数

分类专栏：数据库文章标签：优化器提示 hint 分布式

本文链接：https://blog.csdn.net/qq_17713935/article/details/124099265

版权

数据库专栏收录该内容

53 篇文章 0 订阅

订阅专栏

LightDB22.1新特性-分布式模式下支持使用优化器提示

LightDB 从22.1开始，对于分布式模式下的分布式表，复制表支持使用优化器提示来控制执行计划。使用方式与单机模式一致。支持的优化器提示请参考官方文档。

示例如下

lightdb@postgres=# CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE
lightdb@postgres=# SELECT create_distributed_table('t1', 'id');
create_distributed_table 
--------------------------

(1 row)

lightdb@postgres=# explain select * from t1 where id=10;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Custom Scan (Canopy Adaptive)  (cost=0.00..0.00 rows=0 width=0)
   Task Count: 1
   Tasks Shown: All
   ->  Task
         Node: host=192.168.247.128 port=6432 dbname=postgres
         ->  Index Scan using t1_pkey_102032 on t1_102032 t1  (cost=0.15..2.17 rows=1 width=8)
               Index Cond: (id = 10)
(7 rows)

lightdb@postgres=# explain select /*+seqscan(t1)*/* from t1 where id=10;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Custom Scan (Canopy Adaptive)  (cost=0.00..0.00 rows=0 width=0)
   Task Count: 1
   Tasks Shown: All
   ->  Task
         Node: host=192.168.247.128 port=6432 dbname=postgres
         ->  Seq Scan on t1_102032 t1  (cost=0.00..38.25 rows=1 width=8)
               Filter: (id = 10)
(7 rows)

lightdb@postgres=#

查看hint使用情况

要查看发送到数据节点的SQL上使用了那些优化器提示，可以打开canopy.log_remote_commands 查看发送到数据节点的SQL.

lightdb@postgres=# set canopy.log_remote_commands=on;
SET
lightdb@postgres=# explain select /*+seqscan(t1)*/* from t1 where id=10;
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 6, '2022-04-11 11:14:25.912358+08');
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 1
NOTICE:  issuing SAVEPOINT canopy_explain_savepoint
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 1
NOTICE:  issuing /*+ SeqScan(t1) */EXPLAIN (ANALYZE FALSE, VERBOSE FALSE, COSTS TRUE, BUFFERS FALSE, WAL FALSE, TIMING FALSE, SUMMARY FALSE, FORMAT TEXT) SELECT id, val FROM public.t1_102032 t1 WHERE (id OPERATOR(pg_catalog.=) 10)
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 1
NOTICE:  issuing ROLLBACK TO SAVEPOINT canopy_explain_savepoint
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 1
NOTICE:  issuing COMMIT
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 1
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Custom Scan (Canopy Adaptive)  (cost=0.00..0.00 rows=0 width=0)
   Task Count: 1
   Tasks Shown: All
   ->  Task
         Node: host=192.168.247.128 port=6432 dbname=postgres
         ->  Seq Scan on t1_102032 t1  (cost=0.00..38.25 rows=1 width=8)
               Filter: (id = 10)
(7 rows)

lightdb@postgres=#

要查看SQL中的hint有没有起效，可以打开数据节点上的pg_hint_plan.debug_print, 并设置pg_hint_plan.message_level为notice:

lightdb@postgres=# explain select /*+seqscan(t1)*/* from t1 where id=10;
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 7, '2022-04-11 11:22:55.050998+08');
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  issuing SAVEPOINT canopy_explain_savepoint
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  issuing /*+ SeqScan(t1) */EXPLAIN (ANALYZE FALSE, VERBOSE FALSE, COSTS TRUE, BUFFERS FALSE, WAL FALSE, TIMING FALSE, SUMMARY FALSE, FORMAT TEXT) SELECT id, val FROM public.t1_102032 t1 WHERE (id OPERATOR(pg_catalog.=) 10)
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  pg_hint_plan:
used hint:
SeqScan(t1@lt#-1)
not used hint:
duplication hint:
error hint:
DETAIL:  from 192.168.247.128:6432
NOTICE:  issuing ROLLBACK TO SAVEPOINT canopy_explain_savepoint
DETAIL:  on server chuhx@192.168.247.128:6432 connectionId: 3
NOTICE:  issuing COMMIT
DETAIL:  on server chuhx@192.168.247.128:6432 connectionId: 3
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Custom Scan (Canopy Adaptive)  (cost=0.00..0.00 rows=0 width=0)
   Task Count: 1
   Tasks Shown: All
   ->  Task
         Node: host=192.168.247.128 port=6432 dbname=postgres
         ->  Seq Scan on t1_102032 t1  (cost=0.00..38.25 rows=1 width=8)
               Filter: (id = 10)
(7 rows)

lightdb@postgres=#

注意点

hint 失效

由于在分布式下，一条SQL可能被拆分，导致hint作用的对象被拆分到两条SQL中，hint就会失效。比如对于WITH语句和子查询，如果它们不能被pushed down且没有与外部SQL中的对象关联，那么它们就会被拆分出来独立执行。比如 no_merge, semijoin/antijoin 等作用于子查询的hints就会失效。

lightdb@postgres=# CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE
lightdb@postgres=# CREATE TABLE t2 (id int PRIMARY KEY, val int);
CREATE TABLE
lightdb@postgres=# SELECT create_distributed_table('t1', 'id');
create_distributed_table 
--------------------------

(1 row)

lightdb@postgres=# SELECT create_distributed_table('t2', 'id');
create_distributed_table 
--------------------------

(1 row)

lightdb@postgres=# 
lightdb@postgres=# explain select /*+indexscan(t2@qb) hashjoin(t1 t2@qb)*/* from t1, (select/*+qb_name(qb)*/ * from t2 where t2.id >11) x where t1.id=10;
INFO:  pg_hint_plan: disable hint after spliting sql , hint: "HashJoin" 
DETAIL:  it is invalid because of split SQL, defined at "hashjoin(t1 t2@qb)"
NOTICE:  pg_hint_plan:
used hint:
IndexScan(t2@qb)
not used hint:
duplication hint:
error hint:

NOTICE:  pg_hint_plan:
used hint:
IndexScan(t2@qb)
HashJoin(t1@lt#0 t2@qb)
not used hint:
duplication hint:
error hint:

NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 36, '2022-03-14 16:06:20.313852+08');
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  issuing SAVEPOINT citus_explain_savepoint
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  issuing /*+ IndexScan(t2) */EXPLAIN (ANALYZE FALSE, VERBOSE FALSE, COSTS TRUE, BUFFERS FALSE, WAL FALSE, TIMING FALSE, SUMMARY FALSE, FORMAT TEXT) SELECT id, val FROM public.t2_102136 t2 WHERE (id OPERATOR(pg_catalog.>) 11)
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  pg_hint_plan:
used hint:
IndexScan(t2@lt#-1)
not used hint:
duplication hint:
error hint:
DETAIL:  from 192.168.247.128:6432
NOTICE:  issuing ROLLBACK TO SAVEPOINT citus_explain_savepoint
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  issuing SAVEPOINT citus_explain_savepoint
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  issuing EXPLAIN (ANALYZE FALSE, VERBOSE FALSE, COSTS TRUE, BUFFERS FALSE, WAL FALSE, TIMING FALSE, SUMMARY FALSE, FORMAT TEXT) SELECT t1.id, t1.val, x.id, x.val FROM public.t1_102108 t1, (SELECT intermediate_result.id, intermediate_result.val FROM read_intermediate_result('11_1'::text, 'binary'::canopy_copy_format) intermediate_result(id integer, val integer)) x WHERE (t1.id OPERATOR(pg_catalog.=) 10)
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  issuing ROLLBACK TO SAVEPOINT citus_explain_savepoint
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE:  issuing COMMIT
DETAIL:  on server lightdb@192.168.247.128:6432 connectionId: 3
                                                    QUERY PLAN                   
                                    
-----------------------------------------------------------------------------------
------------------------------------
Custom Scan (Canopy Adaptive)  (cost=0.00..0.00 rows=0 width=0)
->  Distributed Subplan 11_1
        ->  Custom Scan (Canopy Adaptive)  (cost=0.00..0.00 rows=100000 width=8)
            Task Count: 32
            Tasks Shown: One of 32
            ->  Task
                    Node: host=192.168.247.128 port=6432 dbname=postgres
                    ->  Index Scan using t2_pkey_102136 on t2_102136 t2  (cost=0.1
5..24.33 rows=753 width=8)
                        Index Cond: (id > 11)
Task Count: 1
Tasks Shown: All
->  Task
        Node: host=192.168.247.128 port=6432 dbname=postgres
        ->  Nested Loop  (cost=0.16..22.17 rows=1000 width=16)
            ->  Index Scan using t1_pkey_102108 on t1_102108 t1  (cost=0.15..2.1
7 rows=1 width=8)
                    Index Cond: (id = 10)
            ->  Function Scan on read_intermediate_result intermediate_result  (
cost=0.00..10.00 rows=1000 width=8)
(17 rows)

lightdb@postgres=#