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?.

create extension postgis;

create table business(
  id int generated by default as identity primary key,
  business_name text,
  x numeric,
  y numeric,
  z numeric,
  t numeric,
  geom geometry(PointZM,4326) generated always as 
      (st_setsrid(st_makepoint(x,y,z,t),4326)) stored);

select setseed(.4);
insert into business (business_name,x,y,z,t)
select 'business_'||n, x, y, z, t
from generate_series(1,1e5,1)a(n),
  lateral(select   n AS n1,
                   0 x,--180-random()*360 x,
                   0 y,--90 -random()*180 y, 
                   50 -random()*100 z,
                   50 -random()*100 t)b;

insert into business(business_name,x,y,z,t) values
  ('business_d0',0,0,0,0),
  ('business_d1',1,0,0,0),
  ('business_d2',0,1,0,0),
  ('business_d3',0,0,1,0),
  ('business_d4',0,0,0,1);

drop index if exists geom_idx;
create index geom_idx on business using gist(geom gist_geometry_ops_nd);

select * from business limit 5;

CREATE EXTENSION



CREATE TABLE




setseed






SELECT 1



INSERT 0 100000



INSERT 0 5



DROP INDEX



CREATE INDEX




id
business_name
x
y
z
t
geom


1
business_1
0
0
-17.22690410337
-49.3658619161526
01010000E0E610000000000000000000000000000000000000674D2763163A31C0195D3290D4AE48C0

2
business_2
0
0
30.0525388042646
-40.340354600701
01010000E0E6100000000000000000000000000000000000006016DE2E730D3E40E78653BD902B44C0

3
business_3
0
0
-1.6998156895498
-29.1305271004889
01010000E0E6100000000000000000000000000000000000005A81BDEF7132FBBF6FD75B396A213DC0

4
business_4
0
0
-3.1550972556847
1.69759871346233
01010000E0E610000000000000000000000000000000000000CDEB46A1A33D09C08FD6C0445D29FB3F

5
business_5
0
0
35.4744347445728
30.7788939904869
01010000E0E6100000000000000000000000000000000000005D031847BABC41403231B89865C73E40



SELECT 5


vacuum analyze business;

VACUUM


prepare postgis_based as
select id,business_name,x,y,z,t
      ,(select geom from business where id=42)<<->>geom distance 
from business
order by distance
limit 7;

explain analyze execute postgis_based;
execute postgis_based;

PREPARE




QUERY PLAN


Limit  (cost=8.59..9.45 rows=7 width=56) (actual time=0.138..0.159 rows=7 loops=1)

  InitPlan 1 (returns $0)

    ->  Index Scan using business_pkey on business business_1  (cost=0.29..8.31 rows=1 width=48) (actual time=0.007..0.007 rows=1 loops=1)

          Index Cond: (id = 42)

  ->  Index Scan using geom_idx on business  (cost=0.28..12320.38 rows=100005 width=56) (actual time=0.137..0.157 rows=7 loops=1)

        Order By: (geom <<->> $0)

Planning Time: 0.296 ms

Execution Time: 0.216 ms



EXPLAIN




id
business_name
x
y
z
t
distance


42
business_42
0
0
-10.0196083837411
-48.2957109901822
0

31487
business_31487
0
0
-10.1198442476916
-48.4923364963374
0.2207007432989113

9974
business_9974
0
0
-10.3616938756527
-48.3508951457488
0.34650797220554724

29527
business_29527
0
0
-10.5012907098008
-48.2755958509281
0.4821021489948953

2460
business_2460
0
0
-9.75377147442544
-48.7981151349026
0.5684005515362737

85808
business_85808
0
0
-9.63944638365557
-48.7459169700765
0.589244067124664

66958
business_66958
0
0
-10.0070484788601
-48.9255841764578
0.6299983984103381



SELECT 7


prepare postgis_based1 as
select id,business_name,x,y,z,t
      ,geom2<<->>geom distance 
from business,(select 'POINT(0 0 0)'::geometry(PointZ,4326)) as sample(geom2)
order by distance
limit 7;

