(Postgres v13)

I've got a query which takes 2 - 5 seconds to plan. The query joins my languages table and translations table to get translation results for multiple languages. When I add even more languages/translations to load the execution time is exponentially growing.

select
    key0_.id as col_0_0_,
    key0_.name as col_1_0_,
    (select
         count(screenshot60_.id)
     from
         screenshot screenshot60_
             inner join
         key key61_
         on screenshot60_.key_id=key61_.id
     where
    key0_.id=key61_.id) as col_2_0_,
    languages2_.tag as col_3_0_,
    translatio31_.id as col_4_0_,
    translatio31_.text as col_5_0_,
    translatio31_.state as col_6_0_,
    translatio31_.auto as col_7_0_,
    translatio31_.mt_provider as col_8_0_,
    languages3_.tag as col_11_0_,
    translatio32_.id as col_12_0_,
    translatio32_.text as col_13_0_,
    translatio32_.state as col_14_0_,
    translatio32_.auto as col_15_0_,
    translatio32_.mt_provider as col_16_0_,
    
    ... the same over and over many times ...
    
        languages30_.tag as col_227_0_,
    translatio59_.id as col_228_0_,
    translatio59_.text as col_229_0_,
    translatio59_.state as col_230_0_,
    translatio59_.auto as col_231_0_,
    translatio59_.mt_provider as col_232_0_,
    0 as col_233_0_,
    0 as col_234_0_
from
    key key0_
        inner join
    project project1_
    on key0_.project_id=project1_.id
        inner join
    language languages2_
    on project1_.id=languages2_.project_id
        and (
           languages2_.tag='en-US'
           )
        inner join
    language languages3_
    on project1_.id=languages3_.project_id
        and (
           languages3_.tag='es-PE'
           )
    
        ... many times the same ... 
    
        inner join
    language languages30_
    on project1_.id=languages30_.project_id
        and (
           languages30_.tag='es-MX'
           )
        left outer join
    translation translatio31_
    on key0_.id=translatio31_.key_id
        and (
               translatio31_.language_id=languages2_.id
           )
        
        ... many times the same ...
        
        left outer join
    translation translatio59_
    on key0_.id=translatio59_.key_id
        and (
               translatio59_.language_id=languages30_.id
           )
where
    (
            key0_.name in (
            'base_administrative_notes.desc'
            )
        )
  and
    key0_.project_id=836
group by
    key0_.id ,
    languages2_.tag ,
    translatio31_.id ,
    languages3_.tag ,
    translatio32_.id ,
   ... many times the same ...
    languages30_.tag ,
    translatio59_.id
order by
    key0_.name asc nulls first,
    key0_.id asc nulls first limit 1

The visualised EXPLAIN ANALYSE result: https://explain.dalibo.com/plan/uWS (the full query can be found there as well as raw output from explain (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)).

I found in other threads that this can be caused by using too many indexes on the tables, but I only have a unique index on my translations table on key_id and language_id columns.

EDIT: I've found out that setting join_collapse_limit to some value between 1 to 5 reduces the planning to under 200ms. Don't know if this is the best solution, but I am going to use it as a workaround for now.

CodePudding user response：

As Laurenz Albe explained, the planner is probably trying to reorder the joins to optimize the query. With n tables, the number of possible joins order is n! (factorial n).

My suggestion is to :

1. make sure the order is the best in your query
2. set that particular parameter to 1 before the query
3. play the query
4. reset the parameter

You can check Alicja's slide deck (from slide 22) where she illustrates that particular problem with examples here: https://www.postgresql.eu/events/pgconfeu2017/sessions/session/1617/slides/9/FromMinutesToMilliseconds.pdf