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
(
  x INTEGER[], 
  y INTEGER[]
);
INSERT INTO t VALUES
('{10}',      '{10}'),
('{6}',       '{5,6}'),
('{4,5,6}',   '{5,6}'),
('{5,6}',     '{5,6}'),
('{7,8}',     '{11}'),
('{11}',      '{5,6,7}'),
('{11}',      '{8}'),
('{9,10}',    '{6}'),

--
-- records below added for testing!
--

('{11}',      '{5,8,10,11,133}'),  
('{9,10}',    '{4,5,6,8,9,10,11}'),
('{9,10}',    '{4,5,6,8,9,10,11}');
11 rows affected

CREATE OR REPLACE FUNCTION ARRAY_DIFF (array1 ANYARRAY, array2 ANYARRAY)
RETURNS ANYARRAY 
LANGUAGE SQL STRICT IMMUTABLE AS 
$$
  SELECT COALESCE(ARRAY_AGG(elem), '{}')
  FROM 
    UNNEST(array1) elem
  WHERE elem <> ALL (array2)
$$;
SELECT
  ROW_NUMBER() OVER (ORDER BY NULL) rn,
  x, y,
  array_diff(y, x)
FROM t
ORDER BY rn;


rn
x
y
array_diff


1
{10}
{10}
{}

2
{6}
{5,6}
{5}

3
{4,5,6}
{5,6}
{}

4
{5,6}
{5,6}
{}

5
{7,8}
{11}
{11}

6
{11}
{5,6,7}
{5,6,7}

7
{11}
{8}
{8}

8
{9,10}
{6}
{6}

9
{11}
{5,8,10,11,133}
{5,8,10,133}

10
{9,10}
{4,5,6,8,9,10,11}
{4,5,6,8,11}

11
{9,10}
{4,5,6,8,9,10,11}
{4,5,6,8,11}


UPDATE t SET y = array_diff (y, x);
11 rows affected

SELECT * FROM t;


x
y


{10}
{}

{6}
{5}

{4,5,6}
{}

{5,6}
{}

{7,8}
{11}

{11}
{5,6,7}

{11}
{8}

{9,10}
{6}

{11}
{5,8,10,133}

{9,10}
{4,5,6,8,11}

{9,10}
{4,5,6,8,11}

rn	x	y	array_diff
1	{10}	{10}	{}
2	{6}	{5,6}	{5}
3	{4,5,6}	{5,6}	{}
4	{5,6}	{5,6}	{}
5	{7,8}	{11}	{11}
6	{11}	{5,6,7}	{5,6,7}
7	{11}	{8}	{8}
8	{9,10}	{6}	{6}
9	{11}	{5,8,10,11,133}	{5,8,10,133}
10	{9,10}	{4,5,6,8,9,10,11}	{4,5,6,8,11}
11	{9,10}	{4,5,6,8,9,10,11}	{4,5,6,8,11}