多對多關係中不同 ID 的最快查詢

我在 PostgreSQL 9.4 中有這張表：

CREATE TABLE user_operations( 
   id SERIAL PRIMARY KEY, 
   operation_id integer, 
   user_id integer )

該表由~1000-2000不同的操作組成，每個操作對應於所有使用者80000-120000集合的某個子集（每個大約由元素組成） ：S

S = {1, 2, 3, ... , 122655}

參數：

work_mem = 128MB
table_size = 880MB

我也有一個索引operation_id。

問題：user_id對於集合的重要部分operation_id（20％-60％）查詢所有不同的最佳計劃是什麼，

SELECT DISTINCT user_id FROM user_operation WHERE operation_id < 500

可以在 table 上創建更多索引。目前，查詢的計劃是：

HashAggregate  (cost=196173.56..196347.14 rows=17358 width=4) (actual time=1227.408..1359.947 rows=598336 loops=1)
 ->  Bitmap Heap Scan on user_operation  (cost=46392.24..189978.17 rows=2478155 width=4) (actual time=233.163..611.182 rows=2518122 loops=1)
       Recheck Cond: (operation_id < 500)
       ->  Bitmap Index Scan on idx  (cost=0.00..45772.70 rows=2478155 width=0) (actual time=230.432..230.432 rows=2518122 loops=1)
             Index Cond: (operation_id < 500)

在這種情況下，這樣的查詢計劃真的是最優的嗎？我的意思是，我不確定使用Bitmap Heap Scan. 我會感謝任何對相關文章的引用。

user_id 對於集合的重要部分operation_id（20％-60％）查詢所有不同的最佳計劃是什麼。

使用遞歸查詢：

WITH RECURSIVE cte AS (
  (  -- parentheses are required
  SELECT user_id
  FROM   user_operations
  WHERE  operation_id < 500
  ORDER  BY user_id
  LIMIT  1
  )
  UNION ALL
  SELECT u.user_id
  FROM   cte, LATERAL (
     SELECT user_id
     FROM   user_operations
     WHERE  operation_id < 500
     AND    user_id > cte.user_id  -- lateral reference
     ORDER  BY user_id
     LIMIT  1
     ) u
  )
TABLE cte;

(user_id, operation_id)與按該順序排列的列上的索引相結合。我希望在第二列上過濾的索引掃描。相當準確的表統計資訊很重要，因此 Postgres 知道它只需跳過索引中的幾行即可找到下一個user_id。通常，人們可能希望operation_id特別增加統計目標：

ALTER TABLE user_operations ALTER operation_id SET STATISTICS 1000;

由於只有~1000-2000 different operations，這甚至可能沒有必要，但這是一個很小的代價。

細節：

• 優化對一系列時間戳（兩列）的查詢

如果謂詞operation_id < 500是穩定的（始終相同），則將其設為部分索引(user_id)：

CREATE INDEX foo ON user_operations (user_id) WHERE operation_id < 500;

那麼關於這個查詢的統計資訊就operation_id不再相關了。

即使謂詞不穩定，也可能有優化的方法 - 取決於所有可能的條件和值頻率。

性能應該是……好吃。

我在 SO 上的相關答案中優化了技術（附有詳細解釋）：

• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄

如果您有一個單獨的users表，並且可以在您的範例中找到所有使用者的大部分，則可以使用更快的查詢樣式。連結答案中的詳細資訊。

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