TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 20

Indexing for Analysts: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL

  1. Q1What is an index, and how does it speed up reads?
  2. Q2What is the default index type in PostgreSQL (B-Tree)?
  3. Q3Which operators can a B-Tree serve (=, <, >, BETWEEN, ORDER BY)?
  4. Q4What is the write-time cost of having indexes?
  5. Q5Why doesn't an index help a full-table aggregate (SUM of all rows)?
  6. Q6Syntax: CREATE INDEX name ON schema.table (column);
  7. Q7What does IF NOT EXISTS add to CREATE INDEX?
  8. Q8How do you DROP an index?
  9. Q9How do you confirm a query USES an index (EXPLAIN)?
  10. Q10What is a primary key's relationship to an index?
  11. Q11Does a UNIQUE constraint create an index automatically?
  12. Q12What is selectivity, and why do indexes help selective filters most?
  13. Q13Why is an index on a low-cardinality column (e.g. gender) often useless?
  14. Q14What is an Index Only Scan (intuition)?
  15. Q15How do you list existing indexes on a table (pg_indexes)?
  16. Q16Why can't an index help WHERE LOWER(col)=... (plain column index)?
  17. Q17What is CREATE INDEX CONCURRENTLY for (concept)?
  18. Q18Why might the planner ignore an index you created (stats/selectivity)?
  19. Q19What is a composite (multi-column) index (intro)?
  20. Q20What is a partial index (intro)?
  21. Q21Why run ANALYZE after creating an index / loading data?
  22. Q22What does an index cost in disk space, roughly?
  23. Q23When should you NOT add an index (write-heavy, low selectivity)?
  24. Q24Difference between a clustered and non-clustered index (PG has no clustered by default).
  25. Q25Name two analyst queries that clearly benefit from an index.

CREATE SINGLE-COLUMN INDEXES (25) (run on RetailMart; drop afterward to keep it clean)

  1. Q26Create a B-Tree index on sales.orders(cust_id).
  2. Q27Create an index on sales.orders(order_date).
  3. Q28Create an index on customers.customers(email).
  4. Q29Create an index on products.products(brand_id).
  5. Q30Create an index on sales.order_items(prod_id).
  6. Q31Create an index on support.tickets(customer_id).
  7. Q32Create an index on customers.reviews(product_id).
  8. Q33Create an index on stores.employees(store_id).
  9. Q34Create an index on sales.orders(store_id).
  10. Q35Create an index on web_events.page_views(session_id).
  11. Q36Create an index on call_center.calls(customer_id).
  12. Q37Create an index on sales.shipments(order_id).
  13. Q38Create an index on products.products(price).
  14. Q39Create an index on loyalty.members(tier_id).
  15. Q40Create an index on customers.addresses(customer_id).
  16. Q41Create an index IF NOT EXISTS on sales.orders(order_status).
  17. Q42Create an index on payroll.pay_slips(employee_id).
  18. Q43Create an index on marketing.ads_spend(campaign_id).
  19. Q44Create an index on supply_chain.inventory_snapshots(product_id).
  20. Q45Create an index on customers.customers(registration_date).
  21. Q46Create an index on sales.returns(order_id).
  22. Q47Name an index explicitly (idx_orders_custid) for clarity.
  23. Q48Create an index on stores.employees(salary).
  24. Q49Create an index on support.tickets(agent_id).
  25. Q50Drop one of the indexes you created (DROP INDEX).

VERIFY WITH EXPLAIN

  1. Q51EXPLAIN a WHERE cust_id=1234 before creating the index (Seq Scan).
  2. Q52Create the index on cust_id, ANALYZE, then EXPLAIN again (Index Scan?).
  3. Q53Verify an index on order_date is used by a range query.
  4. Q54Verify an email-equality lookup uses the email index.
  5. Q55Show that WHERE LOWER(email)=... does NOT use the plain email index.
  6. Q56Verify a join on cust_id uses the index on the FK side.
  7. Q57Verify ORDER BY order_date LIMIT 10 uses the order_date index.
  8. Q58Show a full-table COUNT(*) ignores indexes (Seq Scan).
  9. Q59Verify a brand_id filter uses its index.
  10. Q60Show a low-selectivity filter (order_status='Delivered') may still Seq Scan.
  11. Q61Verify a prod_id join in order_items uses the index.
  12. Q62EXPLAIN ANALYZE before/after adding an index; compare actual time.
  13. Q63Show an Index Only Scan when selecting only the indexed column.
  14. Q64Show that SELECT * breaks the Index Only Scan.
  15. Q65Verify a date-range BETWEEN uses the index.
  16. Q66Show a leading-wildcard LIKE ignores the index.
  17. Q67Show a prefix LIKE 'a%' can use the index (with appropriate opclass).
  18. Q68Verify an index on price is used by price > 10000.
  19. Q69Show the planner picks Seq Scan when the predicate matches most rows.
  20. Q70Verify a customer_id filter on reviews uses the index.
  21. Q71EXPLAIN to confirm the index name actually appears in the plan.
  22. Q72Show BUFFERS difference before/after indexing a point lookup.
  23. Q73Verify a store_id filter on orders uses its index.
  24. Q74Show how ANALYZE changes the plan after bulk data (concept on RetailMart).
  25. Q75Confirm an index is unused for a query and explain why.

INVENTORY & CLEANUP

  1. Q76List all indexes on sales.orders via pg_indexes.
  2. Q77List all indexes in the sales schema.
  3. Q78Count indexes per table across the database.
  4. Q79Find the index definition (indexdef) for a given index name.
  5. Q80List indexes on customers.customers.
  6. Q81Identify which columns of sales.orders are indexed.
  7. Q82Find tables in sales that have NO non-PK index.
  8. Q83List unique indexes vs non-unique on a table.
  9. Q84Show the size of an index (pg_relation_size) - concept.
  10. Q85Find duplicate/overlapping index candidates (same leading column).
  11. Q86Drop a named index you created earlier.
  12. Q87Drop all the demo indexes you created in Section B (cleanup).
  13. Q88Verify the table is back to only its PK/constraint indexes.
  14. Q89List indexes on products.products and their columns.
  15. Q90Find which index backs the primary key of sales.orders.
  16. Q91List the largest indexes by size (concept query).
  17. Q92Show indexes on a join-heavy table (order_items).
  18. Q93Identify a missing FK index from pg_indexes + foreign keys.
  19. Q94List partial indexes (those with a WHERE in indexdef).
  20. Q95List expression indexes (indexdef contains a function).
  21. Q96Confirm dropping an index doesn't affect query results (only speed).
  22. Q97Re-create an index with a clear naming convention.
  23. Q98Show pg_indexes output filtered to a single table.
  24. Q99Inventory all indexes and flag tables with > 5 indexes.
  25. Q100Produce a tidy index inventory report for the sales schema.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Explain the leftmost-prefix rule for composite indexes.
  2. Q2For WHERE a=? AND b=?, is (a,b) or (b,a) better? How to decide?
  3. Q3Can (a,b) serve WHERE a=? alone? WHERE b=? alone?
  4. Q4When does column order in a composite index matter for ORDER BY?
  5. Q5What is a partial index and a good use-case (active/rare rows)?
  6. Q6What is an expression index (e.g. on LOWER(email))?
  7. Q7How does a covering index (INCLUDE) enable an Index Only Scan?
  8. Q8Why put the equality column before the range column in a composite?
  9. Q9When is a single composite better than two single-column indexes?
  10. Q10When can the planner combine two single-column indexes (BitmapAnd)?
  11. Q11Why does a partial index for WHERE status='Cancelled' stay small?
  12. Q12What must a query's WHERE match for a partial index to be usable?
  13. Q13Why must an expression index match the exact expression in WHERE?
  14. Q14Trade-off: more indexes = faster reads but slower writes. Explain.
  15. Q15What is a redundant index (prefix of another)?
  16. Q16Why is (a) redundant if (a,b) exists for a-only lookups?
  17. Q17When does INCLUDE help vs adding the column to the key?
  18. Q18How does selectivity of the leading column affect a composite's usefulness?
  19. Q19Why might a partial index need its predicate to be IMMUTABLE-ish/stable?
  20. Q20How to index for a frequent (cust_id, order_date DESC) access pattern?
  21. Q21Why does ORDER BY benefit from matching the index's sort order?
  22. Q22What is index bloat and what causes it (concept)?
  23. Q23When does CREATE INDEX CONCURRENTLY matter (no table lock)?
  24. Q24Why ANALYZE after creating an expression index?
  25. Q25Give a 1-line rule for "what to index" for analyst workloads.

