Re: COALESCE patch

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/
>
>
>
>