How PostgreSQL quickly creates an index for each field of a specified table 01/18 Update SLTechnology News&Howtos

How PostgreSQL quickly creates an index for each field of a specified table

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

Editor to share with you how PostgreSQL quickly create an index for each field of the specified table, I believe most people do not know much about it, so share this article for your reference. I hope you will learn a lot after reading this article. Let's learn about it together.

General selection method for matching data type and index type large class selection CodeCategory recommended index interface AArray typesginBBoolean typesbtree, not recommended indexing, poor selectivity 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

Fuzzy query (pg_trgm) gin: varchar gin_trgm_opsTTimespan typesbtreeUUser-defined types-VBit-string types-Xunknown type- subclass selection select typcategory, typname from pg_type order by 1 Magi 2 CodeCategory recommends index interfaces 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- to generate the SQL of create index according to the above rules.

Write a UDF function, write the above specifications into UDF, automatically generate the index SQL for each column, and automatically use the appropriate index method, 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 (), 'yyyymmddhh34miss'); 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 vclassitytypid 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 vtypename from pg_type where oid=v_typid; case v_typca when'A' then res: = array_append (res, format (sql,idxsuffix,'gin',v_attname,'')) When'Delike, 'Numeric,' T'then res: = array_append (res, format (sql,idxsuffix,'btree',v_attname,'')); when 'S'then if vested typnameprinted text'or vested typnameprinted varchar 'then res: = array_append (res, format (sql,idxsuffix,'btree',v_attname,'text_pattern_ops')) Elsif vested typnameplate 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 -- if the string is to support fuzzy queries, use the gin index-- if 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'ified,' R'then res: = array_append (res, format (sql,idxsuffix,'gist',v_attname,'')) -- optional 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'));-- optional default 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; Test

1. Create a test table containing various data types

Create table "Hello 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. Use the UDF provided in this article to generate CREATE INDEX SQL

Select * from unnest (gen_whole_index_sqls ('public',' how do you do? Unnest -create index IF NOT EXISTS i20180903171836.1 on public. " Hello T12 "using btree (C1) tablespace pg_default; create index IF NOT EXISTS i20180903171836 on public." Hello T12 "using btree ("-_ c2&a-b ") tablespace pg_default; create index IF NOT EXISTS i20180903171836 on public." Hello T12 "using btree (c3 text_pattern_ops) tablespace pg_default; create index IF NOT EXISTS i201809031718364th on public." Hello T12 "using btree (c4 text_pattern_ops) tablespace pg_default; create index IF NOT EXISTS i20180903171836 on public." Hello T12 "using btree (c5 bpchar_pattern_ops) tablespace pg_default; create index IF NOT EXISTS i20180903171836 on public." Hello T12 "using btree (c6) tablespace pg_default; create index IF NOT EXISTS i201809031718367th on public." Hello T12 "using btree (c7) tablespace pg_default; create index IF NOT EXISTS i201809031718368 on public." Hello T12 "using btree (c8) tablespace pg_default; create index IF NOT EXISTS i201809031718369 on public." Hello T12 "using gin (c9) tablespace pg_default; create index IF NOT EXISTS i2018090317183610 on public." Hello T12 "using gin (c10) tablespace pg_default; create index IF NOT EXISTS i2018090317183611 on public." Hello T12 "using btree (c12) tablespace pg_default; create index IF NOT EXISTS i2018090317183612 on public." Hello T12 "using btree (c13) tablespace pg_default; create index IF NOT EXISTS i2018090317183613 on public." Hello T12 "using btree (c14) tablespace pg_default; create index IF NOT EXISTS i2018090317183614 on public." Hello T12 "using btree (c15) tablespace pg_default; create index IF NOT EXISTS i2018090317183615 on public." Hello T12 "using gist (c16) tablespace pg_default; create index IF NOT EXISTS i2018090317183616 on public." Hello T12 "using gist (c17) tablespace pg_default; create index IF NOT EXISTS i2018090317183617 on public." Hello T12 "using gist (c19) tablespace pg_default; create index IF NOT EXISTS i2018090317183618 on public." Hello T12 "using gist (c20) tablespace pg_default; create index IF NOT EXISTS i2018090317183619 on public." Hello T12 "using gist (c21) tablespace pg_default; create index IF NOT EXISTS i2018090317183620 on public." Hello T12 "using gist (c22) tablespace pg_default; create index IF NOT EXISTS i2018090317183621 on public." Hello T12 "using gist (c23) tablespace pg_default; create index IF NOT EXISTS i2018090317183622 on public." Hello T12 "using gist (c24) tablespace pg_default; create index IF NOT EXISTS i2018090317183623 on public." Hello T12 "using gist (c25) tablespace pg_default; create index IF NOT EXISTS i2018090317183624 on public." Hello T12 "using hash (c26) tablespace pg_default; create index IF NOT EXISTS i2018090317183625 on public." Hello T12 "using hash (c27) tablespace pg_default; create index IF NOT EXISTS i2018090317183626 on public." Hello T12 "using gin (c29 jsonb_path_ops) tablespace pg_default; (26 rows)

3. Create an index test

Using the previously mentioned method of running background tasks in parallel, multiple indexes are created in parallel, making full use of hardware resources to accelerate.

"PostgreSQL dblink asynchronous invocation practice, running parallel multitasking-for example, opening N parallel background tasks to create indexes, opening N background tasks to run several SQL"

Select * from run_sqls_parallel (6, gen_whole_index_sqls ('public',' Hello)) ast (a text); NOTICE: the last 3 tasks running. NOTICE: whole tasks done. Run_sqls_parallel-(1 row)

4. Inspection

1. This article provides a UDF to generate the SQL that creates the index (returns the SQL array)

Gen_whole_index_sqls ('name space',' table name', 'table space name')

2. Use the method mentioned earlier to run background tasks in parallel, create multiple indexes in parallel, and make full use of hardware resources to accelerate.

For example

Select * from run_sqls_parallel (6, gen_whole_index_sqls ('public',' Hello) as t (a text))

3. Combined with the statistical information of the table (after analyze table), the generation of CREATE INDEX SQL can be done more perfectly.

The above is all the contents of the article "how to quickly create an index for each field of a specified table by PostgreSQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.