Skip to content

.tip

· one of the small things

INCLUDE the columns the query reads as well as the ones it filters

For an index-only scan, every column the query touches has to be in the index. Put the filter columns in the key and the rest in INCLUDE, so they don't bloat the key or the sort.

CREATE INDEX orders_customer_recent ON orders (customer_id, created_at DESC)
  INCLUDE (status, total_cents);

Then check that the plan says Index Only Scan, and that Heap Fetches is close to zero after a vacuum.

postgresqlperformance