| From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
|---|---|
| To: | Andreas <andreasfe(at)pm(dot)me> |
| Cc: | Manu <manuelreyesbravo(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Ayoub Kazar <ayoub(dot)kazar(at)data-bene(dot)io>, Andres Freund <andres(at)anarazel(dot)de>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: O(N^3) planning time in choose_plan_name() since 8c49a484e8e |
| Date: | 2026-11-20 15:33:15 |
| Message-ID: | CA+Tgmob=v0QHEnE9gXC4WTs34U3boy5VoN9TFLyT6ahqs2oUsw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Fri, Nov 20, 2026 at 8:36 AM Andreas <andreasfe(at)pm(dot)me> wrote:
> I can confirm it is a real issue, we have it in a real application. The application generates its SQL, and a typical statement has 250 scalar sublinks (expr_N), around 100 subqueries with the same alias, around 170 CTEs and a UNION ALL with about 100 arms.
>
> With 19.0 this statement needs about 78 ms for planning, with 18.7 about 40 ms. Same data, statistics and build options, and the execution time is the same in both. In a profile about 40% of the planning time is strcmp() in choose_plan_name(). For the whole application this makes the requests 3.5% slower, the heavier ones 5-9%.
Well, that sucks. Sorry about that.
> You wrote that with a list also distinct names can make it O(N^2). We see this too. When I change the statement so most subqueries have their own alias and most scalar sublinks are gone, it still needs about 52 ms on 19.0 (40 ms on 18.7), and most of the difference is still in choose_plan_name(). As far as I see, your hash table idea would solve both cases for us.
>
> So for us this is for sure a real world use case. Generated SQL gets to these numbers in normal use.
OK. Can you test Manu's latest patch and see how that does with your workload?
I would normally be somewhat reluctant to back-patch anything here,
but if people are hitting this on real workloads within days of
release, I think we should.
--
Robert Haas
Databricks
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Peter Eisentraut | 2026-11-20 15:34:53 | Re: compiler attribute alloc_size |
| Previous Message | Raja Sai pranav | 2026-11-20 15:22:11 | pg_waldump: WAL in pg_wal.old/ inside a tar archive is read as WAL |