等待 PostgreSQL 18 – 允许并行创建 GIN 索引

Allow parallel CREATE INDEX for GIN indexes
允许并行为GIN索引创建索引


Allow using parallel workers to build a GIN index, similarly to BTREE
and BRIN. For large tables this may result in significant speedup when
the build is CPU-bound.
允许使用并行worker构建GIN索引，类似于BTREE和BRIN。对于大型表，当构建受到CPU限制时，这可能会导致显著的加速。


The work is divided so that each worker builds index entries on a subset
of the table, determined by the regular parallel scan used to read the
data. Each worker uses a local tuplesort to sort and merge the entries
for the same key. The TID lists do not overlap (for a given key), which
means the merge sort simply concatenates the two lists. The merged
entries are written into a shared tuplesort for the leader.
工作被划分，以便每个worker在表的一个子集上构建索引条目，该子集由用于读取数据的常规并行扫描确定。每个worker都使用本地元组排序来对同一个键的条目进行排序和合并。TID列表不重叠（对于给定的键），这意味着合并排序只是将两个列表连接起来。合并的条目被写入领导者的共享元组排序中。


The leader needs to merge the sorted entries again, before writing them
into the index. But this way a significant part of the work happens in
the workers, and the leader is left with merging fewer large entries,
which is more efficient.
领导者需要在将排序后的条目写入索引之前再次合并它们。但通过这种方式，很大一部分工作发生在工人身上，领导者只需合并更少的大条目，这会更有效率。


Most of the parallelism infrastructure is a simplified copy of the code
used by BTREE indexes, omitting the parts irrelevant for GIN indexes
(e.g. uniqueness checks).
大多数并行基础设施是BTREE索引使用的代码的简化副本，省略了与GIN索引无关的部分（例如唯一性检查）。


Original patch by me, with reviews and substantial improvements by
Matthias van de Meent, certainly enough to make him a co-author.
我的原始补丁，Matthias van de Meent的评论和实质性改进，当然足以让他成为合著者。


Author: Tomas Vondra, Matthias van de Meent
Reviewed-by: Matthias van de Meent, Andy Fan, Kirill Reshke
Discussion: https://postgr.es/m/6ab4003f-a8b8-4d75-a67f-f25ad98582dc%40enterprisedb.com

#!/usr/bin/env ruby
# frozen_string_literal: true
arr = (1..100_000).to_a
1_000_000.times do
  puts "{#{arr.sample(rand() * 100 + 50).join(',')}}"
end

=$ create table test_gin (
    the_array int4[]
);
=$ \copy test_gin from /tmp/test.arrays.txt

=$ set max_parallel_maintenance_workers = 0;
SET


=$ create index qq on test_gin using gin (the_array);
CREATE INDEX
Time: 77111.482 ms (01:17.111)

=$ explain (analyze, buffers) select * from test_gin where the_array @> '{1,2}'::int4[];
                                                    QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Bitmap Heap Scan on test_gin  (cost=51.31..150.05 rows=25 width=421) (actual time=0.093..0.094 rows=1.00 loops=1)
   Recheck Cond: (the_array @> '{1,2}'::integer[])
   Heap Blocks: exact=1
   Buffers: shared hit=4 read=6
   I/O Timings: shared read=0.017
   ->  Bitmap Index Scan on qq  (cost=0.00..51.30 rows=25 width=0) (actual time=0.082..0.082 rows=1.00 loops=1)
         Index Cond: (the_array @> '{1,2}'::integer[])
         Buffers: shared hit=4 read=5
         I/O Timings: shared read=0.013
 Planning:
   Buffers: shared hit=12 read=6 dirtied=4
   I/O Timings: shared read=0.550
 Planning Time: 0.764 ms
 Execution Time: 0.112 ms
(14 rows)

=$ drop index qq;
DROP INDEX


=$ set max_parallel_maintenance_workers = 10;
SET


=$ create index qq on test_gin using gin (the_array);
CREATE INDEX
Time: 53602.822 ms (00:53.603)

USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
…
pgdba     361901 10.2  0.3 282400 209020 ?       Rs   12:41   2:41      \_ postgres: depesz depesz [local] CREATE INDEX
pgdba     391026 99.4  0.0 270036 60428 ?        Rs   13:07   0:29      \_ postgres: parallel worker for PID 361901
…

$ \di+ qq
                                        List of indexes
 Schema │ Name │ Type  │ Owner  │  Table   │ Persistence │ Access method │  Size  │ Description
────────┼──────┼───────┼────────┼──────────┼─────────────┼───────────────┼────────┼─────────────
 public │ qq   │ index │ depesz │ test_gin │ permanent   │ gin           │ 769 MB │ [null]
(1 row)

=$ explain (analyze, buffers) select * from test_gin where the_array @> '{1,2}'::int4[];
                                                    QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Bitmap Heap Scan on test_gin  (cost=34.31..133.05 rows=25 width=421) (actual time=0.152..0.153 rows=1.00 loops=1)
   Recheck Cond: (the_array @> '{1,2}'::integer[])
   Heap Blocks: exact=1
   Buffers: shared hit=4 read=6
   I/O Timings: shared read=0.026
   ->  Bitmap Index Scan on qq  (cost=0.00..34.30 rows=25 width=0) (actual time=0.143..0.144 rows=1.00 loops=1)
         Index Cond: (the_array @> '{1,2}'::integer[])
         Buffers: shared hit=4 read=5
         I/O Timings: shared read=0.023
 Planning:
   Buffers: shared hit=5 read=2 dirtied=1
   I/O Timings: shared read=0.015
 Planning Time: 0.200 ms
 Execution Time: 0.176 ms
(14 rows)