A dashboard runs SELECT symbol, sum(qty) FROM fills WHERE account_id = 42 AND ts >= now() - interval '1 day' GROUP BY symbol 200 times a minute over a 2 billion row table with an index on (account_id, ts). Say what a covering index changes and estimate the io saved when the predicate matches 30,000 rows.

A dashboard runs SELECT symbol, sum(qty) FROM fills WHERE account_id = 42 AND ts >= now() - interval '1 day' GROUP BY symbol 200 times a minute over a 2 billion row table with an index on (account_id, ts). Say what a covering index changes and estimate the io saved when the predicate matches 30,000 rows.

Approach: Separate the index traversal from the fetch of the row itself, then count how many of each the two plans perform for the stated match count.

Including symbol and qty in the index turns the plan into an index only scan and removes about 30,000 scattered heap fetches per execution, which at 0.1 ms each is roughly 3 seconds of io per run and almost the entire cost of the query at 200 runs a minute. With the index on (account_id, ts) alone the engine walks a contiguous range of index entries, which is cheap, then fetches every matching row from the table to read symbol and qty. Those fetches are scattered across 2 billion rows, so they are random io and they dominate. Declaring the index on (account_id, ts) INCLUDE (symbol, qty) puts both output columns in the leaf pages, so the query is answered from the index alone. Three costs come with it. The index is wider, so it holds fewer entries per page and the range scan reads more index pages. Every insert and update to fills now maintains a wider index, which is write amplification on the hot write path. And an index only scan still visits the table for rows whose visibility is unsettled, so a table written constantly and never vacuumed loses most of the benefit.

Follow-up: How would a materialised rollup on (account_id, symbol, minute) compare on read cost and on freshness?

Key concepts: covering index, index only scan, heap fetch, write amplification.