分区表统计

发表于 2021-03-01 更新于 2023-01-25

使用postgresql能简单分区表，但是建立分区表后，如何统计各表的大小？可以简单的用psql工具连上，输入\dt+ mytable*。对于分区表，可以当作正常表来使用。

统计各个分区占用的物理磁盘空间

如果下面的方式查到的数据比较多，还可以换成更具体的名字+*：

\dt+ mytable*

统计各个分区表有多少记录

先创建一个表：

create table td_count_tb_scan (
  title varchar(256),
  cnt bigint ,
  dist_cnt bigint,
  create_time timestamp without time zone
) 
WITH (
  OIDS = FALSE
);

comment on table td_count_tb_scan is 'tb_scan总数统计';
comment on column td_count_tb_scan.title is '统计的日期或其他描述';
comment on column td_count_tb_scan.cnt is '总记录数';
comment on column td_count_tb_scan.dist_cnt is '根据号去重后的总数';
comment on column td_count_tb_scan.create_time is '执行日期';

方式一

往表里面添加数据：

INSERT INTO td_count_tb_scan (SELECT '20200803' title , count(*) cnt, count(distinct ship_id) dist_cnt ,now() create_time from tb_scan_1_prt_p20200803 );
INSERT INTO td_count_tb_scan (SELECT '20200804' title , count(*) cnt, count(distinct ship_id) dist_cnt ,now() create_time from tb_scan_1_prt_p20200804 );
INSERT INTO td_count_tb_scan (SELECT '20200805' title , count(*) cnt, count(distinct ship_id) dist_cnt ,now() create_time from tb_scan_1_prt_p20200805 );
INSERT INTO td_count_tb_scan (SELECT '20200806' title , count(*) cnt, count(distinct ship_id) dist_cnt ,now() create_time from tb_scan_1_prt_p20200806 );
INSERT INTO td_count_tb_scan (SELECT '20200807' title , count(*) cnt, count(distinct ship_id) dist_cnt ,now() create_time from tb_scan_1_prt_p20200807 );
INSERT INTO td_count_tb_scan (SELECT '20200808' title , count(*) cnt, count(distinct ship_id) dist_cnt ,now() create_time from tb_scan_1_prt_p20200808 );

上面的select 包裹的括号可以省略。

方式二

使用联表查询，然后整体一次插入。（整个语句必须全部运行结束，才能出结果）

INSERT INTO td_count_tb_scan (
SELECT '20200701' title , count(*) cnt, count(distinct ship_id) dist_cnt ,'2021-03-01 13:15:00'::timestamp without time zone create_time from tb_scan_1_prt_p20200701 union
SELECT '20200702' title , count(*) cnt, count(distinct ship_id) dist_cnt ,'2021-03-01 13:15:00'::timestamp without time zone create_time from tb_scan_1_prt_p20200702 );

上面的sql执行后，非常的耗费性能。

备注

上面的方式，直接使用了分区表的名称，进行查询的分区剪切，其实，用分区键：时间也是可以的。

效率是一致的。

SELECT '20200702' title , count(*) cnt, count(distinct ship_id) dist_cnt ,'2021-03-01 13:15:00'::timestamp without time zone create_time from tb_scan_1_prt_p20200702;

SELECT '20200702' title , count(*) cnt, count(distinct ship_id) dist_cnt ,'2021-03-01 13:15:00'::timestamp without time zone create_time from tb_scan where create_time >='20200702' and create_time<'20200703';