Postgres 慢 ROW_NUMBER() 函式

我在 postgres 中創建了下表。

test=# \d leaderboard_scores;
                                       Table "public.leaderboard_scores"
  Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
id         | integer                     |           | not null | nextval('leaderboard_scores_id_seq'::regclass)
user_id    | character varying(45)       |           | not null |
score      | integer                     |           |          |
created_at | timestamp without time zone |           |          |
Indexes:
   "leaderboard_scores_pkey" PRIMARY KEY, btree (id)
   "leaderboard_scores_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
   "score_back_user_id" btree (score DESC) INCLUDE (user_id)

我的表大小是 2M 行

我的案例是：

1. 獲取最高分數的使用者（執行速度很快）
2. 獲取特定使用者的排名

為了達到 2 號，我希望使用 row_number 視窗函式，但執行速度非常慢。

我正在使用的查詢

select user_id, row_number() over (order by score desc) from leaderboard_scores order by score desc offset 500000 limit 20;

這個查詢大約需要 900 毫秒，這對於給定使用者的排名來說太長了。

                                                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=52083.47..52085.56 rows=20 width=49) (actual time=1074.712..1074.755 rows=20 loops=1)
  ->  WindowAgg  (cost=0.43..208332.50 rows=1999999 width=49) (actual time=0.086..1043.536 rows=500020 loops=1)
        ->  Index Only Scan using score_back_user_id on leaderboard_scores  (cost=0.43..178332.52 rows=1999999 width=41) (actual time=0.074..807.340 rows=500021 loops=1)
              Heap Fetches: 500021
Planning Time: 0.097 ms
Execution Time: 1074.783 ms
(6 rows)

如何優化以獲得使用者的排名？

您說您想要特定使用者的排名，但這不是您的查詢所做的。

堆取數：500021

清理您的表以減少堆提取。如果表更新很多，您可能需要更改其 autovac 參數以保持 autovacuum 在其上執行足夠頻繁。

例如，

alter table leaderboard_scores set (autovacuum_vacuum_scale_factor =0);
alter table leaderboard_scores set (autovacuum_vacuum_threshold =<rel_pages / 10>);

哪裡<rel_pages / 10>需要手動計算並插入數值。

創建臨時表

成本顯然很高，導致處理的記錄數很高（行數=500021）。我認為即使沒有索引也不會慢很多。如果沒有 LIMIT，下面的總時間將是……讓我們說 2-3 秒？

DROP TABLE IF EXISTS temp_leaderboard_ranks;
SELECT user_id, row_number() OVER (ORDER BY score DESC)
 INTO temp_leaderboard_ranks
 FROM leaderboard_scores ORDER BY score DESC;
CREATE INDEX temp_lb_ranks_idx on temp_leaderboard_ranks(row_number)

或者其他的東西

另一種選擇是使用MATERIALIZED VIEW，大部分與上面相同，就像您可以在其上創建索引一樣，但如果需要刷新更容易並且使用更安全。

將排名添加為列

如果原始表變化很快並且您需要更多實時數據，另一種選擇是使排名表永久化並使用觸發器使其保持最新。

• 在 leaderboard_scores 更改時插入和刪除記錄
• 如果分數列在大多數情況下發生變化，則更新排名表中所有受影響的記錄，它只有少數，分數介於 NEW.score 和 OLD.score 之間

如果你決定觸發的事情，你必須小心等級的變化：如果分數增加，你必須將所有 ID 的等級降低一個，它的分數比 NEW.score 低，排名比前一個高我們的 ID 的排名，並增加這個 ID 的排名（一個或多個……或零）。如果在這種情況下分數也會降低，你必須做相反的事情。雖然它看起來很簡單（現在我也覺得，我解釋得太含糊了），但並發更改和多行更新可能會很棘手。如果您將排名放在排行榜表中，您甚至可以創建漂亮的死鎖。

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