An Index Lookup in PostgreSQL Ends With a Trip to the Heap Page
In PostgreSQL every index leaf entry holds the indexed value plus a ctid, the address of the row in the heap, so finding the entry is only half the job: the engine must then read the heap page to get the rest of the columns and to check whether that row version is visible to your query. Those heap visits are random reads, one per matching row, which is why an index that matches ten rows is wonderful and the same index matching half a million rows can be worse than reading the table. This step is also why a Bitmap Heap Scan exists, which collects the addresses first and then visits pages in order.
Questions this Concept answers
Why must PostgreSQL usually visit the heap page even after the index has located a matching entry?
J
jeremy
Video
How B-tree Indexes Cut Query Cost in PostgreSQL and What They Cost on Write
A database index is a secondary structure that trades storage and write cost for read speed by mapping key values to physical tuple locations (page number plus item identifier), so the planner can bo…