Technical mailing list archives
Re: unused database indexesby
Like and ilike can't effectively use btree indexes. So pretty much any default char type index is useless.
Company id indexes are only effective in multicompany and then typically for the 2nd largest and lower company at least in my experience.
Most stock moves are accessed via pickings rather than directly so unsurprising.
The mail message results are for the most part unsurprising.
I am not sure but I think a unique constraint in postgres also forces an index.
For the majority of unused indexes I just live with it. Btree is not that much overhead.
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
Post to: mailto:email@example.com