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, <[email protected]> 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/ > > > >
