如何按不同的列索引範圍條件和排序？

我使用 PostgreSQL，我有一個這樣的表：

create table transactions (
 uuid uuid,
 user_id integer,
 created_at timestamp,
 amount numeric
);
create index idx_transactions_user_id_created_at on transactions (user_id, created_at);

我想通過這樣的查詢來獲取使用者過去 N 天的“最大交易”：

select *
from transactions
where user_id=1234
 and created_at >= current_timestamp - interval '7 days'
order by amount desc
limit 10;

使用者可以選擇他感興趣的天數和限制。

查詢計劃是這樣的：

                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Limit  (cost=9.54..9.55 rows=2 width=60)
  ->  Sort  (cost=9.54..9.55 rows=2 width=60)
        Sort Key: amount DESC
        ->  Bitmap Heap Scan on transactions  (cost=4.18..9.53 rows=2 width=60)
              Recheck Cond: ((user_id = 1234) AND (created_at >= (CURRENT_TIMESTAMP - '7 days'::interval)))
              ->  Bitmap Index Scan on idx_transactions_user_id_created_at  (cost=0.00..4.17 rows=2 width=0)
                    Index Cond: ((user_id = 1234) AND (created_at >= (CURRENT_TIMESTAMP - '7 days'::interval)))
(7 rows)

因此 postgres 獲取所有匹配 user_id 和 created_at 範圍的行，然後對所有行進行排序並返回前 N 個。

有沒有更好的方法來索引這個？那麼當有數百萬使用者和每個使用者數百萬筆交易時，它可以很好地擴展嗎？到目前為止，我的查詢並不太慢，但我只是想知道理論上是否有可能以 postgres 確切知道從磁碟讀取哪 10 行的任意created_at條件的方式來索引該表？

不，您不能對任何核心索引類型執行此操作。您也許可以使用RUM 擴展，但上次我嘗試使用它進行類似操作時，我無法讓它為此目的工作。

一旦你有足夠的數據需要改進（正如你所指出的，對兩行進行排序並不費力），你可能會通過在 created_at 上進行範圍分區和在(user_id, amount). 這樣它就可以只查詢覆蓋可能符合條件的時間的分區，按索引順序遍歷每個分區，並根據 created_at 過濾掉實際上不符合條件但位於無法排除的分區中的任何行集體。每個單獨的分區掃描一旦滿足 LIMIT 就可以停止，然後它們可以“合併附加”保持現有順序，並將 LIMIT 再次應用於合併集。

在玩了這個之後，我必須修改我的答案。(user_id, created_at, amount)只要您可以稍微重新編寫查詢，GiST 索引就可以做到這一點。您可以通過將“數量”與大於表中任何可能的實際數量的“哨兵”值進行比較來使用 KNN 機制：

select *
from transactions
where user_id=1234
 and created_at >= current_timestamp - interval '7 days'
order by amount <-> 999999999
limit 10;

你確實需要一個真正的最大數量，你不能使用’Infinity’，因為任何有限值和無窮大之間的距離都是無限的，它們都會被捆綁在一起。

GiST 索引的建構和維護速度可能比 btree 索引慢得多，而且這也很難預測。因此，即使有可能，但如果其他具有 btree 索引的技術能夠完成這項工作，可能就不值得了。

如果您每天每個使用者有很多事務，您可以嘗試使用類似於 jjanes 建議的分區的方法：

1. 創建生成的列 create_at_date GENERATED ALWAYS AS (DATE(create_at))
2. 在 (user_id, create_at_date, amount desc) 創建索引
3. 使用查詢生成所需日期列表，然後使用橫向連接獲取每個日期的前 10 個事務並再次對其進行排序。像這樣的東西：

SELECT trns.uuid, trns.amount 
FROM   generate_series(date '2020-11-14'
                    , date '2020-11-20'
                    , interval  '1 day') AS t(day)
JOIN LATERAL (SELECT *
           FROM transactions
           WHERE user_id=1234
               AND create_at_date = t.day
           ORDER BY amount DESC LIMIT 10) AS trns ON true                     
ORDER BY trns.amount DESC LIMIT 10

它將返回與原始查詢稍有不同的數據，因為此條件包括第一個日期的所有事務（current_timestamp - 間隔“7 天”指向一天的中間）。

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