PostgreSQL 快速給指定表每個字段建立索引 - 1

标簽

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

https://github.com/digoal/blog/blob/master/201808/20180822_01.md#%E8%83%8C%E6%99%AF 背景

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

《PostgreSQL 設計優化case - 大寬表任意字段組合查詢索引如何選擇(btree, gin, rum) - (含單個索引列數超過32列的方法)》

滿足任意字段組合查詢的需求。

https://github.com/digoal/blog/blob/master/201808/20180822_01.md#%E4%BE%8B%E5%AD%90-postgresql-11 例子 (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;  
$$;  
           

https://github.com/digoal/blog/blob/master/201808/20180822_01.md#%E5%8F%82%E8%80%83 參考

《PostgreSQL dblink異步調用實踐,跑并行多任務 - 例如開N個并行背景任務建立索引, 開N個背景任務跑若幹SQL》