Postgresql

對大量重複值使用什麼索引？

  • March 17, 2017

讓我們做幾個假設：

我有一個看起來像這樣的表：

a | b
---+---
a | -1
a | 17
 ...
a | 21
c | 17
c | -3
 ...
c | 22

關於我的套裝的事實：

  • 整個表的大小約為 10 10行。
  • 我有 ~ 100k 行，a列中的值a，與其他值類似（例如c）。
  • 這意味著“a”列中有約 100k 個不同的值。
  • 我的大多數查詢將讀取 a 中給定值的全部或大部分值，例如select sum(b) from t where a = 'c'.
  • 該表的編寫方式使得連續值在物理上接近（按順序編寫，或者我們假設CLUSTER在該表和列上使用a）。
  • 該表很少更新，我們只關心讀取速度。
  • 該表相對較窄（例如每個元組約 25 個字節，+ 23 個字節的成本）。

現在的問題是，我應該使用什麼樣的索引？我的理解是：

  • BTree我的問題是 BTree 索引會很大，因為據我所知它將儲存重複值（它必須儲存，因為它不能假設表是物理排序的）。如果 BTree 很大，我最終不得不讀取索引和索引指向的表部分。（我們可以使用fillfactor = 100來減小索引的大小。）
  • BRIN我的理解是，我可以在這裡建立一個小索引，而代價是閱讀無用的頁面。使用小pages_per_range意味著索引更大（這是 BRIN 的問題，因為我需要閱讀整個索引），使用大pages_per_range意味著我會閱讀很多無用的頁面。pages_per_range考慮到這些權衡，是否有一個神奇的公式可以找到一個好的價值？
  • GIN/GiST不確定這些是否相關，因為它們主要用於全文搜尋，但我也聽說它們擅長處理重複鍵。aGIN或GiSTindex 在這裡有幫助嗎？

另一個問題是，Postgres 是否會在查詢計劃器中使用表已編輯（假設沒有更新）的事實CLUSTER（例如，通過二進制搜尋相關的開始/結束頁面）？有點相關，我可以將所有列儲存在 BTree 中並完全刪除表（或實現等效的東西，我相信這些是 SQL Server 中的聚集索引）？是否有一些混合 BTree/BRIN 索引可以在這裡提供幫助？

我寧願避免使用數組來儲存我的值，因為這樣我的查詢最終會變得不那麼可讀（我知道這將通過減少元組的數量來降低每個元組成本的 23 個字節的成本）。

B-Tree

我的問題是 BTree 索引會很大，因為它會儲存重複值（它也有，因為它不能假設表是物理排序的）。如果 BTree 很大，我最終不得不同時讀取索引和索引指向的表的部分……

不一定——擁有一個“覆蓋”的 btree 索引將是最快的讀取時間，如果這就是你想要的（即如果你能負擔得起額外的儲存空間），那麼它是你最好的選擇。

股

我的理解是，我可以在這裡建立一個小索引，而代價是閱讀無用的頁面。使用小pages_per_range意味著索引更大（這是 BRIN 的問題，因為我需要閱讀整個索引），使用大pages_per_range意味著我會閱讀很多無用的頁面。

如果您負擔不起覆蓋 btree 索引的儲存成本，那麼 BRIN 非常適合您，因為您已經有了集群（這對於 BRIN 的有用性至關重要）。BRIN 索引很小，因此如果您選擇合適的值，所有頁面都可能在記憶體中pages_per_range。

是否有一個神奇的公式可以找到考慮到這些權衡的 pages_per_range 的良好值？

沒有神奇的公式，但從pages_per_range 略小於平均值佔用的平均大小（以頁為單位）開始a。您可能正在嘗試最小化：（掃描的 BRIN 頁數）+（掃描的堆頁數）對於典型查詢。Heap Blocks: lossy=n在執行計劃中查找並pages_per_range=1與其他值進行比較pages_per_range——即查看有多少不必要的堆塊被掃描。

杜松子酒/GiST

不確定這些是否相關，因為它們主要用於全文搜尋，但我也聽說它們擅長處理重複鍵。a GIN/ GiSTindex 在這裡有幫助嗎？

GIN 可能值得考慮，但可能不是 GiST——但是如果自然分群真的很好，那麼 BRIN 可能是一個更好的選擇。

這是虛擬數據的不同索引類型之間的範例比較，有點像你的：

表和索引：

create table foo(a,b,c) as
select *, lpad('',20)
from (select chr(g) a from generate_series(97,122) g) a
     cross join (select generate_series(1,100000) b) b
order by a;
create index foo_btree_covering on foo(a,b);
create index foo_btree on foo(a);
create index foo_gin on foo using gin(a);
create index foo_brin_2 on foo using brin(a) with (pages_per_range=2);
create index foo_brin_4 on foo using brin(a) with (pages_per_range=4);
vacuum analyze;

關係大小：

select relname "name", pg_size_pretty(siz) "size", siz/8192 pages, (select count(*) from foo)*8192/siz "rows/page"
from( select relname, pg_relation_size(C.oid) siz
      from pg_class c join pg_namespace n on n.oid = c.relnamespace
      where nspname = current_schema ) z;

