PostgreSQL如何快速给指定表每个字段创建索引

2025-03-12 技术教程

小编给大家分享一下PostgreSQL如何快速给指定表每个字段创建索引，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

数据类型与索引类型匹配的通用选择方法大类选择CodeCategory推荐索引接口AArray typesginBBoolean typesbtree , 不建议加索引，选择性不好CComposite types-DDate/time typesbtreeEEnum types-GGeometric typesgistINetwork address typesgist, spgistNNumeric typesbtreePPseudo-types-RRange typesgist, spgistSString typesbtree : varchar text_pattern_ops , bpchar bpchar_pattern_ops ;
模糊查询(pg_trgm) gin : varchar gin_trgm_opsTTimespan typesbtreeUUser-defined types-VBit-string types-Xunknown type-小类选择

selecttypcategory,typnamefrompg_typeorderby1,2;CodeCategory推荐索引接口Uaclitem-Ubox2d-Ubox2df-Ubox3d-Ubytea-Ucid-Ugbtreekey16-Ugbtreekey32-Ugbtreekey4-Ugbtreekey8-Ugbtreekey_var-UgeographygistUgeometrygistUgidx-Ugtrgm-Ugtsvector-UjsonginUjsonbgin , OPS : jsonb_path_opsUmacaddr-Umacaddr8-Upg_lsn-Upgis_abs-Uraster-Urefcursor-Usmgr-Uspheroid-Utid-Utsquerygin, rumUtsvectorgin, rumUtxid_snapshot-UuuidhashUxidbtreeUxml-依据以上规则，生成create index的SQL

写一个UDF函数，将以上规格写到UDF里面，自动生成每一列的索引SQL，自动使用合适的索引方法，OPS。

createorreplacefunctiongen_whole_index_sqls(v_nspname,v_tblname,v_tbsname)returnstext[]as$$declarev_attnamename;v_typidoid;v_typca"char";v_typnamename;restext[];idxprefixtext:=to_char(clock_timestamp(),'yyyymmddhh34miss');idxsuffixint:=1;sqltext:='createindexIFNOTEXISTSi'||idxprefix||'_%son'||quote_ident(v_nsp)||'.'||quote_ident(v_tbl)||'using%s(%I%s)tablespace'||quote_ident(v_tbs)||';';beginforv_attname,v_typidinselectattname,atttypidfrompg_attributewherenotattisdroppedandattnum>=1andattrelid=(quote_ident(v_nsp)||'.'||quote_ident(v_tbl))::regclassloopselecttypcategory,typnameintov_typca,v_typnamefrompg_typewhereoid=v_typid;casev_typcawhen'A'thenres:=array_append(res,format(sql,idxsuffix,'gin',v_attname,''));when'D','N','T'thenres:=array_append(res,format(sql,idxsuffix,'btree',v_attname,''));when'S'thenifv_typname='text'orv_typname='varchar'thenres:=array_append(res,format(sql,idxsuffix,'btree',v_attname,'text_pattern_ops'));elsifv_typname='bpchar'thenres:=array_append(res,format(sql,idxsuffix,'btree',v_attname,'bpchar_pattern_ops'));elseres:=array_append(res,format(sql,idxsuffix,'btree',v_attname,''));endif;--如果字符串要支持模糊查询，使用gin索引--ifv_typname='text'orv_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,''));--endif;when'G'thenifv_typnamenotin('line')thenres:=array_append(res,format(sql,idxsuffix,'gist',v_attname,''));elsecontinue;endif;when'I','R'thenres:=array_append(res,format(sql,idxsuffix,'gist',v_attname,''));--可选spgist--res:=array_append(res,format(sql,idxsuffix,'spgist',v_attname,''));when'U'thencasev_typnamewhen'geography','geometry'thenres:=array_append(res,format(sql,idxsuffix,'gist',v_attname,''));when'jsonb'thenres:=array_append(res,format(sql,idxsuffix,'gin',v_attname,'jsonb_path_ops'));--可选默认ginops--https://www.postgresql.org/docs/11/static/datatype-json.html#JSON-INDEXING--res:=array_append(res,format(sql,idxsuffix,'gin',v_attname,''));when'tsvector'thenres:=array_append(res,format(sql,idxsuffix,'gin',v_attname,''));when'uuid','xid'thenres:=array_append(res,format(sql,idxsuffix,'hash',v_attname,''));elsecontinue;endcase;elsecontinue;endcase;idxsuffix:=idxsuffix+1;endloop;returnres;end;$$languageplpgsqlstrict;测试

