sql - 并行查询执行正在减慢 Postgresql

Question

配置：

Postgres version: `PostgreSQL 10.6`
Processor Name: Intel Core i7
Processor Speed:    2.2 GHz
Number of Processors:   1
Total Number of Cores:  4
L2 Cache (per Core):    256 KB
L3 Cache:   6 MB
Hyper-Threading Technology: Enabled
Memory: 16 GB

使用相同的转储和 Postgresql 版本在本地和生产上运行相同的查询。但是在本地，它使用多个工作人员并使查询速度变慢。这里发生了什么？为什么会这样？如何使本地使用单个工人从而使其更快？

本地查询：

explain SELECT COUNT(*) FROM "hogwarts" WHERE "hogwarts"."destroy_marker" = true AND ("hogwarts"."id" NOT IN (SELECT "mapped_hogwarts"."hogwart_id" FROM "mapped_hogwarts"));

Finalize Aggregate  (cost=123880322.67..123880322.68 rows=1 width=8)  
->  Gather  (cost=123880322.46..123880322.67 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=123879322.46..123879322.47 rows=1 width=8)
               ->  Parallel Seq Scan on hogwarts  (cost=0.00..123879309.80 rows=5063 width=0)
                     Filter: (destroy_marker AND (NOT (SubPlan 1)))
                     SubPlan 1
                       ->  Materialize  (cost=0.00..3467.60 rows=132040 width=4)
                             ->  Seq Scan on mapped_hogwarts  (cost=0.00..2291.40 rows=132040 width=4)

生产查询

explain SELECT COUNT(*) FROM "hogwarts" WHERE "hogwarts"."destroy_marker" = true AND ("hogwarts"."id" NOT IN (SELECT "mapped_hogwarts"."hogwart_id" FROM "mapped_hogwarts"));

 Aggregate  (cost=10678.88..10678.88 rows=1 width=8)
   ->  Seq Scan on hogwarts  (cost=1417.00..10673.74 rows=10288 width=0)
         Filter: (destroy_marker AND (NOT (hashed SubPlan 1)))
         SubPlan 1
           ->  Seq Scan on mapped_hogwarts  (cost=0.00..1353.86 rows=126285 width=4)

有关在本地运行的查询（例如（分析、缓冲区、详细））的更多信息：

 Finalize Aggregate  (cost=123880322.52..123880322.53 rows=1 width=8) (actual time=635589.193..635589.193 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=13004652 read=8396, temp read=2409553 written=226
   ->  Gather  (cost=123880322.31..123880322.52 rows=2 width=8) (actual time=635480.878..635590.731 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=13004652 read=8396, temp read=2409553 written=226
         ->  Partial Aggregate  (cost=123879322.31..123879322.32 rows=1 width=8) (actual time=635504.402..635504.402 rows=1 loops=3)
               Output: PARTIAL count(*)
               Buffers: shared hit=13004652 read=8396, temp read=2409553 written=226
               Worker 0: actual time=635451.409..635451.409 rows=1 loops=1
                 Buffers: shared hit=6503547 read=2245
               Worker 1: actual time=635581.310..635581.310 rows=1 loops=1
                 Buffers: shared hit=6500035 read=2360
               ->  Parallel Seq Scan on public.hogwarts  (cost=0.00..123879309.80 rows=5002 width=0) (actual time=119.355..635491.922 rows=8017 loops=3)
                     Output: hogwarts.id, hogwarts.key, hogwarts.uuid, hogwarts.hogwart_data, hogwarts.destroy_marker, hogwarts.created_at, hogwarts.updated_at, hogwarts.content_title_text, hogwarts.source_type, hogwarts.source_id
                     Filter: (hogwarts.destroy_marker AND (NOT (SubPlan 1)))
                     Rows Removed by Filter: 44170
                     Buffers: shared hit=13004652 read=8396, temp read=2409553 written=226
                     Worker 0: actual time=105.015..635442.437 rows=6697 loops=1
                       Buffers: shared hit=6503547 read=2245
                     Worker 1: actual time=108.193..635571.569 rows=6693 loops=1
                       Buffers: shared hit=6500035 read=2360
                     SubPlan 1
                       ->  Materialize  (cost=0.00..3467.60 rows=132040 width=4) (actual time=0.007..52.750 rows=132032 loops=24056)
                             Output: mapped_hogwarts.hogwart_id
                             Buffers: shared hit=13004451, temp read=2409553 written=226
                             Worker 0: actual time=0.008..69.863 rows=132035 loops=6698
                               Buffers: shared hit=6503494
                             Worker 1: actual time=0.008..69.807 rows=132023 loops=6695
                               Buffers: shared hit=6499986
                             ->  Seq Scan on public.mapped_hogwarts  (cost=0.00..2291.40 rows=132040 width=4) (actual time=0.007..38.505 rows=132029 loops=13394)
                                   Output: mapped_hogwarts.hogwart_id
                                   Buffers: shared hit=13004451
                                   Worker 0: actual time=0.007..38.535 rows=132035 loops=6698
                                     Buffers: shared hit=6503494
                                   Worker 1: actual time=0.007..38.474 rows=132023 loops=6695
                                     Buffers: shared hit=6499986
 Planning Time: 0.996 ms
 Execution Time: 635591.329 ms

https://github.com/suryapandian/weird/blob/master/db/hogwarts_json.json

标签： sqlpostgresqlparallel-processing