PostgreSQL 可以使用索引來加快計數（不同）查詢嗎？

考慮以下範例：

CREATE TABLE test (
 id SERIAL,
 some_integer INT
);

INSERT INTO test (some_integer)
SELECT FLOOR(RANDOM()*100000) from generate_series(1,100000) s(i);

CREATE INDEX some_integer_idx ON test (some_integer);

EXPLAIN ANALYZE SELECT COUNT(DISTINCT some_integer) from test;

它返回以下查詢計劃：

                                                  QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
Aggregate  (cost=1693.00..1693.01 rows=1 width=8) (actual time=26.343..26.344 rows=1 loops=1)
  ->  Seq Scan on test  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.011..7.282 rows=100000 loops=1)
Planning Time: 0.052 ms
Execution Time: 26.365 ms
(4 rows)

我很驚訝它仍然在測試中進行了順序掃描。簡單地計算索引中的行數不是更快嗎？

B 樹索引中的每一行都有一個單獨的條目，對於重複項也是如此。所以我們永遠不能*“簡單地計算索引中的行數*”。需要讀取和聚合的索引行數與表行數一樣多。

Postgres 13 或更高版本中的索引重複數據刪除可以壓縮重複項，提供更多優化潛力，但主要邏輯仍然存在（1 行，1 個索引元組）。看：

• 複合索引是否也適用於第一個欄位的查詢？

由於 B 樹索引通常小於其表，因此僅索引掃描仍然是一個有吸引力的選擇。但這增加了一些成本：檢查可見性映射，堆獲取不是“全部可見”的數據頁。並且索引往往比表更膨脹（使最初的優勢無效）。並且索引預設為fillfactor90，而表預設為fillfactor100 。可見性映射必須是最新的開始 - 表必須足夠真空。首先添加VACUUM ANALYZE到您的測試中。

您的測試表有 8 個字節的數據，這是可能的最小行大小。而且您創建的重複項很少。即使在完美的條件下，從僅索引掃描中獲得的收益也很少**。**Postgres 甚至沒有嘗試。

添加一個有效負載列來增加表行，並創建更多重複項，瞧，我們看到了一個僅索引掃描：

CREATE TABLE test (
 id serial
, some_integer int
, payload text
);

INSERT INTO test (some_integer, payload)
SELECT trunc(random() * 10000)  -- fewer distinct values, some dupes
    , 'Lorem ipsum dolor sit amet, consectetur adipisicing elit'  -- bigger row !!
FROM   generate_series(1,100000) s;

CREATE INDEX some_integer_idx ON test (some_integer);

VACUUM ANALYZE test;

EXPLAIN ANALYZE SELECT count(DISTINCT some_integer) FROM test;

聚合（成本=2210.29..2210.30 行=1 寬度=8）（實際時間=25.763..25.764 行=1 循環=1）
-> **Index Only Scan** using some_integer_idx on test (cost=0.29..1960.29 rows=100000 width=4) (實際時間=0.030..12.562 rows=100000 loops=1)
堆取數：0
規劃時間：0.184 ms
執行時間：25.891 毫秒

db<>在這裡擺弄

好處隨著相對大小的障礙表>>索引、重複的百分比和“所有可見”數據頁的部分（更新的可見性圖）而增長。

許多重複的優化

現在，如果已經實現了索引跳過掃描，那麼即使沒有有效負載列，索引也可以很好地用於具有許多重複項的情況（與您的範例不同）。但是我們還沒有到那裡（Postgres 15）。

在那之前，我們可以模擬索引跳過掃描：

WITH RECURSIVE cte AS (
  (
  SELECT some_integer AS i
  FROM   test
  ORDER  BY 1
  LIMIT  1
  )
  
  UNION ALL
  SELECT (SELECT t.some_integer
          FROM   test t
          WHERE  t.some_integer > c.i
          ORDER  BY 1
          LIMIT  1)
  FROM   cte c
  WHERE  c.i IS NOT NULL
  )
SELECT count(i) FROM cte;

db<>在這裡擺弄

快多了。看：

• 在每個 GROUP BY 組中選擇第一行？
• 如何獲得每組第二高的價值？
• SELECT DISTINCT 在我的 PostgreSQL 表上比預期的要慢
• 優化 GROUP BY 查詢以檢索每個使用者的最新行

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