Google Professional Cloud Database Engineer Question 114
Select 3Google Cloud PlatformYou are managing a Cloud SQL for PostgreSQL instance hosting a critical application. Users report that some queries are taking significantly longer to execute, and the application occasionally encounters database locking issues. Upon investigation, you notice frequent sequential scans on a large table. What steps should you take to address the performance issue?
- A
Analyze the query execution plans to identify potential missing indexes.
- B
Enable the 'autovacuum' configuration to reduce table bloat.
- C
Create an index on columns frequently used in WHERE clauses or JOIN conditions.
- D
Increase the instance's CPU and memory resources to improve query performance.
- E
Check for long-running transactions that might be causing database locks.
Show answer and explanation
Correct answers: A, C, E
Explanation
Slow-running queries and database locking can often be attributed to missing indexes or long-running transactions. By analyzing query execution plans, you can identify missing indexes that may be causing sequential scans. Creating appropriate indexes can optimize query performance. Additionally, investigating long-running transactions can help resolve locking issues. While enabling 'autovacuum' and scaling resources can improve overall performance, they do not directly address the root cause of this specific issue.
- A. Correct.
Analyzing query execution plans helps identify whether queries are performing sequential scans unnecessarily due to missing indexes.
- B. Incorrect.
While enabling 'autovacuum' is beneficial for managing table bloat and maintaining performance, it does not directly address the issue of slow-running queries or locking caused by missing indexes.
- C. Correct.
Creating indexes on columns frequently used in WHERE clauses or JOIN conditions can significantly improve query performance by reducing the need for sequential scans.
- D. Incorrect.
Increasing CPU and memory resources may help in certain cases, but it does not address the root cause of slow queries or database locking due to missing indexes.
- E. Correct.
Long-running transactions can cause database locks, so identifying and addressing them is crucial for resolving locking issues.