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 main_table (
  id serial PRIMARY KEY,
  value int NOT NULL
);

CREATE TABLE


CREATE TABLE foreign_table (
  id serial PRIMARY KEY,
  value int NOT NULL,
  main_table_id int,
  CONSTRAINT fk_main_table_from_foreign_table
    FOREIGN KEY(main_table_id) 
    REFERENCES main_table(id)
);

CREATE TABLE


CREATE OR REPLACE FUNCTION add_foregn_table_value_to_main_table()
RETURNS TRIGGER AS
$BODY$
  BEGIN
    UPDATE main_table
    SET value = value + NEW.value
    WHERE id = NEW.main_table_id;
    RETURN NEW;
  END;
$BODY$
LANGUAGE 'plpgsql';

CREATE FUNCTION


CREATE TRIGGER add_foregn_table_value_to_main_table
AFTER INSERT
ON foreign_table
FOR EACH ROW
EXECUTE PROCEDURE add_foregn_table_value_to_main_table();

CREATE TRIGGER


INSERT INTO main_table(value) VALUES (10);

INSERT 0 1


INSERT INTO foreign_table(value, main_table_id) VALUES (10, 1);

INSERT 0 1


SELECT * FROM foreign_table;


id
value
main_table_id


1
10
1



SELECT 1


SELECT * FROM main_table;


id
value


1
20



SELECT 1