Index Basics — Series 2

Preview — 3 of 10 questions

What happens when you run DROP INDEX idx_orders_user_id;?

javascript

DROP INDEX idx_orders_user_id;
-- orders table and its data: unchanged
-- queries that relied on this index simply fall back to a sequential scan (or another index, if one exists)

ADeletes the index structure only — the orders table and its data are completely unaffected

BDeletes both the index and all rows from the orders table

CDeletes the orders table entirely

DMarks the index as inactive but keeps it on disk until VACUUM runs

When you create a FOREIGN KEY constraint, is an index automatically created on the referencing column?

javascript

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id)
);

AYes, PostgreSQL automatically indexes every FOREIGN KEY column, just like PRIMARY KEY

BNo — unlike PRIMARY KEY, a FOREIGN KEY constraint does not automatically create an index on the referencing column (user_id); you must create it manually if you need fast lookups/joins on it

CYes, but only if the referenced table has more than 1,000 rows

DNo, and PostgreSQL does not allow manually indexing foreign key columns either

How can an index on created_at help this query beyond just filtering?

javascript

SELECT * FROM orders WHERE created_at > '2024-01-01' ORDER BY created_at;

AIndexes never help with ORDER BY, only WHERE clauses

BThe index automatically reorders the physical table rows to match created_at

CSince a B-tree index stores values in sorted order, the planner can walk the index directly in that order, avoiding a separate, potentially expensive sort step over the matching rows

DORDER BY always forces a sequential scan regardless of available indexes

Answer all 10 questions (7 more), see explanations for every answer, and track your score.