explain analyze execute postgis_based1;
execute postgis_based1;

PREPARE




QUERY PLAN


Limit  (cost=0.28..1.14 rows=7 width=56) (actual time=1.632..1.722 rows=7 loops=1)

  ->  Index Scan using geom_idx on business  (cost=0.28..12320.38 rows=100005 width=56) (actual time=1.632..1.720 rows=7 loops=1)

        Order By: (geom <<->> '01010000A0E6100000000000000000000000000000000000000000000000000000'::geometry(PointZ,4326))

Planning Time: 0.054 ms

Execution Time: 1.736 ms



EXPLAIN




id
business_name
x
y
z
t
distance


100001
business_d0
0
0
0
0
0

100005
business_d4
0
0
0
1
0

67061
business_67061
0
0
-0.000232699105652046
-39.8002922999848
0.000232699105652046

65392
business_65392
0
0
-0.0019007474728383
38.913986390566
0.0019007474728383

8053
business_8053
0
0
0.0024517452727153
18.420606497608
0.0024517452727153

46210
business_46210
0
0
0.00258020904213652
46.3335437918857
0.00258020904213652

45040
business_45040
0
0
0.0034545808726989
44.9821911055047
0.0034545808726989



SELECT 7


prepare postgis_based2 as
select id,business_name,x,y,z,t
      ,geom2<<->>geom distance 
from business,(select 'POINT(0 0 0 0)'::geometry(PointZM,4326)) as sample(geom2)
order by distance
limit 7;

explain analyze execute postgis_based2;
execute postgis_based2;

PREPARE




QUERY PLAN


Limit  (cost=0.28..1.14 rows=7 width=56) (actual time=0.124..0.181 rows=7 loops=1)

  ->  Index Scan using geom_idx on business  (cost=0.28..12320.38 rows=100005 width=56) (actual time=0.123..0.179 rows=7 loops=1)

        Order By: (geom <<->> '01010000E0E61000000000000000000000000000000000000000000000000000000000000000000000'::geometry(PointZM,4326))

Planning Time: 0.057 ms

Execution Time: 0.194 ms



EXPLAIN




id
business_name
x
y
z
t
distance


100001
business_d0
0
0
0
0
0

18049
business_18049
0
0
0.032591229985421
-0.0790162422833731
0.08547370833506245

39427
business_39427
0
0
-0.152780772661544
0.153586720785384
0.2166352817448393

59583
business_59583
0
0
-0.0175589541282477
0.241464075804387
0.24210166619448256

3802
business_3802
0
0
-0.0101400731103567
-0.269685873945576
0.2698764378164061

82379
business_82379
0
0
-0.232590562265635
0.137020505273753
0.26994997410727567

2607
business_2607
0
0
0.0952342343135442
-0.257663234646735
0.27469965757879183



SELECT 7


