PostgreSQL 快速给指定表每个字段创建索引 - 1 (不够完美)

1 minute read

背景

如何快速给表的所有字段，每个字段都加上索引。

《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》

满足任意字段组合查询的需求。

例子 (PostgreSQL 11)

1、如果需要在单个事务中要创建的索引非常多（表多，字段数多），可能超过数据库中配置的锁的上限。取决于一下两个参数。

max_locks_per_transaction=512  
max_connections=2000  

2、为了使用并行创建索引，需要设置足够多的WORKERS。取决于如下参数

max_worker_processes=64

3、创建表空间，用于存储索引。（可选，如果有多个块设备时，建议创建独立的表空间，存放索引）

/dev/mapper/vgdata01-lv01  3.2T  505G  2.7T  16% /data01  
/dev/mapper/vgdata01-lv02  3.2T  105G  3.1T   4% /data02  
  
postgres=# create tablespace tbs1 location '/data02/pg/tbs1';  
CREATE TABLESPACE  

4、设置需要创建索引的表的并行度，创建索引时，可以用并行创建索引的功能。

《PostgreSQL 11 preview - 并行排序、并行索引 (性能线性暴增) 单实例100亿TOP-K仅40秒》

do language plpgsql $$  
declare  
  tables name[] := array['t1','t2','t3'];   -- t1,t2,t3表  
  n name;   
begin  
  foreach n in array tables loop  
    execute format('alter table %s set (parallel_workers =24)', n);   
  end loop;  
end;  
$$;  

5、并行创建索引，t1,t2,t3表，每个字段创建一个索引。使用并行度24.

do language plpgsql $$  
declare  
  tables name[] := array['t1','t2','t3'];   
  n name;   
  x name;   
  i int;  
  sql text;  
  ts1 timestamp;  
  ts2 timestamp;  
begin  
  set max_parallel_maintenance_workers=24;   -- 创建索引时的并行度  
    
  set max_parallel_workers=32;  
  set max_parallel_workers_per_gather=32;  
  set maintenance_work_mem='1GB';  
  
  set min_parallel_table_scan_size=0;  
  set min_parallel_index_scan_size=0;  
  set parallel_setup_cost=0;  
  set parallel_tuple_cost=0;  
  
  foreach n in array tables loop  
    i := 1;    
    for x in select attname from pg_attribute where attrelid=n::regclass and attnum>=1 and not attisdropped loop  
      sql := format('create index IF NOT EXISTS idx_%s__%s on %s (%s) tablespace tbs1', n, i, n, x);   -- 封装创建索引的SQL  
      ts1 := clock_timestamp();  
      raise notice '% start execute: %', ts1, sql;  
      execute sql;  -- 创建索引    
      commit;  -- pg 11已支持procedure, do里面开子事务
      ts2 := clock_timestamp();  
      raise notice '% end execute: % , duration: %', ts2, sql, ts2-ts1;  
      i:=i+1;  
    end loop;  
    execute format('analyze %s', n);   
  end loop;  
end;  
$$;  