Postgres 16

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 version();
create extension if not exists postgis;


version


PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1



CREATE EXTENSION


select ST_Distance( st_point(0  ,90,4326)
                   ,st_point(180,90,4326) );
select ST_Distance( st_point(0  ,90,4326)::geography
                   ,st_point(180,90,4326)::geography
                   ,use_spheroid=>false );
select ST_Distance( st_point(0  ,90,4326)::geography
                   ,st_point(180,90,4326)::geography
                   ,use_spheroid=>true );
with the_north_pole as (select st_point(0,90,4326) geom)
select ST_Distance( st_transform(geom,3857)
                   ,ST_Translate(st_transform(geom,3857),200,0))
from the_north_pole ;


st_distance


180



SELECT 1




st_distance


0



SELECT 1




st_distance


0



SELECT 1




st_distance


200



SELECT 1


create function rel_err(x double precision, a double precision) returns double precision
  strict immutable parallel safe language sql return ((x-a)/a);

with buildings(geom,area) as (values 
  ('polygon(( 10000 10000
             ,20000 10000
             ,20000 20000
             ,10000 20000
             ,10000 10000))'::geometry(polygon,3857),10000^2) )
,srids(srid,is_geography,is_spheroidal) as (values
  (null::int,false,false)
 ,('4326',false,false)
 ,('4326',true,false)
 ,('4326',true,true)
 ,('25832',false,false)
 ,('25833',false,false)
 ,('31467',false,false)
 ,('32631',false,false)
 ,('32632',false,false) )
,areas as (
    select coalesce( srid
                    ,st_srid(geom)) as srid
          ,is_geography
          ,is_spheroidal
          ,area
          ,st_area( st_transform( geom
                                 ,coalesce( srid
                                           ,st_srid(geom)
                                  )
                    )
           )
          ,st_astext(geom)
    from buildings cross join srids
    where not is_geography
    union
    select coalesce( srid
                    ,st_srid(geom)) as srid
          ,is_geography
          ,is_spheroidal
          ,area
          ,st_area( st_transform( geom
                                 ,coalesce( srid
                                           ,st_srid(geom)
                                  )
                    )::geography
                   ,is_spheroidal 
           )
          ,st_astext(geom)
    from buildings cross join srids
    where is_geography)
select *,rel_err(st_area,area)
from areas
order by srid;

CREATE FUNCTION




srid
is_geography
is_spheroidal
area
st_area
st_astext
rel_err


3857
f
f
100000000
100000000
POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))
0

4326
t
f
100000000
99776052.36503963
POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))
-0.0022394763496036826

4326
t
t
100000000
99330020.38191208
POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))
-0.006699796180879175

4326
f
f
100000000
0.008069680354048876
POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))
-0.9999999999193032

25832
f
f
100000000
101681805.25913559
POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))
0.01681805259135589

25833
f
f
100000000
106293525.2399652
POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))
0.062935252399652

31467
f
f
100000000
101758634.09708795
POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))
0.017586340970879494

32631
f
f
100000000
99500872.12061696
POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))
-0.004991278793830424

32632
f
f
100000000
101681805.26240759
POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))
0.01681805262407586



SELECT 9


select st_astext(st_transform('polygon(( 10000 10000
             ,20000 10000
             ,20000 20000
             ,10000 20000
             ,10000 10000))'::geometry(polygon,3857),4326));


st_astext


POLYGON((0.089831528411952 0.089831491608413,0.179663056823904 0.089831491608413,0.179663056823904 0.179662762396133,0.089831528411952 0.179662762396133,0.089831528411952 0.089831491608413))



SELECT 1


select st_transform('point(0 90)'  ::geometry(point,4326),3857)
    <->st_transform('point(180 90)'::geometry(point,4326),3857);


?column?


20037508.342789244



SELECT 1

srid	is_geography	is_spheroidal	area	st_area	st_astext	rel_err
3857	f	f	100000000	100000000	POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))	0
4326	t	f	100000000	99776052.36503963	POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))	-0.0022394763496036826
4326	t	t	100000000	99330020.38191208	POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))	-0.006699796180879175
4326	f	f	100000000	0.008069680354048876	POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))	-0.9999999999193032
25832	f	f	100000000	101681805.25913559	POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))	0.01681805259135589
25833	f	f	100000000	106293525.2399652	POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))	0.062935252399652
31467	f	f	100000000	101758634.09708795	POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))	0.017586340970879494
32631	f	f	100000000	99500872.12061696	POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))	-0.004991278793830424
32632	f	f	100000000	101681805.26240759	POLYGON((10000 10000,20000 10000,20000 20000,10000 20000,10000 10000))	0.01681805262407586