Technical mailing list archives

unused database indexes

Camptocamp France SAS, Alexandre Fayolle - Camptocamp
- 04/14/2016 04:52:42

I've noticed that a large number of indexes on production servers are
never used (idx_scan = 0 after several months). I imagine most of these
are created by select=True on field definitions, and I'm wondering on
the impact these index have on the overall performance of Odoo: since
they are not used, I see no benefit, only costs (in the maintenance of
the index during database insert/update/delete, in the size of the
database and in the time of backup/restore operations).

It could also be that my customers are not using the parts of Odoo which
benefit from these indices. Yet some of these are clearly not going to
be used by the optimizer (indexes on the active column, lots of indexes
on name when the searches are made with LIKE %val%)

Probably some of these are herited from older versions of PostgreSQL and
maybe were used at the time.

Has anyone performed an audit on these indices?

Alexandre Fayolle
Chef de Projet
Tel : +33 4 58 48 20 30

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac Cedex