MIME-Version: 1.0
References: <CAMPYKo0CNfgU64VHtFe0pCP4_H9-bTXQGYKpdZLh8CcdWyGbyw@mail.gmail.com>
 <CAApHDvrds5uuAhgQ9YChVOWLY10z88+ovrFN9=162Tw0kEXgrA@mail.gmail.com> <CAMPYKo27NBuOne_2H1D3xctxnWL6c-K3d_HDrKemDZ7e_fBruQ@mail.gmail.com>
In-Reply-To: <CAMPYKo27NBuOne_2H1D3xctxnWL6c-K3d_HDrKemDZ7e_fBruQ@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 12 Apr 2024 23:39:52 +1200
Message-ID: <CAApHDvrkK+e5Eb-bSSPjfhHmoqpJZ72yLE1reD0x2SNtni9J+g@mail.gmail.com>
Subject: Re: effect on planner of turning a subquery to a table, sql function
 returning table
To: Thierry Henrio <thierry.henrio@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvrkK%2Be5Eb-bSSPjfhHmoqpJZ72yLE1reD0x2SNtni9J%2Bg%40mail.gmail.com>
Precedence: bulk

On Fri, 12 Apr 2024 at 23:27, Thierry Henrio <thierry.henrio@gmail.com> wrote:
>  JIT:
>    Functions: 36
>    Options: Inlining true, Optimization true, Expressions true, Deforming true
>    Timing: Generation 1.949 ms, Inlining 28.891 ms, Optimization 207.481 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=401037.82..503755.82 rows=1467400 width=124) (actual time=416.851..426.534 rows=4670 loops=1)

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=0;

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