Napkin Math #21: Index Merges vs Composite Indexes in Postgres and MySQ

While working with Readwise on optimizing their database, I found myself asking the question: How much faster is a composite index compared to letting the database do an index merge of multiple indexes? Consider this query:

SELECT count(*) /* matches ~100 rows out of 10M */
FROM table
WHERE int1000 = 1 AND int100 = 1
/* int100 rows are 0..99 and int1000 0...9999 */

/* composite index */
CREATE INDEX ON table (int1000, int100)

/* versus single indexes for index merge */
CREATE INDEX ON table (int1000)
CREATE INDEX ON table (int100)

Composite indexes are about 10x faster than index merges. In Postgres, the gap is larger than in MySQL because Postgres doesn’t support index-only scans for queries that involve index merges.

Read the full post here.