Postgres 12

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 * FROM version();


version


PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE user_
(
  user_id INTEGER NOT NULL PRIMARY KEY,
  user_name VARCHAR (25) NOT NULL  
);
INSERT INTO user_ (user_id, user_name)
SELECT 1, 'user_1'
UNION
SELECT 2, 'user_2';


2 rows affected

SELECT * FROM user_ ORDER BY user_id;


user_id
user_name


1
user_1

2
user_2


CREATE TABLE message
(
  message_id INTEGER NOT NULL PRIMARY KEY,
  sender_id INTEGER,
  message_text VARCHAR (100) NOT NULL,
  FOREIGN KEY (sender_id) REFERENCES user_ (user_id)
);
INSERT INTO message 
SELECT 1, 1, 'm1'
UNION 
SELECT 2, 1, 'm2'
UNION 
SELECT 3, 2, 'm3'
UNION 
SELECT 4, 2, 'm4';
4 rows affected

SELECT * FROM message ORDER BY message_id;


message_id
sender_id
message_text


1
1
m1

2
1
m2

3
2
m3

4
2
m4


INSERT INTO message VALUES (5, 56, 'mxxxx');
ERROR:  insert or update on table "message" violates foreign key constraint "message_sender_id_fkey"
DETAIL:  Key (sender_id)=(56) is not present in table "user_".