Postgres 16

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 tbl (a0 int[], a1 int[], a2 int[]);
INSERT INTO tbl VALUES  (null, '{1,2,3}','{11,22,33,44}');

CREATE TABLE



INSERT 0 1


-- AS LATERAL subquery
SELECT *
FROM   tbl t
     , unnest(t.a1, t.a2) u(elem1, elem2);  -- implicit LATERAL


a0
a1
a2
elem1
elem2


null
{1,2,3}
{11,22,33,44}
1
11

null
{1,2,3}
{11,22,33,44}
2
22

null
{1,2,3}
{11,22,33,44}
3
33

null
{1,2,3}
{11,22,33,44}
null
44



SELECT 4


-- in SELECT list directly:
SELECT *, unnest(t.a1) AS elem1, unnest(t.a2) AS elem2
FROM   tbl t


a0
a1
a2
elem1
elem2


null
{1,2,3}
{11,22,33,44}
1
11

null
{1,2,3}
{11,22,33,44}
2
22

null
{1,2,3}
{11,22,33,44}
3
33

null
{1,2,3}
{11,22,33,44}
null
44



SELECT 4


-- Note: NO ROW in SELECT eliminates the row
SELECT *, unnest(t.a0) AS elem0  -- !
FROM   tbl t;


a0
a1
a2
elem0



SELECT 0


-- To preserve all rows:
SELECT *
FROM   tbl t
LEFT   JOIN unnest(t.a0) x(elem0) ON true;  -- LATERAL is implicit here!


a0
a1
a2
elem0


null
{1,2,3}
{11,22,33,44}
null



SELECT 1


-- But the combination of multiple SRF suppresses effect if at least one has rows
SELECT *, unnest(t.a0) AS elem0  -- !
        , unnest(t.a1) AS elem1, unnest(t.a2) AS elem2
FROM   tbl t;


a0
a1
a2
elem0
elem1
elem2


null
{1,2,3}
{11,22,33,44}
null
1
11

null
{1,2,3}
{11,22,33,44}
null
2
22

null
{1,2,3}
{11,22,33,44}
null
3
33

null
{1,2,3}
{11,22,33,44}
null
null
44



SELECT 4


-- Equivalent: ROWS FROM
SELECT *
FROM   tbl t
CROSS  JOIN LATERAL ROWS FROM (unnest(t.a0), unnest(t.a1), unnest(t.a2)) x(elem0, elem1, elem2);


a0
a1
a2
elem0
elem1
elem2


null
{1,2,3}
{11,22,33,44}
null
1
11

null
{1,2,3}
{11,22,33,44}
null
2
22

null
{1,2,3}
{11,22,33,44}
null
3
33

null
{1,2,3}
{11,22,33,44}
null
null
44



SELECT 4


-- Equivalent: ROWS FROM - also eliminates row
SELECT *
FROM   tbl t
CROSS  JOIN LATERAL ROWS FROM (unnest(t.a0)) x(elem0);


a0
a1
a2
elem0



SELECT 0

a0	a1	a2	elem1	elem2
null	{1,2,3}	{11,22,33,44}	1	11
null	{1,2,3}	{11,22,33,44}	2	22
null	{1,2,3}	{11,22,33,44}	3	33
null	{1,2,3}	{11,22,33,44}	null	44