Re: COALESCE patch

Hello all,

Thanks for letting me know that it was handled already.

May the patches we have fixes the problem.

With Regards,
Osama Abdul Qader

On Mon, 7 Sept, 2026, 5:53 pm prankware, <esavelievcode(at)gmail(dot)com> wrote:

> Thanks for the review, and for the clear repro — you're right on both the
> diagnosis and the fix.
> The context was inferred from fcinfo (fn_oid == F_EQJOINSEL), and that
> breaks on recursion: try_coalesce_eq() calls eqjoinsel() through
> DirectFunctionCall, which leaves fcinfo->flinfo NULL. So when a branch is
> itself a COALESCE, the nested eqjoinsel() saw is_eqjoin = false and
> estimated the join as a restriction, which is where the ~6x came from.
> v6 (attached) passes is_eqjoin explicitly, as you suggested:
> eqsel_internal() passes false and eqjoinsel() passes true, and the fcinfo
> check is gone. On your example the estimate drops from ~66M to ~7.8M
> (actual 10M), and the earlier cases are unchanged.
> Feedback is welcome.
>
> Osama Abdul Qader, thanks for the offer, but it's already handled — v6
> (just posted to the thread) passes is_eqjoin explicitly and removes the
> F_EQJOINSEL check, so no extra work is needed here.
>
> Regards, Egor Savelev, Tantor Labs LLC, https://tantorlabs.com
>
>
>
> пн, 7 сент. 2026 г. в 14:50, Osama Abdul Qader <
> osamaabdulqader(dot)cs(at)gmail(dot)com>:
>
>> Hi Ilia,
>>
>> Thanks for pointing this out. I understand the issue with relying on
>> F_EQJOINSEL to determine the equality-join context, especially since
>> PostgreSQL has multiple join selectivity estimators.
>>
>> Is this issue still open? If so, I’d be happy to take a look at
>> implementing the explicit is_eqjoin propagation you suggested and add
>> the necessary regression tests.
>>
>> With Regards,
>>
>> Osama Abdul Qader
>>
>> On Mon, 7 Sept, 2026, 4:34 pm Ilia Evdokimov, <
>> ilya(dot)evdokimov(at)tantorlabs(dot)com> wrote:
>>
>>> While reviewing try_coalesce_eq() I noticed this
>>>
>>> + bool is_eqjoin = (!fcinfo->flinfo != NULL && fcinfo->flinfo->fn_oid ==
>>> F_EQJOINSEL)
>>>
>>> This is checking against one specific selectivity function, but
>>> eqjoinsel() is not the only join-selectivity estimation - it's just the
>>> most common one (used by = operators). pg_proc.dat alone registers over
>>> a dozen others as JOIN estimators. We need a different mechanism. The
>>> only way I see is to stop interfering the context and pass it in
>>> explicitly give try_coalesce_eq() a bool is_eqjoin parameter.
>>>
>>> For example, consider this scenario:
>>>
>>> ```
>>> CREATE TABLE a (x1 int, x2 int, y int);
>>> CREATE TABLE b (w int);
>>>
>>> INSERT INTO a (x1, x2, y)
>>> SELECT
>>> CASE WHEN i % 3 = 0 THEN NULL ELSE i % 1000 END,
>>> CASE WHEN i % 3 = 0 THEN i % 500 ELSE NULL END,
>>> i % 200
>>> FROM generate_series(1, 100000) i;
>>>
>>> INSERT INTO b (w)
>>> SELECT i % 1000
>>> FROM generate_series(1, 100000) i;
>>>
>>> CREATE INDEX a_coalesce_x1x2_idx ON a (COALESCE(x1, x2));
>>>
>>> ANALYZE a, b;
>>>
>>> EXPLAIN ANALYZE
>>> SELECT * FROM a JOIN b ON COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w;
>>> QUERY PLAN
>>>
>>> -----------------------------------------------------------------------------------------------------------------------
>>> Hash Join (cost=2693.00..688019.33 rows=66488333 width=16) (actual
>>> time=11.824..343.641 rows=10000000.00 loops=1)
>>> Hash Cond: (COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w)
>>> Buffers: shared read=886
>>> -> Seq Scan on a (cost=0.00..1443.00 rows=100000 width=12) (actual
>>> time=0.355..2.294 rows=100000.00 loops=1)
>>> Buffers: shared read=443
>>> -> Hash (cost=1443.00..1443.00 rows=100000 width=4) (actual
>>> time=10.691..10.693 rows=100000.00 loops=1)
>>> Buckets: 131072 Batches: 1 Memory Usage: 4540kB
>>> Buffers: shared read=443
>>> -> Seq Scan on b (cost=0.00..1443.00 rows=100000 width=4)
>>> (actual time=0.243..3.641 rows=100000.00 loops=1)
>>> Buffers: shared read=443
>>> Planning:
>>> Buffers: shared hit=139 read=33
>>> Planning Time: 1.712 ms
>>> Execution Time: 431.061 ms
>>> (14 rows)
>>> ```
>>>
>>> Estimated rows are 6 times bigger than actual ones.
>>>
>>> --
>>> Best regards,
>>> Ilia Evdokimov,
>>> Tantor Labs LLC,
>>> https://tantorlabs.com/
>>>
>>>
>>>
>>>