PostgreSQL 快速给指定表每个字段创建索引 - 1
标签
PostgreSQL , 索引 , 所有字段 , 并行创建索引 , max_parallel_maintenance_workers
背景
如何快速给表的所有字段，每个字段都加上索引。
《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;
$$;
参考
《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》
《PostgreSQL 11 preview - 并行排序、并行索引 (性能线性暴增) 单实例100亿TOP-K仅40秒》
《PostgreSQL dblink异步调用实践,跑并行多任务 - 例如开N个并行后台任务创建索引, 开N个后台任务跑若干SQL》