Community: Framework mailing list archives

expert-framework@mail.odoo.com

Re: unused database indexes

by
Graeme Gellatly
- 04/14/2016 09:04:11
Comments :
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 Thu, 14 Apr 2016 9:39 PM Alexandre Fayolle <alexandre.fayolle@camptocamp.com> wrote:
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

_______________________________________________
Mailing-List: https://www.odoo.com/groups/community-framework-62
Post to: mailto:expert-framework@mail.odoo.com
Unsubscribe: https://www.odoo.com/groups?unsubscribe