COMPOSITE INDEXES

  1. Q26Create (cust_id, order_date) on sales.orders for per-customer time queries.
  2. Q27Verify it serves WHERE cust_id=? AND order_date>=?.
  3. Q28Show it serves WHERE cust_id=? alone (leftmost prefix).
  4. Q29Show it does NOT serve WHERE order_date=? alone.
  5. Q30Create (store_id, order_date) and verify a store-time-range query.
  6. Q31Create (brand_id, price) on products; verify brand + price filter.
  7. Q32Choose the better order for WHERE supplier_id=? AND price>?; justify.
  8. Q33Create (order_id, prod_id) on order_items; verify a line lookup.
  9. Q34Create (customer_id, review_date) on reviews; verify latest-per-customer.
  10. Q35Create (agent_id, status) on tickets; verify agent open-ticket query.
  11. Q36Create (cust_id, order_date DESC) to support ORDER BY DESC LIMIT.
  12. Q37Verify the DESC composite removes a Sort node in the plan.
  13. Q38Create (warehouse_id, product_id, snapshot_date) and verify a lookup.
  14. Q39Show two single-col indexes combined via BitmapAnd vs one composite.
  15. Q40Create (region_id, ...) friendly index for store filters.
  16. Q41Create (campaign_id, spend_date) on ads_spend; verify.
  17. Q42Create (employee_id, salary_year) on pay_slips; verify.
  18. Q43Create (call_reason, agent_id) on calls; verify.
  19. Q44Decide column order for WHERE order_status=? AND order_date>=?.
  20. Q45Create a composite to support a GROUP BY cust_id, order_date::month.
  21. Q46Verify a composite enables an Index Only Scan when selecting only its columns.
  22. Q47Add INCLUDE(net_total) to (cust_id, order_date) for a covering scan.
  23. Q48Show the INCLUDE version yields Index Only Scan for SELECT net_total.
  24. Q49Identify a redundant single-column index given a composite; drop it.
  25. Q50Drop the composite indexes you created (cleanup).

PARTIAL & EXPRESSION INDEXES

  1. Q51Create a partial index on orders(order_date) WHERE order_status='Cancelled'.
  2. Q52Verify the partial index serves a Cancelled-orders date query.
  3. Q53Show the partial index is NOT used for a non-Cancelled query.
  4. Q54Create a partial index for unresolved tickets (WHERE resolved_date IS NULL).
  5. Q55Verify it serves the "open tickets" query.
  6. Q56Create an expression index on LOWER(email).
  7. Q57Verify WHERE LOWER(email)='[email protected]' now uses it.
  8. Q58Create an expression index on (first_name || ' ' || last_name).
  9. Q59Verify a full-name equality search uses it.
  10. Q60Create an expression index on date_trunc('month', order_date::timestamp).
  11. Q61Verify a monthly-bucket query uses it.
  12. Q62Create a partial index for high-value orders (WHERE net_total > 50000).
  13. Q63Verify a high-value-orders query uses it.
  14. Q64Create an expression index on (price - cost_price) (margin).
  15. Q65Verify a margin filter uses it.
  16. Q66Create a partial UNIQUE-style index concept (note V3 dup emails block true UNIQUE).
  17. Q67Create a partial index for recent orders (WHERE order_date >= DATE '2025-01-01').
  18. Q68Verify recent-orders queries use it.
  19. Q69Create an expression index on EXTRACT(YEAR FROM order_date).
  20. Q70Show why a range rewrite may beat the expression index anyway.
  21. Q71Create a partial index on reviews WHERE rating <= 2 (negative reviews).
  22. Q72Verify the negative-review query uses it.
  23. Q73Create an expression index on regexp_replace(phone,'\\D','','g') (digits).
  24. Q74Verify a normalized-phone lookup uses it.
  25. Q75Drop all partial/expression demo indexes (cleanup).

