Community: Framework mailing list archives

expert-framework@mail.odoo.com

Re: unused database indexes

by
Camptocamp France SAS, Alexandre Fayolle - Camptocamp
- 04/14/2016 05:06:15
On 14/04/2016 10:52, Alexandre Fayolle wrote:
> Hello,
> 
> 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?
> 
> 


On the instance I'm looking at (Odoo 8, pg 9.5), the stock is heavily
used. The following indices have a scan count of 0:

stock_move_create_date_index
stock_move_date_expected_index
stock_move_invoice_state_index
stock_move_name_index
stock_picking_origin_index
stock_picking_priority_index
stock_quant_company_id_index
stock_quant_package_company_id_index
stock_quant_package_location_id_index
stock_quant_package_name_index
stock_quant_package_owner_id_index
stock_quant_package_packaging_id_index
stock_quant_package_parent_left_index
stock_quant_package_parent_right_index
stock_route_product_route_id_index
stock_route_warehouse_warehouse_id_index
stock_warehouse_company_id_index
stock_warehouse_name_index
stock_location_company_id_index
stock_location_path_auto_index
stock_location_path_location_dest_id_index
stock_location_route_company_id_index
stock_location_route_categ_route_id_index
stock_location_route_move_route_id_index
stock_location_route_procurement_route_id_index
procurement_order_priority_index


Also suspicious given the number of records are the unused indices on
mail tables:

mail_message_message_id_index
mail_message_subtype_id_index
mail_message_res_partner_rel_mail_message_id_index
mail_notification_is_read_index
mail_vote_message_id_index
mail_vote_user_id_index
message_attachment_rel_message_id_index

-- 
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
http://www.camptocamp.com