I'm trying to optimize the query below in order to go under 1 second (but i hope to do better than just that). Initially it took more than 4 seconds to execute. The query is generated by the Odoo's ORM (ERP based on Python) and using Postgresql.

FROM "product_template" as "product_product__product_tmpl_id" 
LEFT JOIN "ir_translation" as "product_product__product_tmpl_id__name" 
  ON ("product_product__product_tmpl_id"."id" = "product_product__product_tmpl_id__name"."res_id" 
  AND "product_product__product_tmpl_id__name"."type" = 'model' 
  AND "product_product__product_tmpl_id__name"."name" = 'product.template,name' 
  AND "product_product__product_tmpl_id__name"."lang" = 'en_US' 
  AND "product_product__product_tmpl_id__name"."value" != ''),
  AND (
  ((("product_product"."active" = true)  AND  
   ("product_product"."default_code"::text ilike '%MSV%')  
   OR  ("product_product__product_tmpl_id"."id" in (
    WITH temp_irt_current (id, name) as (
      SELECT ct.id, coalesce(it.value,ct."name")
      FROM product_template ct
      LEFT JOIN ir_translation it 
       ON (it.name = 'product.template,name' 
       and it.lang = 'en_US' 
       and it.type = 'model' 
       and it.res_id = ct.id 
       and it.value != '')
    SELECT id 
    FROM temp_irt_current 
    WHERE name ilike '%MSV%' 
    order by name)))  
   OR  ("product_product"."alias"::text ilike '%MSV%'))  
   OR  ("product_product"."default_product_name"::text ilike '%MSV%'))  
   OR  ("product_product"."default_product_code"::text ilike '%MSV%'))  
   OR  ("product_product"."sanitized_search"::text ilike '%MSV%')))  
 AND  ("product_product__product_tmpl_id"."sale_ok" = true))
 AND  ("product_product__product_tmpl_id"."company_id" IS NULL   
 OR  ("product_product__product_tmpl_id"."company_id" = 1))) 
 AND (("product_product__product_tmpl_id"."company_id" in (1,7))  
 OR  "product_product__product_tmpl_id"."company_id" IS NULL ) 
ORDER BY "product_product"."default_code" ,
 COALESCE("product_product__product_tmpl_id__name"."value",     "product_product__product_tmpl_id"."name") ,
 "product_product"."id"   limit 11

As a junior software developer, it's the first time that I have to handle that kind of situations/optimizations. I spent my week reading stuff about all this.

My first step was to look into the Postgresql config file to have that as results https://pastebin.com/mrPxs9He for that server configuration :

  • AMD Ryzen 7 3700 PRO - 8c/16t - 3.6 GHz/4.4 GHz
  • 128 GB ECC 2666 MHz
  • 2×960 GB SSD NVMe
  • Debian 10
  • Postgresql 12

My second step was to reduce the number of records inside the ir_translation table, do some cleanings in the indexes. Also some works on the python side.

My latest step was to look into the EXPLAIN command and try to understand each step. And then find what I have to do to optimize the query. It's where I'm stuck for now.

Based on this EXPLAIN https://explain.dalibo.com/plan/Mct#raw, I tried to create several index (btree,gin) but each time, Postgresql still choose to go with the SEQ SCAN. I don't understand why Postgres choose the SEQ SCAN instead of an index.

I have tried several combinations for multi-column indexes but without success. Based on the EXPLAIN, I don't understand which columns I should choose.

I'm looking for advices for the index part. How should I choose my columns correctly ? More generally, am I handling the situation correctly? Am I doing things in the right order?

  • 1
    I guess the best you could do is to stop using the ORM framework that is so bad at generating queries. Also, there isn't an index that can possibly help here, what with six disjunctive search criteria on product_product, all with ilike '%MSV%'. Consider using a text search engine instead.
    – mustaccio
    Jul 10 at 22:59
  • What version of PostgreSQL is this?
    – jjanes
    Jul 11 at 14:24
  • @mustaccio It isn't the wide disjunctions that kill the possibility of indexing; BitmapOr (or just a multi-column GIN index) can easily deal with that. It is that one of the conditions is not even on the same table as the others that is the nail in the coffin.
    – jjanes
    Jul 11 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.