Postgresql

具有 35m 行慢查詢的 Postgres 表。我怎樣才能提高性能？

  • August 28, 2019
SELECT count(c.id) as clickCount 
FROM clicks c 
LEFT JOIN links l on c.link_id = l.id 
LEFT JOIN user_agents ua on c.user_agent_id = ua.id 
                       AND ua.robot IS NULL 
WHERE l.user_id = ?

                                                                    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=337670.79..337670.80 rows=1 width=8) (actual time=3508.630..3508.630 rows=1 loops=1)
  Buffers: shared hit=448334
  ->  Nested Loop  (cost=0.84..334057.39 rows=1445360 width=4) (actual time=0.041..3040.606 rows=6110334 loops=1)
        Buffers: shared hit=448334
        ->  Index Scan using link_user_idx on links l  (cost=0.28..73.63 rows=136 width=4) (actual time=0.017..0.093 rows=136 loops=1)
              Index Cond: (user_id = 1125)
              Buffers: shared hit=24
        ->  Index Scan using click_link_idx on clicks c  (cost=0.56..2208.66 rows=24710 width=12) (actual time=0.003..16.136 rows=44929 loops=136)
              Index Cond: (link_id = l.id)
              Buffers: shared hit=448310
Planning Time: 0.512 ms
Execution Time: 3508.683 ms

3.5 秒

SELECT count(c.id) as clickCount, l.location 
FROM clicks c 
LEFT JOIN links l on c.link_id = l.id 
LEFT JOIN user_agents ua on c.user_agent_id = ua.id 
                       AND ua.robot IS NULL 
WHERE l.user_id = 1125 
GROUP BY l.location 
ORDER BY clickCount, location DESC



                                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=495348.44..495348.49 rows=18 width=524) (actual time=1978.261..1978.261 rows=1 loops=1)
  Sort Key: (count(c.id)), l.location DESC
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=23740 read=38820
  ->  Finalize GroupAggregate  (cost=495334.17..495348.07 rows=18 width=524) (actual time=1978.251..1978.251 rows=1 loops=1)
        Group Key: l.location
        Buffers: shared hit=23740 read=38820
        ->  Gather Merge  (cost=495334.17..495347.35 rows=108 width=524) (actual time=1978.231..1997.869 rows=7 loops=1)
              Workers Planned: 6
              Workers Launched: 6
              Buffers: shared hit=153563 read=257052
              ->  Sort  (cost=494334.08..494334.12 rows=18 width=524) (actual time=1956.989..1956.990 rows=1 loops=7)
                    Sort Key: l.location DESC
                    Sort Method: quicksort  Memory: 25kB
                    Worker 0:  Sort Method: quicksort  Memory: 25kB
                    Worker 1:  Sort Method: quicksort  Memory: 25kB
                    Worker 2:  Sort Method: quicksort  Memory: 25kB
                    Worker 3:  Sort Method: quicksort  Memory: 25kB
                    Worker 4:  Sort Method: quicksort  Memory: 25kB
                    Worker 5:  Sort Method: quicksort  Memory: 25kB
                    Buffers: shared hit=153563 read=257052
                    ->  Partial HashAggregate  (cost=494333.52..494333.70 rows=18 width=524) (actual time=1956.955..1956.956 rows=1 loops=7)
                          Group Key: l.location
                          Buffers: shared hit=153515 read=257052
                          ->  Hash Join  (cost=22.52..494165.25 rows=33654 width=520) (actual time=42.709..1738.826 rows=872905 loops=7)
                                Hash Cond: (c.link_id = l.id)
                                Buffers: shared hit=153515 read=257052
                                ->  Parallel Seq Scan on clicks c  (cost=0.00..476696.35 rows=6638735 width=12) (actual time=0.045..884.436 rows=5690344 loops=7)
                                      Buffers: shared hit=153257 read=257052
                                ->  Hash  (cost=22.29..22.29 rows=19 width=520) (actual time=0.219..0.219 rows=136 loops=7)
                                      Buckets: 1024  Batches: 1  Memory Usage: 15kB
                                      Buffers: shared hit=174
                                      ->  Bitmap Heap Scan on links l  (cost=1.53..22.29 rows=19 width=520) (actual time=0.050..0.186 rows=136 loops=7)
                                            Recheck Cond: (user_id = 1125)
                                            Heap Blocks: exact=22
                                            Buffers: shared hit=174
                                            ->  Bitmap Index Scan on link_user_idx  (cost=0.00..1.52 rows=19 width=0) (actual time=0.036..0.036 rows=136 loops=7)
                                                  Index Cond: (user_id = 1125)
                                                  Buffers: shared hit=20
Planning Time: 0.334 ms
Execution Time: 1998.071 ms

1.9 秒

