Re: Postgresql 13 query engine regression

On 5/10/21 2:13 PM, Jonathan Chen wrote:
> Hi,
> 
> I am running Postgresql 13 as a backend for Odoo, and I believe I have
> discovered a regression with the query engine.

See here:

https://wiki.postgresql.org/wiki/Slow_Query_Questions

for the information needed to get an answer to this sort of question.

> 
> This (simplified) query generated by the ORM takes 47.683s to complete
> (the result set is empty):
> SELECT "account_bank_statement_line".id
> FROM "account_bank_statement_line"
> LEFT JOIN "account_move" AS "account_bank_statement_line__move_id"
>    ON ("account_bank_statement_line"."move_id" =
> "account_bank_statement_line__move_id"."id")
> WHERE
> (
>    ("account_bank_statement_line"."move_id" in
>      (
>        SELECT "account_move".id
>        FROM "account_move"
>        WHERE ("account_move"."state" = 'posted')
>        AND ("account_move"."company_id" IS NULL  OR
> ("account_move"."company_id" in (1)))
>        ORDER BY  "account_move"."id"
>      )
>    )
>    AND ("account_bank_statement_line__move_id"."journal_id" = 29)
> )
> ORDER BY "account_bank_statement_line__move_id"."date" DESC,
> "account_bank_statement_line"."id" DESC LIMIT 1
> 
> If I remove the "LIMIT 1" on the last line, the query completes in 0.036s.
> 
> If I remove the WHERE clause, the query completes in 0.032s.
> 
> If I run the original query on Postgresql 12.6 (on a lower spec'd
> host), it completes in 0.067s.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com