This question has been flagged

Assuming i have 2 models named model_parent and model_child. model_child has a m2o parent_id field referenced to model_parent. Each model has millions of records.

 self.env['model_child'].search([('id', '=', 3), ('parent_id.state', '=', 'done')], limit=1)

 When i execute the search above, odoo searching the parent table with query like that

"select id form model_parent where state = 'search'"

and gathering ids to use in child_model's query. 

"select id form model_child where id = 3 and parent_id in (%s, %s, %s)"

if parent table has millions of records in done state odoo will give millions of ids in second query instead of using id = 3 filter first. If that was a single join query, postgres will handle the search priority by using index but odoo orm not using join queries.

How can i trust dot notation queries in odoo? Is this not a big problem?

Avatar
Discard
Best Answer

if your queries not changing by structure but only by parameters you can consider using raw sql queries in your model in order to retrieve the relevant ids which might make your life easier and query time way shorter. additional btree indexes on queried fields might speed things up too.

ORMs won't make life easy all the time. sometimes workaround is needed.


Avatar
Discard