Learn SQL: Day 6 – Part 14 Interview Questions

Here is a clear, concise breakdown of each question.

Q1: What is an index?

An index is a separate data structure (most commonly a B-tree) created on a database table to speed up data retrieval. Think of it like the index at the back of a textbook: instead of scanning all 500 pages to find a specific term, you look it up in the index and jump straight to the exact page.

• Trade-off: Indexes make SELECT queries much faster, but slow down INSERT, UPDATE, and DELETE operations because the index must be updated every time data changes.

Q2: What is a B-tree?

A B-tree (Balanced Tree) is a self-balancing search tree data structure designed to keep data sorted and allow searches, sequential access, insertions, and deletions in logarithmic time ($O(\log n)$).

• How it works: It stores keys in a multi-level tree hierarchy. Every lookup starts at the root, traverses down nodes based on value comparisons, and reaches a leaf node containing the actual record pointer.
• Why databases use it: Because it stays balanced, a query over 1,000,000 rows only takes roughly 3 to 4 disk reads to find a match, making $O(\log n)$ dramatically faster than an $O(n)$ full table scan.

Q3: EXPLAIN vs. EXPLAIN ANALYZE

Q4: What is a Sequential Scan?

A Sequential Scan (Seq Scan) means PostgreSQL reads the entire table from top to bottom, row by row, block by block on the disk, to find matching records.

• Complexity: $O(n)$ time complexity.
• When it’s good: Small tables (where loading an index adds unnecessary overhead) or queries returning a large percentage of the table.
• When it’s bad: Large tables with specific filters looking for a small handful of rows.

Q5: Why might PostgreSQL ignore an index?

Even if an index exists, the Postgres query planner might skip it and use a Sequential Scan because:

1. Low Table Cardinality / Small Size: If the table fits into a few memory pages, reading it sequentially is faster than fetching index pages + table pages.
2. Low Selectivity (High Fetch Volume): If a query returns >10–20% of the entire table, a sequential disk read is cheaper than random I/O index lookups.
3. Applying Functions on the Indexed Column: WHERE UPPER(email) = 'USER@DOMAIN.COM' invalidates a standard B-tree index on email (use a functional index instead).
4. Outdated Statistics: If ANALYZE hasn’t been run recently, Postgres might miscalculate row estimates and choose a suboptimal plan.
5. Data Type Mismatches: Implicit casting (e.g., comparing a varchar column with an integer literal) can prevent index usage.

Q6: What is selectivity?

Selectivity measures how specific or filtering a query predicate is. It is represented as the ratio of matching rows to total rows in a table.

$$\text{Selectivity} = \frac{\text{Number of Matching Rows}}{\text{Total Rows in Table}}$$

• High Selectivity (close to 0 or fractional %): Returns a tiny fraction of total rows (e.g., searching by user_id = 42). Indexes shine here.
• Low Selectivity (close to 1.0 or 100%): Returns most or all rows (e.g., searching by country IS NOT NULL). Sequential scans win here.

Q7: Should foreign keys be indexed?

Yes, almost always.

While database systems automatically create unique indexes for Primary Keys, they do NOT automatically index Foreign Keys. You should manually create indexes on foreign keys because:

1. Join Speed: JOIN conditions frequently filter on foreign keys (e.g., orders.user_id = users.id).
2. Deletion Cascades & Locks: Deleting a row in a parent table (e.g., deleting a User) with ON DELETE CASCADE requires searching the child table (Orders). Without an index on orders.user_id, Postgres must perform a full sequential scan on Orders and place heavy locks on it.

Q8: Why can too many indexes hurt performance?

1. Slower Writes (INSERT, UPDATE, DELETE): Every write to the table requires updating all associated indexes. 5 indexes on a table mean 6 writes per single row inserted.
2. Increased Storage & Memory Pressure: Indexes consume RAM (buffer pool) and disk space. Large indexes displace cached data pages from memory.
3. Planner Overhead: The Postgres optimizer has to evaluate more execution paths, increasing query planning latency.

Q9: What is the Leftmost Prefix Rule?

The Leftmost Prefix Rule applies to composite (multi-column) indexes like CREATE INDEX idx_abc ON table(a, b, c);.

A composite index can only be used if the query filter includes columns starting from the leftmost column in the index definition.

• Can use index:
  • WHERE a = 1
  • WHERE a = 1 AND b = 2
  • WHERE a = 1 AND b = 2 AND c = 3
• CANNOT use index efficiently (or at all):
  • WHERE b = 2 (skips a)
  • WHERE c = 3 (skips a and b)
  • WHERE b = 2 AND c = 3 (skips a)

Q10: Would you create an index on active BOOLEAN? Why or why not?

Generally, NO.

Why?

A standard BOOLEAN column has extremely low selectivity. It only has 2 possible values (TRUE / FALSE), meaning each value roughly accounts for ~50% of the data. Searching for WHERE active = true would force the engine to fetch half the table, which is far slower via random index access than a fast sequential scan.

The Exception (Partial Indexes)

If active = false accounts for 99% of rows and active = true is rare (1%), or vice-versa, a Partial Index is ideal:

SQL

-- Indexes ONLY the rare subset, keeping the index tiny and ultra-fast
CREATE INDEX idx_active_users ON users (id) WHERE active = true;