Databases & SQL Interview Questions: Indexes

A database index is a separate data structure, typically a B-tree, that lets the database locate matching rows without scanning the whole table, so queries that filter on the indexed column usually run faster. The index must stay in sync with the table, so writes that change indexed data also have to update the index, which adds overhead to INSERT, UPDATE, and DELETE. The statement is true because it names both sides of this classic trade-off: faster reads in exchange for slower writes and extra storage.

The standard SQL syntax for creating an index is CREATE INDEX <index_name> ON <table_name>(<column_name>);. Filling the blank with CREATE produces the valid statement CREATE INDEX idx_email ON users(email);, which creates an index named idx_email on the email column of the users table.

This is true. An index is an auxiliary lookup structure, commonly a B-tree, that lets the engine find matching rows without scanning the whole table, which speeds up reads. The index must also be maintained whenever rows are inserted, updated, or deleted, so write operations pay an extra cost.

Composite B-tree indexes follow the leftmost-prefix rule: the index can be sought efficiently only when the leading column appears in the filter. The index is ordered by last_name first, then first_name, so a filter on first_name alone has no starting point in the index. The query filtering only on first_name must scan the table, while the queries that filter on last_name alone, on last_name and first_name together, or on a range of last_name all constrain the leading column and can use the index.

A B-tree index is ordered by its leading column first and by later columns only within equal leading values, so the index can be traversed directly only when the leading column (last_name) appears in the predicate. A filter on first_name alone omits the leading column; PostgreSQL 17 has no skip scan, so it cannot jump through the index to the matching first_name values and would need a full index scan or a table scan instead. The filters on last_name alone, on a last_name range, and on both columns all constrain the leading column and can seek directly to matching entries.

The statement is false because of the word 'always'. An index speeds up read lookups on that column, but every INSERT, UPDATE, and DELETE must also maintain the index, which adds write overhead and consumes extra storage and memory. On small tables a sequential scan can be as fast or faster, and on low-selectivity columns the optimizer may still choose a full scan because the index would return too many matching rows. For write-heavy workloads the maintenance cost can outweigh the read gains, so indexing is a trade-off rather than a guaranteed improvement.

A composite B-tree index can seek only on a contiguous leftmost prefix of its columns. The filter on dept_id and last_name matches the prefix (dept_id, last_name), so both columns take part in the seek. Filtering on last_name alone skips dept_id, so no prefix match exists. Filtering on dept_id and first_name skips last_name, which breaks the prefix chain, so first_name cannot be seeked. Filtering on first_name and last_name skips dept_id entirely, so neither column can be seeked.

A B-tree stores keys in sorted order, so range and equality predicates on the bare column (>=, >, <, BETWEEN, =) map to a contiguous slice it can scan. Wrapping the column in a function (EXTRACT, a cast) or using a leading-wildcard LIKE or <> makes the index unusable because the stored key order no longer lines up with the predicate.

A B-tree is ordered by the start of the key, so it can seek prefixes (LIKE 'son%') but not patterns that begin with a wildcard (LIKE '%son'), which would require checking every key. Suffix or substring search needs a different approach, such as indexing the reversed string, a trigram index, or full-text search.

A composite B-tree index can perform a directed seek only when the WHERE clause constrains a contiguous prefix starting at the leftmost column. The query filtering on customer_id and order_date constrains the full prefix (customer_id, order_date), and the query filtering on customer_id and status constrains customer_id, so both can seek; in the second, status is applied as a filter after the seek. The query filtering on order_date and status never constrains customer_id, so the index cannot narrow the search to a range and PostgreSQL 17 would use a full index scan or sequential scan instead. The determining principle is the leftmost-prefix rule.

A B-tree index is sorted by its columns in order, so a directed seek requires conditions on a leftmost prefix of the index columns. The query that filters only on order_date and status has no condition on customer_id, the leading column, so PostgreSQL cannot locate a starting point in the tree and at best falls back to scanning the whole index or the table. The other three queries all constrain customer_id (by equality or a range), so they form a valid prefix and can be served by an index seek, with the remaining conditions applied as index or filter conditions.

Although order_date and status are both part of the composite index, the index is a B-tree ordered on (customer_id, order_date, status). A B-tree can only seek when the query predicates include a contiguous prefix from the leftmost column. Since customer_id is not constrained, the index cannot be used for a directed seek; the optimizer would typically choose a full table scan or a full index scan.

A composite B-tree index supports an index seek only when its leading column is constrained, because the tree is ordered by the first column and then by later columns within each prefix. Set I and Set II constrain the leftmost column (and, in Set II, the next column as a contiguous prefix), so the planner can seek to a precise range. Set IV constrains the leftmost column and skips order_date; the seek is on customer_id, and status is evaluated against entries in that range. Set III constrains only later columns, so there is no starting point in the tree and the index must be scanned in full. The version is pinned to PostgreSQL 17 because PostgreSQL 18 added skip scan, which can seek over distinct leading-column values and changes the answer for Set III.

