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,
   geog GEOGRAPHY(Point) 
);

CREATE TABLE data_tbl(
   geoname_id SERIAL PRIMARY KEY,
   pub_name varchar,
   open bool,
   geog GEOGRAPHY(Point) 
);

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


INSERT INTO loop_tbl(num,geog) VALUES(1,'POINT(1 1)');
INSERT INTO loop_tbl(num,geog) VALUES(2, 'POINT(2 2)');
INSERT INTO loop_tbl(num,geog) VALUES(3, 'POINT(3 3)');

INSERT INTO data_tbl(pub_name,open,geog) VALUES('The Angel',true, 'POINT(1.1 1.1)');
INSERT INTO data_tbl(pub_name,open,geog) VALUES('Black Cat',false, 'POINT(2.2 2.2)');
INSERT INTO data_tbl(pub_name,open,geog) VALUES('the Ferret',false, 'POINT(5 5)');

INSERT INTO data_tbl(pub_name,open,geog) VALUES('The Pie & Eel',false, 'POINT(3.3 3.3)');
INSERT INTO data_tbl(pub_name,open,geog) VALUES('The Green Man',true, 'POINT(2 2)');

select * FROM loop_tbl;
select * FROM data_tbl;


1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected



pos_id
num
geog


1
1
0101000020E6100000000000000000F03F000000000000F03F

2
2
0101000020E610000000000000000000400000000000000040

3
3
0101000020E610000000000000000008400000000000000840




geoname_id
pub_name
open
geog


1
The Angel
t
0101000020E61000009A9999999999F13F9A9999999999F13F

2
Black Cat
f
0101000020E61000009A999999999901409A99999999990140

3
the Ferret
f
0101000020E610000000000000000014400000000000001440

4
The Pie & Eel
f
0101000020E61000006666666666660A406666666666660A40

5
The Green Man
t
0101000020E610000000000000000000400000000000000040


insert into link_tbl(pos_id,geoname_id,pub_name, distance)
with single_data_row as (
   select * from data_tbl limit 1
)
select l.pos_id,d.geoname_id,d.pub_name, l.geog <-> d.geog AS dist
from loop_tbl l cross join single_data_row d
WHERE d.open = true
ORDER BY dist
;
3 rows affected

UPDATE data_tbl SET open = false WHERE geoname_id = 1;
select * from link_tbl;
1 rows affected



id
pos_id
geoname_id
pub_name
distance


1
1
1
The Angel
15724.03777240155

2
2
1
The Angel
141501.61141908905

3
3
1
The Angel
298679.3506199334

pos_id	num	geog
1	1	0101000020E6100000000000000000F03F000000000000F03F
2	2	0101000020E610000000000000000000400000000000000040
3	3	0101000020E610000000000000000008400000000000000840

geoname_id	pub_name	open	geog
1	The Angel	t	0101000020E61000009A9999999999F13F9A9999999999F13F
2	Black Cat	f	0101000020E61000009A999999999901409A99999999990140
3	the Ferret	f	0101000020E610000000000000000014400000000000001440
4	The Pie & Eel	f	0101000020E61000006666666666660A406666666666660A40
5	The Green Man	t	0101000020E610000000000000000000400000000000000040

id	pos_id	geoname_id	pub_name	distance
1	1	1	The Angel	15724.03777240155
2	2	1	The Angel	141501.61141908905
3	3	1	The Angel	298679.3506199334