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 unsigned (i OID);
CREATE TABLE signed (i INTEGER);

CREATE TABLE



CREATE TABLE


INSERT INTO unsigned VALUES (0), (1), (2), (2147483647), (2147483648), (2147483649), (4294967294), (4294967295);
INSERT INTO signed VALUES (-2147483648), (-2147483647), (-2), (-1), (0), (1), (2147483646), (2147483647);

INSERT 0 8



INSERT 0 8


SELECT * FROM (
  SELECT i AS unsigned, i::INTEGER AS signed, 'stored as oid' AS format FROM unsigned
  UNION ALL
  SELECT i::OID, i, 'stored as int' FROM signed
) AS x
ORDER BY unsigned;


unsigned
signed
format


0
0
stored as int

0
0
stored as oid

1
1
stored as int

1
1
stored as oid

2
2
stored as oid

2147483646
2147483646
stored as int

2147483647
2147483647
stored as int

2147483647
2147483647
stored as oid

2147483648
-2147483648
stored as int

2147483648
-2147483648
stored as oid

2147483649
-2147483647
stored as oid

2147483649
-2147483647
stored as int

4294967294
-2
stored as int

4294967294
-2
stored as oid

4294967295
-1
stored as int

4294967295
-1
stored as oid



SELECT 16

unsigned	signed	format
0	0	stored as int
0	0	stored as oid
1	1	stored as int
1	1	stored as oid
2	2	stored as oid
2147483646	2147483646	stored as int
2147483647	2147483647	stored as int
2147483647	2147483647	stored as oid
2147483648	-2147483648	stored as int
2147483648	-2147483648	stored as oid
2147483649	-2147483647	stored as oid
2147483649	-2147483647	stored as int
4294967294	-2	stored as int
4294967294	-2	stored as oid
4294967295	-1	stored as int
4294967295	-1	stored as oid