Price index performance on very large stores

[nicka101]

Preconditions and environment

• Magento version 2.4.5+ (or any release after 263c17f)
• Have a lot of products (the more, with more prices, the easier this issue is to reproduce for demo)

Steps to reproduce

1. Reindex catalog_product_index_price
2. Observe that since 263c17f the performance of deleteOutdatedData is much worse (around 800-1200x slower than it should be in our very large instances with ~30 million prices)

Expected result

Price reindex is workable, and uses indexes for its join in deleteOutdatedData, because they exist

Actual result

It's 800-1200x slower than it should be, making a reindex take multiple days (based on the runtime of the individual queries, but can't tell for sure, as i'm not waiting that long), when on previous versions it took 4 hours

Additional information

When catalog_product_index_price_temp is created, it's based on catalog_product_index_price_tmp , which since 263c17f has had different primary key and indexes to the main table, meaning this JOIN has no indexes to use for the select, and is forced to do a full tablescan

We reproduced it as follows:

1. All indexes have been switched to realtime mode - bin/magento indexer:set-mode realtime

2. Then we started indexing only for MSI - bin/magento indexer:reindex inventory

As @vladimirspucko described above, the inventory indexing itself was performed quickly, about a minute for 850k products in our case. But then a plugin came into operation, which starts price indexing in realtime mode:
\Magento\InventoryCatalog\Plugin\InventoryIndexer\Indexer\Stock\Strategy\Sync\PriceIndexUpdatePlugin::afterExecuteList()

which calls \Magento\Catalog\Model\Indexer\Product\Price\Processor::reindexList()

And if the mode is not schedule this indexing has never been performed on our installation with 850k products.

Note: if we use schedule mode, then full inventory indexing takes about 50 seconds for all the same 850k products

Release note

No response

Triage and priority

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

[m2-assistant]

Hi @nicka101. Thank you for your report.
To speed up processing of this issue, make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, Add a comment to the issue:

• @magento give me 2.4-develop instance - upcoming 2.4.x release
• For more details, review the Magento Contributor Assistant documentation.
• Add a comment to assign the issue: @magento I am working on this
• To learn more about issue processing workflow, refer to the Code Contributions.

---

Join Magento Community Engineering Slack and ask your questions in #github channel.
⚠️ According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting.
🕙 You can find the schedule on the Magento Community Calendar page.
📞 The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, join the Community Contributions Triage session to discuss the appropriate ticket.

[m2-assistant]

Hi @engcom-Dash. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue.
• 3. Add Area: XXXXX label to the ticket, indicating the functional areas it may be related to.
• 4. Verify that the issue is reproducible on 2.4-develop branch
     Details

     - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
     - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
     - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

[m2-assistant]

Hi @engcom-Hotel. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue.
• 3. Add Area: XXXXX label to the ticket, indicating the functional areas it may be related to.
• 4. Verify that the issue is reproducible on 2.4-develop branch
  Details

  - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!
• 5. Add label Issue: Confirmed once verification is complete.
• 6. Make sure that automatic system confirms that report has been added to the backlog.