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?.

create table reached_points (
	cell_id int,
	object_id int,
	cost int,
	primary key (cell_id, object_id)
);

CREATE TABLE

insert into reached_points values (1, 2, 3);

INSERT 0 1

create table test_reached_cells (
	cell_id int,
	object_id int,
	reached_cost int,
	primary key (cell_id, object_id)
);

CREATE TABLE

insert into test_reached_cells values (1, 2, 4), (1, 3, 4);

INSERT 0 2

select * from reached_points;

cell_id
object_id
cost

1
2
3

SELECT 1

select * from test_reached_cells;

cell_id
object_id
reached_cost

1
2
4

1
3
4

SELECT 2

insert into reached_points(cell_id, object_id, cost)
select cell_id, object_id, reached_cost from test_reached_cells
on conflict(cell_id, object_id)
do update set cost = excluded.cost

INSERT 0 2

select * from reached_points;

cell_id
object_id
cost

1
2
4

1
3
4

SELECT 2