Google Professional Cloud Database Engineer Question 115
Select 3Google Cloud PlatformYou are managing a Cloud SQL instance hosting a PostgreSQL database. Users have reported that a specific query is running very slowly, and you suspect it is related to database locking and missing indexes. How should you approach diagnosing and resolving this issue?
- A
Use the pg_stat_activity system view to identify queries that are locked and waiting on other transactions.
- B
Run the EXPLAIN or EXPLAIN ANALYZE command on the slow query to identify missing indexes and understand the query execution plan.
- C
Increase the instance size to provide more CPU and memory resources to improve query performance.
- D
Check the pg_locks system view to identify active locks and the transactions causing them.
- E
Use Cloud Monitoring to analyze CPU and memory metrics for the Cloud SQL instance.
Show answer and explanation
Correct answers: A, B, D
Explanation
Diagnosing slow queries in PostgreSQL involves identifying locking issues and optimizing query execution plans. The pg_stat_activity and pg_locks system views are essential for diagnosing locking-related problems, while EXPLAIN or EXPLAIN ANALYZE helps identify missing indexes or inefficient query plans. Increasing instance size or analyzing resource metrics may help indirectly but do not directly address query-level performance issues.
- A. Correct.
Correct. The pg_stat_activity system view provides information about currently active queries and can help identify queries that are blocked or waiting on locks.
- B. Correct.
Correct. Running EXPLAIN or EXPLAIN ANALYZE on the slow query helps you understand the query execution plan and identify potential performance issues such as missing indexes.
- C. Incorrect.
Incorrect. While increasing instance size may provide temporary relief, it does not address the root cause of slow queries, such as locking or missing indexes.
- D. Correct.
Correct. The pg_locks system view provides detailed information about locks in the database and can help identify the specific locks and transactions causing contention.
- E. Incorrect.
Incorrect. While Cloud Monitoring can provide insights into instance resource usage, it does not directly help diagnose query-level issues like locks or missing indexes.