postgresql/lightdb并行创建索引（parallel）
[root@hs-10-20-30-199 bin]# pstack 48430 #0 0x00007fbd9375a62e in strcoll_l () from /lib64/libc.so.6 #1 0x000000000087bc82 in varstrfastcmp_locale (a1p=0x82bb181 "20210418001005667691\005 \t001\033F62105667691", len1=<optimized out>, a2p=0x822b621 "20210418001005200523\005 \t001\033F62105200523", len2=<optimized out>, ssup=0x1de67e8, ssup=0x1de67e8) at varlena.c:2437 #2 0x000000000087be36 in varlenafastcmp_locale (x=137081216, y=136492576, ssup=0x1de67e8) at varlena.c:2273 #3 0x00000000008d4190 in ApplySortComparator (ssup=0x1de67e8, isNull2=<optimized out>, datum2=<optimized out>, isNull1=<optimized out>, datum1=<optimized out>) at ../../../../src/include/utils/sortsupport.h:224 #4 comparetup_index_btree (a=0x7fbd7ebf9148, b=0x7fbd7ebf0de8, state=0x1de61c8) at tuplesort.c:4158 #5 0x00000000008d1cde in qsort_tuple (a=0x7fbd7ebf0de8, a@entry=0x7fbd7eadc048, n=<optimized out>, n@entry=502222, cmp_tuple=cmp_tuple@entry=0x8d4110 <comparetup_index_btree>, state=state@entry=0x1de61c8) at qsort_tuple.c:140 #6 0x00000000008d1be2 in qsort_tuple (a=0x7fbd7eadc048, n=<optimized out>, cmp_tuple=0x8d4110 <comparetup_index_btree>, state=state@entry=0x1de61c8) at qsort_tuple.c:177 #7 0x00000000008d57e6 in tuplesort_sort_memtuples (state=state@entry=0x1de61c8) at tuplesort.c:3498 #8 0x00000000008d5851 in dumptuples (state=state@entry=0x1de61c8, alltuples=alltuples@entry=true) at tuplesort.c:3164 #9 0x00000000008d6f44 in tuplesort_performsort (state=0x1de61c8) at tuplesort.c:1996 #10 0x00000000004e7bf9 in _bt_parallel_scan_and_sort (btspool=btspool@entry=0x1df5740, btspool2=0x1df5770, btshared=0x7fbd95346380, sharedsort=<optimized out>, sharedsort2=0x7fbd953462c0, sortmem=<optimized out>, progress=progress@entry=true) at nbtsort.c:2005 #11 0x00000000004e89b4 in _bt_leader_participate_as_worker (buildstate=<optimized out>, buildstate=<optimized out>) at nbtsort.c:1802 #12 _bt_begin_parallel (request=<optimized out>, isconcurrent=<optimized out>, buildstate=0x7ffccc6a0350) at nbtsort.c:1674 #13 _bt_spools_heapscan (indexInfo=0x1e00e40, buildstate=0x7ffccc6a0350, index=..., heap=0x7fbd95276340) at nbtsort.c:394 #14 btbuild (heap=0x7fbd95276340, index=0x7fbd95270ee0, indexInfo=0x1e00e40) at nbtsort.c:326 #15 0x000000000053e533 in index_build (heapRelation=heapRelation@entry=0x7fbd95276340, indexRelation=indexRelation@entry=0x7fbd95270ee0, indexInfo=indexInfo@entry=0x1e00e40, isreindex=isreindex@entry=false, parallel=parallel@entry=true) at index.c:2962 #16 0x000000000053f9b0 in index_create (heapRelation=heapRelation@entry=0x7fbd95276340, indexRelationName=indexRelationName@entry=0x1df1440 "tbclientsellersync6_pkey", indexRelationId=3314080, indexRelationId@entry=0, parentIndexRelid=parentIndexRelid@entry=0, parentConstraintId=parentConstraintId@entry=0, relFileNode=<optimized out>, indexInfo=indexInfo@entry=0x1e00e40, indexColNames=indexColNames@entry=0x1df2a10, accessMethodObjectId=accessMethodObjectId@entry=403, tableSpaceId=tableSpaceId@entry=0, collationObjectId=collationObjectId@entry=0x1df2a98, classObjectId=classObjectId@entry=0x1df2ab8, coloptions=coloptions@entry=0x1df2ad8, reloptions=reloptions@entry=0, flags=flags@entry=3, constr_flags=0, allow_system_table_mods=false, is_internal=false, constraintId=constraintId@entry=0x7ffccc6a08dc) at index.c:1231 #17 0x00000000005dcd50 in DefineIndex (relationId=3128715, stmt=stmt@entry=0x1d17258, indexRelationId=indexRelationId@entry=0, parentIndexId=parentIndexId@entry=0, parentConstraintId=parentConstraintId@entry=0, is_alter_table=is_alter_table@entry=true, check_rights=true, check_not_in_use=check_not_in_use@entry=false, skip_build=false, quiet=quiet@entry=false) at indexcmds.c:1114 #18 0x00000000005f6f2f in ATExecAddIndex (stmt=0x1d17258, is_rebuild=is_rebuild@entry=false, lockmode=8, rel=0x7fbd95276340, tab=0x1d17058) at tablecmds.c:8128 #19 0x000000000060675e in ATExecCmd (wqueue=wqueue@entry=0x7ffccc6a0c98, tab=tab@entry=0x1d17058, rel=rel@entry=0x7fbd95276340, cmd=0x1df2870, lockmode=lockmode@entry=8, cur_pass=cur_pass@entry=8, context=context@entry=0x7ffccc6a0f30) at tablecmds.c:4649 #20 0x0000000000608d2f in ATRewriteCatalogs (context=<optimized out>, lockmode=8, wqueue=0x7ffccc6a0c98) at tablecmds.c:4534 #21 ATController (parsetree=parsetree@entry=0x1d16708, rel=<optimized out>, cmds=<optimized out>, recurse=<optimized out>, lockmode=lockmode@entry=8, context=context@entry=0x7ffccc6a0f30) at tablecmds.c:4131 #22 0x0000000000609994 in AlterTable (stmt=stmt@entry=0x1d16708, lockmode=lockmode@entry=8, context=context@entry=0x7ffccc6a0f30) at tablecmds.c:3781 #23 0x00000000007966de in ProcessUtilitySlow (pstate=pstate@entry=0x1d16f48, pstmt=pstmt@entry=0x1d165f8, queryString=queryString@entry=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n", context=context@entry=PROCESS_UTILITY_TOPLEVEL, params=params@entry=0x0, queryEnv=queryEnv@entry=0x0, qc=qc@entry=0x7ffccc6a1b20, dest=0x1cbda68) at utility.c:1568 #24 0x00000000007959a4 in standard_ProcessUtility (pstmt=pstmt@entry=0x1d165f8, queryString=queryString@entry=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n", context=context@entry=PROCESS_UTILITY_TOPLEVEL, params=params@entry=0x0, queryEnv=queryEnv@entry=0x0, dest=dest@entry=0x1cbda68, qc=qc@entry=0x7ffccc6a1b20) at utility.c:1305 #25 0x00007fbd89f37808 in ProcessUtilityInternal (pstmt=0x1d165f8, pstmt@entry=0x1cbd4d8, queryString=queryString@entry=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n", context=context@entry=PROCESS_UTILITY_TOPLEVEL, params=params@entry=0x0, queryEnv=queryEnv@entry=0x0, dest=dest@entry=0x1cbda68, completionTag=completionTag@entry=0x7ffccc6a1b20) at commands/utility_hook.c:581 #26 0x00007fbd89f38060 in multi_ProcessUtility (pstmt=0x1cbd4d8, queryString=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x1cbda68, completionTag=0x7ffccc6a1b20) at commands/utility_hook.c:263 #27 0x00007fbd873ba3de in pgss_ProcessUtility (pstmt=0x1cbd4d8, queryString=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x1cbda68, qc=0x7ffccc6a1b20) at pg_stat_statements.c:1156 #28 0x00007fbd871b157c in ltsa_ProcessUtility (pstmt=0x1cbd4d8, queryString=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x1cbda68, qc=0x7ffccc6a1b20) at lt_stat_activity.c:315 #29 0x00007fbd86b8fc69 in pgaudit_ProcessUtility_hook (pstmt=0x1cbd4d8, queryString=<optimized out>, context=<optimized out>, params=<optimized out>, queryEnv=<optimized out>, dest=<optimized out>, qc=0x7ffccc6a1b20) at pgaudit.c:1412 #30 0x00007fbd8697748b in pg_hint_plan_ProcessUtility (pstmt=<optimized out>, queryString=<optimized out>, context=<optimized out>, params=<optimized out>, queryEnv=<optimized out>, dest=<optimized out>, qc=0x7ffccc6a1b20) at pg_hint_plan.c:5846 #31 0x00007fbd86553fcc in LtStandbyProcessUtility (pstmt=0x1cbd4d8, queryString=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n", context=<optimized out>, params=0x0, queryEnv=<optimized out>, dest=0x1cbda68, qc=0x7ffccc6a1b20) at lt_standby_forward.c:351 #32 0x00007fbd86339ec7 in pathman_process_utility_hook (first_arg=0x1cbd4d8, queryString=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=<optimized out>, dest=<optimized out>, queryCompletion=0x7ffccc6a1b20) at src/hooks.c:1113 #33 0x0000000000796390 in ProcessUtility (pstmt=pstmt@entry=0x1cbd4d8, queryString=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n", context=context@entry=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=dest@entry=0x1cbda68, qc=qc@entry=0x7ffccc6a1b20) at utility.c:753 #34 0x0000000000792dac in PortalRunUtility (portal=0x1d858d8, pstmt=0x1cbd4d8, isTopLevel=<optimized out>, setHoldSnapshot=<optimized out>, dest=0x1cbda68, qc=0x7ffccc6a1b20) at pquery.c:1161 #35 0x00000000007937f9 in PortalRunMulti (portal=portal@entry=0x1d858d8, isTopLevel=isTopLevel@entry=true, setHoldSnapshot=setHoldSnapshot@entry=false, dest=dest@entry=0x1cbda68, altdest=altdest@entry=0x1cbda68, qc=qc@entry=0x7ffccc6a1b20) at pquery.c:1314 #36 0x00000000007942ed in PortalRun (portal=portal@entry=0x1d858d8, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x1cbda68, altdest=altdest@entry=0x1cbda68, qc=qc@entry=0x7ffccc6a1b20) at pquery.c:783 #37 0x000000000078fffe in exec_simple_query (query_string=0x1cbc508 "ALTER TABLE ONLY fund60trans2.tbclientsellersync6\n ADD CONSTRAINT tbclientsellersync6_pkey PRIMARY KEY (in_client_no, bank_no, seller_code, asset_acc);\n\n\n") at postgres.c:1326 #38 0x0000000000791358 in PostgresMain (argc=<optimized out>, argv=argv@entry=0x1d20fc8, dbname=0x1d20ee8 "fund60_perf", username=<optimized out>) at postgres.c:4445 #39 0x000000000048847e in BackendRun (port=<optimized out>, port=<optimized out>) at postmaster.c:4883 #40 BackendStartup (port=0x1d19c10) at postmaster.c:4567 #41 ServerLoop () at postmaster.c:1854 #42 0x000000000071b076 in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x1cb6f50) at postmaster.c:1488 #43 0x000000000048946d in main (argc=3, argv=0x1cb6f50) at main.c:231
内部是采用fork and join模式进行并行创建。
需要注意的是，PG在老版本中不支持在一个表上同时创建索引索引，会加锁，即使加concurrent子句也只是不阻塞DDL而已。对于大表而言，建议走lightdb分布式版，索引维护的性能可提升数量级。
