$ ./guess_the_languageSQL & Databases
SQL & Databases · hard · #23

What does INCLUDE (total) make possible?

-- PostgreSQL 11+
CREATE INDEX ON orders (customer_id)
  INCLUDE (total);

SELECT total FROM orders
WHERE customer_id = 42;
  1. A uniqueness check on total
  2. An index-only scan for this query
  3. Partitioning the table by total
  4. Sorting the index by total too
Storing total in the index leaves makes it covering, so the query can be answered without visiting the table heap.
Play SQL & Databases ▸