[ad_1]
My PostgreSQL Version 13. and below is parallel related parameters.
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 |
When I Run bleow Query, It works good. (only 3 secs)
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
But, Without “EXPLAIN Analyze” That Query not use parallel process, when i see the pg_stat_activity. Only 1 process works. so Elapsed time is double. (6 secs)
T1 Table size is 3GB.
Thany for your help.
[ad_2]