Postgres 13

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 13.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

CREATE TABLE other_table
(
  somekey SERIAL   NOT NULL PRIMARY KEY,
  other_stuff TEXT NOT NULL
  
);

CREATE TABLE

INSERT INTO other_table (other_stuff) VALUES 
('otos_1'),
('otos_2'),
('otos_3'),
('otos_4'),
('otos_5'),
('otos_6');

INSERT 0 6

SELECT * FROM other_table;

somekey
other_stuff

1
otos_1

2
otos_2

3
otos_3

4
otos_4

5
otos_5

6
otos_6

SELECT 6

CREATE TABLE my_table
(
  id SERIAL NOT NULL PRIMARY KEY,
  some_row INTEGER NOT NULL,
  some_other_row TEXT NOT NULL,
  
  CONSTRAINT mt_ot_id_fk FOREIGN KEY (id) REFERENCES other_table (somekey)
);

INSERT INTO my_table (some_row, some_other_row)
VALUES
(1000, 'sor_100'),
(2000, 'sor_200'),
(3000, 'sor_300');

CREATE TABLE

INSERT 0 3

SELECT * FROM my_table;

id
some_row
some_other_row

1
1000
sor_100

2
2000
sor_200

3
3000
sor_300

SELECT 3

SHOW TRANSACTION ISOLATION LEVEL;

transaction_isolation

read committed

SHOW

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET

SHOW TRANSACTION ISOLATION LEVEL;

transaction_isolation

read committed

SHOW

BEGIN TRANSACTION;
WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    4000,
    'sor_400'
  ) RETURNING id
)
(SELECT id FROM new_row);
SELECT MAX(id) FROM my_table;

COMMIT;
SELECT MAX(id) FROM my_table;

BEGIN

id

4

SELECT 1

max

4

SELECT 1

COMMIT

max

4

SELECT 1

SELECT * FROM my_table;

id
some_row
some_other_row

1
1000
sor_100

2
2000
sor_200

3
3000
sor_300

4
4000
sor_400

SELECT 4

WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    4000,
    'sor_400'
  ) RETURNING *
)
SELECT
   mt.id AS mt_id, mt.some_row AS mt_sr, mt.some_other_row AS mt_sor,
   ot.somekey AS ot_sk, ot.other_stuff AS ot_os
FROM new_row mt
LEFT JOIN other_table ot
  ON ot.somekey = mt.id
;

mt_id
mt_sr
mt_sor
ot_sk
ot_os

5
4000
sor_400
5
otos_5

SELECT 1

SELECT * FROM my_table;

id
some_row
some_other_row

1
1000
sor_100

2
2000
sor_200

3
3000
sor_300

4
4000
sor_400

5
4000
sor_400

SELECT 5

id	some_row	some_other_row
1	1000	sor_100
2	2000	sor_200
3	3000	sor_300
4	4000	sor_400
5	4000	sor_400