Postgres 9.6

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

INSERT 0 1


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


a1
a2
elem1
elem2


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

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

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

{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


a1
a2
elem1
elem2


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

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

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

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

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

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

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

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

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

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

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

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



SELECT 12

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