min_parallel_table_scan_size

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

min_parallel_table_scan_size 的演示示例

postgres=# CREATE TABLE foo (id int, val text);
CREATE TABLE

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

此时，表 foo 包含的数据少于 8MB，因此没有启动并行扫描

postgres=# \dt+ foo 
                                     List of tables
 Schema | Name | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+------+-------+----------+-------------+---------------+---------+-------------
 public | foo  | table | postgres | permanent   | heap          | 6704 kB | 
(1 row)

postgres=# EXPLAIN ANALYZE SELECT * FROM foo WHERE id % 2 = 1;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..2334.00 rows=500 width=33) (actual time=0.018..18.427 rows=50000 loops=1)
   Filter: ((id % 2) = 1)
   Rows Removed by Filter: 50000
   Buffers: shared hit=834
 Planning Time: 0.054 ms
 Execution Time: 21.612 ms
(6 rows)

插入更多数据后，表大小超过 8MB，则会启动并行扫描

postgres=# INSERT INTO foo values(generate_series(100001,200000), clock_timestamp());
INSERT 0 100000

postgres=# \dt+ foo 
                                    List of tables
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description 
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | foo  | table | postgres | permanent   | heap          | 13 MB | 
(1 row)

postgres=# EXPLAIN ANALYZE SELECT * FROM foo WHERE id % 2 = 1;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..4530.55 rows=999 width=33) (actual time=0.905..32.689 rows=100000 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Buffers: shared hit=1667
   ->  Parallel Seq Scan on foo  (cost=0.00..3430.65 rows=588 width=33) (actual time=0.021..18.552 rows=50000 loops=2)
         Filter: ((id % 2) = 1)
         Rows Removed by Filter: 50000
         Buffers: shared hit=1667
 Planning Time: 0.138 ms
 Execution Time: 37.802 ms
(10 rows)

• PostgreSQL 文档: min_parallel_table_scan_size