姓名 | 尺寸 | 頁 | 行/頁
:----------------- | :------ | ----: | --------:
富 | 149 MB | 19118 | 135
foo_btree_covering | 56 MB | 7132 | 364
foo_btree | 56 MB | 7132 | 364
foo_gin | 2928 KB | 366 | 7103
foo_brin_2 | 264 KB | 33 | 78787
foo_brin_4 | 136 KB | 17 | 152941

覆蓋 btree：

explain analyze select sum(b) from foo where a='a';

| 查詢計劃 |
| :---------------------------------------------------------------------------------------------------------------------------------------------- |
| 聚合（成本=3282.57..3282.58 行=1 寬度=8）（實際時間=45.942..45.942 行=1 循環=1）|
| -> Index Only Scan using foo_btree_covering on foo (cost=0.43..3017.80 rows=105907 width=4) (實際時間=0.038..27.286 rows=100000 loops=1) |
| 索引條件：(a = 'a'::text) |
| 堆取數：0 |
| 規劃時間：0.099 ms |
| 執行時間：45.968 毫秒 |

普通的 btree：

drop index foo_btree_covering;
explain analyze select sum(b) from foo where a='a';

| 查詢計劃 |
| :-------------------------------------------------------------------------------------------------------------------------------- |
| 聚合（成本=4064.57..4064.58 行=1 寬度=8）（實際時間=54.242..54.242 行=1 循環=1）|
| -> 在 foo 上使用 foo_btree 進行索引掃描（成本=0.43..3799.80 行=105907 寬度=4）（實際時間=0.037..33.084 行=100000 循環=1）|
| 索引條件：(a = 'a'::text) |
| 規劃時間：0.135 ms |
| 執行時間：54.280 毫秒 |

BRIN pages_per_range=4：

drop index foo_btree;
explain analyze select sum(b) from foo where a='a';

| 查詢計劃 |
| :-------------------------------------------------------------------------------------------------------------------------------- |
| 聚合（成本=21595.38..21595.39 行=1 寬度=8）（實際時間=52.455..52.455 行=1 循環=1）|
| -> foo 上的點陣圖堆掃描（成本=888.78..21330.61 行=105907 寬度=4）（實際時間=2.738..31.967 行=100000 循環=1）|
| 重新檢查條件：(a = 'a'::text) |
| 索引重新檢查刪除的行數：96 |
| 堆塊：有損=736 |
| -> foo_brin_4 上的點陣圖索引掃描（成本=0.00..862.30 行=105907 寬度=0）（實際時間=2.720..2.720 行=7360 循環=1）|
| 索引條件：(a = 'a'::text) |
| 規劃時間：0.101 ms |
| 執行時間：52.501 毫秒 |

BRIN pages_per_range=2：

drop index foo_brin_4;
explain analyze select sum(b) from foo where a='a';

| 查詢計劃 |
| :-------------------------------------------------------------------------------------------------------------------------------- |
| 聚合（成本=21659.38..21659.39 行=1 寬度=8）（實際時間=53.971..53.971 行=1 循環=1）|
| -> foo 上的點陣圖堆掃描（成本=952.78..21394.61 行=105907 寬度=4）（實際時間=5.286..33.492 行=100000 循環=1）|
| 重新檢查條件：(a = 'a'::text) |
| 索引重新檢查刪除的行數：96 |
| 堆塊：有損=736 |
| -> foo_brin_2 上的點陣圖索引掃描（成本=0.00..926.30 行=105907 寬度=0）（實際時間=5.275..5.275 行=7360 循環=1）|
| 索引條件：(a = 'a'::text) |
| 規劃時間：0.095 ms |
| 執行時間：54.016 毫秒 |

杜松子酒：

drop index foo_brin_2;
explain analyze select sum(b) from foo where a='a';

| 查詢計劃 |
| :--------------------------------------------------------------------------------------------------------------------------------- |
| 聚合（成本=21687.38..21687.39 行=1 寬度=8）（實際時間=55.331..55.331 行=1 循環=1）|
| -> foo 上的點陣圖堆掃描（成本=980.78..21422.61 行=105907 寬度=4）（實際時間=12.377..33.956 行=100000 循環=1）|
| 重新檢查條件：(a = 'a'::text) |
| 堆塊：精確=736 |
| -> foo_gin 上的點陣圖索引掃描（成本=0.00..954.30 行=105907 寬度=0）（實際時間=12.271..12.271 行=100000 循環=1）|
| 索引條件：(a = 'a'::text) |
| 規劃時間：0.118 ms |
| 執行時間：55.366 毫秒 |

dbfiddle在這裡

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

相關問答

Postgresql

大表的高效分頁

  • January 30, 2022
檢視問答
Postgresql

如何在 PostgreSQL 中使 DISTINCT ON 更快？

  • September 10, 2021
檢視問答
Postgresql

日期查詢範圍內未使用的索引

  • July 21, 2020
檢視問答
Postgresql

具有最新時間戳的行

  • May 18, 2020
檢視問答
Postgresql

索引：如果節點數相同，則整數與字元串的性能

  • May 3, 2020
檢視問答
Postgresql

理解“點陣圖堆掃描”和“序列掃描”

  • April 11, 2020
檢視問答