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

select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


CREATE TABLE anillas(
  id_anilla serial PRIMARY KEY,
  anilla varchar(7),
  codigo int
  );
INSERT INTO anillas (anilla, codigo) VALUES
  ('A1',123),('A1',123),('A1',122),
  ('A2',321),('A2',321),
  ('A3',654),('A3',123);
SELECT * FROM anillas;
7 rows affected



id_anilla
anilla
codigo


1
A1
123

2
A1
123

3
A1
122

4
A2
321

5
A2
321

6
A3
654

7
A3
123


SELECT anilla,array_agg(distinct codigo)
  FROM anillas
  GROUP BY 1
  HAVING COUNT(DISTINCT codigo)>1;


anilla
array_agg


A1
{122,123}

A3
{123,654}