Google Professional Cloud Database Engineer Question 118
Select 3Google Cloud PlatformYou are managing a Cloud SQL PostgreSQL instance for a retail application. Users have reported that certain queries are taking significantly longer to execute, especially during high-traffic periods. Upon investigation, you notice frequent table scans and locking on a key table. What actions should you take to resolve the performance issues?
- A
Analyze the query execution plans using the EXPLAIN or EXPLAIN ANALYZE command to identify missing indexes.
- B
Add indexes to columns used in WHERE clauses or JOIN conditions based on query patterns.
- C
Increase the Cloud SQL instance's CPU and memory resources to handle the workload.
- D
Use the pg_stat_activity view to monitor active queries and identify locking issues.
- E
Disable autovacuum on the affected table to reduce contention.
Show answer and explanation
Correct answers: A, B, D
Explanation
To resolve slow-running queries and database locking issues, it is important to identify the root cause. Query execution plans (via EXPLAIN or EXPLAIN ANALYZE) can highlight inefficiencies like missing indexes. Adding indexes can optimize query performance, while monitoring with pg_stat_activity can help identify locking issues. Increasing resources or disabling autovacuum might provide temporary relief but are not long-term solutions to the underlying problems.
- A. Correct.
Analyzing the query execution plans using EXPLAIN or EXPLAIN ANALYZE helps identify missing indexes and performance bottlenecks in SQL queries. This is a crucial step in diagnosing slow queries.
- B. Correct.
Adding indexes to columns used in WHERE clauses or JOIN conditions can significantly improve query performance by reducing the need for full table scans.
- C. Incorrect.
While increasing CPU and memory might temporarily alleviate performance issues, it does not address the root cause of slow queries or locking. This is not the optimal solution in this scenario.
- D. Correct.
Using the pg_stat_activity view helps you monitor active queries and detect locking or long-running queries, which are key to diagnosing and resolving the issue.
- E. Incorrect.
Disabling autovacuum is generally not recommended as it can lead to table bloat and other performance issues over time. It is unlikely to address the immediate problem of slow queries and locking.