A company runs an OLTP application on Amazon RDS for PostgreSQL. The database stores customer orders. The application frequently queries orders by customer_id and order_date. The orders table has 100 million rows. The query performance has degraded over time. The database has a single index on customer_id. The company needs to improve query performance without changing the application code. Which design change should be made?
A composite index supports queries filtering by both columns efficiently.
Why this answer
The query performance has degraded because the existing single-column index on customer_id can filter by customer but still requires a full sort or scan within that customer's rows to satisfy the order_date condition. Creating a composite index on (customer_id, order_date) allows the database to use a single index seek to locate the exact rows matching both columns, eliminating the need for an additional sort or filter pass. This directly addresses the query pattern without any application code changes.
Exam trap
The trap here is that candidates often choose partitioning (Option A) because they think it automatically speeds up queries, but without changing the query to leverage partition pruning, partitioning alone does not improve index-based lookups; the correct solution is to add a covering composite index that matches the query filter order.
How to eliminate wrong answers
Option A is wrong because partitioning by order_date would require rewriting queries to include partition pruning hints or rely on the query planner to eliminate partitions, which does not change the application code requirement and would not improve performance for queries filtering by customer_id without also including order_date in the index. Option B is wrong because upgrading to a larger instance type only adds more CPU and memory, which may mask the symptom but does not fix the root cause of missing index coverage for the query pattern. Option C is wrong because enabling Performance Insights only helps identify bottlenecks after they occur; it does not make any design change to improve query performance.