Quick Answer
The answer is to create an index on the columns used in the WHERE clause. When a Cloud SQL PostgreSQL instance performs a full table scan on a 100 GB table, it means the query planner lacks an efficient access path, forcing it to read every row sequentially. An index on the filtered columns enables an index scan, which dramatically reduces disk I/O by locating only the matching rows. On the Google Professional Cloud Database Engineer exam, this scenario tests your understanding of query optimization and the PostgreSQL planner’s behavior; a common trap is to add more memory or increase resources, but without an index, the scan remains unavoidable. Remember the memory tip: “No index means no shortcut—the planner walks the whole store.” For reporting queries on large tables, always verify that WHERE clause columns are indexed before scaling the instance.
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.
You are tuning a Cloud SQL for PostgreSQL instance that runs reporting queries. The slowest query performs a full table scan on a 100 GB table. Which action is most likely to improve performance?
Clue words in this question
Noticing these words before you look at the options changes how you read each choice.
Clue: "most likely"
Why it matters: Probability qualifier — the question wants the most probable cause or outcome, not a guaranteed one. Eliminate low-probability options.
Create an index on the columns used in the WHERE clause
An index allows the database to locate rows quickly.
Increase shared_buffers to 50% of instance memory
Why wrong: Doesn't address the lack of an index.
Set statement_timeout to 30 seconds
Why wrong: This would kill the query instead of optimizing it.
Move the query to a read replica
Why wrong: Read replica also does a full scan if index is missing.
Answer choices
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
Create an index on the columns used in the WHERE clause
A full table scan on a 100 GB table indicates that PostgreSQL has no efficient access path to retrieve the required rows. Creating an index on the columns used in the WHERE clause allows the query planner to use an index scan instead of a sequential scan, drastically reducing the number of disk pages read and improving query performance. This is the most direct and effective optimization for a query that filters rows without an index.
Key principle: Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Answer analysis
For each option: why learners choose it and why it is or isn't the right answer here.
Create an index on the columns used in the WHERE clause
Why this is correct
An index allows the database to locate rows quickly.
Clue confirmation
The clue word "most likely" in the question point toward this answer.
Related concept
Read the scenario before looking for a memorised answer.
Increase shared_buffers to 50% of instance memory
Why it's wrong here
Doesn't address the lack of an index.
Set statement_timeout to 30 seconds
Why it's wrong here
This would kill the query instead of optimizing it.
Move the query to a read replica
Why it's wrong here
Read replica also does a full scan if index is missing.
Common exam traps
Google Cloud often tests the misconception that simply adding more memory or offloading work to a replica will fix performance issues caused by missing indexes, but the root cause—lack of an access path—must be addressed directly.
Detailed technical explanation
PostgreSQL's query planner estimates the cost of sequential scans versus index scans based on statistics like relpages and reltuples. Without a suitable index, the planner defaults to a sequential scan even for selective queries, reading every page from disk. Creating a B-tree index on the WHERE columns provides a sorted structure that allows the planner to switch to an index scan, reducing I/O from millions of pages to only those containing matching rows. In practice, for a 100 GB table, this can reduce query time from minutes to seconds.
Key takeaway
Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Real-world example
A company's IT admin needs to give a contractor read-only access to production logs without sharing account credentials. Using role-based access control (RBAC) and temporary scoped permissions — not a permanent shared password — is the correct pattern. Questions like this test whether you can apply least-privilege access across cloud identity services.
What to study next
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 — study guide chapter
Learn the concepts, then practise the questions
Monitor and optimize database performance practice questions
Targeted practice on this topic area only
All PCDE questions
503 questions across all exam domains
Google Professional Cloud Database Engineer study guide
Full concept coverage aligned to exam objectives
PCDE practice test guide
How to use practice tests most effectively before exam day
Related practice questions
Use these pages to review the topic behind this question. This is how one missed question becomes focused revision.
Practise PCDE questions linked to Plan and manage database infrastructure.
Practise PCDE questions linked to Define data structures and implement SQL for Business Intelligence.
Practise PCDE questions linked to Design and implement database schemas.
Practise PCDE questions linked to Monitor and optimize database performance.
Practise PCDE questions linked to PCDE fundamentals.
Practise PCDE questions linked to PCDE scenario.
Practise PCDE questions linked to PCDE troubleshooting.
Practice this exam
Short sessions build daily habit. Longer sessions build exam-day stamina. Try a timed session to simulate real conditions.
FAQ
Monitor and optimize database performance — This question tests Monitor and optimize database performance — Read the scenario before looking for a memorised answer..
The correct answer is: Create an index on the columns used in the WHERE clause — A full table scan on a 100 GB table indicates that PostgreSQL has no efficient access path to retrieve the required rows. Creating an index on the columns used in the WHERE clause allows the query planner to use an index scan instead of a sequential scan, drastically reducing the number of disk pages read and improving query performance. This is the most direct and effective optimization for a query that filters rows without an index.
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
Yes — watch for: "most likely". Probability qualifier — the question wants the most probable cause or outcome, not a guaranteed one. Eliminate low-probability options.
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 →
Last reviewed: Jun 30, 2026
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.