VERIFY & MEASURE

  1. Q76EXPLAIN ANALYZE a point lookup before/after a single-col index; compare time.
  2. Q77EXPLAIN ANALYZE a date-range before/after a composite; compare.
  3. Q78Measure BUFFERS before/after indexing a hot query.
  4. Q79Show a Sort disappears after a matching DESC composite.
  5. Q80Confirm an Index Only Scan via a covering index.
  6. Q81Confirm a partial index reduces scanned rows vs full index.
  7. Q82Show the planner choosing Bitmap when selectivity is medium.
  8. Q83Compare two candidate composites for the same query; pick the winner.
  9. Q84Show that adding the wrong index doesn't change the plan (planner ignores).
  10. Q85Measure the plan for a join before/after indexing the FK.
  11. Q86Confirm ANALYZE was needed for the planner to use a new expression index.
  12. Q87Show an Index Only Scan breaks when you add a non-covered column.
  13. Q88Demonstrate leftmost-prefix: composite used for a-only, ignored for b-only.
  14. Q89Measure ORDER BY ... LIMIT with vs without a matching index.
  15. Q90Show a low-selectivity filter still Seq Scans despite an index.
  16. Q91Compare single composite vs two singles (BitmapAnd) on a 2-predicate query.
  17. Q92Verify a partial index is smaller (pg_relation_size) than the full one.
  18. Q93Confirm dropping a redundant index leaves the plan unchanged.
  19. Q94Show a covering index removing heap fetches (Heap Fetches=0 after VACUUM).
  20. Q95Measure a GROUP BY before/after an index supporting it.
  21. Q96Demonstrate the index that best serves a customer-orders-by-date page.
  22. Q97Pick the right index for a "top products per brand by price" query.
  23. Q98Verify the chosen index, then DROP it to keep RetailMart clean.
  24. Q99Produce a before/after measurement table for one optimized query.
  25. Q100Recommend a minimal index set (<=3) for the customer-360 query and justify each.

Interview grade, edge cases

CONCEPTUAL

  1. Q1Given WHERE a=? AND b BETWEEN ? AND ? ORDER BY c, design the ideal index.
  2. Q2Equality-then-range-then-sort: why that column order in a composite.
  3. Q3When does INCLUDE(cols) beat extending the key (cols)?
  4. Q4How to detect a redundant index (leftmost-prefix duplication).
  5. Q5Why are too many indexes harmful on a write-heavy table?
  6. Q6When will the planner refuse a perfectly good index (low selectivity)?
  7. Q7Partial index design: choosing the predicate to maximize hit-rate, minimize size.
  8. Q8Covering index requirements for an Index Only Scan (+ visibility map).
  9. Q9CREATE INDEX CONCURRENTLY: benefits, costs, failure mode (INVALID index).
  10. Q10BitmapAnd vs single composite - when each wins.
  11. Q11Why an expression index must match the query's exact expression.
  12. Q12How to support both (a,b) and (a) lookups without two indexes.
  13. Q13When a DESC index matters for ORDER BY ... DESC LIMIT.
  14. Q14Index for a join: which side and which column.
  15. Q15Why a GROUP BY on a high-cardinality column rarely benefits from an index.
  16. Q16How statistics (n_distinct, MCV) influence whether your index is chosen.
  17. Q17Trade-off of a wide covering index (size/write) vs Index Only Scan benefit.
  18. Q18Detecting unused indexes (pg_stat_user_indexes idea; not installed metric here).
  19. Q19When extended statistics beat adding an index (correlated columns).
  20. Q20Why rewrite the query first, then index (analyst priority).
  21. Q21Partial + covering combined: the "active rows fast read" pattern.
  22. Q22How leading-column selectivity decides composite usefulness.
  23. Q23Index maintenance: REINDEX vs bloat; when needed.
  24. Q24Why a foreign key without an index hurts both joins and deletes.
  25. Q25A decision tree: rewrite -> stats -> index -> MV (Topic 25).

