Optimising a query
The method for a slow query: measure, read the plan, find the guilty operation, apply a remedy (index, usable predicate, useful columns, join, keyset pagination, statistics) and measure again.
- 22 cards
- 5 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
A few cards from the deck
Sargable predicate: what is it?
Show the answer
A WHERE condition written so the database can rely on an index: the column appears alone on one side, with no function or computation altering it.
WHERE EXTRACT(YEAR FROM date_commande) = 2024computes a value for every row and stops the index on date_commande from helping.WHERE date_commande >= '2024-01-01' AND date_commande < '2025-01-01'allows it.An index exists on date_commande and the query stays slow. Which rewrite of the filter lets it be used?
```sql
WHERE EXTRACT(YEAR FROM date_commande) = 2024`WHERE date_commande >= '2024-01-01' AND date_commande < '2025-01-01'WHERE TO_CHAR(date_commande, 'YYYY') = '2024' AND date_commande IS NOT NULLWHERE EXTRACT(YEAR FROM date_commande) IN (2024) AND date_commande IS NOT NULLWHERE date_commande + 0 >= '2024-01-01' AND date_commande + 0 < '2025-01-01'
Show the answer
WHERE date_commande >= '2024-01-01' AND date_commande < '2025-01-01'The right rewrite compares the bare column to bounds: the database goes down the index to the start of the year and reads to the end. The other three still apply a function or a computation to the column.
To know whether a remedy works, change only … thing at a time, then … the query again.
Show the answer
To know whether a remedy works, change only one thing at a time, then measure the query again.
If you change three things at once, you no longer know which one helped or which one hurt. Measuring before and after is the only proof.
Keyset pagination: what is it?
Show the answer
Pagination that restarts from the last key seen with
WHERE id > ? ORDER BY id LIMIT n, instead of skipping a number of rows with OFFSET.With an index on the key, the database goes straight to the right position: page 5,000 costs as much as page 1, whereas OFFSET rereads and discards all previous rows.