Postgresql

提高 COUNT、JOIN 和 GROUP BY 查詢性能

  • June 14, 2020

我正在嘗試獲取給定使用者的每組計數並在下面編寫查詢。執行計劃看起來不錯。但是，在某些情況下，使用者有大約 10k 篇文章，並且在載入一點時，查詢開始變得非常慢並且 CPU 使用率上升。

如何提高以下查詢的性能？

我正在使用 PostgreSQL 11。

SELECT COUNT(*) AS occurences, seller_nickname AS value, seller_id AS id 
FROM data.saved_articles 
JOIN aggregated.offers USING (offer_id) 
WHERE user_id = 406943491 
GROUP BY seller_nickname, seller_id;

範例結果集：

occurences    value     id
1             "nick1"   id-1
8             "nick2"   id-2

查詢計劃：

Finalize GroupAggregate  (cost=50262.89..51401.41 rows=9427 width=23) (actual time=58.418..68.209 rows=3527 loops=1)
 Output: count(*), offers.seller_nickname, offers.seller_id
 Group Key: offers.seller_nickname, offers.seller_id
 Buffers: shared hit=17448 dirtied=75
 ->  Gather Merge  (cost=50262.89..51248.22 rows=7856 width=23) (actual time=58.413..66.732 rows=4549 loops=1)
       Output: offers.seller_nickname, offers.seller_id, (PARTIAL count(*))
       Workers Planned: 2
       Workers Launched: 2
       Buffers: shared hit=37116 dirtied=150
       ->  Partial GroupAggregate  (cost=49262.86..49341.42 rows=3928 width=23) (actual time=45.467..46.616 rows=1516 loops=3)
             Output: offers.seller_nickname, offers.seller_id, PARTIAL count(*)
             Group Key: offers.seller_nickname, offers.seller_id
             Buffers: shared hit=37116 dirtied=150
             Worker 0: actual time=31.676..32.206 rows=726 loops=1
               Buffers: shared hit=4337 dirtied=16
             Worker 1: actual time=46.740..48.138 rows=1841 loops=1
               Buffers: shared hit=15331 dirtied=59
             ->  Sort  (cost=49262.86..49272.68 rows=3928 width=15) (actual time=45.458..45.738 rows=3073 loops=3)
                   Output: offers.seller_nickname, offers.seller_id
                   Sort Key: offers.seller_nickname, offers.seller_id
                   Sort Method: quicksort  Memory: 436kB
                   Worker 0:  Sort Method: quicksort  Memory: 110kB
                   Worker 1:  Sort Method: quicksort  Memory: 314kB
                   Buffers: shared hit=37116 dirtied=150
                   Worker 0: actual time=31.666..31.759 rows=1077 loops=1
                     Buffers: shared hit=4337 dirtied=16
                   Worker 1: actual time=46.732..47.088 rows=3811 loops=1
                     Buffers: shared hit=15331 dirtied=59
                   ->  Nested Loop  (cost=229.92..49028.37 rows=3928 width=15) (actual time=0.407..35.412 rows=3073 loops=3)
                         Output: offers.seller_nickname, offers.seller_id
                         Inner Unique: true
                         Buffers: shared hit=37086 dirtied=150
                         Worker 0: actual time=0.096..28.787 rows=1077 loops=1
                           Buffers: shared hit=4322 dirtied=16
                         Worker 1: actual time=0.103..36.065 rows=3811 loops=1
                           Buffers: shared hit=15316 dirtied=59
                         ->  Parallel Bitmap Heap Scan on data.saved_articles  (cost=229.49..21594.86 rows=3928 width=4) (actual time=0.368..1.219 rows=3073 loops=3)
                               Output: saved_articles.offer_id
                               Recheck Cond: (saved_articles.user_id = 406943491)
                               Heap Blocks: exact=67
                               Buffers: shared hit=178
                               Worker 0: actual time=0.056..0.384 rows=1077 loops=1
                                 Buffers: shared hit=12
                               Worker 1: actual time=0.061..1.154 rows=3811 loops=1
                                 Buffers: shared hit=59
                               ->  Bitmap Index Scan on pk_saved_articles_user_id_offer_id  (cost=0.00..227.13 rows=9427 width=0) (actual time=0.941..0.941 rows=9220 loops=1)
                                     Index Cond: (saved_articles.user_id = 406943491)
                                     Buffers: shared hit=40
                         ->  Index Scan using pk_offers_offer_id on aggregated.offers  (cost=0.43..6.98 rows=1 width=19) (actual time=0.011..0.011 rows=1 loops=9220)
                               Output: offers.offer_id, offers.seller_id, offers.seller_nickname, offers.title, offers.condition, offers.ends_at, offers.current_price, offers.buynow_price, offers.category_id, offers.category_name_fr, offers.category_name_de, offers.category_name_it, offers.created_at, offers.updated_at, offers.bid_count, offers.quantity, offers.increment, offers.offer_type
                               Index Cond: (offers.offer_id = saved_articles.offer_id)
                               Buffers: shared hit=36908 dirtied=150
                               Worker 0: actual time=0.026..0.026 rows=1 loops=1077
                                 Buffers: shared hit=4310 dirtied=16
                               Worker 1: actual time=0.009..0.009 rows=1 loops=3811
                                 Buffers: shared hit=15257 dirtied=59
