Partial and Expression Indexes
99% of these rows will never be queried by status again. Index only the 1% that matters, and measure exactly how much smaller that makes it.
A regular index has to include an entry for every row in the table, whether or not that row will ever be found through it. When a query pattern only ever cares about a small, identifiable slice of a table — active rows, pending orders, unprocessed events — indexing the other 99% is pure waste: it makes the index bigger, slower to maintain on every write, and slower to scan through even for the rows that do matter. A partial index fixes this directly, by attaching a WHERE clause to CREATE INDEX itself, so only rows matching that condition are ever included in the index structure at all.\n\nA related but different problem: a query that needs to match 'user@example.com' case-insensitively against stored emails of any capitalization cannot use a plain index on the email column at all, since the index is sorted by the literal stored value, not by any transformation of it. An expression index solves this by indexing the output of a function — lower(email) — rather than the raw column, letting a query written the same way use the index directly.
A Partial Index, Measured Against a Full One
CREATE INDEX idx_partial_pending ON idx_orders2(status) WHERE status = 'pending';
SELECT pg_size_pretty(pg_relation_size('idx_partial_pending'));
-- 16 kB
CREATE INDEX idx_full_status ON idx_orders2(status);
SELECT pg_size_pretty(pg_relation_size('idx_full_status'));
-- 784 kB
With 1,000 pending rows out of 101,000 total, the partial index is roughly 49 times smaller than a full index on the same column — because it only ever contains entries for the 1,000 rows matching its WHERE clause, not all 101,000.
The Partial Index, Used and Correctly Bypassed
EXPLAIN SELECT * FROM idx_orders2 WHERE status = 'pending';
Index Scan using idx_partial_pending on idx_orders2 (cost=0.15..31.14 rows=933 width=18)
No Index Cond line at all — the WHERE clause on the query exactly matches the WHERE clause baked into the index, so PostgreSQL does not even need to recheck the condition; every row the index contains already qualifies.
EXPLAIN SELECT * FROM idx_orders2 WHERE status = 'completed';
Seq Scan on idx_orders2 (cost=0.00..1857.50 rows=100067 width=18)
Filter: (status = 'completed'::text)
Correctly falls back to a sequential scan — the partial index structurally does not contain any completed rows at all, so it cannot answer this query, and there is no full index present to fall back on either.
An Expression Index for Case-Insensitive Lookups
CREATE INDEX idx_lower_email ON idx_users(lower(email));
EXPLAIN SELECT * FROM idx_users WHERE lower(email) = 'user500@example.com';
-- Index Scan using idx_lower_email, Index Cond: (lower(email) = ...)
EXPLAIN SELECT * FROM idx_users WHERE email = 'User500@Example.com';
-- Seq Scan, Filter: (email = 'User500@Example.com'::text)
The index is built on the result of lower(email), not on email itself — so a query wrapping the column in lower() the same way finds a direct match, while a query comparing the raw column, even to an equivalent value, cannot use it at all.
Partial index
An index built with a WHERE clause in its own CREATE INDEX statement, restricting which rows are ever included in the index structure. Only queries whose own WHERE clause is provably implied by the index's WHERE clause can use it — a query for a status the partial index excludes falls back to whatever other index or scan method is available, exactly as if the excluded rows were never indexed at all, because they genuinely are not.
Expression index
An index built on the result of a function or expression applied to one or more columns, rather than on a raw column value directly — CREATE INDEX ON table(lower(col)) indexes the lowercased value of col, not col itself. A query can only use it when written to apply the identical expression, since the index has no entries for the raw, untransformed column value at all.
📏 Build a Partial Index and Measure It Against a Full One
Create a partial index covering only pending rows, then create a full index on the same column, and compare their real sizes.
psql -U postgres -d beer_db -c "CREATE INDEX idx_partial_pending ON idx_orders2(status) WHERE status = 'pending';" -c "SELECT pg_size_pretty(pg_relation_size('idx_partial_pending')) AS partial_idx;"psql -U postgres -d beer_db -c "CREATE INDEX idx_full_status ON idx_orders2(status);" -c "SELECT pg_size_pretty(pg_relation_size('idx_full_status')) AS full_idx;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX idx_partial_pending ON idx_orders2(status) WHERE status = 'pending';" -c "SELECT pg_size_pretty(pg_relation_size('idx_partial_pending')) AS partial_idx;" SET CREATE INDEX partial_idx ------------- 16 kB (1 row) student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX idx_full_status ON idx_orders2(status);" -c "SELECT pg_size_pretty(pg_relation_size('idx_full_status')) AS full_idx;" SET CREATE INDEX full_idx ---------- 784 kB (1 row)
✅ Confirm the Partial Index Serves Its Own Query
Drop the full index so only the partial one remains, then confirm EXPLAIN uses it for a query on pending status.
psql -U postgres -d beer_db -c "DROP INDEX idx_full_status;"psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_orders2 WHERE status = 'pending';"student@lab:~$ psql -U postgres -d beer_db -c "DROP INDEX idx_full_status;" SET DROP INDEX student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_orders2 WHERE status = 'pending';" SET QUERY PLAN --------------------------------------------------------------------------------------------- Index Scan using idx_partial_pending on idx_orders2 (cost=0.15..31.14 rows=933 width=18) (1 row)
🚧 Confirm It Correctly Falls Back for Excluded Rows
Run the same kind of query for completed status, and confirm the partial index cannot and does not serve it.
psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_orders2 WHERE status = 'completed';"student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_orders2 WHERE status = 'completed';" SET QUERY PLAN -------------------------------------------------------------------- Seq Scan on idx_orders2 (cost=0.00..1857.50 rows=100067 width=18) Filter: (status = 'completed'::text) (2 rows)
🔡 Build an Expression Index for Case-Insensitive Lookups
Create an index on lower(email), then compare a query using lower() against one comparing the raw column.
psql -U postgres -d beer_db -c "CREATE INDEX idx_lower_email ON idx_users(lower(email));"psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_users WHERE lower(email) = 'user500@example.com';"psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_users WHERE email = 'User500@Example.com';"student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX idx_lower_email ON idx_users(lower(email));" SET CREATE INDEX student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_users WHERE lower(email) = 'user500@example.com';" SET QUERY PLAN ---------------------------------------------------------------------------------- Index Scan using idx_lower_email on idx_users (cost=0.41..8.43 rows=1 width=25) Index Cond: (lower(email) = 'user500@example.com'::text) (2 rows) student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_users WHERE email = 'User500@Example.com';" SET QUERY PLAN ------------------------------------------------------------ Seq Scan on idx_users (cost=0.00..1857.50 rows=1 width=25) Filter: (email = 'User500@Example.com'::text) (2 rows)
Lab 3.2.2 complete. Partial and expression indexes, measured rather than assumed:\n\n\n Partial index size vs full index : ✅ 16 kB vs 784 kB — ~49x smaller\n Partial index serves its own query : ✅ Index Scan, no recheck needed\n Partial index correctly bypassed : ✅ Seq Scan for excluded status\n Expression index on lower(email) : ✅ used for lower(), bypassed for raw column\n
Enable JavaScript to run the live terminal and track your progress.