I'm using Aurora Postgres version 17 and I'm experiencing some performance issues with one of my queries. I've shared the execution plan [here](https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd221492).
1. After creating an index on the "tran_date" column in the "txn_tbl" table, the execution plan shifted from a "sequence scan" to an "Index Scan Backward." I'm curious about what this means. Since the index only covers the "tran_date" column, how do the other columns still get read from the table?
2. The query is taking a lot of time during a nested loop join. The data type for `df.ent_id` is "int8" while `m.ent_id` is "Numeric 12". I tried creating an index on the expression `(df.ent_id)::numeric`, but it didn't seem to change the execution plan or speed up the query.
Here's a snippet of the nested loop join performance:
-> Nested Loop (cost=266.53..1548099.38 rows=411215 width=20) (actual time=6.009..147.695 rows=1049 loops=1)
Join Filter: ((df.ent_id)::numeric = m.ent_id)
Rows Removed by Join Filter: 513436
Buffers: shared hit=1939
Any advice on improving query performance would be appreciated!
2 Answers
It sounds like your query's main bottleneck might be the nested loop join. When you create an index, an "Index Scan Backward" indicates it can fetch data from the index, but to get values for the other columns, the database may still need to access the table. Consider using a covering index that includes all the columns needed for your query to potentially avoid accessing the table altogether.
Also, for your nested loop, you could look into rewriting the join or possibly using a materialized view, especially if the data doesn't change too often. In some cases, reducing the number of rows processed by the join can help too. The key would be to optimize the join more than just focusing on the index itself.
You definitely want to check how many rows are being fetched and consider adjusting your query logic. Sometimes, simplifying or restructuring your joins can yield better performance. Have you looked into using the EXPLAIN command to get more insights about where it's slowing down? It might reveal additional areas for improvement. And yeah, the data type mismatch could also be affecting the execution plan. Try to keep data types consistent across joins, when possible.
Thanks for the tips! I will run EXPLAIN to further investigate the bottlenecks and see if I can adjust my joins or data types accordingly.
That's a good point! A covering index might really help reduce the time spent on those table scans. Also, if possible, try to analyze the data distribution and optimize your query's WHERE clauses to minimize the number of rows that need to be scanned.