Postgres 16

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 "transaction" (
  transaction_id uuid, 
  transaction_date timestamptz,
  mutation text);
create table new_dividends (
  transaction_id uuid, 
  transaction_date timestamptz, 
  mutation text, 
  effect text);
create table dividend (
  transaction_id uuid, 
  effect text);

insert into new_dividends values
  (gen_random_uuid(),now(),'mutation1','effect1');

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 1

table "transaction";

transaction_id
transaction_date
mutation

SELECT 0

table new_dividends;

transaction_id
transaction_date
mutation
effect

1fdf6681-bac1-48c6-8a01-a80e742acb8a
2023-10-31 10:39:29.333697+00
mutation1
effect1

SELECT 1

table dividend;

transaction_id
effect

SELECT 0

WITH inserted AS (
    INSERT INTO transaction (transaction_id, transaction_date, mutation)
    SELECT transaction_id, transaction_date, mutation FROM new_dividends
    RETURNING transaction_id
)
INSERT INTO dividend (transaction_id, effect)
SELECT transaction_id, effect FROM inserted natural join new_dividends;

INSERT 0 1

table "transaction";

transaction_id
transaction_date
mutation

1fdf6681-bac1-48c6-8a01-a80e742acb8a
2023-10-31 10:39:29.333697+00
mutation1

SELECT 1

table new_dividends;

transaction_id
transaction_date
mutation
effect

1fdf6681-bac1-48c6-8a01-a80e742acb8a
2023-10-31 10:39:29.333697+00
mutation1
effect1

SELECT 1

table dividend;

transaction_id
effect

1fdf6681-bac1-48c6-8a01-a80e742acb8a
effect1

SELECT 1