Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rvIMU-00B48z-EF for pgsql-general@arkaria.postgresql.org; Fri, 12 Apr 2024 14:58:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rvIMT-00FHhJ-4i for pgsql-general@arkaria.postgresql.org; Fri, 12 Apr 2024 14:58:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rvIMS-00FHgs-NA for pgsql-general@lists.postgresql.org; Fri, 12 Apr 2024 14:58:40 +0000 Received: from mail-ed1-x532.google.com ([2a00:1450:4864:20::532]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rvIMP-000Z6V-EO for pgsql-general@lists.postgresql.org; Fri, 12 Apr 2024 14:58:40 +0000 Received: by mail-ed1-x532.google.com with SMTP id 4fb4d7f45d1cf-56e1baf0380so1115014a12.3 for ; Fri, 12 Apr 2024 07:58:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712933915; x=1713538715; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=kaTU67//3yASCqk1tm8s+SMFkckNR81C9QaPFJkK7AY=; b=K0Hz9Ak0DvYLXAQKnPxLNvVspGc5qcALf5qpHEhqA6zkaOjQ6v0+H12VJ+1w/sQYRX s9wLVkk8G97JmH3yWZQJsb1C2sIicxCzKKd515HYm1rjxzRQ38/1EgVbJFZqlDynMVbS pP4ss4CyM4Wak1Mi5rnFJlBbm24bgJBYBChUx4RW5CXGqEAFu45DgBZ4p4XAs0dMx2mD ZxFBgbLDJFQzEoTDBggsSAwn7eUKWDMCmTVRYXzQynWXX6bkfE4yB5TrSoQneCI1PUWX d/Tv9WowFtz9yRhEKBnUKWUzWtbzujF7bQ3jlnkjit/SDEDZ9DAyiLTvWkrBYp3ykOAu cdsA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712933915; x=1713538715; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=kaTU67//3yASCqk1tm8s+SMFkckNR81C9QaPFJkK7AY=; b=D36OQNyRUROHHPv4u4Ii5ueOtE3Kw0Bl6wTS9AZPPbcwodOWzw/uoxW9QKh4/hw+kn Bo7u2eUKnyAWzO0V37nO3DDO3wvf8v7iP+XwrTfV1Fm0QSFD+tn1/vLWRZjhssAeSKEa klqbfWxOJTMI5vOwBK30+lI/nhWkpcjRhPIY6dnk5hIPo2Zo8niAV3PrgzfpXfNcQYhU fgImo0/WjYN7xoZrpT01aU6eGQ8dynIxGCZSGLUX3uKmSEHmgBHfajVmAQU98OrUDUC0 ikN0YEK6O1QRmdgVaLmGJ74sYKbEfVA+7cOxE7LdYRKpnmdt8gZluskzqVAC4yz6kLEJ NQ3A== X-Gm-Message-State: AOJu0YwuDm+gAZ6Oi0q+25UPTzkVWgGfCP+z2TM88rcwXIwLwUrrTPp/ R6JuGj/+ubFmavJ9sS8oKnPNL5J3HH75xC8agtlplUNFlJOcI9b67wCCJ7tgXfzEWggEdLuJrk2 L1y6hfjE7e8h5Kpy7SKC/tSsrLbc= X-Google-Smtp-Source: AGHT+IF2jYM7mXqWFmMnaL6hsRd5GdIJ5xHkkY3Vi861Qkbw7a0gJPcEShCW2OYydkXjSB4IpNsL9P3mTcdbTlIV130= X-Received: by 2002:a17:906:6c86:b0:a52:413a:b28a with SMTP id s6-20020a1709066c8600b00a52413ab28amr271213ejr.2.1712933915103; Fri, 12 Apr 2024 07:58:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Thierry Henrio Date: Fri, 12 Apr 2024 16:58:23 +0200 Message-ID: Subject: Re: effect on planner of turning a subquery to a table, sql function returning table To: David Rowley Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000008898c60615e78161" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008898c60615e78161 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks David! Setting jit to 0: GroupAggregate (cost=3D401037.82..503755.82 rows=3D1467400 width=3D124) (= actual time=3D56.603..68.908 rows=3D4670 loops=3D1) Group Key: t.device_id, t.date, t.start_time, t.end_time, (timerange(((t_1.value ->> 0))::time without time zone, ((t_1.value ->> 1))::time without time zone)) Buffers: shared hit=3D3067 -> Sort (cost=3D401037.82..404706.32 rows=3D1467400 width=3D96) (actua= l time=3D56.583..57.847 rows=3D22908 loops=3D1) Sort Key: t.device_id, t.date, t.start_time, t.end_time, (timerange(((t_1.value ->> 0))::time without time zone, ((t_1.value ->> 1))::time without time zone)), t.rank Sort Method: quicksort Memory: 3229kB Buffers: shared hit=3D3067 -> Nested Loop (cost=3D2.99..100268.62 rows=3D1467400 width=3D96= ) (actual time=3D0.076..31.219 rows=3D22908 loops=3D1) Buffers: shared hit=3D3067 -> Nested Loop (cost=3D2.98..55962.20 rows=3D14674 width= =3D64) (actual time=3D0.055..6.589 rows=3D11454 loops=3D1) Buffers: shared hit=3D3067 -> Nested Loop (cost=3D2.54..39.31 rows=3D500 width= =3D40) (actual time=3D0.041..0.047 rows=3D1 loops=3D1) Buffers: shared hit=3D3 -> Index Scan using campaigns_pkey on campaigns c (cost=3D0.28..8.30 rows=3D1 width=3D355) (actual time=3D0.011..0.012 row= s=3D1 loops=3D1) Index Cond: (id =3D 11870) Buffers: shared hit=3D3 -> Hash Join (cost=3D2.26..26.01 rows=3D500 width=3D40) (actual time=3D0.028..0.031 rows=3D1 loops=3D1) Hash Cond: (EXTRACT(dow FROM d.date) =3D ((j.dow)::integer)::numeric) -> Function Scan on generate_series d (cost=3D0.01..10.01 rows=3D1000 width=3D8) (actual time=3D0.007..0.008 row= s=3D1 loops=3D1) -> Hash (cost=3D1.00..1.00 rows=3D100 width=3D64) (actual time=3D0.013..0.014 rows=3D7 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Function Scan on jsonb_each j (cost=3D0.00..1.00 rows=3D100 width=3D64) (actual time=3D0.007..0.008 rows= =3D7 loops=3D1) -> Index Scan using device_timeslots_date_index on device_timeslots t (cost=3D0.43..111.56 rows=3D29 width=3D32) (actual time=3D0.013..4.282 rows=3D11454 loops=3D1) Index Cond: ((date =3D d.date) AND (date >=3D '2024-04-26'::date) AND (date <=3D '2024-04-26'::date)) Buffers: shared hit=3D3064 -> Memoize (cost=3D0.01..1.01 rows=3D100 width=3D32) (actu= al time=3D0.000..0.000 rows=3D2 loops=3D11454) Cache Key: j.times Cache Mode: binary Hits: 11453 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Function Scan on jsonb_array_elements t_1 (cost=3D0.00..1.00 rows=3D100 width=3D32) (actual time=3D0.002..0.002 rows= =3D2 loops=3D1) Planning: Buffers: shared hit=3D4 Planning Time: 0.512 ms Execution Time: 69.476 ms So as far as I understand, correct me when I'm wrong: 1) EXPLAIN (ANALYZE, BUFFERS) shows the jit timing, which mere EXPLAIN did not thank! I found the https://www.postgresql.org/docs/current/jit-decision.html#JIT-DECISION 2) the subquery is overestimated, estimated 500, actual 1 in the Nested Loop. And it amplified by its outer Nested Loop -> Nested Loop (cost=3D2.99..100268.62 rows=3D*1467400* width=3D= 96) (actual time=3D0.067..27.102 rows=3D*22908* loops=3D1) Buffers: shared hit=3D3067 -> Nested Loop (cost=3D2.98..55962.20 rows=3D14674 width= =3D64) (actual time=3D0.055..5.750 rows=3D11454 loops=3D1) Buffers: shared hit=3D3067 -> Nested Loop (cost=3D2.54..39.31 rows=3D*500* width=3D40) (actual time=3D0.039..0.044 rows=3D*1* loops=3D1) The scan of the temp table was overestimated too, but it is mitigated by the Merge Join -> Merge Join (cost=3D67127.99..67631.11 rows=3D*33165* width=3D= 64) (actual time=3D7.642..13.455 rows=3D*22908* loops=3D1) Merge Cond: (z.date =3D t.date) Buffers: shared hit=3D3064, local hit=3D1 -> Sort (cost=3D78.60..81.43 rows=3D1130 width=3D40) (actu= al time=3D0.011..0.012 rows=3D2 loops=3D1) Sort Key: z.date Sort Method: quicksort Memory: 25kB Buffers: local hit=3D1 -> Seq Scan on z11870 z (cost=3D0.00..21.30 rows=3D*= 1130* width=3D40) (actual time=3D0.006..0.007 rows=3D*2* loops=3D1) Buffers: local hit=3D1 The cost estimation is better in (B) than (A) (even when the estimated rows of z11870 are off). So I have options : O1) set jit=3D0, O2) temp table, O3) rework schema desi= gn (a variation on O2). Thanks! On Fri, Apr 12, 2024 at 1:40=E2=80=AFPM David Rowley = wrote: > On Fri, 12 Apr 2024 at 23:27, Thierry Henrio > wrote: > > JIT: > > Functions: 36 > > Options: Inlining true, Optimization true, Expressions true, > Deforming true > > Timing: Generation 1.949 ms, Inlining 28.891 ms, Optimization 207.48= 1 > ms, Emission 134.907 ms, Total 373.228 ms > > Execution Time: 429.037 ms > > It looks very much like the majority of the extra time is being spent > doing JIT compilation. This triggers for plan A but not plan B. You > can see from: > > > GroupAggregate (cost=3D401037.82..503755.82 rows=3D1467400 width=3D124= ) > (actual time=3D416.851..426.534 rows=3D4670 loops=3D1) > > that the top-level row estimates are off. This makes the estimated > cost higher than it actually is. The planner opts to have tuple > deforming and expression evaluation JIT compiled to try to speed up > the plan thinking it's worthwhile. It's not in this case. > > You can switch JIT off to try without with: > > SET jit=3D0; > > You might want to consider editing postgresql.conf and raising the > jit_above_cost, jit_inline_above_cost and jit_optimize_above_cost > values to some higher value or disable JIT completely. > > SELECT pg_reload_conf(); -- to reload the config file afterwards. > > David > --0000000000008898c60615e78161 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks David!

Settin= g jit to 0:

=C2=A0GroupAggrega= te =C2=A0(cost=3D401037.82..503755.82 rows=3D1467400 width=3D124) (actual t= ime=3D56.603..68.908 rows=3D4670 loops=3D1)
=C2=A0 =C2=A0Group Key: t.de= vice_id, t.date, t.start_time, t.end_time, (timerange(((t_1.value ->>= 0))::time without time zone, ((t_1.value ->> 1))::time without time = zone))
=C2=A0 =C2=A0Buffers: shared hit=3D3067
=C2=A0 =C2=A0-> =C2= =A0Sort =C2=A0(cost=3D401037.82..404706.32 rows=3D1467400 width=3D96) (actu= al time=3D56.583..57.847 rows=3D22908 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Sort Key: t.device_id, t.date, t.start_time, t.end_time, (time= range(((t_1.value ->> 0))::time without time zone, ((t_1.value ->&= gt; 1))::time without time zone)), t.rank
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Sort Method: quicksort =C2=A0Memory: 3229kB
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Buffers: shared hit=3D3067
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Nested Loop =C2=A0(cost=3D2.99..100268.62 rows=3D1467400 wid= th=3D96) (actual time=3D0.076..31.219 rows=3D22908 loops=3D1)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D3067
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested L= oop =C2=A0(cost=3D2.98..55962.20 rows=3D14674 width=3D64) (actual time=3D0.= 055..6.589 rows=3D11454 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D3067
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&= gt; =C2=A0Nested Loop =C2=A0(cost=3D2.54..39.31 rows=3D500 width=3D40) (act= ual time=3D0.041..0.047 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffer= s: shared hit=3D3
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using c= ampaigns_pkey on campaigns c =C2=A0(cost=3D0.28..8.30 rows=3D1 width=3D355)= (actual time=3D0.011..0.012 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D 11870)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D3
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&= gt; =C2=A0Hash Join =C2=A0(cost=3D2.26..26.01 rows=3D500 width=3D40) (actua= l time=3D0.028..0.031 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Hash Cond: (EXTRACT(dow FROM d.date) =3D ((j.dow)::integer= )::numeric)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Func= tion Scan on generate_series d =C2=A0(cost=3D0.01..10.01 rows=3D1000 width= =3D8) (actual time=3D0.007..0.008 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Hash =C2=A0(cost=3D1.00..1.00 rows= =3D100 width=3D64) (actual time=3D0.013..0.014 rows=3D7 loops=3D1)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buckets: 1024= =C2=A0Batches: 1 =C2=A0Memory Usage: 9kB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Function Scan on jsonb_ea= ch j =C2=A0(cost=3D0.00..1.00 rows=3D100 width=3D64) (actual time=3D0.007..= 0.008 rows=3D7 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using device_timeslot= s_date_index on device_timeslots t =C2=A0(cost=3D0.43..111.56 rows=3D29 wid= th=3D32) (actual time=3D0.013..4.282 rows=3D11454 loops=3D1)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Index Cond: ((date =3D d.date) AND (date >=3D '2024-04-= 26'::date) AND (date <=3D '2024-04-26'::date))
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Buffers: shared hit=3D3064
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Memoize =C2=A0(cost=3D0.01..1.01 rows= =3D100 width=3D32) (actual time=3D0.000..0.000 rows=3D2 loops=3D11454)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Cache Key: j.times
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Cache Mode: binary
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Hits: 11453 =C2=A0Misse= s: 1 =C2=A0Evictions: 0 =C2=A0Overflows: 0 =C2=A0Memory Usage: 1kB
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&= gt; =C2=A0Function Scan on jsonb_array_elements t_1 =C2=A0(cost=3D0.00..1.0= 0 rows=3D100 width=3D32) (actual time=3D0.002..0.002 rows=3D2 loops=3D1)=C2=A0Planning:
=C2=A0 =C2=A0Buffers: shared hit=3D4
=C2=A0Planning = Time: 0.512 ms
=C2=A0Execution Time: 69.476 ms

So as= far as I understand, correct me when I'm wrong:

1)=C2=A0E= XPLAIN (ANALYZE, BUFFERS) shows the jit timing, which mere EXPLAIN did not = thank!
I found the https://www.postgresql.org/docs/current/jit-d= ecision.html#JIT-DECISION

2) the subquery is overest= imated, estimated 500, actual 1 in the Nested Loop.
=
And it amplified by its outer Nested Loop

=C2=A0 =C2=A0 = =C2=A0 =C2=A0=C2=A0 -> =C2=A0Nested Loop =C2=A0(cost=3D2.99..100268.62 r= ows=3D1467400 width=3D96) (actual time=3D0.067..27.102 rows=3D229= 08 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: shared hit=3D3067
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0-> =C2=A0Nested Loop =C2=A0(cost=3D2.98..55962.20 rows=3D14= 674 width=3D64) (actual time=3D0.055..5.750 rows=3D11454 loops=3D1)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Bu= ffers: shared hit=3D3067
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested Loop =C2=A0(cost=3D2.54..= 39.31 rows=3D500 width=3D40) (actual time=3D0.039..0.044 rows=3D1= loops=3D1)

The scan of the temp table was overestima= ted too, but it is mitigated by the Merge Join

=C2=A0 =C2=A0 =C2=A0 = =C2=A0=C2=A0 -> =C2=A0Merge Join =C2=A0(cost=3D67127.99..67631.11 rows= =3D33165 width=3D64) (actual time=3D7.642..13.455 rows=3D22908 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Merg= e Cond: (z.date =3D t.date)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Buffers: shared hit=3D3064, local hit=3D1
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Sort =C2=A0(cost=3D78.60.= .81.43 rows=3D1130 width=3D40) (actual time=3D0.011..0.012 rows=3D2 loops= =3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Sort Key: z.date
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Method: quicksort =C2=A0Memory: 25kB=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: local hit=3D1
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq Scan on z11870 z =C2=A0(cost= =3D0.00..21.30 rows=3D1130 width=3D40) (actual time=3D0.006..0.007 r= ows=3D2 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: local hit=3D1<= br>
The cost estimation is better in (B) than (A) (even when = the estimated rows of z11870 are off).

So I have o= ptions : O1) set jit=3D0, O2) temp table, O3) rework schema design (a varia= tion on O2).
Thanks!


On Fri, Apr 12, 2024 at 1:40=E2=80=AFPM David Rowley <dgrowleyml@gmail.com> wrote:
On Fri, 12 Apr 20= 24 at 23:27, Thierry Henrio <thierry.henrio@gmail.com> wrote:
>=C2=A0 JIT:
>=C2=A0 =C2=A0 Functions: 36
>=C2=A0 =C2=A0 Options: Inlining true, Optimization true, Expressions tr= ue, Deforming true
>=C2=A0 =C2=A0 Timing: Generation 1.949 ms, Inlining 28.891 ms, Optimiza= tion 207.481 ms, Emission 134.907 ms, Total 373.228 ms
>=C2=A0 Execution Time: 429.037 ms

It looks very much like the majority of the extra time is being spent
doing JIT compilation.=C2=A0 This triggers for plan A but not plan B.=C2=A0= You
can see from:

> GroupAggregate=C2=A0 (cost=3D401037.82..503755.82 rows=3D1467400 width= =3D124) (actual time=3D416.851..426.534 rows=3D4670 loops=3D1)

that the top-level row estimates are off.=C2=A0 This makes the estimated cost higher than it actually is.=C2=A0 The planner opts to have tuple
deforming and expression evaluation JIT compiled to try to speed up
the plan thinking it's worthwhile. It's not in this case.

You can switch JIT off to try without with:

SET jit=3D0;

You might want to consider editing postgresql.conf and raising the
jit_above_cost, jit_inline_above_cost and jit_optimize_above_cost
values to some higher value or disable JIT completely.

SELECT pg_reload_conf(); -- to reload the config file afterwards.

David
--0000000000008898c60615e78161--