Community: Framework mailing list archives

expert-framework@mail.odoo.com

Re: unused database indexes

by
Graeme Gellatly
- 07/31/2016 06:45:49
I'm not very experienced in either, but here are my thoughts.

How would you materialize a list of 300,000 products, by definition they are already materialized?  About the only related field possible worth materializing is the price if you have many nested pricelists as thats a relatively expensive operation which is used often, but we solved that with a recursive sql function to pull back all the nested rules in one loop.  But as a general comment, no we don't currently use materialized views, although have followed anybox's work on this closely in the past, and short of capability being directly integrated by Odoo would go that path with a few changes if we needed.  Aggregates of account moves and stock transactions of historical data seems the best use case.

IRT partions, 300,000 is nothing really of mostly short text data.  Master data tables don't have a lot of inserts so all you really care about is read time, and partitioning can't solve that for how the ORM searches for products.  Most user initiated ORM queries are generally openended ilike searches (and we space split the string as well, so really ugly open ended ilike searches), which I cannot imagine a single keying function that would make sense.  That holds true for a lot of tables.  Maybe in some tables it would be nice, but that is a lot of effort.  And for me, just good general database management and tuning does 90% of what I'd currently consider partitions or materialized views for and lets me live inside the ORM rather than having to hack at core code. 

On Sun, Jul 31, 2016 at 9:21 PM, Cody Kitterman <ckitterm@gmail.com> wrote:
Graeme:

     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...

R/S
Cody K.

On Fri, Jul 29, 2016 at 7:17 PM, Graeme Gellatly <gdgellatly@gmail.com> 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 <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


_______________________________________________
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