Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE T1
AS
          SELECT 1 AS ID, 	'Car' AS Type, 	234 AS Model_ID, 	5 AS Freq
UNION ALL SELECT 2 	,'SUV', 	12, 	6
UNION ALL SELECT 3 	,'EV', 	NULL ,	NULL
UNION ALL SELECT 4 	,'HV', 	NULL, 	NULL

SELECT 4


CREATE TABLE T2
AS
      SELECT 3 	AS Id,'EV' AS Type, 	NULL::INT AS Model_ID ,	NULL::INT AS Freq
UNION ALL SELECT 4 	,'HV', 	NULL, 	NULL

SELECT 2


SELECT *  
FROM T1 AS T, T2 AS N 
WHERE T.ID       IS NOT DISTINCT FROM N.ID 
  AND T.TYPE     IS NOT DISTINCT FROM N.TYPE 
  AND T.MODEL_ID IS NOT DISTINCT FROM N.MODEL_ID 
  AND T.FREQ     IS NOT DISTINCT FROM N.FREQ;


id
type
model_id
freq
id
type
model_id
freq


3
EV
null
null
3
EV
null
null

4
HV
null
null
4
HV
null
null



SELECT 2


SELECT * FROM t1
INTERSECT
SELECT * FROM T2


id
type
model_id
freq


3
EV
null
null

4
HV
null
null



SELECT 2

id	type	model_id	freq	id	type	model_id	freq
3	EV	null	null	3	EV	null	null
4	HV	null	null	4	HV	null	null