Re:Explain plan shows fewer shared blocks when index+table compared to index alone?

From: Sergei Kornilov <sk(at)zsrv(dot)org>
To: Amin Jaffer <aminjaffer(at)gmail(dot)com>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re:Explain plan shows fewer shared blocks when index+table compared to index alone?
Date: 2023-04-04 07:51:03
Message-ID: 925761680594663@yxrvbqz6ajyithqd.vla.yp-c.yandex.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello

This block is reading and checking the visibility map, I think. We don't have to check the visibility map during the index scan - we still need to get a tuple from the table, we can check the visibility for current transaction there. With index only scan, we need to check the visibility map: if the tuple is visible to all transactions, then we return it. Otherwise, we read the tuple from the table as in the index scan (this is your case, as indicated by "Heap Fetches: 1")

Index only scan does not mean that we will not read the tuple from the table. It means that we can skip reading the table if the visibility map allows it for given tuple.

regards, Sergei

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tim Slechta 2023-04-04 14:46:15 Why are commits consuming most of the database time?
Previous Message Amin Jaffer 2023-04-04 06:21:11 Explain plan shows fewer shared blocks when index+table compared to index alone?