教你处理数仓慢SQL常见定位问题

select
    a.pid,
    a.sessionid,
    a.datname,
    a.usename,
    a.application_name,
    a.client_addr,
    a.xact_start,
    a.query_start,
    (now() - a.query_start)::text as query_runtime,
    a.unique_sql_id,
    w.wait_status,
    w.wait_event,
    w.locktag,
    w.lockmode,
    w.block_sessionid,
    a.query
from
    pg_stat_activity a join
    pg_thread_wait_status w on
    a.sessionid = w.sessionid
where
    a.pid <> pg_backend_pid()
    and a.state = 'active'
    and a.client_addr is not null
order by
    query_runtime desc;

SELECT
  *,
  finish_time - start_time as run_time
FROM
  dbe_perf.statement_history
WHERE
  start_time > '2022-07-08 18:00:00'
   AND start_time < '2022-07-08 19:00:00'
   -- 根据unique_query_id可以过滤出特定的查询
   -- AND unique_query_id = 123456
ORDER BY
  run_time desc;

SELECT
  *,
  total_elapse_time / n_calls as avg_elapse_time
FROM
  dbe_perf.statement
WHERE
  unique_query_id = 123456;

-- 抓取指定unique_sql_id的全量SQL信息
-- 示例：unique_sql_id为3267119089，全量SQL级别为L2，相当于track_stmt_stat_level='L2,off'
select * from dynamic_func_control('LOCAL', 'STMT', 'TRACK', '{"3267119089", "L2"}');
-- 打开之后，查询statement_history
-- 关闭抓取，清理
select * from dynamic_func_control('LOCAL', 'STMT', 'UNTRACK', '{"3267119089"}');
select * from dynamic_func_control('LOCAL', 'STMT', 'LIST', '{}');
select * from dynamic_func_control('LOCAL', 'STMT', 'CLEAN', '{}');

SELECT
*
FROM
dbe_perf.local_active_session
WHERE
query_start_time > '2022-07-08 18:00:00'
AND query_start_time < '2022-07-08 19:00:00'
AND unique_query ilike '%%';

select pg_terminate_session(pid,sessionid);

select * from pg_stats where tablename = '表名';
select * from pg_stats where tablename = '表名' and attname = '列名';

analyze tablename;

ALTER TABLE tablename ALTER COLUMN colname SET (n_distinct = 实际值);
analyze tablename; -- analyze执行后生效
​
-- 取消设置
ALTER TABLE tablename ALTER COLUMN colname RESET (n_distinct);
analyze tablename; -- analyze执行后生效