1、创建测试表，包含各种数据类型

createtable"你好t12"(c1int,"-_c2&a-b"int8,c3text,c4varchar(1000),c5char(1000),c6"char",c7timestamp,c8interval,c9int[],c10tsvector,c11tsquery,c12time,c13date,c14numeric,c15float,c16point,c17box,c18line,c19circle,c20inet,c21cidr,c22int8range,c23tsrange,c24geometry,c25geography,c26uuid,c27xid,c28json,c29jsonb);

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

select*fromunnest(gen_whole_index_sqls('public','你好t12','pg_default'));unnest------------------------------------------------------------------------------------------------------------------------------createindexIFNOTEXISTSi20180903171836_1onpublic."你好t12"usingbtree(c1)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_2onpublic."你好t12"usingbtree("-_c2&a-b")tablespacepg_default;createindexIFNOTEXISTSi20180903171836_3onpublic."你好t12"usingbtree(c3text_pattern_ops)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_4onpublic."你好t12"usingbtree(c4text_pattern_ops)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_5onpublic."你好t12"usingbtree(c5bpchar_pattern_ops)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_6onpublic."你好t12"usingbtree(c6)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_7onpublic."你好t12"usingbtree(c7)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_8onpublic."你好t12"usingbtree(c8)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_9onpublic."你好t12"usinggin(c9)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_10onpublic."你好t12"usinggin(c10)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_11onpublic."你好t12"usingbtree(c12)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_12onpublic."你好t12"usingbtree(c13)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_13onpublic."你好t12"usingbtree(c14)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_14onpublic."你好t12"usingbtree(c15)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_15onpublic."你好t12"usinggist(c16)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_16onpublic."你好t12"usinggist(c17)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_17onpublic."你好t12"usinggist(c19)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_18onpublic."你好t12"usinggist(c20)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_19onpublic."你好t12"usinggist(c21)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_20onpublic."你好t12"usinggist(c22)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_21onpublic."你好t12"usinggist(c23)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_22onpublic."你好t12"usinggist(c24)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_23onpublic."你好t12"usinggist(c25)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_24onpublic."你好t12"usinghash(c26)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_25onpublic."你好t12"usinghash(c27)tablespacepg_default;createindexIFNOTEXISTSi20180903171836_26onpublic."你好t12"usinggin(c29jsonb_path_ops)tablespacepg_default;(26rows)

3、创建索引测试

使用之前提到的并行跑后台任务的方法，并行创建多个索引，充分利用硬件资源加速。

《PostgreSQL dblink异步调用实践,跑并行多任务 - 例如开N个并行后台任务创建索引, 开N个后台任务跑若干SQL》

select*fromrun_sqls_parallel(6,gen_whole_index_sqls('public','你好t12','pg_default'))ast(atext);NOTICE:thelast3tasksrunning.NOTICE:wholetasksdone.run_sqls_parallel-------------------(1row)

4、检验

postgres=#\d你好t12Table"public.你好t12"Column|Type|Collation|Nullable|Default----------+-----------------------------+-----------+----------+---------c1|integer|||-_c2&a-b|bigint|||c3|text|||c4|charactervarying(1000)|||c5|character(1000)|||c6|"char"|||c7|timestampwithouttimezone|||c8|interval|||c9|integer[]|||c10|tsvector|||c11|tsquery|||c12|timewithouttimezone|||c13|date|||c14|numeric|||c15|doubleprecision|||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(c29jsonb_path_ops)"i20180903171855_3"btree(c3text_pattern_ops)"i20180903171855_4"btree(c4text_pattern_ops)"i20180903171855_5"btree(c5bpchar_pattern_ops)"i20180903171855_6"btree(c6)"i20180903171855_7"btree(c7)"i20180903171855_8"btree(c8)"i20180903171855_9"gin(c9)

1、本文提供了一个UDF，用于生成创建索引的SQL(返回SQL数组)

gen_whole_index_sqls('namespace','表名','表空间名')

2、使用之前提到的并行跑后台任务的方法，并行创建多个索引，充分利用硬件资源加速。

例如

select*fromrun_sqls_parallel(6,gen_whole_index_sqls('public','你好t12','pg_default'))ast(atext);

3、结合表的统计信息(analyze table后)，可以把生成CREATE INDEX SQL做得更加完美。

以上是“PostgreSQL如何快速给指定表每个字段创建索引”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！