Skip to content
Tech Interview Prep
Technical interview guide

Indexing & Query Performance

Why some queries are instant and others scan the whole table, and how an index (usually a B-tree) changes that.

Read
25 min
Practice MCQs
25
Interview Q&A
25
Edition
v3
Editorial status
Reviewed

Scope: SQL principles with PostgreSQL 18 examples; vendor-specific behavior must be verified.

Curated: · Written: · Reviewed:

25 questions

MCQ-25A query is performing poorly, and the DBA is considering adding an index to resolve the slow execution time. Before committing to the index, what is the most critical initial check to determine if indexing is the correct solution?
MCQ-20On a 5,000,000-row `orders` table, this query is slow: ```sql SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped'; ``` Two plans are captured: ``` EXPLAIN Index Scan using orders_customer_idx on orders (cost=0.43..24.15 rows=8 width=120) EXPLAIN ANALYZE Index Scan using orders_customer_idx on orders (cost=0.43..24.15 rows=8 width=120) (actual time=0.048..2871.3 rows=412000 loops=1) Planning Time: 0.180 ms Execution Time: 2871.6 ms ``` Which conclusion does the second output support?
MCQ-23A nightly report on PostgreSQL 16 runs in 7.3 s. `orders` has 5,000,000 rows and `customers` has 200,000. The plan runs single-process: no Gather node, no parallel workers: ``` Nested Loop (actual time=0.05..7300.00 rows=180000 loops=1) -> Seq Scan on orders (actual time=0.02..1900.00 rows=180000 loops=1) -> Index Scan using customers_pkey on customers (actual time=0.02..0.03 rows=1 loops=180000) ``` In EXPLAIN ANALYZE, `actual time` is the average per loop. Roughly how much total time did the inner Index Scan consume?
MCQ-21A table has a composite B-tree index on (a, b, c). Which predicate set lets the index scan set its bounds from all three columns, instead of seeking on a prefix and filtering the rest?
MCQ-15A Postgres 16 query on a 40M-row `orders` table runs `SELECT id, total FROM orders WHERE customer_id = 42 AND status = 'shipped'`. The only index is on `(customer_id)`. EXPLAIN ANALYZE reports `Index Scan using orders_customer_id_idx`, `Index Cond: (customer_id = 42)`, `Filter: (status = 'shipped')`, `Rows Removed by Filter: 11800`, `rows=200`, `actual time=0.03..38.2 ms`. Which statement about this scan is correct?
MCQ-3`events` holds 40M rows across 8,000 tenants (about 5,000 rows each) and has a B-tree index on `(tenant_id, created_at)`. Which query can use that index to read only the rows it returns?
MCQ-4`events` holds 200M rows, spread evenly across 200 tenants and one year of `created_at` values. The only index on it is `idx_created (created_at, tenant_id)`. A dashboard runs this on every refresh: ```sql SELECT count(*), avg(payload_len) FROM events WHERE tenant_id = 42 AND created_at >= now() - interval '30 days'; ``` `EXPLAIN (ANALYZE, BUFFERS)` shows `Index Scan using idx_created`, 16,400,000 index entries read, 1.4 s. Which change brings the entries read down the most, and to what?
MCQ-13On MySQL 8.0, a `users` table has 5,000,000 rows and an index on `phone VARCHAR(20)`. Query A runs `SELECT id FROM users WHERE phone = 5551234;` and `EXPLAIN` reports `type: ALL, rows: 5000000`. Query B runs `SELECT id FROM users WHERE phone = '5551234';` and `EXPLAIN` reports `type: ref, rows: 1`. Why does Query A examine every row while Query B examines one?
MCQ-2A MySQL 8.0 `events` table holds 300M rows with an index on `created_at DATETIME(3)`. `SELECT id FROM events WHERE YEAR(created_at) = 2026` scans the whole table. Which rewrite is sargable and returns exactly the same rows?
MCQ-1A table has `CREATE INDEX idx_desc ON items (description)`, and `SELECT description FROM items WHERE description LIKE '%widget%'` runs a sequential scan. Which explanation accounts for the planner's choice?
MCQ-12On SQL Server 2022, `Orders` has a clustered primary key on `OrderId` and a nonclustered index `IX_Customer` on `CustomerId`. The query `SELECT OrderId, Total, ShipDate FROM Orders WHERE CustomerId = 42` returns 30 rows, and its plan is a seek on IX_Customer plus 30 key lookups. A developer adds `INCLUDE (Total)` to IX_Customer. What is the result?
MCQ-11An `orders` table in Postgres 16 has `CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at)`. The workload's only `customer_id`-only query is `SELECT id, total_cents FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20`. Four other indexes are up for removal review. Which index is the first redundancy candidate?
MCQ-24A `users` table has a boolean `is_deleted` column. In which situation is an index on that column worth its write and storage cost?
MCQ-6A table `events` holds 1,000,000 rows in 10,000 pages (100 rows per page) and has a valid index on `kind`. The query `WHERE kind = 'click'` matches 250,000 rows, spread evenly across the pages. The planner's cost constants are seq_page_cost = 1.0 and random_page_cost = 4.0. Why does the planner's estimate for the index path lose to the sequential scan?
MCQ-5On PostgreSQL 16, `orders` holds 40M rows; `tenant_id` has 5,000 distinct values and `status` has 4. A query filters `WHERE tenant_id = 42 AND status = 'pending'`. EXPLAIN ANALYZE shows the planner estimated 2,000 rows from that filter but the scan returned 380,000, and it chose a Nested Loop that ran 380,000 index lookups into `customers` in 11 s. Which change makes the planner estimate this filter correctly?
MCQ-18PostgreSQL 15. `shipments` holds 20,000,000 rows. `carrier` has 200 distinct values and `'FastFreight'` is 0.1% of rows; `service_level` has 4 values and `'freight'` is 0.1% of rows. FastFreight is a freight-only carrier. This ad-hoc query: ```sql SELECT s.id, c.name FROM shipments s JOIN customers c ON c.id = s.customer_id WHERE s.carrier = 'FastFreight' AND s.service_level = 'freight'; ``` `EXPLAIN (ANALYZE, BUFFERS)` shows a nested loop whose inner `customers_pkey` index scan runs 20,000 times; the `shipments` index scan reports `rows=20` estimated against `actual rows=20000`, and the query takes 41 s. `pg_stats` shows `carrier` MCV `'FastFreight'` = 0.001 and `service_level` MCV `'freight'` = 0.001. `pg_stat_user_tables` shows `last_analyze` 15 minutes ago and `n_mod_since_analyze = 0`. `service_level` has no index. What is the cause of the 1,000× underestimate?
MCQ-7An `events` table holds 240 million rows. `severity` is `'error'` for 0.25% of rows (about 600,000) and takes five other values. Two queries run continuously: - Q1: `SELECT id FROM events WHERE severity = 'error' AND created_at > now() - interval '15 minutes'`. Returns about 40 rows. - Q2: `SELECT id FROM events WHERE created_at > now() - interval '15 minutes'`. Returns about 9,000 rows. The table has `idx_full` on `(created_at)`, 5.2 GB. The team adds `idx_partial` on `(created_at) WHERE severity = 'error'`, 18 MB, and keeps `idx_full`. What does the planner do for Q2?
MCQ-9`events` holds 40M rows and `payload` is jsonb. `CREATE INDEX events_payload_gin ON events USING gin (payload);` already exists. `SELECT id FROM events WHERE payload->'meta' @> '{"source":"web"}';` takes 6.1 s and `EXPLAIN (ANALYZE)` shows a Seq Scan over all 40M rows, while `WHERE payload @> '{"type":"click"}'` on the same table uses `events_payload_gin` in 12 ms. Which index makes the first query use an index scan?
MCQ-16A 2-year-old `events` table holds 21 billion rows (8 TB) appended in `ts` order and never updated or deleted. Queries are `WHERE ts >= $1 AND ts < $2` over one-hour windows anywhere in the history, each returning about 1.2 million rows. The volume has 300 GB free and cannot be expanded this quarter, and the team measured that a B-tree on `ts` would be about 420 GB. Which index should they create?
MCQ-8`orders` holds 200M rows across 2,000,000 pages. A nightly job updated 15M rows, touching 150,000 pages, and committed. Autovacuum is enabled with default settings (`autovacuum_vacuum_scale_factor = 0.2`, `autovacuum_vacuum_threshold = 50`) and has not run on `orders` since. The next morning, `EXPLAIN (ANALYZE, BUFFERS)` on `SELECT count(*) FROM orders` shows `Index Only Scan using orders_pkey` with `Heap Fetches: 14,982,331`. What is the cause?
MCQ-22`orders` holds 40 million rows in a 6.2 GB heap, and the application updates `status` on about 2,000 rows per second. The B-tree index `orders_status_idx` is 3.1 GB; `pgstatindex('orders_status_idx')` reports `avg_leaf_density` 31% and `leaf_fragmentation` 74%. Autovacuum runs on `orders` every few minutes and completes each time. `pg_stat_activity` shows one session `idle in transaction` since six days ago with `backend_xmin` 84213377, and a logical replication slot `analytics` whose `confirmed_flush_lsn` has not moved in six days. The service is 24/7 and cannot take an ACCESS EXCLUSIVE lock on `orders`. Which action will let the dead entries in `orders_status_idx` actually be removed?
MCQ-14PostgreSQL 16. `customers` holds 2M rows and `orders` holds 40M rows; `orders.customer_id` is declared `REFERENCES customers(id)`, and no index exists on `orders.customer_id`. `DELETE FROM customers WHERE id = 12345;` runs for 7.4 s, and `EXPLAIN (ANALYZE, BUFFERS)` on it shows a sequential scan reading all 40M rows of `orders`. What does that plan tell you?
MCQ-10A team runs a plain CREATE INDEX on a 400 GB table. For the 35 minutes it runs, INSERT and UPDATE p99 goes from 12 ms to the app's 30 s client timeout, while SELECT p99 stays at 8 ms. Which lock behaviour accounts for that split?
MCQ-19On the team's staging hardware, a 300M-row load into a table with five B-tree indexes takes 11 hours. Run A keeps the indexes in place; run B drops them, loads, then rebuilds, and finishes in 3 hours. Which mechanism accounts for most of the 8-hour difference?
MCQ-17A Postgres 16 table takes 6,000 INSERTs/s around the clock. Benchmarks on this workload show that each secondary B-tree index adds 25% of the base per-row write cost. A dashboard team asks for four secondary indexes to serve 20 queries per minute. Which outcome should decide whether to add them?