Slow response time with 300 000 products

[Jean-Luc WEYL]

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()

[Borja López Soilán (NeoPolus) (borjals)]

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(osv.osv):
    ...
    _columns = {
        ...
        'value': fields.reference('Value', selection=_models_get2, size=128, select=1),
        'res_id': fields.reference('Resource', selection=_models_get, size=128, select=1),
        'company_id': fields.many2one('res.company', 'Company', select=1),
        'fields_id': fields.many2one('ir.model.fields', 'Fields', ondelete='cascade', required=True, select=1)
    }
    ...

[Jean-Luc WEYL (jean-luc-weyl)]

Yes, good point.

[Anup(SerpentCS) (anup-serpent)]

Hello

@Jean-Luc
       Thanks for pointing out the issue it's a good point for improving the performance.
@Borja Lopez,
       Indeed a very good suggestion to add index using select.

It has been fixed by revision 2125 <email address hidden>

Thanks.