标签

PostgreSQL , 索引 , 所有字段 , 并行创建索引 , max_parallel_maintenance_workers

背景

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

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

例子 (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、设置需要创建索引的表的并行度，创建索引时，可以用并行创建索引的功能。

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;  $$;

参考