Look into IS DISTINCT FROM as an alternative to nullability semantics compensation

[roji]

Following discussion in dotnet/efcore#29624

Note that we used to do this a long time ago (2016), but at that point IS DISTINCT FROM did not use indexes, so we moved away from that (see #28). We should check what the situation is in modern PostgreSQL.

[NinoFloris]

Seems PG still has indexing deficiencies here (tested on PG 16)

CREATE TABLE numbers AS SELECT x FROM generate_series(1,1000000) x;
CREATE INDEX numbers_x_idx ON numbers (x);
ANALYZE numbers;

EXPLAIN SELECT * FROM numbers WHERE x IS NOT DISTINCT FROM 500; -- bad
EXPLAIN SELECT * FROM numbers WHERE x IN (500, null); -- good 
EXPLAIN	SELECT * FROM numbers WHERE x = 500; -- good

Some context from 2014 https://www.postgresql.org/message-id/6FC83909-5DB1-420F-9191-DBE533A3CEDE%40excoventures.com

[roji]

Thanks for checking @NinoFloris, bummer.