标签
PostgreSQL , Greenplum , 统计信息 , 自动统计信息 , broadcast motion , 执行计划
背景
数据库执行计划的好坏，与数据库的SQL优化器息息相关。Greenplum有两套优化器，legacy query optimizer 与 ORCA。
这两个优化器都是CBO优化器，都需要依赖统计信息，如果统计信息不准确，可能生成的执行计划就不准确。
例如我们有一个这样的QUERY，发现怎么跑都跑不出来。
观察执行计划，发现有一个节点用到了broadcast motion，也就是广播。
-> Broadcast Motion 512:512 (slice1; segments: 512) (cost=0.00..6.13 rows=1 width=16)
-> Append-only Columnar Scan on xxxx (cost=0.00..1.00 rows=1 width=16)
当JOIN字段并非分布键时，Greenplum会根据表的大小，选择重分布或广播。（小表广播，大表的话多阶段JOIN）。
《HybridDB PostgreSQL "Sort、Group、distinct 聚合、JOIN" 不惧怕数据倾斜的黑科技和原理 - 多阶段聚合》
而这个执行计划跑偏的SQL，恰恰是在一个大表上触发了广播(broadcast motion)，这不正常。
select count(*) xxxx;
返回有31亿数据。
查询pg_class，值有1条记录，占用0个BLOCK
select * from pg_class where relname='xxxx';
relpages | 1
reltuples | 0
执行analyze，收集统计信息，执行计划恢复。
(Greenplum对于超级大表，收集统计信息时，会构建临时表来进行采样分析)
digoal=> analyze verbose xxxxxxxx;
INFO: Executing SQL: select sum(gp_statistics_estimate_reltuples_relpages_oid(c.oid))::float4[] from gp_dist_random('pg_class') c where c.oid=112293
INFO: ANALYZE estimated reltuples=3091824896.000000, relpages=47509120.000000 for table xxxxxxxx
INFO: ANALYZE building sample table of size 173762 on table xxxxxxxx because it has too many rows.
INFO: Executing SQL: create table pg_temp.pg_analyze_112293_59 as ( select Ta.xx,....Ta.xxx from public.xxxxxxxx as Ta where random() < 0.00005620053343591280281543731689453125 limit 173762 ) distributed randomly
INFO: Created sample table pg_temp.pg_analyze_112293_59 with nrows=173762
INFO: ANALYZE computing statistics on attribute xx
INFO: Executing SQL: select count(*)::float4 from pg_temp_440803.pg_analyze_112293_59 as Ta where Ta.xx is null
INFO: nullfrac = 0.178474
INFO: Executing SQL: select avg(pg_column_size(Ta.xx))::float4 from pg_temp_440803.pg_analyze_112293_59 as Ta where Ta.xx is not null
INFO: avgwidth = 21.418087
INFO: Executing SQL: select count(*)::float4 from (select Ta.xx from pg_temp_440803.pg_analyze_112293_59 as Ta group by Ta.xx) as Tb
INFO: count(ndistinct()) gives 142751.000000 values.
INFO: Executing SQL: select count(v)::float4 from (select Ta.xx as v, count(Ta.xx) as f from pg_temp_440803.pg_analyze_112293_59 as Ta group by Ta.xx) as foo where f > 1
INFO: ndistinct = -1.000000
..........
收集统计信息后，执行计划恢复，没有broadcast了，执行也秒级返回了。
让Greenplum自动收集统计信息
对于在函数内 或 函数外执行DML时，内核会跟踪表的记录数变更影响的数据量，我们可以设置什么时候收集统计信息：
none：不收集
on_no_stats：没有统计信息时，收集
on_change：当写入、更新量超过阈值（gp_autostats_on_change_threshold参数设置的行数，默认为20亿）后，自动收集统计信息。
Automatic Statistics Collection
Greenplum Database can be set to automatically run ANALYZE on a table that either has no statistics or has
changed significantly when certain operations are performed on the table. For partitioned tables, automatic
statistics collection is only triggered when the operation is run directly on a leaf table, and then only the leaf
table is analyzed.
Automatic statistics collection has three modes:
• none disables automatic statistics collection.
• on_no_stats triggers an analyze operation for a table with no existing statistics when any of the
commands CREATE TABLE AS SELECT, INSERT, or COPY are executed on the table.
• on_change triggers an analyze operation when any of the commands CREATE TABLE AS SELECT,
UPDATE, DELETE, INSERT, or COPY are executed on the table and the number of rows affected exceeds
the threshold defined by the gp_autostats_on_change_threshold configuration parameter.
The automatic statistics collection mode is set separately for commands that occur within a procedural
language function and commands that execute outside of a function:
• The gp_autostats_mode configuration parameter controls automatic statistics collection behavior
outside of functions and is set to on_no_stats by default.
• The gp_autostats_mode_in_functions parameter controls the behavior when table operations are
performed within a procedural language function and is set to none by default.
With the on_change mode, ANALYZE is triggered only if the number of rows affected exceeds the threshold
defined by the gp_autostats_on_change_threshold configuration parameter. The default value for this
parameter is a very high value, 2147483647, which effectively disables automatic statistics collection;
you must set the threshold to a lower number to enable it. The on_change mode could trigger large,
unexpected analyze operations that could disrupt the system, so it is not recommended to set it globally. It
could be useful in a session, for example to automatically analyze a table following a load.
To disable automatic statistics collection outside of functions, set the gp_autostats_mode parameter to
none:
gpconfigure -c gp_autostats_mode -v none
To enable automatic statistics collection in functions for tables that have no statistics, change
gp_autostats_mode_in_functions to on_no_stats:
gpconfigure -c gp_autostats_mode_in_functions -v on_no_stats
Set the log_autostats system configuration parameter to on if you want to log automatic statistics
collection operations.
为了让数据库产生准确的执行计划，建议要么用户自己调度analyZE收集统计信息，要么自动收集。
时间： 2024-07-29 05:18:39