[Solved] You created a partial index:CREATE INDEX idx_recent_orders ON orders (order_date) WHERE order_date > CURRENT_DATE - INTERVAL '30 days';But queries filtering order_date > CURRENT_DATE - INTERVAL '30... | PostgreSQL

PostgreSQL - Indexing Strategies

You created a partial index:
CREATE INDEX idx_recent_orders ON orders (order_date) WHERE order_date > CURRENT_DATE - INTERVAL '30 days';
But queries filtering order_date > CURRENT_DATE - INTERVAL '30 days' are not using the index. What is the likely problem?

APartial indexes cannot use date intervals

BThe index is missing the column <code>order_date</code>

CThe partial index condition uses a non-immutable function, so it cannot be used

DThe WHERE clause in the index is invalid syntax

Step-by-Step Solution

Solution:

Step 1: Check partial index WHERE clause
The WHERE clause uses CURRENT_DATE, which is non-immutable (changes daily).
Step 2: Understand index usage limitation
PostgreSQL partial indexes require immutable conditions to be used by queries.
Final Answer:
The partial index condition uses a non-immutable function, so it cannot be used -> Option C
Quick Check:
Non-immutable functions block partial index usage [OK]

Quick Trick: Partial index WHERE must use immutable expressions [OK]

Common Mistakes:

Assuming syntax error causes issue
Thinking partial indexes can't use date columns
Ignoring function immutability rules

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

You created a partial index:

Step 1: Check partial index WHERE clause

Step 2: Understand index usage limitation

Final Answer:

Quick Check:

Want More Practice?