Postgres 9.5

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, z int[]);  --!
INSERT INTO t VALUES
  (1,NULL)
, (2,'{NULL}')
, (3,'{NULL,NULL}')
, (4,'{1,NULL}')
, (5,'{NULL,2}')
, (6,'{3,NULL,4}')
, (7,'{5,6,7}');

INSERT 0 7


SELECT id, NULLIF(z, '{NULL}') AS z
FROM   t;


id
z


1
null

2
null

3
{NULL,NULL}

4
{1,NULL}

5
{NULL,2}

6
{3,NULL,4}

7
{5,6,7}



SELECT 7


SELECT *
FROM  (
   SELECT id, NULLIF(z, '{NULL}') AS z
   FROM   t
   ) sub
WHERE z IS NULL;


id
z


1
null

2
null



SELECT 2

id	z
1	null
2	null
3	{NULL,NULL}
4	{1,NULL}
5	{NULL,2}
6	{3,NULL,4}
7	{5,6,7}