Q3 and Q5 are the queries that cannot use the composite index for range narrowing. The leftmost-prefix rule states that a multi-column B-tree index is usable for range filtering only when predicates begin with the first indexed column and continue without gaps. Q3 (WHERE country = 'US') skips the leading region column, and Q5 (WHERE order_date > '2024-01-01') skips both region and country. In both cases the relevant index entries are distributed throughout the entire index rather than within one contiguous range, so a B-tree range scan offers no selectivity benefit and the PostgreSQL optimizer falls back to a sequential (full table) scan. Q1, Q2, and Q4 all start with region — the leftmost column — so they can traverse a bounded slice of the index and do not require a full scan.

A B-tree on (region, country, order_date) is ordered first by region, then by country within each region, then by date. The query that filters only on country and order_date has no predicate on the leading column, so there is no leftmost prefix to seek on, and without skip-scan support the engine must scan the whole index or table. The query filtering on region and order_date still seeks on region, which narrows the rows even though order_date cannot be used as a seek key past the skipped country column. The query with region and country equality plus a date range uses the full prefix, and the query with an IN list on region and equality on country still seeks on the leading columns.

An index that contains all the columns needed to satisfy a query (columns in the SELECT list, WHERE predicates, JOIN conditions, and ORDER BY clause) lets the database return results directly from the index structure without a separate lookup into the base table (heap or clustered index). This is called a covering index, and the query is said to be covered by it. The benefit is avoiding random I/O to the base table, which is often the most expensive part of executing the query.

Indexes speed reads but every index is an extra structure the database must keep consistent on every write, so more indexes mean slower INSERT/UPDATE/DELETE and more disk and cache pressure. The skill is indexing the columns that selective queries actually filter or join on, not indexing everything.

A composite index is usable only on a left-to-right prefix of its columns. Filtering on the leading tenant_id, or on tenant_id plus a range on the next column created_at, seeks efficiently, and the index's sort order also satisfies the matching ORDER BY after WHERE tenant_id = 42. Filtering on created_at alone (no tenant_id predicate) skips the leading column, so the index can't seek — this is the classic left-prefix rule.

A B-tree index is sorted and balanced, so a lookup descends from the root through internal nodes to a leaf in logarithmic time, comparing the key at each level to choose one child. The leaf holds (or points to) the row location, so the engine reads only the few pages on that path instead of the whole table. The cost is O(log n) page reads versus O(n) for a sequential scan — the reason an equality or range predicate on an indexed column is so much faster.

Normally an index points back to the table row to fetch columns it doesn't store, costing an extra read per match. When the index already holds all referenced columns, the engine does an index-only scan and never touches the table — a big win for hot, selective queries.

A composite B-tree can seek on a column only if every column to its left has an equality predicate, with the first column being constrained at all. Q1 has equalities on customer_id and status followed by a range on created_at, so all three columns are seek keys and the scan covers only the matching slice. Q2 has an equality on customer_id, but the missing status predicate means created_at is not anchored and is only a post-visit filter, so the seek count is 1. Q3 has a range on the leading column, which bounds the scan but prevents status from being a seek key, so the seek count is 1 and the scanned range is wider than Q2's. Q4 has no predicate on the leading column, so there is no seek entry point and the seek count is 0; PostgreSQL 17 has no skip scan to work around this, though PostgreSQL 18 can sometimes do so.

A B-tree index on created_at supports range scans when the WHERE clause applies a range predicate (>, >=, <, <=, or BETWEEN) directly to the indexed column. For example, WHERE created_at > '2024-01-01' lets the optimizer descend the tree to the first leaf entry whose key satisfies the predicate, then scan forward through the leaf pages, returning only matching rows. Equality (created_at = ...) can also use the index, but it is a point lookup rather than a range scan. Predicates on other columns, or expressions wrapping created_at (for example date_trunc(...)), prevent a direct range seek unless a matching expression index exists. Note that the planner may still choose a sequential scan on small tables or when many rows match, so the clause only makes the index usable.

Correlation only enters the cost of a plain Index Scan. In cost_index() (via the B-tree cost estimator), the planner interpolates the heap page-fetch cost between a best case (sequential, when the heap is in index order) and a worst case (one random fetch per tuple) using the squared correlation. Because it is squared, a correlation of -1.0 is treated exactly like 1.0, so a high-magnitude value of either sign lowers the estimated heap I/O. The Bitmap Heap Scan estimate (cost_bitmap_heap_scan()) instead estimates distinct heap pages fetched from the selectivity and table size, and does not use correlation. Therefore a near-0 correlation does not make the plain index scan cheaper, a low correlation does not raise the bitmap estimate, and a negative correlation is not a worst case.