Planning Time: 0.274 ms
Execution Time: 68.714 ms

為了提高性能，我可能會將結果集限制為每組 100 個。

表和索引：

CREATE TABLE aggregated.offers (
   offer_id integer NOT NULL,
   seller_id integer NOT NULL,
   seller_nickname character varying(30) NOT NULL,
   title character varying(60) NOT NULL
);

ALTER TABLE aggregated.offers
   ADD CONSTRAINT pk_offers_offer_id PRIMARY KEY (offer_id);

CREATE INDEX idx_offers_seller_id ON aggregated.offers (seller_id);

CREATE TABLE data.saved_articles (
   user_id integer NOT NULL,
   offer_id integer NOT NULL,
   created_at timestamp with time zone NOT NULL,
   updated_at timestamp with time zone,
   CONSTRAINT pk_saved_articles_user_id_offer_id PRIMARY KEY (user_id, offer_id),
   CONSTRAINT fk_saved_articles_offer_id FOREIGN KEY (offer_id)
       REFERENCES aggregated.offers (offer_id) MATCH SIMPLE
       ON UPDATE NO ACTION
       ON DELETE NO ACTION
)

CREATE INDEX idx_saved_articles_offer_id ON data.saved_articles (offer_id);

編輯：

從最簡單的方法開始，我執行VACUUM並創建了一個索引：

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT COUNT(*) AS occurences, seller_nickname AS value, seller_id AS id 
FROM data.saved_articles 
JOIN aggregated.offers USING (offer_id) 
WHERE user_id = 406943491 
GROUP BY seller_nickname, seller_id

但是，它似乎沒有任何重大影響。

計劃：

Finalize GroupAggregate  (cost=54772.66..56138.73 rows=11312 width=23) (actual time=54.118..61.137 rows=3527 loops=1)
 Output: count(*), offers.seller_nickname, offers.seller_id
 Group Key: offers.seller_nickname, offers.seller_id
 Buffers: shared hit=17925
 ->  Gather Merge  (cost=54772.66..55954.92 rows=9426 width=23) (actual time=54.112..61.691 rows=4482 loops=1)
       Output: offers.seller_nickname, offers.seller_id, (PARTIAL count(*))
       Workers Planned: 2
       Workers Launched: 2
       Buffers: shared hit=38070
       ->  Partial GroupAggregate  (cost=53772.64..53866.90 rows=4713 width=23) (actual time=40.581..41.995 rows=1494 loops=3)
             Output: offers.seller_nickname, offers.seller_id, PARTIAL count(*)
             Group Key: offers.seller_nickname, offers.seller_id
             Buffers: shared hit=38070
             Worker 0: actual time=43.304..45.268 rows=1882 loops=1
               Buffers: shared hit=16389
             Worker 1: actual time=24.626..25.086 rows=603 loops=1
               Buffers: shared hit=3756
             ->  Sort  (cost=53772.64..53784.42 rows=4713 width=15) (actual time=40.571..40.921 rows=3073 loops=3)
                   Output: offers.seller_nickname, offers.seller_id
                   Sort Key: offers.seller_nickname, offers.seller_id
                   Sort Method: quicksort  Memory: 437kB
                   Worker 0:  Sort Method: quicksort  Memory: 323kB
                   Worker 1:  Sort Method: quicksort  Memory: 76kB
                   Buffers: shared hit=38070
                   Worker 0: actual time=43.293..43.759 rows=3969 loops=1
                     Buffers: shared hit=16389
                   Worker 1: actual time=24.616..24.709 rows=913 loops=1
                     Buffers: shared hit=3756
                   ->  Nested Loop  (cost=216.53..53485.09 rows=4713 width=15) (actual time=0.457..30.793 rows=3073 loops=3)
                         Output: offers.seller_nickname, offers.seller_id
                         Inner Unique: true
                         Buffers: shared hit=38040
                         Worker 0: actual time=0.191..32.516 rows=3969 loops=1
                           Buffers: shared hit=16374
                         Worker 1: actual time=0.171..22.287 rows=913 loops=1
                           Buffers: shared hit=3741
                         ->  Parallel Bitmap Heap Scan on data.saved_articles  (cost=216.10..24104.73 rows=4713 width=4) (actual time=0.371..1.275 rows=3073 loops=3)
                               Output: saved_articles.offer_id
                               Recheck Cond: (saved_articles.user_id = 406943491)
                               Heap Blocks: exact=60
                               Buffers: shared hit=166
                               Worker 0: actual time=0.075..1.420 rows=3969 loops=1
                                 Buffers: shared hit=66
                               Worker 1: actual time=0.059..0.344 rows=913 loops=1
                                 Buffers: shared hit=12
                               ->  Bitmap Index Scan on pk_saved_articles_user_id_offer_id  (cost=0.00..213.27 rows=11312 width=0) (actual time=0.929..0.929 rows=9220 loops=1)
                                     Index Cond: (saved_articles.user_id = 406943491)
                                     Buffers: shared hit=28
                         ->  Index Only Scan using tmp_id on aggregated.offers  (cost=0.43..6.23 rows=1 width=19) (actual time=0.009..0.009 rows=1 loops=9220)
                               Output: offers.offer_id, offers.seller_id, offers.seller_nickname
                               Index Cond: (offers.offer_id = saved_articles.offer_id)
                               Heap Fetches: 9129
                               Buffers: shared hit=37874
                               Worker 0: actual time=0.007..0.007 rows=1 loops=3969
                                 Buffers: shared hit=16308
                               Worker 1: actual time=0.023..0.023 rows=1 loops=913
                                 Buffers: shared hit=3729
