Indexes make reads fast. They are not free.
Writes get slower
Each INSERT, UPDATE and DELETE changes the table and every index on it.
INSERT one rowyou write
table + index 1 + index 2 + index 3PostgreSQL writes
The lab copies 100 000 rows into a table without indexes and into a table with 4 indexes. The second copy takes several times longer.
Indexes take space
sql
SELECT pg_size_pretty(pg_relation_size('members')) AS table_size,
pg_size_pretty(pg_indexes_size('members')) AS index_size;pg_relation_size('t')is the size of a table or one index, in bytes.pg_indexes_size('t')is the size of all the indexes of tablet.pg_size_pretty(…)writes a size in kB or MB.
With six indexes, members uses about 3.7 MB for the table and 7.3 MB for the indexes: twice the size of the data.
When the planner skips an index
An index can exist and still not appear in the plan. The planner picks the cheapest plan:
| Case | Example | Plan |
|---|---|---|
| small table | 12 rows in one page | Seq Scan: one page is cheaper than the index |
| most rows match | joined_on > '2020-03-01' (97 % of rows) | Seq Scan |
| few rows match | joined_on > '2025-06-01' (1 % of rows) | Bitmap Heap Scan on the index |
| function or arithmetic on the column | lower(email) = '…', joined_on + 7 = '…' | Seq Scan |
The index holds the column's values as stored. It has no values of lower(email) or of joined_on + 7. Two fixes:
- Write the condition on the bare column:
joined_on = '2021-06-02', notjoined_on + 7 = '2021-06-09'. - Index the expression: an index can hold the result of an expression. Put the expression in its own parentheses:
sql
CREATE INDEX members_lower_email_idx ON members ((lower(email)));Then WHERE lower(email) = '…' can use it.
Index on purpose: add an index for a query that is slow and frequent, on columns with many different values. Do not index every column.