DESIGN THE INDEX FOR A QUERY

  1. Q26SCENARIO: "Customer order history page" - WHERE cust_id=? ORDER BY order_date DESC LIMIT 20. Design + verify.
  2. Q27Design for WHERE store_id=? AND order_date>=? (store recent orders).
  3. Q28Design for WHERE brand_id=? AND price BETWEEN ? AND ?.
  4. Q29Design for a join order_items.prod_id = products.product_id.
  5. Q30Design for WHERE order_status='Cancelled' AND order_date>=? (partial).
  6. Q31Design for "latest review per product" (product_id, review_date DESC).
  7. Q32Design for WHERE agent_id=? AND status='Open' (tickets queue).
  8. Q33Design for ORDER BY net_total DESC LIMIT 10 (global leaderboard).
  9. Q34Design for WHERE customer_id=? on reviews (FK lookup).
  10. Q35Design for a (region via store) revenue rollup access pattern.
  11. Q36Design for WHERE LOWER(email)=? (expression index).
  12. Q37Design for WHERE date_trunc('month',order_date::timestamp)=? (expression).
  13. Q38Design for WHERE net_total>50000 (partial high-value).
  14. Q39Design for a (warehouse_id, product_id, snapshot_date) point lookup.
  15. Q40Design for WHERE campaign_id=? AND spend_date>=? on ads_spend.
  16. Q41Design for "employees by store ordered by salary DESC".
  17. Q42Design for WHERE resolved_date IS NULL (open tickets partial).
  18. Q43Design a covering index so SELECT cust_id, net_total is Index Only.
  19. Q44Design for WHERE call_reason=? AND call_start_time>=?.
  20. Q45Design for WHERE pincode=? on addresses.
  21. Q46Design for a recent-orders partial index (WHERE order_date >= '2025-01-01').
  22. Q47Design for "top products per brand by price" supporting query.
  23. Q48Design for a normalized-phone lookup (expression on digits).
  24. Q49Verify each designed index is actually used (EXPLAIN), then plan to drop.
  25. Q50Drop all designed indexes (cleanup to keep RetailMart pristine).

COVERING & PARTIAL IN PRACTICE

  1. Q51Build a covering index for the customer-order-history page (INCLUDE net_total, order_status).
  2. Q52Confirm Index Only Scan and Heap Fetches=0 (after VACUUM).
  3. Q53Partial index: open tickets (resolved_date IS NULL) + verify size vs full.
  4. Q54Partial index: Cancelled orders + verify usage and non-usage.
  5. Q55Covering index for "orders list by store" (INCLUDE the displayed columns).
  6. Q56Partial + covering: high-value orders with INCLUDE for the report columns.
  7. Q57Expression + partial: LOWER(email) WHERE tier='Platinum'.
  8. Q58Covering for a join + projection to avoid heap fetches.
  9. Q59Partial index for recent reviews (review_date >= '2025-01-01').
  10. Q60Covering index enabling Index Only Scan for a GROUP BY cust_id SUM.
  11. Q61Partial index for negative reviews (rating <= 2) + verify.
  12. Q62Expression index for margin (price-cost_price) + verify a margin filter.
  13. Q63Covering index for "latest order per customer" DISTINCT ON pattern.
  14. Q64Partial index for active customers proxy (registration_date recent).
  15. Q65Compare Index Only Scan vs Index Scan + heap for the same query.
  16. Q66Show INCLUDE columns don't affect ordering but enable covering.
  17. Q67Partial index predicate that the query must match exactly - prove it.
  18. Q68Covering index for ads_spend rollups by platform.
  19. Q69Partial index for unshipped orders (status in a set) - design.
  20. Q70Verify a covering index's size vs a plain one (pg_relation_size).
  21. Q71Show a partial index ignored when the query predicate doesn't match.
  22. Q72Covering index for pay_slips lookups by employee+year.
  23. Q73Combine partial + expression + covering in one purposeful index.
  24. Q74Measure read speedup from the covering index (EXPLAIN ANALYZE).
  25. Q75Drop all covering/partial demo indexes (cleanup).

