PG统计信息不正确的一个案例
Tags： PG
👉 本文共约1462个字，系统预计阅读时间或需6分钟。
统计信息不对
在手工进行分析之后，为什么这些数据还是 0 呢
原来这个表是基于 TimescaleDB 的一个超表，分区在 TimescaleDB 中被称之为 chunk ，TimescaleDB 会自动调整 chunk 的大小，且按时间、空间自动分片。
因此这个问题现象和分区表是类似的，看个栗子 👇🏻
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | CREATE TABLE ptab01 ( id int not null, tm timestamptz primary key not null ) PARTITION BY RANGE (tm); create table ptab01_202201 partition of ptab01 for values from ('2022-01-01') to ('2022-02-01'); create table ptab01_202202 partition of ptab01 for values from ('2022-02-01') to ('2022-03-01'); create table ptab01_202203 partition of ptab01 for values from ('2022-03-01') to ('2022-04-01'); create table ptab01_202204 partition of ptab01 for values from ('2022-04-01') to ('2022-05-01'); create table ptab01_202205 partition of ptab01 for values from ('2022-05-01') to ('2022-06-01'); create table ptab01_202206 partition of ptab01 for values from ('2022-06-01') to ('2022-07-01'); insert into ptab01 select extract(epoch from seq), seq from generate_series('2022-01-01'::timestamptz, '2022-06-30 23:59:59'::timestamptz, interval '10 seconds') as seq; postgres=# select n_tup_ins,n_live_tup,last_analyze,last_autoanalyze from pg_stat_user_tables where relname = 'ptab01_202201'; n_tup_ins | n_live_tup | last_analyze | last_autoanalyze -----------+------------+-------------------------------+------------------------------- 267840 | 267840 | 2022-12-15 15:16:19.605814+08 | 2022-12-15 15:11:03.496317+08 (1 row) postgres=# select n_tup_ins,n_live_tup,last_analyze,last_autoanalyze from pg_stat_user_tables where relname = 'ptab01'; n_tup_ins | n_live_tup | last_analyze | last_autoanalyze -----------+------------+-------------------------------+------------------ 0 | 0 | 2022-12-15 15:16:19.549188+08 | (1 row) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
