Postgres 11

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 diagnoses (
    id int,
    code varchar
);

INSERT INTO diagnoses VALUES
(1, '486'),
(2, '58281'),
(3, '5855'),
(4, '4254'),
(5, '48729'),
(6, '58381'),
(7, '5589'),
(8, '45829'),
(9, '53100'),
(10, '53081'),
(11, '48283'),
(12, '5845'),
(13, '5119');

CREATE TABLE

INSERT 0 13

 select cast(code as char(5)) as code
 from diagnoses where code between '4254' and '5855' 

code

486

58281

5855

4254

48729

58381

5589

45829

53100

53081

48283

5845

5119

SELECT 13

 select cast(code as char(5)) as code
 from diagnoses where cast(code as int) between '4254' and '5855' 

code

5855

4254

5589

5845

5119

SELECT 5