Hi,
I’m trying to improve the performance of supply stock wizard. Currently or database have the following figures:
- Product Template: 4.500 records
- Product Product: 60.000 records (there are products that have more than 100 variants other just one)
- Stock move: 93.000 records.
On our current database the process more than 12 minutes as we currently have the following functions executed:
- Shipments internal are created
- Purchase requests are created
- New shipments are generated as we have order points to generate internal shipments to supply demand for the first internal shipments
- Purchase requests are recomputed (but nothing new is created).
After some digging I found most of the time is spent reading the 60.000 products. Which is run every in every step (internal and purchase requests).
For example I just found that changing this code from generate_shipment_internal :
products = Product.search([
('type', 'in', ['goods', 'assets']),
], order=[('id', 'ASC')])
product_ids = [p.id for p in products]
Into:
products = Product.search([
('type', 'in', ['goods', 'assets']),
], order=[('id', 'ASC')], query=True)
cursor = Transaction().connection.cursor()
cursor.execute(*products)
product_ids = [p[0] for p in cursor.fetchall()]
To avoid reading all the product fields provided a performance improvement of 2minutes and 24seconds.
This has a very big impact on the database and I’m wondering which may cause the ORM to take so much time to load this data. I executed the query used to read the products and the database and it just takes 50ms:
# EXPLAIN ANALYZE SELECT "a"."id" AS "id", "a"."active" AS "active", "a"."code" AS "code", "a"."color" AS "color", "a"."create_date" AS "create_date", "a"."create_uid" AS "create_uid", "a"."manufacturer_code" AS "manufacturer_code", "a"."odoo_id" AS "odoo_id", "a"."replaced_by" AS "replaced_by", "a"."size" AS "size", "a"."suffix_code" AS "suffix_code", "a"."template" AS "template", "a"."write_date" AS "write_date", "a"."write_uid" AS "write_uid", CAST(EXTRACT('EPOCH' FROM COALESCE("a"."write_date", "a"."create_date")) AS VARCHAR) AS "_timestamp" FROM "product_product" AS "a" LEFT JOIN "product_template" AS "b" ON ("b"."id" = "a"."template") WHERE ((("b"."type" IN ('goods', 'assets')) AND true) AND ((("b"."consumable" = false) OR ("b"."consumable" IS NULL)) AND true) AND (("b"."purchasable" = true) AND true) AND ("a"."active" AND "b"."active")) ORDER BY "a"."id" ASC
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.58..8234.56 rows=58149 width=105) (actual time=0.022..48.284 rows=60295 loops=1)
-> Index Scan using product_product_pkey on product_product a (cost=0.29..5191.45 rows=60753 width=73) (actual time=0.008..11.871 rows=60762 loops=1)
Filter: active
Rows Removed by Filter: 153
-> Memoize (cost=0.29..0.32 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=60762)
Cache Key: a.template
Cache Mode: logical
Hits: 56315 Misses: 4447 Evictions: 0 Overflows: 0 Memory Usage: 446kB
-> Index Scan using product_template_pkey on product_template b (cost=0.28..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=4447)
Index Cond: (id = a.template)
Filter: (((NOT consumable) OR (consumable IS NULL)) AND purchasable AND active AND ((type)::text = ANY ('{goods,assets}'::text[])))
Rows Removed by Filter: 0
Planning Time: 0.254 ms
Execution Time: 50.174 ms
Do you have some clue on how to dig and properly understand which is causing the performance degradation?