prepare postgis_based3 as
select id,business_name,x,y,z,t,geom<<->>geom2 dist
from business,(select ST_Polygon( 
                  ST_GeomFromEWKT('LINESTRING(0 0 0, 0 0 9, 0 9 9,0 9 0,0 0 0,
                                              9 0 0, 9 0 9, 9 9 9,9 9 0,9 0 0,
                                              0 0 0)'), 4326)) as sample(geom2)
where geom &&& geom2
limit 7;

explain analyze execute postgis_based3;
execute postgis_based3;

PREPARE




QUERY PLAN


Limit  (cost=0.28..8.30 rows=1 width=56) (actual time=0.051..0.094 rows=7 loops=1)

  ->  Index Scan using geom_idx on business  (cost=0.28..8.30 rows=1 width=56) (actual time=0.050..0.092 rows=7 loops=1)

        Index Cond: (geom &&& '01030000A0E6100000010000000B000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002240000000000000000000000000000022400000000000002240000000000000000000000000000022400000000000000000000000000000000000000000000000000000000000000000000000000000224000000000000000000000000000000000000000000000224000000000000000000000000000002240000000000000224000000000000022400000000000002240000000000000224000000000000022400000000000000000000000000000224000000000000000000000000000000000000000000000000000000000000000000000000000000000'::geometry)

Planning Time: 0.771 ms

Execution Time: 0.128 ms



EXPLAIN




id
business_name
x
y
z
t
dist


498
business_498
0
0
0.133593046135275
-1.750900657462
0

23960
business_23960
0
0
0.274075509215123
-1.79863857987304
5.551115123125783e-17

33876
business_33876
0
0
0.11827507218414
-0.603213301265626
0

72683
business_72683
0
0
0.197884719161664
-1.80981142914469
0

86802
business_86802
0
0
0.107219710001893
-1.19615159654101
0

94419
business_94419
0
0
0.387589772227948
-1.4950770424621
0

8138
business_8138
0
0
0.41254985183032
-40.9422663309833
5.551115123125783e-17



SELECT 7

id	business_name	z	t	geom
1	business_1	-17.22690410337	-49.3658619161526	01010000E0E610000000000000000000000000000000000000674D2763163A31C0195D3290D4AE48C0
2	business_2	30.0525388042646	-40.340354600701	01010000E0E6100000000000000000000000000000000000006016DE2E730D3E40E78653BD902B44C0
3	business_3	-1.6998156895498	-29.1305271004889	01010000E0E6100000000000000000000000000000000000005A81BDEF7132FBBF6FD75B396A213DC0
4	business_4	-3.1550972556847	1.69759871346233	01010000E0E610000000000000000000000000000000000000CDEB46A1A33D09C08FD6C0445D29FB3F
5	business_5	35.4744347445728	30.7788939904869	01010000E0E6100000000000000000000000000000000000005D031847BABC41403231B89865C73E40

id	business_name	z	t	distance
42	business_42	-10.0196083837411	-48.2957109901822	0
31487	business_31487	-10.1198442476916	-48.4923364963374	0.2207007432989113
9974	business_9974	-10.3616938756527	-48.3508951457488	0.34650797220554724
29527	business_29527	-10.5012907098008	-48.2755958509281	0.4821021489948953
2460	business_2460	-9.75377147442544	-48.7981151349026	0.5684005515362737
85808	business_85808	-9.63944638365557	-48.7459169700765	0.589244067124664
66958	business_66958	-10.0070484788601	-48.9255841764578	0.6299983984103381

id	business_name	z	t	distance
100001	business_d0	0	0	0
100005	business_d4	0	1	0
67061	business_67061	-0.000232699105652046	-39.8002922999848	0.000232699105652046
65392	business_65392	-0.0019007474728383	38.913986390566	0.0019007474728383
8053	business_8053	0.0024517452727153	18.420606497608	0.0024517452727153
46210	business_46210	0.00258020904213652	46.3335437918857	0.00258020904213652
45040	business_45040	0.0034545808726989	44.9821911055047	0.0034545808726989

id	business_name	z	t	distance
100001	business_d0	0	0	0
18049	business_18049	0.032591229985421	-0.0790162422833731	0.08547370833506245
39427	business_39427	-0.152780772661544	0.153586720785384	0.2166352817448393
59583	business_59583	-0.0175589541282477	0.241464075804387	0.24210166619448256
3802	business_3802	-0.0101400731103567	-0.269685873945576	0.2698764378164061
82379	business_82379	-0.232590562265635	0.137020505273753	0.26994997410727567
2607	business_2607	0.0952342343135442	-0.257663234646735	0.27469965757879183

id	business_name	z	t	dist
498	business_498	0.133593046135275	-1.750900657462	0
23960	business_23960	0.274075509215123	-1.79863857987304	5.551115123125783e-17
33876	business_33876	0.11827507218414	-0.603213301265626	0
72683	business_72683	0.197884719161664	-1.80981142914469	0
86802	business_86802	0.107219710001893	-1.19615159654101	0
94419	business_94419	0.387589772227948	-1.4950770424621	0
8138	business_8138	0.41254985183032	-40.9422663309833	5.551115123125783e-17