Search Postgresql Archives

Re: Postgresql 13 query engine regression

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

sorry to jump in, but looks like I have a similar pb on a 12.6 instance.
on a quite simple request, 
with limit 1000 it takes 27ms, and with limit 10, it takes 145000 ms
looking at both the explain analyze plans, there is a huge difference:
with limit 1000, postgres do an optimization of the plan putting join in an efficient order. and using a hash left join 
with limit 10: no optimization. so inefficient order and reading of a huge amount of data.and using a nested loop left join

stats  have been updated through vacuum analyze.

If interested I can put the plans (in another thread...) 


Marc MILLAS
Senior Architect
+33607850334



On Mon, May 10, 2021 at 11:13 PM Jonathan Chen <jonc@xxxxxxxxxxx> wrote:
Hi,

I am running Postgresql 13 as a backend for Odoo, and I believe I have
discovered a regression with the query engine.

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.
--
Jonathan Chen <jonc@xxxxxxxxxxx>



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux