查看并行的 worker 数
postgres=# explain select count(1) from tbase_1;
QUERY PLAN
---------------------------------------------------------------------------------------
Finalize Aggregate (cost=118.81..118.83 rows=1 width=8)
-> Remote Subquery Scan on all (dn001,dn002) (cost=118.80..118.81 rows=1 width=0)
-> Partial Aggregate (cost=18.80..18.81 rows=1 width=8)
-> Seq Scan on tbase_1 (cost=0.00..18.80 rows=880 width=0)
(4 rows)
postgres=# analyze tbase_1;
ANALYZE
postgres=# explain select count(1) from tbase_1;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Parallel Finalize Aggregate (cost=14728.45..14728.46 rows=1 width=8)
-> Parallel Remote Subquery Scan on all (dn001,dn002) (cost=14728.33..14728.45 rows=1 width=0)
-> Gather (cost=14628.33..14628.44 rows=1 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=13628.33..13628.34 rows=1 width=8)
-> Parallel Seq Scan on tbase_1 (cost=0.00..12586.67 rows=416667 width=0)
(6 rows)
上面第一个查询没走并行,第二个查询 analyze 后走并行才是正确的,建议大数据量更新再执行 analyze。
查看各节点的执行计划是否一致
./tbase_run_sql_dn_master.sh "explain select * from tbase_2 where f2=1"
dn006 --- psql -h 172.16.0.13 -p 11227 -d postgres -U tbase -c "explain select * from tbase_2 where f2=1"
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on tbase_2 (cost=2.18..7.70 rows=4 width=40)
Recheck Cond: (f2 = 1)
-> Bitmap Index Scan on tbase_2_f2_idx (cost=0.00..2.18 rows=4 width=0)
Index Cond: (f2 = 1)
(4 rows)
dn002 --- psql -h 172.16.0.42 -p 11012 -d postgres -U tbase -c "explain select * from tbase_2 where f2=1"
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tbase_2_f2_idx on tbase_2 (cost=0.42..4.44 rows=1 width=14)
Index Cond: (f2 = 1)
(2 rows)
两个 dn 的执行计划不一致,最大可能是数据倾斜或者是执行计划被禁用。
如有可能,DBA 可以配置在系统空闲时执行全库 analyze 和 vacuum。