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 mytable(
   Value VARCHAR(6) NOT NULL PRIMARY KEY
);
INSERT INTO mytable(Value) VALUES ('100');
INSERT INTO mytable(Value) VALUES ('a');
INSERT INTO mytable(Value) VALUES ('aa');
INSERT INTO mytable(Value) VALUES ('bbb');
INSERT INTO mytable(Value) VALUES ('cccc');
INSERT INTO mytable(Value) VALUES ('99');
INSERT INTO mytable(Value) VALUES ('9');
INSERT INTO mytable(Value) VALUES ('1');
INSERT INTO mytable(Value) VALUES ('19');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select
*
from mytable where value < '100'


value


1


select
*
from mytable where value > '100'


value


a

aa

bbb

cccc

99

9

19


select
*
from mytable where cast(value as integer) > 100
ERROR:  invalid input syntax for integer: "a"