主页 / dba / 问题 / 262513
Accepted
Gabriele D'Onufrio
Asked: 2020-03-24 03:19:26 +0800 CST

用于在 postgresql 性能中查找所有可以转换为 int 字段的 bigint 的脚本

  • 772

我想压缩一个 postgresql 11 db，我的想法是将大量 bigint 转换为 int 数据类型，当且仅当考虑到内部数据时 int 数据类型的限制存在合理的滞后。

那么，你能帮我找到一个脚本来找到所有可以在 postgresql 中转换为 int 字段的 bigint 吗？

postgresql performance

1 个回答

  1. Best Answer

    以下脚本应该有助于完成这项工作：

    create or replace procedure cbi ()
language plpgsql
as $$
--
declare 
s text;
max_value bigint;
c cursor for
 select 
  pg_namespace.nspname, 
  pg_class.relname, 
  pg_attribute.attname, 
  pg_type.typname 
 from pg_class
 join pg_namespace
 on pg_class.relnamespace = pg_namespace.oid
 and pg_namespace.nspname <> 'pg_catalog'
 join pg_attribute
 on pg_class.oid = pg_attribute.attrelid
 join pg_type
 on pg_attribute.atttypid = pg_type.oid
 and pg_type.typname='int8'
 where pg_class.relkind in ('r','v');
begin
 for r in c
 loop
  s := 'select  max(' || r.attname || ') from ' || r.nspname || '.' || r.relname; 
  execute s into max_value;
  if max_value <= +2147483647 and max_value >= -2147483648 
  then
   raise notice '%.%.% max=% can be replaced with int.', r.nspname, r.relname, r.attname, max_value;
  else
   raise notice '%.%.% max=% CANNOT be replaced with int.', r.nspname, r.relname, r.attname, max_value;
  end if;
 end loop;
end;
--
$$;

    例子：

    postgres=# call cbi();
NOTICE:  public.pg_stat_statements.queryid max=9148650842799727408 CANNOT be replaced with int.
NOTICE:  public.pg_stat_statements.calls max=118 can be replaced with int.
NOTICE:  public.pg_stat_statements.rows max=4060311 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_hit max=577699 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_read max=637 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_dirtied max=991 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_written max=991 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_hit max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_read max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_dirtied max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_written max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.temp_blks_read max=171 can be replaced with int.
NOTICE:  public.pg_stat_statements.temp_blks_written max=171 can be replaced with int.
NOTICE:  public.t.x max=1 can be replaced with int.
CALL
postgres=#
    • 1

相关问题