GCP Cloud SQL PostgreSQL performance degradation when running complex queries
I've been banging my head against this for hours. I'm experiencing important performance degradation when executing complex SQL queries on my Cloud SQL for PostgreSQL instance. My instance is a db-f1-micro running PostgreSQL 13.4, and the queries often involve multiple joins and aggregations. Initially, I had no issues, but recently, I've noticed that some queries that used to complete in under a second are now taking several seconds or even timing out. I've tried optimizing the queries by adding indexes, particularly on foreign key relationships, but the performance improvement has been minimal. I also monitored the CPU and memory usage during query execution; the CPU spikes to 100% during heavy loads, which seems to correlate with the slowdowns. Here’s a sample query that has been problematic: ```sql SELECT o.order_id, SUM(oi.quantity) AS total_quantity FROM orders o JOIN order_items oi ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id WHERE p.category = 'Electronics' GROUP BY o.order_id ORDER BY total_quantity DESC; ``` I have also reviewed the query execution plans and noticed that the planner is choosing a sequential scan instead of an index scan in some cases, which I suspect is contributing to the slowdown. I've tried using the `EXPLAIN ANALYZE` command to diagnose the queries, but I'm not sure how to interpret the results effectively. Furthermore, I enabled query logs to check for any slow queries, but the only thing that stands out is that the cache hit ratio is below 80%, which seems low for my workload. Could this be related to the machine type, or am I missing some configuration settings that could help improve performance? Any advice on further optimizing these queries or adjusting the instance settings would be greatly appreciated. Is this even possible?