Database Indexing Quiz

Database Indexing Quiz

Test your knowledge of B-trees, indexing strategies, and query performance optimization.

1. 1. What is a database index?

   • A backup copy of the entire table stored on a separate disk
   • A separate data structure that maps column values to row locations, speeding up data retrieval
   • A constraint that enforces row uniqueness across a table
   • A transaction log that records every INSERT, UPDATE, and DELETE

2. 2. Which of the following best describes the read vs. write trade-off of adding an index?

   • Indexes speed up both reads and writes equally
   • Indexes slow down reads because the planner must check the index first
   • Indexes speed up reads but add overhead to INSERT, UPDATE, and DELETE operations
   • Indexes only affect DELETE performance, not SELECT

3. 3. What distinguishes a primary index from a secondary index?

   • A primary index is always a hash index; secondary indexes are always B-Trees
   • A primary index is built on the primary key and typically determines physical row order; secondary indexes are on other columns
   • A primary index can only be defined at table creation; secondary indexes can be added at any time
   • There is no practical difference: the terms are interchangeable

4. 4. What is a full table scan, and when does the database use one?

   • Reading every row in a table sequentially; used when no suitable index exists or when the planner estimates it is cheaper than an index scan
   • Reading only the first and last rows of a table to estimate table size
   • A special operation that rebuilds all indexes on the table
   • A scan that only reads rows matching a WHERE clause

5. 5. Given the query `SELECT * FROM orders WHERE customer_id = 42`, which condition allows the database to use an index on `customer_id`?

   • The table must have fewer than 1,000 rows
   • An index must exist on the `customer_id` column
   • `customer_id` must be the first column defined in the table
   • The query must use `LIMIT 1`

6. 6. What does a UNIQUE index guarantee?

   • That the indexed column is never NULL
   • That no two rows share the same value in the indexed column(s)
   • That the index is stored in a separate tablespace from the table data
   • That SELECT queries on that column always return results in sorted order

7. 7. In PostgreSQL, what does the `EXPLAIN` command show you?

   • The SQL standard version of your query after automatic rewriting
   • The query execution plan the planner intends to use, including node types, estimated costs, and row counts
   • A list of all indexes on the tables referenced in the query
   • The wall-clock time the query took to execute

8. 8. What is the key difference between a clustered and a non-clustered index?

   • A clustered index stores the actual row data in index order; a non-clustered index stores only pointers to the data rows
   • A clustered index can only be on a single column; non-clustered indexes support multiple columns
   • Non-clustered indexes are faster for all query types
   • Clustered indexes require more disk space than non-clustered indexes

9. 9. Why are indexes on low-cardinality columns (e.g., a boolean `is_active`) often ineffective?

   • The database cannot build B-Tree indexes on boolean columns
   • When a column has very few distinct values, an index scan may touch most of the table, making a sequential scan cheaper
   • Low-cardinality indexes are automatically dropped by the database engine
   • Indexes on booleans require a special storage engine not available in standard PostgreSQL

10. 10. What happens to an index when you update a row's indexed column?

    • Nothing: indexes are refreshed only during the next VACUUM
    • The old index entry is deleted and a new entry is inserted to reflect the updated value
    • The entire index is rebuilt from scratch after every UPDATE
    • The index entry is marked as invalid until the next SELECT is executed

11. 11. When the B-Tree index is traversed to find a key value, what is the time complexity of the search?

    • O(n): linear in the number of rows
    • O(log n): logarithmic in the number of rows
    • O(1): constant time regardless of table size
    • O(n²): quadratic in the number of rows

12. 12. Which statement correctly contrasts a hash index with a B-Tree index?

    • Hash indexes support range queries (>, <, BETWEEN); B-Trees do not
    • Hash indexes offer O(1) equality lookups but cannot support range queries or ORDER BY; B-Trees support all of these
    • Hash indexes store data in sorted order, making range scans very fast
    • B-Tree and hash indexes have identical performance characteristics; the choice is purely cosmetic

13. 13. Given a composite index on `(last_name, first_name)`, which WHERE clause can use the index efficiently?

    • `WHERE first_name = 'Alice'` (skipping `last_name`)
    • `WHERE last_name = 'Smith'`
    • `WHERE first_name = 'Alice' AND last_name = 'Smith'`: only when both columns appear
    • Neither column can use the index unless the query includes all indexed columns

14. 14. What is a covering index?

    • An index that spans every column in the table
    • An index that contains all columns needed to satisfy a query, allowing the engine to return results from the index alone without accessing the table heap
    • An index that automatically expands to cover new columns as they are added to the table
    • An index that covers NULL values that standard indexes would normally skip

15. 15. What is index selectivity, and why does it matter to the query planner?

    • The number of pages the index occupies on disk; higher page counts mean faster scans
    • The ratio of distinct values to total rows; high selectivity means few rows per key value, making the index more useful for lookups
    • Whether the index was created on a primary key column
    • The percentage of queries in the workload that reference the indexed column

16. 16. What is a partial index in PostgreSQL?

    • An index built on only the first N bytes of a text column
    • An index that includes only rows satisfying a WHERE predicate, reducing its size and maintenance cost
    • An index that covers only half of a composite key
    • An index automatically created by the planner for a single query execution

