How To Dev

Introduction Covering Index is used to enable Index-Only Scan with Non-key Columns , initialized using the INCLUDE clause PostgreSQL supports three types of indexes for this feature: B-Tree (Most common), GiST and SP-GiST All other index types ( GIN, BRIN, Hash ) do NOT support the INCLUDE clause. How it works When you create an index with INCLUDE as follows: CREATE INDEX idx_orders_user_id ON orders (user_id) INCLUDE (total_amount, status) Main column (user_id): Located at the upper nodes and leaf nodes of the B-Tree. Used for searching, filtering ( WHERE ) and sorting ( ORDER BY ). Payload columns (total_amount, status): ONLY stored at Leaf nodes. They do not participate in tree sorting or searching operations. The main purpose is to help Postgres perform an Index-Only Scan , meaning Postgres retrieves data for total_amount and status directly from the index without reading the Heap Page , making the SELECT query execution extremely fast. Advantages Avoiding placing those colu...