天天看點
傳回

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

标簽

PostgreSQL , 索引 , 所有字段 , 并行建立單個索引 , max_parallel_maintenance_workers , 異步調用 , dblink , 并行建立多個索引 , adhoc查詢

https://github.com/digoal/blog/blob/master/201809/20180903_03.md#%E8%83%8C%E6%99%AF 背景

PostgreSQL 支援豐富的類型、索引，統計資訊。

不同的應用場合，資料類型，可使用不同的索引接口（btree,hash,gin,gist,spgist,brin,bloom等）達到不同的效果。

https://www.postgresql.org/docs/11/static/datatype.html https://www.postgresql.org/docs/11/static/functions.html https://www.postgresql.org/docs/11/static/indexes.html

在某些業務場合，業務上可能需要對任意字段組合進行查詢，可以使用gin或rum 聯合索引接口（把需要搜尋的字段都塞到一個索引裡面，内部使用bitmap scan），但是這些索引建立速度比較慢，并且索引最多隻能放32列（寫死限制 src/include/pg_config_manual.h INDEX_MAX_KEYS）。

是以另一種選擇是使用單列獨立索引，當單列獨立索引過濾性不夠好時，資料庫自動會選擇多個索引做bitmap 合并掃描.

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

那麼每一列應該使用什麼索引接口？

除了取決于資料類型，還取決于資料的分布，柱狀圖。

《PostgreSQL 9種索引的原理和應用場景》 《PostgreSQL SQL自動優化案例 - 極簡，自動推薦索引》 《自動選擇正确索引通路接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》

https://github.com/digoal/blog/blob/master/201809/20180903_03.md#%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B%E4%B8%8E%E7%B4%A2%E5%BC%95%E7%B1%BB%E5%9E%8B%E5%8C%B9%E9%85%8D%E7%9A%84%E9%80%9A%E7%94%A8%E9%80%89%E6%8B%A9%E6%96%B9%E6%B3%95 資料類型與索引類型比對的通用選擇方法

https://www.postgresql.org/docs/11/static/catalog-pg-type.html#CATALOG-TYPCATEGORY-TABLE

https://github.com/digoal/blog/blob/master/201809/20180903_03.md#%E5%A4%A7%E7%B1%BB%E9%80%89%E6%8B%A9 大類選擇

Code Category 推薦 索引接口
A Array types gin
B Boolean types btree , 不建議加索引，選擇性不好
C Composite types -
D Date/time types btree
E Enum types
G Geometric types gist
I Network address types gist, spgist
N Numeric types
P Pseudo-types
R Range types
S String types

btree : varchar text_pattern_ops , bpchar bpchar_pattern_ops ; 

模糊查詢(pg_trgm) gin : varchar gin_trgm_ops
T Timespan types
U User-defined types
V Bit-string types
X unknown type

https://github.com/digoal/blog/blob/master/201809/20180903_03.md#%E5%B0%8F%E7%B1%BB%E9%80%89%E6%8B%A9 小類選擇 

select typcategory , typname from pg_type order by 1,2;
aclitem
box2d
box2df
box3d
bytea
cid
gbtreekey16
gbtreekey32
gbtreekey4
gbtreekey8
gbtreekey_var
geography
geometry
gidx
gtrgm
gtsvector
json
jsonb gin , OPS : jsonb_path_ops
macaddr
macaddr8
pg_lsn
pgis_abs
raster
refcursor
smgr
spheroid
tid
tsquery gin, rum
tsvector
txid_snapshot
uuid hash
xid
xml

https://github.com/digoal/blog/blob/master/201809/20180903_03.md#%E4%BE%9D%E6%8D%AE%E4%BB%A5%E4%B8%8A%E8%A7%84%E5%88%99%E7%94%9F%E6%88%90create-index%E7%9A%84sql 依據以上規則，生成create index的SQL

寫一個UDF函數，将以上規格寫到UDF裡面，自動生成每一列的索引SQL，自動使用合适的索引方法，OPS。

