Indeks Komposit dan Indeks Covering

Many queries filter on more than one column. A composite index spans multiple columns and can serve such queries far better than separate single-column indexes.

List columns in the order you want them indexed.

A composite index on (a, b) helps queries filtering on a alone or a AND b, but generally NOT queries filtering only on b.

PostgreSQL can use any leftmost prefix of the index. So (a, b, c) serves filters on a, on a+b, and on a+b+c.

Put the most selective or most frequently filtered column first, often an equality column before a range column, to maximize how many rows the index eliminates early.

A normal index scan finds matching rows then visits the table (the heap) to read the other columns. Those extra fetches cost I/O.

A covering index contains every column the query needs, so PostgreSQL answers from the index alone, an Index Only Scan, with no heap visit.

The INCLUDE clause adds non-key columns to the index leaf pages so they are available without being part of the searchable key.

Check the plan; you want to see Index Only Scan rather than Index Scan plus heap fetches.

Index Only Scans still need the heap when pages are not marked all-visible. Keeping tables well-vacuumed maintains the visibility map so scans stay index-only.

More columns mean larger indexes and slower writes. Add covering columns only for hot queries, not everywhere.

You learned how composite indexes serve multi-column filters under the leftmost prefix rule, how column order and selectivity matter, and how covering indexes with INCLUDE enable Index Only Scans, balanced against larger size and write cost.