PostgreSQL: How to optimize slow queries with multiple LEFT JOINs on large tables?
I'm converting an old project and I need help solving I'm relatively new to this, so bear with me..... I am facing significant performance issues when executing a query that involves multiple `LEFT JOIN`s across large tables in PostgreSQL 13... The query runs extremely slow, taking over 30 seconds to return results, and I suspect that it is related to the inefficient joins. Here's the query I'm trying to optimize: ```sql SELECT a.id, a.name, b.value, c.description FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id LEFT JOIN table_c c ON b.id = c.b_id WHERE a.status = 'active'; ``` Tables `table_a`, `table_b`, and `table_c` have 1 million, 2 million, and 1 million rows, respectively. I've created indexes on the foreign keys `a_id` and `b_id`, but the execution plan still shows a `Seq Scan` on `table_a`, which seems to be the bottleneck. I ran the query with `EXPLAIN ANALYZE`, and it indicates that the `LEFT JOIN` operations are causing the slow performance. Here's a snippet of the execution plan: ``` Plan: Seq Scan on table_a a (cost=0.00..15758.00 rows=1000000 width=24) (actual time=0.018..23023.88 rows=1000000 loops=1) Left Join (cost=0.00..45689.10 rows=1000000 width=40) (actual time=0.019..23024.15 rows=1000000 loops=1) -> Seq Scan on table_b b (cost=0.00..20000.00 rows=2000000 width=20) (actual time=0.020..15000.04 rows=2000000 loops=1) -> Seq Scan on table_c c (cost=0.00..9999.99 rows=1000000 width=20) (actual time=0.018..23023.85 rows=1000000 loops=1) ``` I have tried adding various indexes and even rewriting the query, but the execution time remains the same. I am unsure if I am missing something in terms of query optimization or if PostgreSQL is not utilizing the indexes correctly. Any suggestions on how to improve this query's performance would be greatly appreciated! How would you solve this? My development environment is Ubuntu. Has anyone else encountered this? For context: I'm using Sql on CentOS. I'd love to hear your thoughts on this. Has anyone else encountered this?