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 t(i int[], t text[]);
INSERT INTO t VALUES
   ('{1,2,3,4,5,6}'::int[], '{foo,bar,baz,bubba,ham}'::text[])
 , ('{1,2,3}'::int[], '{foo,bar,baz}'::text[])
 , ('{1,2}'::int[], '{foo,bar}'::text[])
 , ('{}'::int[], '{}'::text[]);
4 rows affected

CREATE OR REPLACE FUNCTION f_array_remove_elem_95(anyarray, int)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[1:$2-1] || $1[$2+1:2147483647]';
SELECT *
     , i[1:2] || i[4:2147483647] AS i_no3
     , t[1:2] || t[4:2147483647] AS t_no3
     , f_array_remove_elem_95(i, 3) AS i_no3_func
     , f_array_remove_elem_95(t, 3) AS t_no3_func
FROM  t;


i
t
i_no3
t_no3
i_no3_func
t_no3_func


{1,2,3,4,5,6}
{foo,bar,baz,bubba,ham}
{1,2,4,5,6}
{foo,bar,bubba,ham}
{1,2,4,5,6}
{foo,bar,bubba,ham}

{1,2,3}
{foo,bar,baz}
{1,2}
{foo,bar}
{1,2}
{foo,bar}

{1,2}
{foo,bar}
{1,2}
{foo,bar}
{1,2}
{foo,bar}

{}
{}
{}
{}
{}
{}


CREATE OR REPLACE FUNCTION f_array_remove_elem(anyarray, int)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[:$2-1] || $1[$2+1:]';
SELECT *
     , i[:2] || i[4:] AS i_no3  -- new short notation
     , t[:2] || t[4:] AS t_no3
     , f_array_remove_elem(i, 3) AS i_no3_func
     , f_array_remove_elem(t, 3) AS t_no3_func
FROM  t;


i
t
i_no3
t_no3
i_no3_func
t_no3_func


{1,2,3,4,5,6}
{foo,bar,baz,bubba,ham}
{1,2,4,5,6}
{foo,bar,bubba,ham}
{1,2,4,5,6}
{foo,bar,bubba,ham}

{1,2,3}
{foo,bar,baz}
{1,2}
{foo,bar}
{1,2}
{foo,bar}

{1,2}
{foo,bar}
{1,2}
{foo,bar}
{1,2}
{foo,bar}

{}
{}
{}
{}
{}
{}

i	t	i_no3	t_no3	i_no3_func	t_no3_func
{1,2,3,4,5,6}	{foo,bar,baz,bubba,ham}	{1,2,4,5,6}	{foo,bar,bubba,ham}	{1,2,4,5,6}	{foo,bar,bubba,ham}
{1,2,3}	{foo,bar,baz}	{1,2}	{foo,bar}	{1,2}	{foo,bar}
{1,2}	{foo,bar}	{1,2}	{foo,bar}	{1,2}	{foo,bar}
{}	{}	{}	{}	{}	{}