為什麼 EXPLAIN 不顯示索引掃描的堆提取

我試圖比較覆蓋 b-tree 索引和簡單 b-tree 索引之間的潛在性能差異，並與EXPLAIN(ANALYZE,BUFFERS)輸出混淆。

測試環境

-- function to fill test table
CREATE OR REPLACE FUNCTION fillTable (n INTEGER)
  RETURNS INTEGER AS $rowsCount$
DECLARE
  counter INTEGER := 0 ;
BEGIN

  IF (n < 1) THEN
     RETURN 0 ;
  END IF;

  LOOP
     EXIT WHEN counter = n ;
     counter := counter + 1 ;
     insert into key_value_test(key, value) VALUES (counter,counter);
  END LOOP ;

  return counter;

END ;
$rowsCount$
LANGUAGE plpgsql;

簡單b樹索引的測試案例

drop table key_value_test;
create table key_value_test
(
   key bigint not null,
   value integer not null,
   test VARCHAR(100),
   CONSTRAINT pk_key_value_test
   PRIMARY KEY(key)
);

SELECT  fillTable(1000000);

vacuum;

EXPLAIN( ANALYZE,BUFFERS ) SELECT  key, value
FROM key_value_test
WHERE  key = 740080;

試執行結果：

Index Scan using pk_key_value_test on key_value_test  (cost=0.42..8.44 rows=1 width=12) (actual time=0.013..0.014 rows=1 loops=1)   
Index Cond: (key = 913680)   
Buffers: shared hit=4 
Planning Time: 0.062 ms 
Execution Time: 0.026 ms

覆蓋b-tree索引的測試案例

drop table if exists key_value_test;
create table key_value_test
(
   key bigint not null,
   value integer not null,
   CONSTRAINT pk_key_value_test
     PRIMARY KEY(key) include(value)
);
SELECT  fillTable(1000000);

EXPLAIN( ANALYZE,BUFFERS ) SELECT  key, value
FROM key_value_test
WHERE  key = 740080;

vacuum; 

EXPLAIN( ANALYZE,BUFFERS ) SELECT  key, value
FROM key_value_test
WHERE  key = 740080;

試執行結果

抽真空前：

Index Only Scan using pk_key_value_test on key_value_test  (cost=0.43..8.45 rows=1 width=12) (actual time=0.127..0.128 rows=1 loops=1)
 Index Cond: (key = 740080)
 Heap Fetches: 1
 Buffers: shared hit=4
Planning Time: 0.078 ms
Execution Time: 0.146 ms

抽真空後：

Index Only Scan using pk_key_value_test on key_value_test  (cost=0.42..4.44 rows=1 width=12) (actual time=0.143..0.144 rows=1 loops=1)
 Index Cond: (key = 740080)
 Heap Fetches: 0
 Buffers: shared hit=4
Planning Time: 0.273 ms
Execution Time: 0.156 ms

我的問題是為什麼Heap Fetches只顯示僅索引掃描，但對於索引掃描，它的資訊是隱藏的？現在我看到我對簡單索引和覆蓋索引的共享緩衝區具有相同的請求計數。那麼在這種情況下，覆蓋索引的性能影響在哪裡呢？真的很奇怪，我無法使用 EXPLAIN 來衡量潛在的性能影響。

索引掃描從堆中獲取每一行。這就是使它不是僅索引掃描的原因。僅顯示僅索引掃描的計數才有意義，因為這是提供資訊的唯一情況。

通常，“緩衝區：”行可能會提供更多資訊（對於實際情況，您有不止一行處於危險之中）。但是對於您的 IOS，該行在兩種情況下都顯示為 4。沒有改變的原因是在清理之前，表沒有可見性映射，因此沒有要讀入緩衝區的頁面。它意識到虛擬機不存在，而是點擊表格頁面。在清理之後，它會點擊（現在存在的）vm 頁面，這告訴我們不需要點擊表。所以你總是得到 4 個緩衝區命中，3 個用於索引，1 個用於 vm 或表。

如果您要部分填充表，然後進行清理以使 vm 存在，然後插入另一條記錄，然後查詢該記錄，您會看到 IOS 現在命中 5 個緩衝區。3 用於索引，1 用於可見性地圖頁面，表示該頁面並非全部可見，1 用於表。

如果您有多次命中索引的大型查詢，那麼解釋緩衝區命中計數會變得非常棘手。在單個查詢執行中，它會在最近使用的可見性地圖頁面上保留一個圖釘。在您仍然按住別針的情況下重複點擊同一頁面不會算作新的緩衝區命中。僅當您需要切換到 vm 中的不同頁面時，它才算作新的緩衝區命中。

所有這些都不太可能非常有效。在真實硬體上建構真實大小的真實數據集，並針對它執行真實查詢，並測量實際性能。一旦您有需要解釋的合理性能差異，您就可以深入研究細節以找到解釋。但是試圖從單行查詢中推斷出來並不是很有用。

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