create or replace function gen_whole_index_sqls(  
  v_nsp name,   
  v_tbl name,   
  v_tbs name   
) returns text[] as $$  
declare  
  v_attname name;  
  v_typid oid;  
  v_typca "char";  
  v_typname name;  
  res text[];  
  idxprefix text := to_char(clock_timestamp(),'yyyymmddhh24miss');  
  idxsuffix int := 1;  
  sql text := 'create index IF NOT EXISTS i'||idxprefix||'_%s on '||quote_ident(v_nsp)||'.'||quote_ident(v_tbl)||' using %s (%I %s) tablespace '||quote_ident(v_tbs)||' ;';  
begin   
  
  for v_attname,v_typid in select attname,atttypid from pg_attribute where not attisdropped and attnum >= 1 and attrelid=(quote_ident(v_nsp)||'.'||quote_ident(v_tbl))::regclass  
  loop  
  
  select typcategory,typname into v_typca,v_typname from pg_type where oid=v_typid;  
  
  case v_typca  
  when 'A' then  
    res := array_append(res, format(sql,idxsuffix,'gin',v_attname,''));  
  when 'D', 'N', 'T' then  
    res := array_append(res, format(sql,idxsuffix,'btree',v_attname,''));  
  when 'S' then  
    if v_typname='text' or v_typname='varchar' then  
      res := array_append(res, format(sql,idxsuffix,'btree',v_attname,'text_pattern_ops'));  
    elsif v_typname='bpchar' then  
      res := array_append(res, format(sql,idxsuffix,'btree',v_attname,'bpchar_pattern_ops'));  
    else  
      res := array_append(res, format(sql,idxsuffix,'btree',v_attname,''));  
    end if;  
    -- 如果字元串要支援模糊查詢，使用gin索引  
    -- if v_typname='text' or v_typname='varchar' then  
    --   res := array_append(res, format(sql,idxsuffix,'gin',v_attname,'gin_trgm_ops'));  
    -- else   
    --   res := array_append(res, format(sql,idxsuffix,'btree',v_attname,''));  
    -- end if;   
  when 'G' then  
    if v_typname not in ('line') then  
      res := array_append(res, format(sql,idxsuffix,'gist',v_attname,''));  
    else  
      continue;  
    end if;  
  when 'I', 'R' then  
    res := array_append(res, format(sql,idxsuffix,'gist',v_attname,''));  
    -- 可選spgist  
    -- res := array_append(res, format(sql,idxsuffix,'spgist',v_attname,''));  
  when 'U' then  
    case v_typname   
    when 'geography', 'geometry' then  
      res := array_append(res, format(sql,idxsuffix,'gist',v_attname,''));  
    when 'jsonb' then  
      res := array_append(res, format(sql,idxsuffix,'gin',v_attname,'jsonb_path_ops'));  
      -- 可選預設gin ops   
      -- https://www.postgresql.org/docs/11/static/datatype-json.html#JSON-INDEXING   
      -- res := array_append(res, format(sql,idxsuffix,'gin',v_attname,''));  
    when 'tsvector' then  
      res := array_append(res, format(sql,idxsuffix,'gin',v_attname,''));  
    when 'uuid', 'xid' then  
      res := array_append(res, format(sql,idxsuffix,'hash',v_attname,''));  
    else  
      continue;  
    end case;  
  else  
    continue;  
  end case;  
  
  idxsuffix := idxsuffix+1;  
  
end loop;  
  
return res;  
    
end;  
$$ language plpgsql strict;

https://github.com/digoal/blog/blob/master/201809/20180903_03.md#%E6%B5%8B%E8%AF%95 測試

1、建立測試表，包含各種資料類型

create table "你好t12" (  
c1 int,  
"-_c2&a-b" int8,  
c3 text,  
c4 varchar(1000),  
c5 char(1000),  
c6 "char",  
c7 timestamp,  
c8 interval,  
c9 int[],  
c10 tsvector,  
c11 tsquery,  
c12 time,  
c13 date,  
c14 numeric,  
c15 float,  
c16 point,  
c17 box,  
c18 line,  
c19 circle,  
c20 inet,  
c21 cidr,  
c22 int8range,  
c23 tsrange,  
c24 geometry,  
c25 geography,  
c26 uuid,  
c27 xid,  
c28 json,  
c29 jsonb  
);

