Re: Catching up with performance & PostgreSQL 15

Hi,

On November 30, 2022 3:47:32 AM PST, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>On 2022-11-29 Tu 16:06, David Rowley wrote:
>> On Wed, 30 Nov 2022 at 03:31, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
>>>> IMO it was a mistake to turn JIT on in the default config, so that's one
>>>> thing you'll likely want to change.
>>> I wouldn't necessarily go quite that far, but I do think that the
>>> default cost thresholds for invoking it are enormously too low,
>>> or else there are serious bugs in the cost-estimation algorithms
>>> for deciding when to use it. A nearby example[1] of a sub-1-sec
>>> partitioned query that took 30sec after JIT was enabled makes me
>>> wonder if we're accounting correctly for per-partition JIT costs.
>> I'm very grateful for JIT. However, I do agree that the costs need to work.
>>
>> The problem is that the threshold to turn JIT on does not consider how
>> many expressions need to be compiled. It's quite different to JIT
>> compile a simple one-node plan with a total cost of 100000 than to JIT
>> compile a plan that costs the same but queries 1000 partitions. I
>> think we should be compiling expressions based on the cost of the
>> individial node rather than the total cost of the plan. We need to
>> make some changes so we can more easily determine the number of times
>> a given node will be executed before we can determine how worthwhile
>> JITting an expression in a node will be.
>>
>
>I think Alvaro's point is that it would have been better to work out
>these wrinkles before turning on JIT by default. Based on anecdotal
>reports from the field I'm inclined to agree.

The problem is that back when it was introduced these problems didn't exist to a significant degree. JIT was developed when partitioning was very minimal- and the problems we're seeing are almost exclusively with queries with many partitions. The problems really only started much more recently. It also wasn't enabled in the first release..

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.