I have encountered a scenario where the same query on a PostgreSQL database is exhibiting different index selection and join strategies between the QA and Prod environments. I'm trying to understand the possible reasons behind this behaviour.
Here are the details:
- Smaller dataset compared to Prod
- Query uses nested loop join
- Query uses
- List item
- Larger dataset compared to QA
- Query uses merge join
- Query uses
- Size of
idx_user_id_customer_idindex is 118GB while
idx_customer_idindex is 85GB
Both environments have the same set of indexes. The main differences lie in the size of the data and the execution plans chosen by the query optimizer.
Prod explain log: https://explain.depesz.com/s/28la
QA explain log: https://explain.depesz.com/s/zM6e
1. What could be the possible reasons for the disparity in index selection and join strategy between the two environments?
2. Are there any specific factors that influence the optimizer's decision-making process?
Here is what I think, please correct me If I am wrong and add more information:
- It's using nested loop join instead of merge join because on QA there might be fewer rows for the same record on one side.
- The index size of
idx_user_id_customer_idis large that's why it is ignoring it. Or might be the selectivity of
user_idis low than
customer_idthat's why it's picking