Performance & indexing in the SQL assessment

What it covers

How indexes are used, reading a query plan at a basic level, and recognising patterns that prevent an index from helping.

Topics in the practice set for this area

• performance
• DDL / performance

How it is tested

In the timed assessment, questions in this area use these formats: multiple choice, written scenario. Each attempt draws its own set of questions, and this area gets its own sub-score on the credential when the attempt includes enough questions from it. The assessment questions themselves are never published; the practice set below is written separately for study.

What to practice

• Create a composite index and test which WHERE clauses can and cannot use it.
• Run EXPLAIN on a slow query, identify the scan, and change the query or index so it goes away.
• List the trade-offs an extra index adds to writes and storage.

Try a practice question

From the free practice set (3 questions in this area). Answers and explanations are in the practice set.

• Which scenario does NOT typically benefit from a B-tree index on a column?
• EXPLAIN ANALYZE in Postgres shows a 'Seq Scan' on a million-row table for a query with a WHERE clause. The most likely cause is:
• Compared to DELETE FROM table_name, TRUNCATE TABLE table_name is generally:

More SQL study guides

• How to Pass the Provyn SQL Assessment
• Query construction
• Joins & set logic
• Aggregation & windows
• Transactions & correctness