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,180-random()*360 x,
                   90-random()*180 y, 
                   50-random()*100 z,
                   50-random()*100 t)b(n1,x,y,z,t);

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
-62.0168547721321
-88.8585514490748
30.0525388042646
-40.340354600701
01010000E0E6100000618B134C28024FC06BEDC681F23656C06016DE2E730D3E40E78653BD902B44C0

2
business_2
-6.11933648237928
-52.4349487808799
-3.1550972556847
1.69759871346233
01010000E0E61000009E27C457337A18C039A8D266AC374AC0CDEB46A1A33D09C08FD6C0445D29FB3F

3
business_3
127.707965080462
55.4020091828764
-39.4429369634399
-3.49758405111016
01010000E0E6100000D6D2C44C4FED5F40AA92720975B34B40FB148E28B2B843C0113CD4580DFB0BC0

4
business_4
-179.751397185458
25.0435992428371
24.0368039454993
-17.0437100269781
01010000E0E6100000273B1C720B7866C09F1DEA51290B39408448BEFB6B093840D3609094300B31C0

5
business_5
-18.5088616346001
-51.5049251128779
12.5346636750161
39.9121963579516
01010000E0E6100000E9CB8EC1448232C0AE5ED762A1C049C04D1BED6FBF1129405A77AAD9C2F44340



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.53 rows=7 width=73) (actual time=0.428..1.624 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.008..0.008 rows=1 loops=1)

          Index Cond: (id = 42)

  ->  Index Scan using geom_idx on business  (cost=0.28..13468.38 rows=100005 width=73) (actual time=0.427..1.622 rows=7 loops=1)

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

Planning Time: 0.351 ms

Execution Time: 1.689 ms



EXPLAIN




id
business_name
x
y
z
t
distance


42
business_42
-91.8261216469166
7.7997970642875
2.93153014852416
13.8754985934873
0

39781
business_39781
-91.651074810554
10.1567444370512
1.08794511732091
19.9058199716022
6.734198096546056

10212
business_10212
-96.0308356409243
6.16780328827525
6.99214194619289
17.9070448007494
7.285942385786669

78354
business_78354
-94.2913320527778
12.8802423990292
5.95771554931872
17.565112372697
7.393188423419308

82772
business_82772
-90.3963886636943
13.9114309111029
-2.37759157752899
15.9845495479146
8.487112236283533

47097
business_47097
-93.041286078567
14.2177864450063
-1.89386518525991
11.233109282625
8.540133118659915

94516
business_94516
-87.3058458168426
1.38796268779309
1.78446589911467
18.3034143744421
9.08111822448362



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.22 rows=7 width=73) (actual time=0.393..1.582 rows=7 loops=1)

  ->  Index Scan using geom_idx on business  (cost=0.28..13468.38 rows=100005 width=73) (actual time=0.392..1.579 rows=7 loops=1)

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

Planning Time: 0.060 ms

Execution Time: 1.597 ms



EXPLAIN




id
business_name
x
y
z
t
distance


100005
business_d4
0
0
0
1
0

100001
business_d0
0
0
0
0
0

9025
business_9025
0.117328427947513
-0.142229236110069
0.371706852253226
9.13287847495934
0.41492300444912683

100002
business_d1
1
0
0
0
1

100003
business_d2
0
1
0
0
1

100004
business_d3
0
0
1
0
1

22961
business_22961
-1.03067863356961
-0.145105533783706
0.176508027894194
-18.6492336297432
1.0557031521894167



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.22 rows=7 width=73) (actual time=0.415..1.333 rows=7 loops=1)

  ->  Index Scan using geom_idx on business  (cost=0.28..13468.38 rows=100005 width=73) (actual time=0.414..1.330 rows=7 loops=1)

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

Planning Time: 0.094 ms

Execution Time: 1.353 ms



EXPLAIN




id
business_name
x
y
z
t
distance


100001
business_d0
0
0
0
0
0

100002
business_d1
1
0
0
0
1

100003
business_d2
0
1
0
0
1

100005
business_d4
0
0
0
1
1

100004
business_d3
0
0
1
0
1

79588
business_79588
-3.58536256009714
-3.76835959560049
2.51733332252828
0.853287066842292
5.841269091922765

