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 location(
   id integer ,
   timestamp integer,
   lat real,
   lon real
);
INSERT INTO location
SELECT 1, 1460464791, 41.178237, -8.5947137 UNION ALL	
SELECT 1, 1460464983, 41.177956, -8.5953581 UNION ALL
SELECT 1, 1460464984, 41.1780008, -8.5952012 UNION ALL	
SELECT 1, 1460464985, 41.1779522, -8.595209 UNION ALL	
SELECT 1, 1460464986, 41.177981, -8.5951491;
ALTER TABLE location ADD COLUMN bearing REAL;
SELECT * FROM location;
5 rows affected



id
timestamp
lat
lon
bearing


1
1460464791
41.178238
-8.594714
null

1
1460464983
41.177956
-8.595358
null

1
1460464984
41.178
-8.5952015
null

1
1460464985
41.17795
-8.595209
null

1
1460464986
41.177982
-8.595149
null


WITH cte AS (
SELECT timestamp, atand((LEAD(lon) OVER (ORDER BY timestamp) - lon)/(LEAD(lat) OVER (ORDER BY timestamp) - lat)) bearing
FROM location
)
UPDATE location
SET bearing = cte.bearing
FROM cte
WHERE location.timestamp = cte.timestamp;
5 rows affected

SELECT * FROM location;


id
timestamp
lat
lon
bearing


1
1460464791
41.178238
-8.594714
66.32166

1
1460464983
41.177956
-8.595358
73.68615

1
1460464984
41.178
-8.5952015
8.746162

1
1460464985
41.17795
-8.595209
63.072323

1
1460464986
41.177982
-8.595149
null

id	timestamp	lat	lon	bearing
1	1460464791	41.178238	-8.594714	null
1	1460464983	41.177956	-8.595358	null
1	1460464984	41.178	-8.5952015	null
1	1460464985	41.17795	-8.595209	null
1	1460464986	41.177982	-8.595149	null