Why EXPLAIN Does Not Show Heap Fetches for Index Scan in PostgreSQL

performancepostgresqlpostgresql-11postgresql-performance

I tried to compare potential perfomance difference between covering b-tree index and simple b-tree index and was confused with EXPLAIN(ANALYZE,BUFFERS) output.

Test environment

-- 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;

Test case for simple b-tree index

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;

Test run results:

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

Test case for covering b-tree index

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;

Test run results

Before vacuum:

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

After vacuum:

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

My qiestion is why Heap Fetches shows only for index-only scans, but for index scans it information is hidden?
Now i see that i has same request count to shared buffers for simple index and covering index. So where is perfomance
impact for covering indexes in this situation? It realy strange, that i can't measure potential perfomance impact using EXPLAIN.

Best Answer

Index scans fetch from the heap for every single row. That is what makes it not be an index-only scan. It only makes sense to display the count for index-only scans, as that is the only case in which it is informative.

The line "Buffers:" line may be more informative in general (for a realistic case where you have more than one row at stake). But for your IOS that line shows 4 in both cases. The reason that that doesn't change is that before vacuuming, the table doesn't have a visibility map, so there is no page to be read into a buffer. It realizes the vm doesn't exist, an hits the table page instead. After vacuuming, it hits the (now existing) vm page, which tells us doesn't need to hit the table. So you always get 4 Buffers hit, 3 for the index and 1 for either the vm or the table.

If you were to partially populate the table, then vacuum so that the vm exists, then insert another record, and then query for that record, you would see that the IOS now hits 5 buffers. 3 for the index, one for the visibility map page which says the page is not all visible, and 1 for the table.

If you have large queries which hits the index many times, then interpreting the buffer hit count gets really tricky. Within a single query execution, it keeps a pin on the most recently used visibility map page. Hitting the same page repeatedly while you still hold a pin on it doesn't count as a new buffer hit. Only if you need to switch to a different page in the vm does it count as a new buffer hit.

All of this unlikely to be very effective. Make up a realistic data set of a realistic size on realistic hardware, and run realistic queries against it, and measure actual performance. Once you have legitimate performance differences which need explaining, you can dig into the details to find the explanation. But trying to extrapolate up from a one-row query is not very useful.

Related Question