When you have a product table with more more than 300 000, a search request can take a long time (20-30s).
We have managed to bring that time back to 1-2s by adding some indexes.
We have added the following definition for class ir_property in file base/res/ir_property.py :
def _auto_init(self, cr, context={}):
super(ir_property, self)._auto_init(cr, context)
cr.execute('SELECT indexname FROM pg_indexes WHERE indexname = \'ir_property_res_id_index\'')
if not cr.fetchone():
cr.execute('CREATE INDEX ir_property_res_id_index ON ir_property (res_id)')
cr.commit()
cr.execute('SELECT indexname FROM pg_indexes WHERE indexname = \'ir_property_fields_id_index\'')
if not cr.fetchone():
cr.execute('CREATE INDEX ir_property_fields_id_index ON ir_property (fields_id)')
cr.commit()
cr.execute('SELECT indexname FROM pg_indexes WHERE indexname = \'ir_property_value_index\'')
if not cr.fetchone():
cr.execute('CREATE INDEX ir_property_value_index ON ir_property (value)')
cr.commit()
Wouldn't it be better to alter the 'columns' definition (adding select=1), so the ORM creates the indexes by itself?
Something like:
class ir_property(
...
_columns = {
...
'value': fields.
'res_id': fields.
}
...