Improving Join Performance in Cloud SQL PostgreSQL

You are a cloud database engineer for a financial services firm. The firm uses Cloud SQL for PostgreSQL to support a BI reporting tool. The main table 'transactions' has 500 million rows and is growing daily. Reports often run aggregations over date ranges and group by account_id. The 'transactions' table has indexes on date and account_id separately. Despite these indexes, the reporting queries are slow, often taking over 30 minutes. The database is deployed on a high-memory machine with 32 vCPUs and 256 GB RAM. You notice that the queries perform sequential scans instead of using indexes. What is the most likely reason, and what single change would you make to improve performance?

Quick Answer

The answer is to create a composite index on (date, account_id). This is correct because PostgreSQL cannot efficiently combine separate indexes on date and account_id for queries that filter by a date range and then group by account_id; the query planner often estimates that a sequential scan is cheaper than the overhead of a bitmap scan merging two indexes, especially on large tables. On the Google Professional Cloud Database Engineer exam, this scenario tests your understanding of index design and the query planner’s cost-based decisions—a common trap is assuming separate indexes are sufficient, but PostgreSQL requires a composite index to satisfy both the filter and the sort order in a single index scan. Remember the memory tip: “One index to rule them all”—when a query has both a WHERE and a GROUP BY, a composite index covering both columns in the correct order (filter first, then group) eliminates the need for separate sorts and bitmap scans, directly improving join performance in Cloud SQL PostgreSQL.

⚠ Common exam trap

Google Cloud often tests the misconception that adding separate indexes on each column is sufficient for multi-column queries, but the trap here is that PostgreSQL cannot efficiently combine separate indexes for both filtering and grouping without a composite index that matches the query's access pattern.

Answer choices

Why each option matters

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 (date, account_id)

The reporting queries filter by date ranges and group by account_id, but the existing separate indexes on date and account_id cannot be combined efficiently for both conditions. PostgreSQL's query planner often chooses a sequential scan over using two separate indexes because it estimates that reading the entire table is cheaper than the bitmap scan overhead of combining them. A composite index on (date, account_id) allows the database to directly locate rows matching the date range and then access them in account_id order, eliminating the need for a separate sort or join step.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
Partition the table by date using PostgreSQL declarative partitioning
Why it's wrong here
Partitioning can help, but the queries already filter by date range; the main issue is the grouping by account_id, which a composite index addresses better.
✓
Create a composite index on (date, account_id)
Why this is correct
A composite index that matches the query's WHERE and GROUP BY can drastically reduce the data scanned.
✗
Increase the shared_buffers setting to 128 GB
Why it's wrong here
While larger buffers can help, they still require reading data into memory, and the optimizer might still choose a sequential scan if the query reads a large fraction of the table.
✗
Disable sequential scans by setting enable_seqscan = off
Why it's wrong here
Forcing the query planner can lead to even worse plans; it's better to provide a proper index.

Go deeper

1 more way this is tested on PCDE

These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.

Variation 1. A company uses Cloud SQL for PostgreSQL for its BI database. Queries involving joins on large tables are slow. Which TWO strategies should they implement to improve join performance? (Choose TWO.)

medium

✓ A.Denormalize tables to reduce the number of joins
✓ B.Add indexes on the columns used in JOIN conditions
C.Increase the number of CPU cores on the instance
D.Create read replicas for the join queries
E.Use connection pooling to reduce connection overhead

Why A: Denormalizing tables reduces the number of joins required in queries by combining related data into fewer tables. This directly minimizes the computational overhead of join operations in Cloud SQL for PostgreSQL, which is especially beneficial for large BI datasets where join performance is critical.

Written by Johnson Ajibi, MSc IT Security

Senior Network & Security Engineer · founder of Courseiva

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.

Improving Join Performance in Cloud SQL PostgreSQL

Why each option matters

Option-by-option breakdown

Related to this question

1 more way this is tested on PCDE