SELECT sum(r.amount * c.revshare_influencer) as amount, r.month as month, r.year as year 
FROM revenue r 
JOIN clicks c on c.id = r.click_id 
WHERE r.user_id = 1125 
GROUP BY r.year, r.month 
ORDER BY r.year desc, r.month desc



                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate  (cost=57546.34..62229.59 rows=22711 width=40) (actual time=2765.425..2914.399 rows=13 loops=1)
  Group Key: r.year, r.month
  Buffers: shared hit=1755012 read=8976, temp read=1198 written=1204
  ->  Gather Merge  (cost=57546.34..61621.83 rows=32388 width=40) (actual time=2762.219..2936.577 rows=52 loops=1)
        Workers Planned: 3
        Workers Launched: 3
        Buffers: shared hit=6407432 read=20902, temp read=4374 written=4398
        ->  Partial GroupAggregate  (cost=56546.30..56816.20 rows=10796 width=40) (actual time=2749.492..2865.023 rows=13 loops=4)
              Group Key: r.year, r.month
              Buffers: shared hit=6407432 read=20902, temp read=4374 written=4398
              ->  Sort  (cost=56546.30..56573.29 rows=10796 width=52) (actual time=2681.282..2736.792 rows=318604 loops=4)
                    Sort Key: r.year DESC, r.month DESC
                    Sort Method: external merge  Disk: 9584kB
                    Worker 0:  Sort Method: external merge  Disk: 8480kB
                    Worker 1:  Sort Method: external merge  Disk: 8608kB
                    Worker 2:  Sort Method: external merge  Disk: 8320kB
                    Buffers: shared hit=6407432 read=20902, temp read=4374 written=4398
                    ->  Nested Loop  (cost=361.57..55823.06 rows=10796 width=52) (actual time=113.962..2503.475 rows=318604 loops=4)
                          Buffers: shared hit=6407411 read=20902
                          ->  Parallel Bitmap Heap Scan on revenue r  (cost=361.01..27825.97 rows=10796 width=40) (actual time=113.683..358.398 rows=318604 loops=4)
                                Recheck Cond: (user_id = 1125)
                                Heap Blocks: exact=14271
                                Buffers: shared hit=37228 read=15519
                                ->  Bitmap Index Scan on revenue_user_idx  (cost=0.00..352.64 rows=33468 width=0) (actual time=104.289..104.289 rows=1274414 loops=1)
                                      Index Cond: (user_id = 1125)
                                      Buffers: shared read=3485
                          ->  Index Scan using clicks_idx_id on clicks c  (cost=0.56..2.59 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=1274414)
                                Index Cond: (id = r.click_id)
                                Buffers: shared hit=6370183 read=5383
Planning Time: 0.369 ms
Execution Time: 2939.455 ms

2.9 秒

表clicks包含 35m 行，表revenue包含 56m 行。伺服器是 linode 32gb 16 核 ubuntu。

我對此做了什麼？

它對我來說是 mysql，但之前沒有處理過這些數據。我一直在研究提高這個數據庫的性能。我一直在調整 postgres 的性能。有 0 個改進（work_mem,shared_buffers）並嘗試理解我不太擅長的查詢解釋器。

我正在考慮表分區/物化視圖，但我也讀過表分區可能無效。或者調整這個查詢會帶來至少 50% 的性能提升？由於這些表將來會堆積更多，克服這個問題的理想方法是什麼？

Postgres 版本： PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit

Postgres 設置：

max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16

到目前為止有幫助的事情（從〜10-15s到〜2-4s）：

  • 將 Postgres 從升級9.5到11.5（允許並行化，也可能有其他好處）
  • 去除不必要的DISTINCT

一些進一步的想法：

  1. 研究如何允許 Postgres 選擇僅索引掃描，而不是最慢的索引掃描花費最多時間（注意，多列索引的列順序至關重要）。
  2. 如果您可以鼓勵使用散列或合併連接來代替嵌套循環，它們可能會更快。這些列的更好的統計數據可能會有所幫助，甚至可能是多變數的。它目前低估了 4 倍的行數，因此可能會選擇具有更好資訊的不同計劃。值得注意的是，您可能還需要額外的排序索引來啟用合併連接。有關擴展統計資訊和加入操作的更多資訊。

此外，還有一些工具可以讓您更輕鬆地理解和共享查詢執行計劃。

免責聲明：我在最新列出的 pgMustard 上工作。

引用自：https://dba.stackexchange.com/questions/245217

相關問答

Postgresql

類似查詢的執行性能問題

  • April 2, 2022
檢視問答
Postgresql

PostgreSQL - 多列 B-Tree 索引如何與第一列的 order by 和第二列的 IN 查找一起工作？

  • January 13, 2022
檢視問答
Postgresql

更新可被 100 整除的列值

  • December 14, 2018
檢視問答
Postgresql

求和運算怎麼可能比計數快得多？

  • December 6, 2018
檢視問答
Postgresql

更新“最舊”行

  • November 18, 2018
檢視問答
Postgresql

如何提高大銷售表中不同供應商的查詢性能？

  • July 25, 2018
檢視問答