From: | Marc Millas <marc(dot)millas(at)mokadb(dot)com> |
---|---|
To: | Jonathan Chen <jonc(at)chen(dot)org(dot)nz> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Postgresql 13 query engine regression |
Date: | 2021-05-10 22:25:53 |
Message-ID: | CADX_1ab5S628jmj-fqcTTkMaunNNN9JbE1wuKsSyrJR=2BRmZw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
www.mokadb.com
On Mon, May 10, 2021 at 11:13 PM Jonathan Chen <jonc(at)chen(dot)org(dot)nz> 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(at)chen(dot)org(dot)nz>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-05-10 22:34:30 | Re: Postgresql 13 query engine regression |
Previous Message | Adrian Klaver | 2021-05-10 21:47:35 | Re: Postgresql 13 query engine regression |