Technical mailing list archives

RE: unused database indexes

Bista Solutions US, Ray Carnes
- 07/29/2016 18:22:34

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: