Postgres 更喜歡慢速 Seq 掃描而不是快速索引掃描

表

我有以下兩張表。

books，其中包含約 500 萬行：

                                 Table "public.books"
Column  |           Type           |                     Modifiers
---------+--------------------------+----------------------------------------------------
id      | integer                  | not null default nextval('books_id_seq'::regclass)
run__id | integer                  |
time    | timestamp with time zone | not null
venue   | character varying        | not null
base    | character varying        | not null
quote   | character varying        | not null
Indexes:
   "books_pkey" PRIMARY KEY, btree (id)
   "run_books_index" UNIQUE, btree (run__id, id)
Foreign-key constraints:
   "books_run__id_fkey" FOREIGN KEY (run__id) REFERENCES runs(id)
Referenced by:
   TABLE "orders" CONSTRAINT "orders_book__id_fkey" FOREIGN KEY (book__id) REFERENCES books(id)

和orders，其中包含約 30 億行：

         Table "public.orders"
 Column  |       Type       | Modifiers
----------+------------------+-----------
book__id | integer          | not null
is_buy   | boolean          | not null
qty      | double precision | not null
price    | double precision | not null
Indexes:
   "orders_pkey" PRIMARY KEY, btree (book__id, is_buy, price)
Foreign-key constraints:
   "orders_book__id_fkey" FOREIGN KEY (book__id) REFERENCES books(id)

詢問

我想執行以下查詢：

SELECT * FROM books b JOIN orders o ON o.book__id = b.id WHERE b.run__id = 1

Postgres 建議以下執行計劃：

orderbooks=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM books b JOIN orders o ON o.book__id = b.id WHERE b.run__id = 1;
                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Hash Join  (cost=2465.94..58122020.57 rows=762939 width=53) (actual time=1394110.723..2561879.775 rows=45216 loops=1)
  Hash Cond: (o.book__id = b.id)
  Buffers: shared hit=4080 read=18437586
  ->  Seq Scan on orders o  (cost=0.00..47292761.72 rows=2885110272 width=21) (actual time=0.018..2265529.184 rows=2883798728 loops=1)
        Buffers: shared hit=4073 read=18437586
  ->  Hash  (cost=2448.52..2448.52 rows=1393 width=32) (actual time=0.024..0.024 rows=15 loops=1)
        Buckets: 2048  Batches: 1  Memory Usage: 17kB
        Buffers: shared hit=4
        ->  Index Scan using run_books_index on books b  (cost=0.43..2448.52 rows=1393 width=32) (actual time=0.011..0.012 rows=15 loops=1)
              Index Cond: (run__id = 1)
              Buffers: shared hit=4
Planning time: 2.228 ms
Execution time: 2561882.272 ms
(13 rows)

IE。依次掃描 中的約 30 億行orders，這需要約 40 分鐘。

如果我禁用順序掃描，Postgres 會建議以下 -更快- 查詢：

orderbooks=> SET enable_seqscan = OFF;
SET
orderbooks=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM books b JOIN orders o ON o.book__id = b.id WHERE b.run__id = 1;
                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=1.14..219271454.14 rows=762939 width=53) (actual time=0.024..15.234 rows=45216 loops=1)
  Buffers: shared hit=707
  ->  Index Scan using run_books_index on books b  (cost=0.43..2448.52 rows=1393 width=32) (actual time=0.011..0.014 rows=15 loops=1)
        Index Cond: (run__id = 1)
        Buffers: shared hit=4
  ->  Index Scan using orders_pkey on orders o  (cost=0.70..156529.57 rows=87819 width=21) (actual time=0.007..0.551 rows=3014 loops=15)
        Index Cond: (book__id = b.id)
        Buffers: shared hit=703
Planning time: 0.302 ms
Execution time: 18.054 ms
(10 rows)

問題

為什麼 Postgres 更喜歡慢速執行計劃？我看它的成本比快的便宜，怎麼來的？

筆記

1. 我VACUUM (VERBOSE, ANALYZE)在嘗試執行/分析查詢之前不久就跑了。
2. 如果我不請求orders.qty輸出中的列，則 Postgres 選擇快速查詢（無需禁用順序掃描）：

orderbooks=> EXPLAIN SELECT b.id, b.run__id, b.time, b.venue, b.base, b.quote, o.book__id, o.is_buy, o.price FROM books b JOIN orders o ON o.book__id = b.id WHERE b.run__id = 1;
                                         QUERY PLAN
-----------------------------------------------------------------------------------------------
Nested Loop  (cost=1.14..6473136.93 rows=762939 width=45)
  ->  Index Scan using run_books_index on books b  (cost=0.43..2448.52 rows=1393 width=32)
        Index Cond: (run__id = 1)
  ->  Index Only Scan using orders_pkey on orders o  (cost=0.70..3766.96 rows=87819 width=13)
        Index Cond: (book__id = b.id)
(5 rows)

領域

我試圖建模的資料結構是一個限價訂單簿。表中的一行books包含有關單個訂單簿的資訊，而orders表中的一行描述了給定簿中存在的單個訂單。

我已經選擇不允許同一個訂單簿有多個相同價格的訂單——因此(book__id, is_buy, price)主鍵是orders. 原因是我不需要區分相同價格的多個不同訂單（例如來自不同人的訂單）。因此，如果對於給定的訂單簿，我的輸入數據包含兩個價格相同的訂單，我只需將其轉換為數量為兩個訂單數量之和的單個訂單。

版本

x86_64-pc-linux-gnu 上的 PostgreSQL 9.6.19，由 Debian clang 版本 10.0.1 編譯，64 位

似乎有幾件事：

• 估計是錯誤的。由於您已經ANALYZEd 兩個表，請使用更高的default_statistics_target.
• 由於索引掃描似乎估計太昂貴，也許您的設置random_page_cost太高了。還有，也許effective_cache_size是太低了。

如果您沒有從書籍中選擇所有內容，那麼覆蓋索引可能是一個想法……

詳細說明您的錯誤估計問題：

Postgres 估計會從索引掃描中獲得 1393 行run_books_index，但實際上只找到 15 行。相差約 100 倍。

…（成本=0.43..2448.52行=1393寬度=32）（實際時間=0.011..0.012行=15循環=1）

你跑了之後ANALYZE，所以我們不能責怪過時的統計數據。

因此，Postgres 被誤導為期望從中獲取的行數比最終檢索到的行數要多得多，這非常有利於順序掃描*。*orders

您的替代查詢僅從table獲取book__id、is_buy和。可以直接使用PK 索引從 PK 索引中讀取，即使在獲取表的大部分時，它也很便宜。price``orders``orders_pkey``Index Only Scan

*不過，PK(book__id, is_buy, price)讓我想知道您的**關係模型？*每本書只能is_buy以相同的價格訂購一個（或兩個，考慮到）？您的查詢仍然發現每本書約 3000 個訂單。我很難理解這一點。

**SELECT ***通常是不必要的，並且本身會拖累性能。您的替代查詢似乎更聰明。

但是，雖然獲取的列多於多列索引所涵蓋的列，但**在 just 上的索引(book__id)**會更有效，因為它在最小形式中要小約 1/3，而且通常也不那麼臃腫。

提高統計目標將收集更多關於“最常見值”的數據。但這也導致對其餘部分的更好估計。

收集和使用更多的統計數據也會增加成本。對於幾個大表中對查詢計劃至關重要的幾列，請考慮選擇性地定位這些列，並將全域保留為default_statistics_target預設值。像：

ALTER TABLE books ALTER run__id SET STATISTICS 2000;

請記住，更多的統計數據只能幫助處理不規則的數據分佈。

您提到了 中的約 30 億行orders，但沒有提到 的基數books，所以 2000 是在黑暗中拍攝的，假設它books不會小很多（考慮到 中的奇數 PK orders）。遠高於預設值 100，但仍低於最大值 10000。

有關的：

• 具有 btree 索引的 jsonb 列的統計資訊不一致
• 優化對一系列時間戳（兩列）的查詢

考慮一下 Laurenz 的其他建議。這些設置通常很重要。

如果這些都沒有幫助，特別是如果您的表非常大並且數據分佈不佳，您可能會嘗試通過手動設置不同值的比率來強迫您的運氣：

ALTER TABLE books ALTER run__id SET (n_distinct = -0.07);

-1 和 0 之間的負值表示固定的頻率比。您的查詢計劃顯示單個 15 行run__id。1/15 =~ 0.07。設置迫使始終期望該頻率。手冊中的詳細資訊**n_distinct**。有關的：

• Postgres 有時使用劣質索引來表示 WHERE a IN (…) ORDER BY b LIMIT N

但是強制估計可能會在以後咬你。

另一種選擇，特別是如果您不能（或不會）對數據庫配置進行任何建議的更改：使用帶有子查詢的不同**LATERAL查詢**：

SELECT *
FROM   books b
CROSS  JOIN  LATERAL (
  SELECT *
  FROM   orders
  WHERE  book__id = b.id
  ) o
WHERE b.run__id = 1;

這是另一種解決方法。通常為此目的更昂貴，但它非常有利於在 上進行索引掃描orders，這似乎更重要。看：

• Postgres 有時使用劣質索引來表示 WHERE a IN (…) ORDER BY b LIMIT N

最後，Postgres 在估計和查詢計劃方面逐漸變得更加智能，因此升級到目前版本也可能會有所幫助。9.6 版已經過時了。

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