Postgres 15

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?.
SELECT ST_DistanceSphere('POINT(0 0)'::geometry,
                         'POINT(180 0)'::geometry) -- half_the_circumference
       /pi() -- 2*pi*r is the circumference, so half the circumference/pi is the radius
       AS PostgreSQL_assumed_radius;

postgresql_assumed_radius

6371008.771415061

SELECT 1

SELECT ST_DistanceSphere(ST_SetSRID('POINT(0 0)'::geometry,4326),
                         ST_SetSRID('POINT(180 0)'::geometry,4326)) -- half_the_circumference
       /pi() -- 2*pi*r is the circumference, so half the circumference/pi is the radius
       AS PostgreSQL_assumed_radius_for_4326;

postgresql_assumed_radius_for_4326

6371008.771415061

SELECT 1

SELECT ST_DistanceSphere(
  st_geomfromtext('POINT(0 -90)'), 
  st_geomfromtext('POINT(0 90)')
) AS latitudinal_distance_pole_to_pole;

SELECT ST_DistanceSphere(
  st_geomfromtext('POINT(0 0)'), 
  st_geomfromtext('POINT(180 0)')
) AS longitudinal_around_half_the_globe;

latitudinal_distance_pole_to_pole

20015114.35223369

SELECT 1

longitudinal_around_half_the_globe

20015114.35223369

SELECT 1

SELECT ST_DistanceSphere(
  st_geomfromtext('POINT(-74.0060 40.7128)'), -- New York
  st_geomfromtext('POINT(-118.2437 34.0522)') -- Los Angeles
) AS distance;

distance

3935751.67323537

SELECT 1

SELECT ST_DistanceSphere(
  st_setsrid(st_geomfromtext('POINT(-74.0060 40.7128)'),4326), -- New York
  st_setsrid(st_geomfromtext('POINT(-118.2437 34.0522)'),4326) -- Los Angeles
) AS distance;

distance

3935751.67323537

SELECT 1

SELECT ST_DistanceSphere(
  st_geomfromtext('POINT(-74.0060 40.7128)'), -- New York
  st_geomfromtext('POINT(-118.2437 34.0522)'), -- Los Angeles
  6378100 --custom radius, conforming to MongoDB's config
) AS distance;

distance

3940132.347594769

SELECT 1

select * from spatial_ref_sys where srid=4326;--6378137

srid
auth_name
auth_srid
srtext
proj4text

4326
EPSG
4326
GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]
+proj=longlat +datum=WGS84 +no_defs

SELECT 1