REDUNDANCY, LIMITS & WHEN-NOT-TO-INDEX

  1. Q76SCENARIO: A table has (a), (a,b), (a,b,c) - identify the redundant ones to drop.
  2. Q77Detect a single-column index made redundant by a composite.
  3. Q78Show that a full-table SUM gains nothing from any index.
  4. Q79Show a low-selectivity status filter Seq Scans despite an index.
  5. Q80Show GROUP BY on a high-cardinality key doesn't benefit from an index.
  6. Q81Identify which of 5 proposed indexes are worth keeping.
  7. Q82Show extended statistics fixing an estimate instead of an index.
  8. Q83Demonstrate a query that should be rewritten, not indexed.
  9. Q84Estimate write-amplification from adding 3 indexes to a hot table (concept).
  10. Q85Find overlapping indexes (same leading columns) to consolidate.
  11. Q86Show a wide covering index whose write cost outweighs its read benefit.
  12. Q87Decide single composite vs two singles for a 2-predicate query (measure).
  13. Q88Detect an unused index candidate by query pattern (no query uses it).
  14. Q89Show CONCURRENTLY avoids a long lock vs plain CREATE INDEX (concept).
  15. Q90Decide when a partial index's predicate is too broad to help.
  16. Q91Show a leading-wildcard search needs a different index type (trigram, Day-6 note).
  17. Q92Show ORDER BY random() can't benefit from an index (use TABLESAMPLE).
  18. Q93Identify an FK lacking an index that slows a frequent join.
  19. Q94Quantify the rows-returned threshold where index beats Seq Scan.
  20. Q95Decide between indexing vs materialized view for a nightly metric.
  21. Q96Produce a "drop these redundant indexes" recommendation list.
  22. Q97Produce a "create these 3 indexes" recommendation for the dashboard.
  23. Q98Verify each recommended index helps, then DROP to keep RetailMart pristine.
  24. Q99Write an indexing policy for the analytics team (what/when/how to verify).
  25. Q100Full index audit: inventory, redundancies, missing, recommendations - sales schema.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Pick the index type for: equality, range, full-text, similarity, time-correlated append, geometry.
  2. Q2B-Tree vs Hash: when is Hash worth it (equality-only, large keys)?
  3. Q3GIN vs GiST for full-text/array/jsonb - read vs update tradeoffs.
  4. Q4BRIN for huge append-only time-series - when it shines, when it fails.
  5. Q5Covering index economics: read benefit vs write/size cost.
  6. Q6Partial index design to maximize selectivity per byte.
  7. Q7Expression index pitfalls: volatility, exact-match requirement.
  8. Q8Multi-query index-set design: cover N queries with the fewest indexes.
  9. Q9Write amplification: each index = extra work per INSERT/UPDATE/DELETE.
  10. Q10Index bloat causes and REINDEX [CONCURRENTLY] remediation.
  11. Q11HOT updates and how fewer indexed columns enable them (concept).
  12. Q12fillfactor and update-heavy tables (concept).
  13. Q13Extended statistics vs indexes for correlated predicates.
  14. Q14Index-only scans + visibility map + VACUUM interplay.
  15. Q15When BitmapAnd/Or of singles beats a tailored composite.
  16. Q16Choosing leading column by selectivity AND by query shape.
  17. Q17Partial unique indexes for "one active per group" (note V3 dup-email limit).
  18. Q18CONCURRENTLY build/drop in production; INVALID index recovery.
  19. Q19Index for ORDER BY + LIMIT (DESC composite) vs top-N window.
  20. Q20When to push to a materialized view (Topic 25) instead of more indexes.
  21. Q21Detecting unused/duplicate indexes systematically.
  22. Q22Sizing indexes: pg_relation_size and budget per table.
  23. Q23Covering vs INCLUDE vs key-extension decision matrix.
  24. Q24Trigram (pg_trgm) indexes for LIKE '%x%' - extension + GIN/GiST.
  25. Q25A staff-level "index review" checklist for a schema.

INDEX-TYPE SELECTION

  1. Q26B-Tree composite for (cust_id, order_date DESC) - create + verify Index-Only top-N.
  2. Q27BRIN on web_events.page_views(view_timestamp) - create + verify on a range scan.
  3. Q28Compare BRIN vs B-Tree size on the timestamp column (pg_relation_size).
  4. Q29Hash index on a high-cardinality equality column - create + verify.
  5. Q30Expression B-Tree on LOWER(email) - verify case-insensitive lookup.
  6. Q31Expression B-Tree on date_trunc('month',order_date::timestamp) - verify.
  7. Q32(practice) GIN on to_tsvector('english',review_text) for full-text - note + design.
  8. Q33(practice) GIN trigram on (first_name||' '||last_name) for fuzzy - pg_trgm note.
  9. Q34(practice) GiST trigram for ORDER BY name <-> 'query' - note + design.
  10. Q35Partial B-Tree for open tickets (resolved_date IS NULL) - verify.
  11. Q36Covering B-Tree (INCLUDE) for the orders list page - verify Index Only.
  12. Q37B-Tree for FK join order_items.prod_id - verify Nested Loop/Index.
  13. Q38BRIN on sales.orders(order_date) - does it help a wide range? Compare to B-Tree.
  14. Q39Expression index on (price - cost_price) for margin filters - verify.
  15. Q40Partial + expression: LOWER(email) WHERE tier='Platinum' - verify.
  16. Q41B-Tree DESC for ORDER BY net_total DESC LIMIT - verify Sort removed.
  17. Q42(practice) GIN on a JSONB column (Topic 24 data) - note only.
  18. Q43Choose index type for "find similar product names" (trigram, practice).
  19. Q44Choose index type for "orders in last 90 days" on a huge table (BRIN vs B-Tree).
  20. Q45Choose index type for "exact email match" (B-Tree vs Hash) and justify.
  21. Q46Covering index for a GROUP BY rollup enabling Index Only Scan.
  22. Q47Partial index for high-value recent orders (two predicates).
  23. Q48Compare planner choice with B-Tree present vs absent for a range.
  24. Q49Verify each created index is used, then plan cleanup.
  25. Q50Drop all created indexes (keep RetailMart pristine).

