The answer is to create a composite index on (status, order_date). This is correct because the WHERE clause combines an equality predicate on status with a range predicate on order_date; a composite index with the equality column first allows the database to perform an index seek for the exact status value, then a contiguous range scan on the ordered order_date column, eliminating the need for a full table scan or additional sorting. On the Google Professional Cloud Database Engineer exam, this tests your understanding of index key column order—a common trap is placing the range column first, which would force a scan of all matching range values before filtering on status. Remember the memory tip: “Equality first, range second” to ensure the index supports both predicates efficiently with minimal I/O.
PCDE Monitor and optimize database performance Practice Question
This PCDE practice question tests your understanding of monitor and optimize database performance. Read the scenario carefully and evaluate each option against the stated constraints before committing to an answer. After answering, compare your reasoning against the explanation and wrong-answer breakdown below. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.
Exhibit
Refer to the exhibit.
```
Cloud SQL Query Insight output:
Query ID: 1234
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND status = 'SHIPPED';
Average latency: 3.5s
Total executions: 10000
No index used (sequential scan on orders table, size 50 GB)
```
Refer to the exhibit. What is the most effective optimization for this query?
Refer to the exhibit.
```
Cloud SQL Query Insight output:
Query ID: 1234
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND status = 'SHIPPED';
Average latency: 3.5s
Total executions: 10000
No index used (sequential scan on orders table, size 50 GB)
```
A
Increase the instance memory to 30 GB
Why wrong: Memory increase does not eliminate full table scans.
B
Create a composite index on (status, order_date)
Index allows efficient range scan and filter.
C
Remove the WHERE clause and fetch all rows in application
Why wrong: Would increase data transfer and latency.
D
Partition the orders table by month
Why wrong: Might help but queries still scan partitions without proper index.
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create a composite index on (status, order_date)
The query filters on `status` and `order_date`, so a composite index on `(status, order_date)` allows the database to perform an index seek on the equality predicate (`status`) and then a range scan on the ordered column (`order_date`), avoiding a full table scan. This is the most effective optimization because it directly supports the WHERE clause with minimal I/O and no sorting overhead.
Key principle: Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
✗
Increase the instance memory to 30 GB
Why it's wrong here
Memory increase does not eliminate full table scans.
✓
Create a composite index on (status, order_date)
Why this is correct
Index allows efficient range scan and filter.
Related concept
Read the scenario before looking for a memorised answer.
✗
Remove the WHERE clause and fetch all rows in application
Why it's wrong here
Would increase data transfer and latency.
✗
Partition the orders table by month
Why it's wrong here
Might help but queries still scan partitions without proper index.
Common exam traps
Common exam trap: answer the scenario, not the keyword
Google Cloud often tests the misconception that partitioning alone improves query performance, but without a supporting index, partitioning only reduces the scan scope to a subset of partitions and does not eliminate the need for a full scan within those partitions.
Detailed technical explanation
How to think about this question
A composite index on `(status, order_date)` leverages the B-tree structure where the leading column handles equality lookups and the second column provides ordered access for range scans, eliminating the need for a separate sort operation. In PostgreSQL or MySQL, this index can be used for both `WHERE status = 'active' AND order_date BETWEEN '2024-01-01' AND '2024-01-31'` and for `ORDER BY order_date` without additional sorting. Real-world scenarios often involve queries with a mix of equality and range conditions, making composite indexes with the equality column first a standard optimization pattern.
KKey Concepts to Remember
Read the scenario before looking for a memorised answer.
Find the constraint that changes the correct option.
Eliminate answers that are true in general but not in this case.
TExam Day Tips
→Watch for words such as best, first, most likely and least administrative effort.
→Review why wrong options are wrong, not only why the correct option is correct.
Key takeaway
Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Real-world example
How this comes up in practice
A cloud solutions architect for a retail company is evaluating services for a new workload. The correct answer here reflects best practice for the specific scenario described — not a general cloud recommendation. Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option. Cloud exam questions reward reading the constraint carefully: the same technology can be right or wrong depending on the use case.
What to study next
Got this wrong? Here's your next step.
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
Monitor and optimize database performance — This question tests Monitor and optimize database performance — Read the scenario before looking for a memorised answer..
What is the correct answer to this question?
The correct answer is: Create a composite index on (status, order_date) — The query filters on `status` and `order_date`, so a composite index on `(status, order_date)` allows the database to perform an index seek on the equality predicate (`status`) and then a range scan on the ordered column (`order_date`), avoiding a full table scan. This is the most effective optimization because it directly supports the WHERE clause with minimal I/O and no sorting overhead.
What should I do if I get this PCDE question wrong?
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
What is the key concept behind this question?
Read the scenario before looking for a memorised answer.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.