Re: to_jsonb performance on array aggregated correlated subqueries

On Fri, Aug 12, 2022 at 3:02 PM Rick Otten wrote: > > > On Fri, Aug 12, 2022 at 2:50 PM Nico Heller wrote: > >> Good day, >> >> consider the following query: >> >> WITH aggregation( >> SELECT >> a.*, >>(SELECT array_agg(b.*) FROM b WHERE b.a_id = a.id) as "bs", >>

Re: to_jsonb performance on array aggregated correlated subqueries

Here are the query plans (I hope my anonymization didn't break them). I ran every query a couple times before copying the plan to avoid timing issues because of disk access. Ignore the sequential scan on one of the tables, it's very small (will change in the future) so Postgres opts for a faster

Re: to_jsonb performance on array aggregated correlated subqueries

Am 12.08.2022 um 21:15 schrieb Rick Otten: On Fri, Aug 12, 2022 at 3:07 PM Nico Heller wrote: Am 12.08.2022 um 21:02 schrieb Rick Otten: On Fri, Aug 12, 2022 at 2:50 PM Nico Heller wrote: Good day, consider the following query: WITH aggregation(

Re: to_jsonb performance on array aggregated correlated subqueries

Hi, On 2022-08-12 18:49:58 +, Nico Heller wrote: > WITH aggregation( >     SELECT >    a.*, >   (SELECT array_agg(b.*) FROM b WHERE b.a_id = a.id) as "bs", >   (SELECT array_agg(c.*) FROM c WHERE c.a_id = a.id) as "cs", >   (SELECT array_agg(d.*) FROM d WHERE d.

Re: to_jsonb performance on array aggregated correlated subqueries

On Fri, Aug 12, 2022 at 07:02:36PM +, Nico Heller wrote: > I knew I forgot something: We are currently on 13.6. When was this issue > fixed? There's a WIP/proposed fix, but the fix is not released. I asked about your version because jit was disabled by default in v11. But it's enabled by defau

Re: to_jsonb performance on array aggregated correlated subqueries

Am 12.08.2022 um 21:02 schrieb Rick Otten: On Fri, Aug 12, 2022 at 2:50 PM Nico Heller wrote: Good day, consider the following query: WITH aggregation( SELECT     a.*,    (SELECT array_agg(b.*) FROM b WHERE b.a_id = a.id ) as "b

Re: to_jsonb performance on array aggregated correlated subqueries

I knew I forgot something: We are currently on 13.6. When was this issue fixed? Am 12.08.2022 um 20:56 schrieb Justin Pryzby: What version of postgres ? I wonder if you're hitting the known memory leak involving jit. Try with jit=off or jit_inline_above_cost=-1. Good day, conside

Re: to_jsonb performance on array aggregated correlated subqueries

What version of postgres ? I wonder if you're hitting the known memory leak involving jit. Try with jit=off or jit_inline_above_cost=-1. -- Justin

to_jsonb performance on array aggregated correlated subqueries

Good day, consider the following query: WITH aggregation(     SELECT    a.*,   (SELECT array_agg(b.*) FROM b WHERE b.a_id = a.id) as "bs",   (SELECT array_agg(c.*) FROM c WHERE c.a_id = a.id) as "cs",   (SELECT array_agg(d.*) FROM d WHERE d.a_id = a.id) as "ds",