Skip to Content
SQL Advanced: Queries That Hold Up Under Load
Advanced
0 likes
Courses
SQL Advanced: Queries That Hold Up Under Load

Check Yourself: Indexes That Earn Their Cost

Five scenario questions on column order, index-only scans, partial indexes, expression indexes and exclusion constraints. If you miss more than one, reread the lessons in this section.

5 min SQLDatabasesTesting and DebuggingPerformance Optimization Advanced
Prev Next
Fullscreen Share
Course content

Sign in to save your progress.

Course Home
1. A table has an index on (customer_id, order_date) and 8 different customers. A query filters only on order_date. On PostgreSQL 18, what can happen?
The index is used only if order_date is added to INCLUDE. The planner can use the index with a skip scan, because the first column has few different values. The index is always as fast as one that starts with order_date. The index can never be used, because the leading column is missing.
2. An Index Only Scan shows Heap Fetches: 14000 on a table that was just updated heavily. What brings the number back down?
Adding more columns to INCLUDE. ANALYZE, which refreshes the statistics. REINDEX, which rebuilds the index. VACUUM, which updates the visibility map so pages can be answered from the index alone.
3. There is a partial index on orders (customer_id) WHERE status = 'new'. The application sends the query as a prepared statement with status = $1, and the plan is generic. What happens?
The query fails with an error about the partial index. The index is used only for the first five executions and then dropped. The index is used, because the parameter is replaced before planning. The index is not used, because the planner cannot prove that the parameter equals new.
4. The column email has an index, but WHERE lower(email) = 'ana@example.com' reads the whole table. What is the right fix?
Create an index on the expression lower(email), and use the same expression in the query. Add INCLUDE (email) to the existing index. Use email ILIKE 'ana%' instead. Change the index to BRIN.
5. No two bookings for the same room may overlap in dates, and two users may book at the same moment. Which design enforces this?
A BRIN index on the date column. A check in the application before each INSERT. An exclusion constraint on a GiST index, with room compared by equals and the date range compared by overlap. A UNIQUE constraint on room and start date.

About

Five scenario questions on column order, index-only scans, partial indexes, expression indexes and exclusion constraints. If you miss more than one, reread the lessons in this section.

Duration
5 min
Last update
09/18/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.