MULTI-QUERY INDEX STRATEGY

  1. Q51SCENARIO: Cover the 5 hottest analyst queries on sales.orders with the fewest indexes.
  2. Q52Identify the minimal index set for customer-360 (orders, reviews, tickets joins).
  3. Q53Design one composite that serves 3 different store queries (prefix reuse).
  4. Q54Decide which queries share a leading column and can reuse one index.
  5. Q55Avoid redundancy: pick (a,b,c) over (a)+(a,b)+(a,b,c).
  6. Q56Index set for the monthly revenue dashboard (date + store + category access).
  7. Q57Index set for the funnel/cohort queries (customer + date access).
  8. Q58Index set for the support workload (agent + status + date).
  9. Q59Index set for product analytics (brand + price + units joins).
  10. Q60Decide covering vs non-covering per query in the set.
  11. Q61Balance read benefit vs total write cost for the chosen set.
  12. Q62Detect two proposed indexes that overlap and merge them.
  13. Q63Decide which access patterns are better served by an MV (Topic 25).
  14. Q64Prioritize index creation by query frequency x slowness.
  15. Q65Design indexes for a join-heavy BI dashboard (FK coverage).
  16. Q66Decide partial vs full for the "active rows" queries in the set.
  17. Q67Index set for web_events analytics (session + timestamp + customer).
  18. Q68Index set for inventory/snapshots lookups.
  19. Q69Verify the whole proposed set against the real queries (EXPLAIN each).
  20. Q70Estimate total index size for the proposed set (sum pg_relation_size).
  21. Q71Show one index in the set being used by multiple queries (prefix reuse).
  22. Q72Remove the least valuable index from the set and re-justify.
  23. Q73Document the index set as a migration plan (for practice / prod).
  24. Q74Re-verify after ANALYZE that all set indexes are chosen.
  25. Q75Drop the entire experimental set (cleanup).

MAINTENANCE & TRADEOFFS

  1. Q76SCENARIO: A write-heavy table is slowing down - decide which indexes to drop and why.
  2. Q77Measure index bloat conceptually and plan a REINDEX CONCURRENTLY.
  3. Q78Show write-amplification: time an INSERT-like workload with N indexes (practice).
  4. Q79Identify unused indexes to drop (by query-pattern reasoning).
  5. Q80Decide fillfactor for an update-heavy table (concept).
  6. Q81Plan CONCURRENTLY rebuilds to avoid locks (sequence of steps).
  7. Q82Detect an INVALID index from a failed CONCURRENTLY build and fix.
  8. Q83Trade covering width vs write cost for the orders list index.
  9. Q84Decide when extended statistics replace a would-be index.
  10. Q85Quantify the read win vs the write/size cost for one candidate index.
  11. Q86Decide MV vs index for an expensive nightly aggregate.
  12. Q87Plan an index lifecycle: create CONCURRENTLY, verify, monitor, drop if unused.
  13. Q88Show VACUUM's role in keeping Index Only Scans effective.
  14. Q89Decide whether to keep a partial index as data distribution shifts.
  15. Q90Detect overlapping indexes and propose consolidation with sizes.
  16. Q91Balance an index that helps reads but blocks HOT updates (concept).
  17. Q92Establish a monitoring plan for index usage (pg_stat_user_indexes concept).
  18. Q93Decide reindex cadence for a bloat-prone table.
  19. Q94Right-size the index budget for the sales schema.
  20. Q95Produce a "drop list" and a "keep list" with justifications.
  21. Q96Plan a safe production rollout of 3 new indexes.
  22. Q97Verify post-rollout that target queries improved (EXPLAIN ANALYZE).
  23. Q98Document rollback (DROP INDEX) for each change.
  24. Q99Write the team's indexing runbook (create/verify/monitor/retire).
  25. Q100Full strategy memo: index set, types, maintenance, MV boundary - for the analytics platform.