PostgreSQL并行查询只在分析解释时起作用。没有分析解释，单个过程起作用。

Question

我的PostgreSQL版本13.和下面是并行相关的参数。

SELECT name, setting FROM pg_Settings WHERE name LIKE '%parallel%'

name                            |setting|
--------------------------------+-------+
enable_parallel_append          |on     |
enable_parallel_hash            |on     |
force_parallel_mode             |off    |
max_parallel_maintenance_workers|4      |
max_parallel_workers            |96     |
max_parallel_workers_per_gather |2      |
min_parallel_index_scan_size    |64     |
min_parallel_table_scan_size    |1024   |
parallel_leader_participation   |on     |
parallel_setup_cost             |1000   |
parallel_tuple_cost             |0.1    |

当我运行bleow查询时，它工作得很好。(只有3秒)

EXPLAIN (analyze) 
SELECT   t1_code
        ,COUNT(1)  AS cnt
FROM     t1 a
WHERE    1=1
GROUP BY t1_code
      
      
    Finalize GroupAggregate  (cost=620185.13..620185.64 rows=2 width=12) (actual time=2953.797..3186.877 rows=2 loops=1)
      Group Key: t1_code
      ->  Gather Merge  (cost=620185.13..620185.60 rows=4 width=12) (actual time=2953.763..3186.835 rows=6 loops=1)
            Workers Planned: 2
            Workers Launched: 2
            ->  Sort  (cost=619185.11..619185.11 rows=2 width=12) (actual time=2926.805..2926.808 rows=2 loops=3)
                  Sort Key: t1_code
                  Sort Method: quicksort  Memory: 25kB
                  Worker 0:  Sort Method: quicksort  Memory: 25kB
                  Worker 1:  Sort Method: quicksort  Memory: 25kB
                  ->  Partial HashAggregate  (cost=619185.08..619185.10 rows=2 width=12) (actual time=2926.763..2926.768 rows=2 loops=3)
                        Group Key: t1_code
                        Batches: 1  Memory Usage: 24kB
                        Worker 0:  Batches: 1  Memory Usage: 24kB
                        Worker 1:  Batches: 1  Memory Usage: 24kB
                        ->  Parallel Seq Scan on t1 a  (cost=0.00..551015.72 rows=13633872 width=4) (actual time=0.017..1412.845 rows=10907098 loops=3)
    Planning Time: 1.295 ms
    JIT:
      Functions: 21
      Options: Inlining true, Optimization true, Expressions true, Deforming true
      Timing: Generation 2.595 ms, Inlining 156.371 ms, Optimization 112.165 ms, Emission 63.886 ms, Total 335.017 ms
    Execution Time: 3243.358 ms 

但是，在没有“解释分析”的情况下，当我看到pg_stat_activity时，查询不使用并行进程。只有一个过程起作用。所以经过的时间是双倍的。(6秒)

T1表大小为3GB。

谢谢你的帮助。

<<下面的另一个测试>>是详细缓冲区选项的结果。以同样的方式，在没有分析的情况下，下面的查询使用单个进程。而orce_parallel_mode对ON没有影响。

EXPLAIN(ANALYZE, VERBOSE, BUFFERS)
SELECT COUNT(*) 
FROM (
     SELECT 
           ID
      FROM T2
     WHERE CODE1 <> '003'
       AND CODE2     <> 'Y'
       AND CODE3      <> 'Y'
     GROUP BY ID  
     ) t1 ;
      
Aggregate  (cost=204350.48..204350.49 rows=1 width=8) (actual time=2229.919..2229.997 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=216 read=140248 dirtied=10
  I/O Timings: read=1404.532
  ->  Finalize HashAggregate  (cost=202326.98..203226.31 rows=89933 width=14) (actual time=2128.682..2199.811 rows=605244 loops=1)
        Output: T2.ID
        Group Key: T2.ID
        Batches: 1  Memory Usage: 53265kB
        Buffers: shared hit=216 read=140248 dirtied=10
        I/O Timings: read=1404.532
        ->  Gather  (cost=182991.39..201877.32 rows=179866 width=14) (actual time=1632.564..1817.564 rows=1019855 loops=1)
              Output: T2.ID
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=216 read=140248 dirtied=10
              I/O Timings: read=1404.532
              ->  Partial HashAggregate  (cost=181991.39..182890.72 rows=89933 width=14) (actual time=1592.762..1643.902 rows=339952 loops=3)
                    Output: T2.ID
                    Group Key: T2.ID
                    Batches: 1  Memory Usage: 32785kB
                    Buffers: shared hit=216 read=140248 dirtied=10
                    I/O Timings: read=1404.532
                    Worker 0:  actual time=1572.928..1624.075 rows=327133 loops=1
                      Batches: 1  Memory Usage: 28689kB
                      JIT:
                        Functions: 8
                        Options: Inlining false, Optimization false, Expressions true, Deforming true
                        Timing: Generation 1.203 ms, Inlining 0.000 ms, Optimization 0.683 ms, Emission 9.159 ms, Total 11.046 ms
                      Buffers: shared hit=72 read=43679 dirtied=2
                      I/O Timings: read=470.405
                    Worker 1:  actual time=1573.005..1619.235 rows=330930 loops=1
                      Batches: 1  Memory Usage: 28689kB
                      JIT:
                        Functions: 8
                        Options: Inlining false, Optimization false, Expressions true, Deforming true
                        Timing: Generation 1.207 ms, Inlining 0.000 ms, Optimization 0.673 ms, Emission 9.169 ms, Total 11.049 ms
                      Buffers: shared hit=63 read=44135 dirtied=6
                      I/O Timings: read=460.591
                    ->  Parallel Seq Scan on T2  (cost=0.00..176869.37 rows=2048806 width=14) (actual time=10.934..1166.528 rows=1638627 loops=3)
                          Filter: (((T2.CODE1)::text <> '003'::text) AND ((T2.CODE2)::text <> 'Y'::text) AND ((T2.CODE3)::text <> 'Y'::text))
                          Rows Removed by Filter: 24943
                          Buffers: shared hit=216 read=140248 dirtied=10
                          I/O Timings: read=1404.532
                          Worker 0:  actual time=10.083..1162.319 rows=1533436 loops=1
                            Buffers: shared hit=72 read=43679 dirtied=2
                            I/O Timings: read=470.405
                          Worker 1:  actual time=10.083..1161.430 rows=1561181 loops=1
                            Buffers: shared hit=63 read=44135 dirtied=6
                            I/O Timings: read=460.591
Planning:
  Buffers: shared hit=70
Planning Time: 0.253 ms
JIT:
  Functions: 31
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 4.451 ms, Inlining 0.000 ms, Optimization 2.182 ms, Emission 29.515 ms, Total 36.148 ms
Execution Time: 2234.037 ms

Answer 1

我解决了问题。这个问题是一个DBeaver问题。使用psql，将执行并行处理，并将DBeaver视为单个进程。在DBeaver中，并行处理是由CTRL+ALT+Shift+A执行的，但是，当执行Ctrl+Enter时，它被视为一个单独的进程。

Answer 2

当您运行解释分析时，您实际上是在执行查询，就好像它是输入到psql中一样。此外，没有返回给用户的结果，结果通过管道传输到/dev/null。在DBeaver中执行查询时，它使用驱动程序执行查询，并可能将获取大小设置为非零值，从而关闭并行查询。详情请参见https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html。