17. 17. What is a function-based (expression) index?

    • An index stored as a stored procedure that regenerates itself nightly
    • An index built on the result of an expression or function applied to one or more columns, enabling index scans on computed predicates
    • An index that triggers a user-defined function whenever a row is inserted
    • An index whose key values are encrypted using a hash function for security

18. 18. What is index bloat and how does VACUUM help in PostgreSQL?

    • Index bloat occurs when an index has too many columns; VACUUM removes extra columns automatically
    • Index bloat is the accumulation of dead tuple references in index pages from UPDATEs and DELETEs; VACUUM reclaims those pages and marks them reusable
    • Index bloat means the index file exceeds the size of the table; VACUUM compresses both to the same size
    • Index bloat is caused by too many concurrent reads; VACUUM resets read counters

19. 19. Why might the PostgreSQL query planner ignore an index even when one exists on the filtered column?

    • PostgreSQL never ignores an index once it is created: it is always used
    • The planner's cost model estimates that a sequential scan is cheaper, often because the predicate matches a large fraction of rows or table statistics are stale
    • The index can only be used if the query includes an ORDER BY clause on the indexed column
    • Indexes are only used for JOIN operations, never for WHERE clause filtering

20. 20. Given a B-Tree index on a `name` column, which LIKE pattern can use the index?

    • `WHERE name LIKE '%smith'` (suffix wildcard)
    • `WHERE name LIKE '%smith%'` (both sides wildcard)
    • `WHERE name LIKE 'smith%'` (prefix wildcard)
    • None: LIKE never uses a B-Tree index

21. 21. Which PostgreSQL index type is designed for geometric and spatial data types such as points, boxes, and polygons?

    • GIN (Generalized Inverted Index)
    • BRIN (Block Range INdex)
    • GiST (Generalized Search Tree)
    • SPGIST (Space-Partitioned GiST)

22. 22. A GIN (Generalized Inverted Index) is most appropriate for which type of column?

    • A single-value integer primary key
    • A column containing composite values such as arrays, JSONB, or `tsvector` full-text search documents
    • A monotonically increasing timestamp column in an append-only table
    • A low-cardinality boolean column

23. 23. What is a BRIN index and for which table characteristic is it best suited?

    • A block range index that stores min/max values per range of pages; best for large, physically ordered (correlated) columns like `created_at` in append-only tables
    • A balanced range index that replaces B-Trees for all numeric columns
    • A bitmap range index used to speed up bitmap heap scans on random data
    • A binary range index that only works with UUID primary keys

24. 24. What is 'write amplification' in the context of LSM-tree (Log-Structured Merge-tree) indexes?

    • Each logical write is physically written multiple times across different levels of the LSM tree during compaction
    • The database writes a redundant copy of every row to the transaction log
    • Write amplification refers to the overhead of updating a B-Tree's parent nodes after a leaf split
    • It is the extra latency caused by flushing the write-ahead log to disk

25. 25. What is the purpose of `CREATE INDEX CONCURRENTLY` in PostgreSQL?

    • It builds the index on multiple CPU cores simultaneously to reduce build time
    • It builds the index without taking an exclusive table lock, allowing concurrent reads and writes during index creation at the cost of a longer build time
    • It creates the index on a replica before promoting it to the primary, avoiding downtime
    • It defers index enforcement until after a COMMIT, improving transaction throughput

26. 26. What is an 'invisible index' (supported in MySQL 8+ and Oracle) and what is its primary use case?

    • An index encrypted at rest so its contents cannot be read by unauthorized users
    • An index that is maintained by the engine but hidden from the query planner, used to safely test the impact of dropping an index before actually removing it
    • An index stored in memory only, never persisted to disk
    • An index on system catalog tables that is not exposed in information_schema views

27. 27. How does PostgreSQL's MVCC (Multi-Version Concurrency Control) model contribute to index bloat over time?

    • MVCC compresses old row versions, causing indexes to fragment over time
    • Every UPDATE creates a new heap tuple version while leaving the old version in place; both the old and new index entries coexist until VACUUM reclaims the dead tuples
    • MVCC forces the index to store the transaction ID alongside every key, doubling its size
    • MVCC has no effect on indexes: only heap pages accumulate dead tuples

28. 28. When does the PostgreSQL planner choose a Bitmap Index Scan over a plain Index Scan?

    • When the query returns only a single row
    • When multiple indexes must be combined (AND/OR) or when the estimated row count is moderate, allowing heap pages to be read once in physical order rather than via many random seeks
    • When the table has no heap pages: data is stored in the index itself
    • Bitmap Index Scan is always faster and is always preferred over a plain Index Scan

29. 29. In a partitioned table, what is a key consideration when creating indexes to ensure all partitions benefit?

    • Indexes must be created on the partition key column only; other columns cannot be indexed
    • Creating an index on the parent partitioned table in PostgreSQL 11+ automatically propagates the index to all existing and future partitions
    • Each partition requires a manually created index with a unique name; there is no automatic propagation
    • Partitioned tables do not support B-Tree indexes; only BRIN indexes are available

30. 30. Which PostgreSQL system view would you query to find the number of index scans, sequential scans, and tuples fetched for user-created indexes?

    • `pg_indexes`
    • `information_schema.table_constraints`
    • `pg_stat_user_indexes`
    • `pg_index`