[15.0][IMP] website_sale_product_attribute_value_filter_existing: performance

chienandalu commented 1 year ago

Use _search so we can improve the orm query with an IN SELECT instead of a bare set of ids.

In a database with lots of products before the change:

"GET /shop HTTP/1.1" 200 - 487 0.910 1.281
"GET /shop HTTP/1.1" 200 - 678 1.512 3.608
"GET /shop HTTP/1.1" 200 - 663 1.094 1.662
"GET /shop HTTP/1.1" 200 - 515 0.831 1.343

After the change:

"GET /shop HTTP/1.1" 200 - 248 0.462 0.767
"GET /shop HTTP/1.1" 200 - 375 0.578 0.970
"GET /shop HTTP/1.1" 200 - 245 0.626 0.714
"GET /shop HTTP/1.1" 200 - 293 0.608 0.822

Query plans compared:

Using IN (<ids>)
- PLAN FOR QUERY 1 (fetch ids): https://explain.dalibo.com/plan/9383def674bc1666 (execution time: 40,1ms, plan: 5,86 ms)
- PLAN FOR QUERY 2 (select in ids): https://explain.dalibo.com/plan/3c9d5cah03g47123 (execution time: 22,1ms, plan: 27 ms) total execution time: 62,2ms
Using IN SELECT
- PLAN FOR QUERY (select in select): https://explain.dalibo.com/plan/g8c35f93f991dhff (execution time: 38,2ms, plan: 3,09 ms)

cc @Tecnativa

ping @carlosdauden @pedrobaeza

(inspired by @nseinlet performance talk ;) (https://www.odoo.com/es_ES/event/odoo-experience-2023-3735/track/performance-python-5833))

nseinlet commented 1 year ago

@rco-odoo is the original author, I was just spreading the news.

nseinlet commented 1 year ago

You could probably comment with an explain plan before/after. My 2 cents.

github-actions[bot] commented 8 months ago

There hasn't been any activity on this pull request in the past 4 months, so it has been marked as stale and it will be closed automatically if no further activity occurs in the next 30 days. If you want this PR to never become stale, please ask a PSC member to apply the "no stale" label.

pedrobaeza commented 8 months ago

/ocabot merge patch

pedrobaeza commented 8 months ago

/ocabot merge patch

OCA-git-bot commented 8 months ago

This PR looks fantastic, let's merge it! Prepared branch 15.0-ocabot-merge-pr-875-by-pedrobaeza-bump-patch, awaiting test results.

OCA-git-bot commented 8 months ago

Congratulations, your PR was merged at bf40fa0eb421354b8470192d595f413ce6169ce2. Thanks a lot for contributing to OCA. ❤️

OCA / e-commerce

[15.0][IMP] website_sale_product_attribute_value_filter_existing: performance #875