Postgres 14

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 arrs (
  col1 int,
  col2 text[]
);

INSERT INTO arrs (col1, col2) VALUES (1, '{"a", "b", "c"}');
INSERT INTO arrs (col1, col2) VALUES (2, NULL);
INSERT INTO arrs (col1, col2) VALUES (3, '{"a", "d", "d"}');

select * from arrs;

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

col1
col2

1
{a,b,c}

2
null

3
{a,d,d}

SELECT 3

-- all values
with t as (select unnest(col2) as elems
from arrs)
select array_agg(t.elems)
from t;

array_agg

{a,b,c,a,d,d}

SELECT 1

-- with distinct values
with t as (select distinct unnest(col2) as elems
from arrs)
select array_agg(t.elems)
from t;

array_agg

{a,b,c,d}

SELECT 1