0

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:

QA Environment:

  • Smaller dataset compared to Prod
  • Query uses nested loop join
  • Query uses idx_user_id_id_customer_id index
  • List item

Prod Environment:

  • Larger dataset compared to QA
  • Query uses merge join
  • Query uses idx_customer_id index
  • Size of idx_user_id_customer_id index is 118GB while idx_customer_id index 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_id is large that's why it is ignoring it. Or might be the selectivity of user_id is low than customer_id that's why it's picking customer_id
5
  • Those plans are seriously mangled. Both of them look like multiple plans got intermixed together.
    – jjanes
    Commented Jul 9, 2023 at 12:54
  • Oh! I just noticed I, by mistake, appended to the already existing example of query plan on despez. I will update soon. Thank for pointing out.
    – sujeet
    Commented Jul 9, 2023 at 14:22
  • @jjanes Updated the explain logs.
    – sujeet
    Commented Jul 10, 2023 at 5:15
  • The parameter values are different, and the number of rows found in customer is different. Commented Jul 10, 2023 at 9:19
  • One plan expects 61 rows (and finds far more than that), and one expects 1 row. Without first examining that difference nothing else matters. If the data differs so starkly, why wouldn't it use a different plan?
    – jjanes
    Commented Jul 10, 2023 at 19:09

1 Answer 1

1
  1. What could be the possible reasons for the disparity in index selection and join strategy between the two environments?

QA Environment: Smaller dataset compared to Prod

...

Prod Environment: Larger dataset compared to QA

That's the disparity, the different data, mostly the amount of rows difference.

  1. Are there any specific factors that influence the optimizer's decision-making process?

Yes, the size of data. Different data operations in the query plan are more efficient depending on the size of data being operated on. Nested Loops are typically more efficient for smaller sets of data being joined to. Merge Join is better for larger datasets.

It's tough maintaining multiple environments consistent enough to always get the same query plans for all queries, but to do so you'd have to maintain a pretty similar set of data across environments, in all of your relevant tables.

2
  • What's your take on difference between used indexes?
    – sujeet
    Commented Jul 9, 2023 at 14:19
  • 1
    @sujeet Same reason as the rest of my answer. Index selection is based on what the SQL engine thinks is most efficient to use for that particular query based on the exact data and it's statistics. Different data can affect one index being more performant for scanning for example.
    – J.D.
    Commented Jul 11, 2023 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.