2、使用本文提供的UDF，生成CREATE INDEX SQL

select * from  unnest(gen_whole_index_sqls('public','你好t12','pg_default'));  
  
                                                            unnest                                                              
------------------------------------------------------------------------------------------------------------------------------  
 create index IF NOT EXISTS i20180903171836_1 on public."你好t12" using btree (c1 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_2 on public."你好t12" using btree ("-_c2&a-b" ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_3 on public."你好t12" using btree (c3 text_pattern_ops) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_4 on public."你好t12" using btree (c4 text_pattern_ops) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_5 on public."你好t12" using btree (c5 bpchar_pattern_ops) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_6 on public."你好t12" using btree (c6 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_7 on public."你好t12" using btree (c7 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_8 on public."你好t12" using btree (c8 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_9 on public."你好t12" using gin (c9 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_10 on public."你好t12" using gin (c10 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_11 on public."你好t12" using btree (c12 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_12 on public."你好t12" using btree (c13 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_13 on public."你好t12" using btree (c14 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_14 on public."你好t12" using btree (c15 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_15 on public."你好t12" using gist (c16 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_16 on public."你好t12" using gist (c17 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_17 on public."你好t12" using gist (c19 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_18 on public."你好t12" using gist (c20 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_19 on public."你好t12" using gist (c21 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_20 on public."你好t12" using gist (c22 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_21 on public."你好t12" using gist (c23 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_22 on public."你好t12" using gist (c24 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_23 on public."你好t12" using gist (c25 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_24 on public."你好t12" using hash (c26 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_25 on public."你好t12" using hash (c27 ) tablespace pg_default ;  
 create index IF NOT EXISTS i20180903171836_26 on public."你好t12" using gin (c29 jsonb_path_ops) tablespace pg_default ;  
(26 rows)

3、建立索引測試

使用之前提到的并行跑背景任務的方法，并行建立多個索引，充分利用硬體資源加速。

《PostgreSQL dblink異步調用實踐,跑并行多任務 - 例如開N個并行背景任務建立索引, 開N個背景任務跑若幹SQL》 
select * from run_sqls_parallel(6, gen_whole_index_sqls('public','你好t12','pg_default')) as t(a text);  
  
  
NOTICE:  the last 3 tasks running.  
NOTICE:  whole tasks done.  
 run_sqls_parallel   
-------------------  
   
(1 row)

4、檢驗

postgres=# \d 你好t12  
                         Table "public.你好t12"  
  Column  |            Type             | Collation | Nullable | Default   
----------+-----------------------------+-----------+----------+---------  
 c1       | integer                     |           |          |   
 -_c2&a-b | bigint                      |           |          |   
 c3       | text                        |           |          |   
 c4       | character varying(1000)     |           |          |   
 c5       | character(1000)             |           |          |   
 c6       | "char"                      |           |          |   
 c7       | timestamp without time zone |           |          |   
 c8       | interval                    |           |          |   
 c9       | integer[]                   |           |          |   
 c10      | tsvector                    |           |          |   
 c11      | tsquery                     |           |          |   
 c12      | time without time zone      |           |          |   
 c13      | date                        |           |          |   
 c14      | numeric                     |           |          |   
 c15      | double precision            |           |          |   
 c16      | point                       |           |          |   
 c17      | box                         |           |          |   
 c18      | line                        |           |          |   
 c19      | circle                      |           |          |   
 c20      | inet                        |           |          |   
 c21      | cidr                        |           |          |   
 c22      | int8range                   |           |          |   
 c23      | tsrange                     |           |          |   
 c24      | geometry                    |           |          |   
 c25      | geography                   |           |          |   
 c26      | uuid                        |           |          |   
 c27      | xid                         |           |          |   
 c28      | json                        |           |          |   
 c29      | jsonb                       |           |          |   
Indexes:  
    "i20180903171855_1" btree (c1)  
    "i20180903171855_10" gin (c10)  
    "i20180903171855_11" btree (c12)  
    "i20180903171855_12" btree (c13)  
    "i20180903171855_13" btree (c14)  
    "i20180903171855_14" btree (c15)  
    "i20180903171855_15" gist (c16)  
    "i20180903171855_16" gist (c17)  
    "i20180903171855_17" gist (c19)  
    "i20180903171855_18" gist (c20)  
    "i20180903171855_19" gist (c21)  
    "i20180903171855_2" btree ("-_c2&a-b")  
    "i20180903171855_20" gist (c22)  
    "i20180903171855_21" gist (c23)  
    "i20180903171855_22" gist (c24)  
    "i20180903171855_23" gist (c25)  
    "i20180903171855_24" hash (c26)  
    "i20180903171855_25" hash (c27)  
    "i20180903171855_26" gin (c29 jsonb_path_ops)  
    "i20180903171855_3" btree (c3 text_pattern_ops)  
    "i20180903171855_4" btree (c4 text_pattern_ops)  
    "i20180903171855_5" btree (c5 bpchar_pattern_ops)  
    "i20180903171855_6" btree (c6)  
    "i20180903171855_7" btree (c7)  
    "i20180903171855_8" btree (c8)  
    "i20180903171855_9" gin (c9)  
           
postgres=# \di i20180903171855_*  
                    List of relations  
 Schema |        Name        | Type  |  Owner   |  Table    
--------+--------------------+-------+----------+---------  
 public | i20180903171855_1  | index | postgres | 你好t12  
 public | i20180903171855_10 | index | postgres | 你好t12  
 public | i20180903171855_11 | index | postgres | 你好t12  
 public | i20180903171855_12 | index | postgres | 你好t12  
 public | i20180903171855_13 | index | postgres | 你好t12  
 public | i20180903171855_14 | index | postgres | 你好t12  
 public | i20180903171855_15 | index | postgres | 你好t12  
 public | i20180903171855_16 | index | postgres | 你好t12  
 public | i20180903171855_17 | index | postgres | 你好t12  
 public | i20180903171855_18 | index | postgres | 你好t12  
 public | i20180903171855_19 | index | postgres | 你好t12  
 public | i20180903171855_2  | index | postgres | 你好t12  
 public | i20180903171855_20 | index | postgres | 你好t12  
 public | i20180903171855_21 | index | postgres | 你好t12  
 public | i20180903171855_22 | index | postgres | 你好t12  
 public | i20180903171855_23 | index | postgres | 你好t12  
 public | i20180903171855_24 | index | postgres | 你好t12  
 public | i20180903171855_25 | index | postgres | 你好t12  
 public | i20180903171855_26 | index | postgres | 你好t12  
 public | i20180903171855_3  | index | postgres | 你好t12  
 public | i20180903171855_4  | index | postgres | 你好t12  
 public | i20180903171855_5  | index | postgres | 你好t12  
 public | i20180903171855_6  | index | postgres | 你好t12  
 public | i20180903171855_7  | index | postgres | 你好t12  
 public | i20180903171855_8  | index | postgres | 你好t12  
 public | i20180903171855_9  | index | postgres | 你好t12  
(26 rows)

https://github.com/digoal/blog/blob/master/201809/20180903_03.md#%E5%B0%8F%E7%BB%93 小結

1、本文提供了一個UDF，用于生成建立索引的SQL(傳回SQL數組)

gen_whole_index_sqls('name space','表名','表空間名')

2、使用之前提到的并行跑背景任務的方法，并行建立多個索引，充分利用硬體資源加速。

例如

select * from run_sqls_parallel(6, gen_whole_index_sqls('public','你好t12','pg_default')) as t(a text);

3、結合表的統計資訊(analyze table後)，可以把生成CREATE INDEX SQL做得更加完美。

https://github.com/digoal/blog/blob/master/201809/20180903_03.md#%E5%8F%82%E8%80%83 參考

https://www.postgresql.org/docs/11/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY 《PostgreSQL 快速給指定表每個字段建立索引》
sql 關系型資料庫 postgresql 索引 引擎表索引無效 sql建立表 RDS MySQL引擎表索引無效 使用sql建立表 sql建立索引
上一篇: PostgreSQL dblink異步調用實踐,跑并行多任務 - 例如開N個并行背景任務建立索引, 開N個背景任務跑若幹SQL
下一篇: PostgreSQL 相似搜尋插件介紹大彙總 (cube,rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)

繼續閱讀