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?.

select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE table_1 (
  ind int[],
  tar int[]
);

INSERT INTO table_1
  (ind, tar)
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}');
8 rows affected

select
    ind,
    tar,
    ind & tar as common_el,
    tar - (ind & tar) as  new_tar
from
    table_1


ind
tar
common_el
new_tar


{10}
{10}
{10}
{}

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

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

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

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

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

{11}
{8}
{}
{8}

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

ind	tar	common_el	new_tar
{10}	{10}	{10}	{}
{6}	{5,6}	{6}	{5}
{4,5,6}	{5,6}	{5,6}	{}
{5,6}	{5,6}	{5,6}	{}
{7,8}	{11}	{}	{11}
{11}	{5,6,7}	{}	{5,6,7}
{11}	{8}	{}	{8}
{9,10}	{6}	{}	{6}