29792
business_29792
-0.0632122348617088
0.434635336447897
5.68814681431253
1.93095946111732
6.02299944246865



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=73) (actual time=0.079..0.139 rows=7 loops=1)

  ->  Index Scan using geom_idx on business  (cost=0.28..8.30 rows=1 width=73) (actual time=0.079..0.137 rows=7 loops=1)

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

Planning Time: 1.063 ms

Execution Time: 0.193 ms



EXPLAIN




id
business_name
x
y
z
t
dist


28244
business_28244
5.72116919812677
7.3992692748329
4.78124402413529
-33.7814034873472
3.6487080291257397

38432
business_38432
3.82661331655294
8.13126882409394
4.60934410856502
-44.3746473086066
3.9239856435022054

46457
business_46457
5.47325365857458
4.13355651284742
6.58945930219546
44.4355780706455
4.27184342088162

100001
business_d0
0
0
0
0
0

100002
business_d1
1
0
0
0
0

100003
business_d2
0
1
0
0
0

100005
business_d4
0
0
0
1
0



SELECT 7

id	business_name	x	y	z	t	geom
1	business_1	-62.0168547721321	-88.8585514490748	30.0525388042646	-40.340354600701	01010000E0E6100000618B134C28024FC06BEDC681F23656C06016DE2E730D3E40E78653BD902B44C0
2	business_2	-6.11933648237928	-52.4349487808799	-3.1550972556847	1.69759871346233	01010000E0E61000009E27C457337A18C039A8D266AC374AC0CDEB46A1A33D09C08FD6C0445D29FB3F
3	business_3	127.707965080462	55.4020091828764	-39.4429369634399	-3.49758405111016	01010000E0E6100000D6D2C44C4FED5F40AA92720975B34B40FB148E28B2B843C0113CD4580DFB0BC0
4	business_4	-179.751397185458	25.0435992428371	24.0368039454993	-17.0437100269781	01010000E0E6100000273B1C720B7866C09F1DEA51290B39408448BEFB6B093840D3609094300B31C0
5	business_5	-18.5088616346001	-51.5049251128779	12.5346636750161	39.9121963579516	01010000E0E6100000E9CB8EC1448232C0AE5ED762A1C049C04D1BED6FBF1129405A77AAD9C2F44340

id	business_name	x	y	z	t	distance
42	business_42	-91.8261216469166	7.7997970642875	2.93153014852416	13.8754985934873	0
39781	business_39781	-91.651074810554	10.1567444370512	1.08794511732091	19.9058199716022	6.734198096546056
10212	business_10212	-96.0308356409243	6.16780328827525	6.99214194619289	17.9070448007494	7.285942385786669
78354	business_78354	-94.2913320527778	12.8802423990292	5.95771554931872	17.565112372697	7.393188423419308
82772	business_82772	-90.3963886636943	13.9114309111029	-2.37759157752899	15.9845495479146	8.487112236283533
47097	business_47097	-93.041286078567	14.2177864450063	-1.89386518525991	11.233109282625	8.540133118659915
94516	business_94516	-87.3058458168426	1.38796268779309	1.78446589911467	18.3034143744421	9.08111822448362

id	business_name	x	y	z	t	distance
100005	business_d4	0	0	0	1	0
100001	business_d0	0	0	0	0	0
9025	business_9025	0.117328427947513	-0.142229236110069	0.371706852253226	9.13287847495934	0.41492300444912683
100002	business_d1	1	0	0	0	1
100003	business_d2	0	1	0	0	1
100004	business_d3	0	0	1	0	1
22961	business_22961	-1.03067863356961	-0.145105533783706	0.176508027894194	-18.6492336297432	1.0557031521894167

id	business_name	x	y	z	t	dist
28244	business_28244	5.72116919812677	7.3992692748329	4.78124402413529	-33.7814034873472	3.6487080291257397
38432	business_38432	3.82661331655294	8.13126882409394	4.60934410856502	-44.3746473086066	3.9239856435022054
46457	business_46457	5.47325365857458	4.13355651284742	6.58945930219546	44.4355780706455	4.27184342088162
100001	business_d0	0	0	0	0	0
100002	business_d1	1	0	0	0	0
100003	business_d2	0	1	0	0	0
100005	business_d4	0	0	0	1	0