Community: Framework mailing list archives

expert-framework@mail.odoo.com

Re: unused database indexes

by
Graeme Gellatly
- 07/29/2016 18:58:58
                indname                |       indrelid       | indam |    indkey_names
---------------------------------------+----------------------+-------+--------------------
 account_invoice_name_trgm_idx         | account_invoice      | gist  | {name}
 account_invoice_number_trgm_idx       | account_invoice      | gist  | {number}
 ir_translation_src_hash_idx           | ir_translation       | hash  | {src}
 ir_translation_value_trgm_gin_idx     | ir_translation       | gin   | {value}
 mrp_production_name_trgm_idx          | mrp_production       | gist  | {name}
 mrp_production_origin_trgm_idx        | mrp_production       | gist  | {origin}
 procurement_order_origin_trgm_idx     | procurement_order    | gist  | {origin}
 product_product_default_code_trgm_idx | product_product      | gin   | {default_code}
 product_product_ean13_tgm_idx         | product_product      | gin   | {ean13}
 product_product_name_trgm_gin_idx     | product_product      | gin   | {name}
 purchase_order_name_trgm_idx          | purchase_order       | gist  | {name}
 purchase_order_origin_trgm_idx        | purchase_order       | gist  | {origin}
 res_partner_display_name_idx          | res_partner          | gist  | {display_name}
 res_partner_name_trgm_gin_idx         | res_partner          | gin   | {name}
 res_partner_street_tgm                | res_partner          | gin   | {street}
 sale_order_client_order_ref_trgm_idx  | sale_order           | gist  | {client_order_ref}
 sale_order_name_trgm_idx              | sale_order           | gist  | {name}
 stock_picking_origin_trgm_idx         | stock_picking        | gist  | {origin}
 stock_production_lot_name_trgm_idx    | stock_production_lot | gist  | {name}
 stock_production_lot_prefix_trgm_idx  | stock_production_lot | gist  | {prefix}
 stock_production_lot_ref_trgm_idx     | stock_production_lot | gist  | {ref}

is all the non btree ones.  Couldn't be bothered doing the whole list but there are some important compound/partial ones as well that use btree based on standard common orm queries (company_id, active etc). Take it in the context of a manufacture to order firm with over 300,000 products, delivering each order to a different address, every manufactured item serialized where the originating SO number is the most searched item. V7.

On Sat, Jul 30, 2016 at 10:30 AM, Ray Carnes <ray.carnes@bistasolutions.com> wrote:

Hi Graeme,

 

Are you allowed/able to share the indices in that system?

 

ie: the output of

 

SELECT i.relname as indname,

       idx.indrelid::regclass,

       am.amname as indam,

       ARRAY(

       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)

       FROM generate_subscripts(idx.indkey, 1) as k

       ORDER BY k

       ) as indkey_names

FROM   pg_index as idx

JOIN   pg_class as i

ON     i.oid = idx.indexrelid

JOIN   pg_am as am

ON     i.relam = am.oid

JOIN   pg_namespace as ns

ON     ns.oid = i.relnamespace

AND    ns.nspname = ANY(current_schemas(false));

 

Ray.

 

From: Graeme Gellatly [mailto:gdgellatly@gmail.com]
Sent: Friday, July 29, 2016 2:03 PM
To: Community: Framework <expert-framework@mail.odoo.com>
Subject: Re: unused database indexes

 

Used in production for 4 years. The cost is by far worth the benefit IMO. Multiple orders of magnitude faster searches on things like partner names, sale names, products, account moves. No noticeable write slow down. Use gin for master data, gist for stuff that changes regularly, although haven't tried gin on regularly changing data since they improved it's write speed.

The main issue for others will be lack of unaccent support as postgres treats unaccent as volatile by default. Idk what a standard Odoo index / data split looks like but ours is roughly 50 50.

 

On Thu, 28 Jul 2016 7:13 PM Alexandre Fayolle <alexandre.fayolle@camptocamp.com> wrote:

On 27/07/2016 17:01, Leonardo Rochael Almeida wrote:

 

> Another point raised in the original thread was the fact that most CHAR

> field indexes are useless since most searches on these fields are

> through LIKE and ILIKE operators.

> 

> For these cases, we could be using trigram indexes:

> 

> https://www.postgresql.org/docs/current/static/pgtrgm.html

> 

 

 

Careful evaluation of trigram indexes should be done. I'm concerned of

the cost of these, both disk-wise and CPU-wise for the often updated

tables.

 

-- 

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

_______________________________________________
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