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 t
    (id int, session_os int);
    
INSERT INTO t VALUES
    (641, 1),
    (641, 2),
    (642, NULL),
    (642, 2),
    (643, NULL);
    
SELECT * FROM t

CREATE TABLE



INSERT 0 5




id
session_os


641
1

641
2

642
null

642
2

643
null



SELECT 5


SELECT 
    *
FROM
    t
JOIN (
    SELECT unnest(ARRAY[1, null]) as a
)s ON s.a IS NOT DISTINCT FROM t.session_os


id
session_os
a


641
1
1

642
null
null

643
null
null



SELECT 3


SELECT 
    id
FROM
    t
JOIN (
    SELECT unnest(ARRAY[1, null]) as a
)s ON s.a IS NOT DISTINCT FROM t.session_os


id


641

642

643



SELECT 3


SELECT
    id,
    ARRAY_AGG(session_os)
FROM t
WHERE id IN (
    SELECT 
        id
    FROM
        t
    JOIN (
        SELECT unnest(ARRAY[1, null]) as a
    )s ON s.a IS NOT DISTINCT FROM t.session_os
)
GROUP BY id


id
array_agg


641
{1,2}

642
{NULL,2}

643
{NULL}



SELECT 3

id	session_os	a
641	1	1
642	null	null
643	null	null