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 loop_tbl(
   pos_id SERIAL PRIMARY KEY,
   num int
);

CREATE TABLE data_tbl(
   geoname_id SERIAL PRIMARY KEY,
   num int
);

CREATE TABLE link_tbl(
   id SERIAL PRIMARY KEY,
   pos_id int,
   geoname_id int,
   distance double precision
);


INSERT INTO loop_tbl(num) VALUES(5);
INSERT INTO loop_tbl(num) VALUES(6);
INSERT INTO loop_tbl(num) VALUES(7);

INSERT INTO data_tbl(num) VALUES(2);
INSERT INTO data_tbl(num) VALUES(999);

--SELECT round( CAST(float8 '3.1415927' as numeric), 2);

select * FROM loop_tbl;
select * FROM data_tbl;

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected



pos_id
num


1
5

2
6

3
7




geoname_id
num


1
2

2
999


insert into link_tbl(pos_id,geoname_id, distance)
with single_data_row as (
   select * from data_tbl limit 1
)
select l.pos_id,d.geoname_id, l.num/d.num::decimal
from loop_tbl l cross join single_data_row d;
3 rows affected

select * from link_tbl;


id
pos_id
geoname_id
distance


1
1
1
2.5

2
2
1
3

3
3
1
3.5