min_parallel_index_scan_size

• 19
• 18
• 17
• 16
• 15
• 14
• 13
• 12
• 11
• 10

min_parallel_index_scan_size 的基本用法示例

postgres=# CREATE TABLE foo (id INT);
CREATE TABLE

postgres=# CREATE UNIQUE INDEX foo_ix ON foo(id);
CREATE INDEX

postgres=# INSERT INTO foo VALUES (generate_series(1,1000));
INSERT 0 1000

此时创建的索引远小于 min_parallel_index_scan_size，因此不使用并行扫描

postgres=# \di+ foo_ix 
                                        List of indexes
 Schema |  Name  | Type  |  Owner   | Table | Persistence | Access method | Size  | Description 
--------+--------+-------+----------+-------+-------------+---------------+-------+-------------
 public | foo_ix | index | postgres | foo   | permanent   | btree         | 40 kB | 
(1 row)

postgres=# EXPLAIN SELECT * FROM foo WHERE id % 2 = 1;
                     QUERY PLAN                     
----------------------------------------------------
 Seq Scan on foo  (cost=0.00..20.00 rows=5 width=4)
   Filter: ((id % 2) = 1)
(2 rows)

一旦索引大小超过 min_parallel_index_scan_size，将启动并行扫描

postgres=# INSERT INTO foo VALUES (generate_series(1001,1000000));
INSERT 0 999000

postgres=# \di+ foo_ix 
                                        List of indexes
 Schema |  Name  | Type  |  Owner   | Table | Persistence | Access method | Size  | Description 
--------+--------+-------+----------+-------+-------------+---------------+-------+-------------
 public | foo_ix | index | postgres | foo   | permanent   | btree         | 21 MB | 
(1 row)

postgres=# EXPLAIN SELECT * FROM foo WHERE id % 2 = 1;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Gather  (cost=1000.00..12653.00 rows=5000 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on foo  (cost=0.00..11153.00 rows=2083 width=4)
         Filter: ((id % 2) = 1)
(4 rows)

• PostgreSQL 文档: min_parallel_index_scan_size