Postgres & SQL in practice

Indexes, connection pooling, locking, row-level security, schema design and query patterns: the Postgres and SQL that holds up in production, on any host.

Add to library

Try a card

Postgres & SQL in practice1 of 3

A query filters a large table on an unindexed column. What does EXPLAIN show, and what's the fix?

Say it in your head first.

Questions

01A query filters a large table on an unindexed column. What does EXPLAIN show, and what's the fix?
02For customers c join orders o on o.customer_id = c.id, which side of the join needs an index?
03Does Postgres index foreign key columns automatically?
04A query filters on status = 'pending' and created_at > '2024-01-01'. One composite index or two single-column ones?
05In a composite index, which columns go first?
06Can an index on (status, created_at) serve where created_at > '2024-01-01' on its own?
07What does include (...) add to an index, and why?
08What is a partial index, and when does it pay off?

…and 53 more cards. The answers are yours to find.