3

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.

  • I am not entirely sure, but: I think an Index Only Scan needs a heap fetch if the visibility information in the index isn't up-to-date. So having could indicate that autovacuum (or auto analyze) hasn't updated the visibility map – a_horse_with_no_name Dec 18 '19 at 18:55
3

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.

| improve this answer | |
  • Thanks for the answer! About real cases: my case is a simple key-value. The difference is data size - it should contain around 100 000000 records instead of 1000000. If I understood correctly from your answer - if data contains in shared buffers then no significant difference between index scan and index-only scan. We will work with shared buffer reads all the time. In my case first 3 always will do from index, and last 1 will do from cached table page for simple index and from vm page for covering index. Is my reasoning true? – Frank59 Dec 19 '19 at 5:19
  • Even if all data is in shared_buffers, the vm might be faster as most of it will fit in L2/L3 cache rather than in slower main memory. This is hardware dependent, so is hard to answer except through testing on the actual hardware. The differences probably will not be huge, but if every nanosecond counts they could add up. – jjanes Dec 19 '19 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.