Methods of reading database Tables

In this post, Two typical access methods, sequential scan and B-tree index scan, are outlined here:

✔️ Sequential scan:

All tuples in all pages are sequentially read by scanning all line pointers in each page. show below-

✔️ B-tree index scan:

An index file contains index tuples, each of which is composed of an index key and a TID pointing to the target heap tuple.

If the index tuple with the key that you are looking for has been found, PostgreSQL reads the desired heap tuple using the obtained TID value.

For example, In below image , TID value of the obtained index tuple is ‘(block = 7, Offset = 2)’. It means that the target heap tuple is 2nd tuple in the 7th page within the table, so PostgreSQL can read the desired heap tuple without unnecessary scanning in the pages.

✔️ References:

1. https://age.apache.org/
2. https://github.com/apache/age
3. https://www.interdb.jp/pg/index.html