Planning Time: 0.275 ms
Execution Time: 63.720 ms

由於我需要在另一列上執行相同的查詢，因此我不會觸及 PK，而是嘗試seller_nickname在不同的表上進行聚合，以便能夠在聚合後添加它。

您的查詢似乎已經執行正常。擠出更快時間的一些想法：

僅索引掃描aggregated.offers

seller_nickname似乎在功能上依賴於seller_id. varchar(30)額外讀取和分組 a 比僅基於a 更昂貴integer。刪除seller_nickname基本查詢應該會使其更快。然後，您可以使用子句添加seller_id到 PKINCLUDE以獲取覆蓋索引和非常快速的僅索引掃描aggregated.offers：

ALTER TABLE aggregated.offers
 DROP CONSTRAINT pk_offers_offer_id 
, ADD  CONSTRAINT pk_offers_offer_id PRIMARY KEY (offer_id) INCLUDE (seller_id);

因為我們都offer_id達到了局部最優。看：seller_id``int4

更快的查詢：

SELECT count(*) AS occurrences, o.seller_id AS id  -- note occurrences with "rr"
FROM   data.saved_articles a
JOIN   aggregated.offers   o USING (offer_id) 
WHERE  a.user_id = 406943491 
GROUP  BY o.seller_id;

如果您確實需要包含seller_nickname在結果中，請在查找表的聚合*之後添加它。*假設一個表seller（你通常會擁有），理想情況下有一個索引(seller_id, seller_nickname)用於另一個僅索引掃描：

SELECT ao.*, s.seller_nickname
FROM  (  -- query from above
  SELECT count(*) AS occurrences, o.seller_id AS id
  FROM   data.saved_articles a
  JOIN   aggregated.offers   o USING (offer_id) 
  WHERE  a.user_id = 406943491 
  GROUP  BY o.seller_id
  ) ao
LEFT   JOIN data.seller s USING (seller_id);

應該仍然更快，尤其是結果中每個賣家的行數超過幾行。

或者，您可以在子句中添加seller_nicknamePK 索引INCLUDE以加快原始查詢。或者保持 PK 不變並在(offer_id, seller_id, seller_nickname). 但是，雖然seller_id在 PK 索引中包含整數列是非常輕量級的，並且通常幾乎沒有任何缺點（請閱讀連結的答案！），但添加另一個列varchar(30)的成本更高。並且您會希望保持 PK 索引快速用於其他目的。

autovacuum為了data.saved_articles

您的 PK 指數(user_id, offer_id)似乎非常適合這項任務。但是，我看到一個Bitmap Index Scan on pk_saved_articles_user_id_offer_idwith rows=9220，然後是一個Parallel Bitmap Heap Scan on data.saved_articleswith rows=3073- 吞下約 40% 的查詢時間。

沒有僅索引掃描。只有三分之一的索引元組產生一個堆元組。這表明 autovacuum 存在問題：過時的可見性映射和大量死索引元組。如果可能（在表上獲取排他鎖，並發工作負載是否允許？），嘗試：

VACUUM FULL ANALYZE data.saved_articles;

或用於在物理儲存中CLUSTER對具有相同的行進行分群。user_id

CLUSTER data.saved_articles USING pk_saved_articles_user_id_offer_id;
ANALYZE data.saved_articles;

然後，即使寫入負載saved_articles阻止了僅索引掃描，也可以從堆中讀取更少的塊。但這是一種一次性的效果，隨著時間的推移會逐漸惡化。

無論哪種方式，如果這樣可以提高性能，請autovacuum為 table 設置更積極的設置data.saved_articles和/或考慮使用pg_repackor pg_squeeze- 它可以替換VACUUM FULL/CLUSTER並在並發負載下工作。

有關的：

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

相關問答

Postgresql

如何更快地搜尋一個有 8000 萬條記錄的表？

  • June 25, 2020
檢視問答
Postgresql

優化查詢以在分區表中跨多天獲取數據

  • August 4, 2022
檢視問答
Postgresql

具有大 IN 的 Postgres 查詢，並且在臨時表上加入似乎不起作用

  • July 7, 2022
檢視問答
Postgresql

排除目前行會削弱視窗函式的性能

  • July 5, 2022
檢視問答
Postgresql

這是 DELETE … WHERE EXISTS 查詢計劃 O(n*2) 還是 O(n^2)？

  • July 4, 2022
檢視問答
Postgresql

提高 postgres 中的查詢性能

  • March 29, 2022
檢視問答