In senior interviews, just knowing what an index is won't do. You will be asked why the planner chose a sequential scan, which column goes first in a composite index, and why a function in the WHERE clause makes a query slow. This set covers reading plans, designing indexes, sargable conditions, join algorithms and the statistics the optimiser relies on. I use PostgreSQL terms, but the same ideas work in MySQL and SQL Server.
How to practise: read each question and tap the option you think is correct. You will see right away whether you got it, along with a short explanation, and your score is tracked at the bottom of the screen. This set has 15 questions.
Topics covered in this set
- Reading EXPLAIN and EXPLAIN ANALYZE
- Index selectivity and when scans win
- Composite index column order
- Covering indexes, partial indexes and index-only scans
- Sargable predicates and pagination
- Nested loop, hash and merge joins; planner statistics
SQL MCQs with answers and explanations
Q1. In a PostgreSQL plan line Seq Scan on orders (cost=0.00..1834.00 rows=100000 width=40), what do the two cost numbers mean?
a) Milliseconds before and after execution
b) The estimated startup cost and the estimated total cost, in arbitrary planner units
c) CPU and memory usage
d) Minimum and maximum rows
Q2. EXPLAIN ANALYZE shows rows=12 estimated but rows=48000 actual for a filter step. What is the most likely cause and first fix?
a) The index is corrupt; drop the table
b) The server needs more CPU cores
c) The planner's statistics are stale or too coarse; run ANALYZE (or raise the statistics target) so estimates match the data
d) The query has a syntax error
Q3. A query returns about 40% of a large table's rows, and the planner uses a sequential scan even though the filtered column is indexed. Why?
a) Reading most of the table sequentially is cheaper than many random index lookups when the predicate is not selective
b) Indexes only work on primary keys
c) The index must be rebuilt every day
d) The planner ignores indexes on Mondays
Q4. In a users table with millions of rows, which column has the highest selectivity and gains the most from a B-tree index for equality lookups?
a) email (unique per user)
b) gender
c) country (about 50 values)
d) is_active (true/false)
Q5. A frequent query is WHERE tenant_id = ? AND created_at >= ? ORDER BY created_at. Which composite index fits best?
a) (tenant_id, created_at)
b) (created_at) only
c) Two separate single-column indexes only
d) (created_at, tenant_id)
Q6. What is a covering index?
a) An index on every column of the table
b) An index that contains all the columns a query needs, so the database can answer it from the index without reading the table
c) An index stored on a separate disk
d) An index on a view
Q7. Which predicate is sargable, meaning it can use an index on created_at?
a) WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
b) WHERE created_at + INTERVAL '1 day' > NOW()
c) WHERE YEAR(created_at) = 2026
d) WHERE CAST(created_at AS TEXT) LIKE '2026%'
Q8. Why is WHERE name LIKE '%kumar' slow even with a B-tree index on name?
a) A leading wildcard means the index cannot be used to find a starting point, so every row is checked
b) LIKE is deprecated
c) The index only works for numbers
d) B-trees cannot store text
Q9. In MySQL, phone is a VARCHAR column with an index, and a query uses WHERE phone = 9876543210 (a number). What is the likely effect?
a) The query fails to parse
b) Only the first row is returned
c) The index is used normally
d) MySQL converts the column value of every row for comparison, so the index cannot be used and the table is scanned
Q10. When is a nested loop join the planner's best choice?
a) Only for FULL OUTER JOIN
b) When there is no join condition
c) When both inputs are huge and unsorted
d) When the outer input is small and the inner table has an index on the join column
Q11. How does a hash join work?
a) It only works with primary keys
b) It sorts both inputs and walks them together
c) It builds a hash table from the smaller input on the join key and probes it with rows from the larger input; it works only for equality joins
d) It compares every row with every other row
Q12. A merge join is most attractive when:
a) The tables are in different databases
b) One table has a single row
c) The join condition uses LIKE
d) Both inputs are already sorted on the join key, for example by an index
Q13. What does running ANALYZE orders do in PostgreSQL?
a) Prints the query plan
b) Rebuilds all indexes
c) Deletes dead rows
d) Samples the table and updates statistics such as most common values, histograms and distinct counts used by the planner
Q14. Why does ORDER BY id LIMIT 20 OFFSET 200000 get slower as users page deeper, and what is the usual fix?
a) OFFSET locks the table
b) The database must read and discard all skipped rows; keyset pagination such as WHERE id > :last_id ORDER BY id LIMIT 20 avoids that
c) LIMIT does not work with ORDER BY
d) OFFSET is cached; there is no problem
Q15. Most queries on a large jobs table look only for status = 'pending', which is under 1% of rows. Which index is most efficient?
a) A hash index on id
b) No index
c) An index on every column
d) A partial index such as CREATE INDEX ON jobs (created_at) WHERE status = 'pending'
Quick revision notes
- Plan cost is startup..total in planner units. In EXPLAIN ANALYZE, compare estimated rows with actual rows.
- Indexes help when a condition matches few rows. When it matches a big part of the table, a sequential scan wins.
- Composite index: equality columns first, then range or sort column.
- For sargable queries, keep the indexed column untouched and the types matching. Avoid leading wildcards, and avoid SELECT * if you want index-only scans.
- Nested loop for a small outer side with an indexed inner side. Hash for big equality joins. Merge for sorted inputs. Keep statistics fresh with ANALYZE.
Frequently asked questions
How do I start tuning a slow query?
Run EXPLAIN ANALYZE. Find the step that takes the most time, or where estimated and actual rows are furthest apart. Then fix the statistics, the index or the query for that step.
Can I force PostgreSQL to use an index?
Core PostgreSQL has no hints. You can test by setting enable_seqscan = off in your session, but the real fix is usually better statistics, a better index or a sargable rewrite.
Is EXPLAIN ANALYZE safe on production?
It really runs the statement. For a SELECT that is usually fine. For INSERT, UPDATE or DELETE, wrap it in a transaction and roll back.
Comments
Post a Comment