[Solved] Consider the table orders(id INT, total NUMERIC) and the index:CREATE INDEX idx_total_rounded ON orders ((ROUND(total)));What will the query SELECT * FROM orders WHERE ROUND(total) = 100; do? | PostgreSQL

PostgreSQL - Indexing Strategies

Consider the table orders(id INT, total NUMERIC) and the index:
CREATE INDEX idx_total_rounded ON orders ((ROUND(total)));
What will the query SELECT * FROM orders WHERE ROUND(total) = 100; do?

AThrow a syntax error due to incorrect index usage

BUse the idx_total_rounded expression index to speed up the query

CPerform a sequential scan ignoring the index

DUse a default index on total column

Step-by-Step Solution

Solution:

Step 1: Match query expression with index expression
The index is created on ROUND(total), and the query filters by ROUND(total) = 100, so they match.
Step 2: Determine query plan behavior
PostgreSQL will use the expression index idx_total_rounded to speed up the query.
Final Answer:
Use the idx_total_rounded expression index to speed up the query -> Option B
Quick Check:
Matching expressions in query and index enable index usage [OK]

Quick Trick: Expression index used only if query expression matches exactly [OK]

Common Mistakes:

Assuming expression indexes cause errors
Thinking default column indexes are used instead
Believing expression indexes are ignored in queries

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Consider the table orders(id INT, total NUMERIC) and the index:

Step 1: Match query expression with index expression

Step 2: Determine query plan behavior

Final Answer:

Quick Check:

Want More Practice?