Technical mailing list archives

Re: unused database indexes

Cody Kitterman
- 07/31/2016 05:05:53

     I don't mean to jump off topic, but I noticed the "over 300,000 products" and was wondering if you guys were making use of Materialized Views and/or partitions. I'm still kicking tires, here, but the idea of employing them―with some added row level security―has been rattling around a bit...

Cody K.

On Fri, Jul 29, 2016 at 7:17 PM, Graeme Gellatly <> wrote:
                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 <> wrote:

Hi Graeme,


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


ie: the output of


SELECT i.relname as indname,


       am.amname as indam,


       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));




From: Graeme Gellatly []
Sent: Friday, July 29, 2016 2:03 PM
To: Community: Framework <>
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 <> 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:






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




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:

Post to:

Post to: