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 if not exists postgis;
select postgis_full_version();

create table my_shapes as
select row_number()over() as id, initial_seed,
     st_buffer( dp.geom
		       ,3+random()*13
		       ,format('quad_segs=%s',(1+random()*8)::int)) as geom
from (select setseed(.42)::text as initial_seed) s,
  st_dumppoints(
			st_generatepoints(
				st_expand('point(0 0)'::Geometry,500)
				,1200
				,42
			)
		)dp;

create index on my_shapes using gist(geom)with(fillfactor=100);

CREATE EXTENSION




postgis_full_version


POSTGIS="3.4.0 0874ea3" [EXTENSION] PGSQL="160" GEOS="3.11.1-CAPI-1.17.1" PROJ="9.1.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)"



SELECT 1



SELECT 1200



CREATE INDEX


vacuum analyze my_shapes;

VACUUM


--PostGIS 2.2.0+
explain analyze verbose 
create table my_shapes_clustered as
select (st_dump(collection)).geom, cluster_number
from(
  select ST_ClusterIntersecting(geom) clustered_collection 
  from my_shapes) s
,unnest(clustered_collection) with ordinality as u(collection,cluster_number);

select cluster_number,st_area(st_union(geom)),count(*)--,array_agg(id)
from my_shapes_clustered 
group by cluster_number 
order by cluster_number 
limit 8;


QUERY PLAN


Result  (cost=226.63..6633.14 rows=10000 width=40) (actual time=7.209..13.768 rows=1200 loops=1)

  Output: ((st_dump(u.collection))).geom, u.cluster_number

  ->  ProjectSet  (cost=226.63..283.14 rows=10000 width=40) (actual time=7.186..13.390 rows=1200 loops=1)

        Output: st_dump(u.collection), u.cluster_number

        ->  Nested Loop  (cost=226.63..226.84 rows=10 width=40) (actual time=7.106..7.444 rows=789 loops=1)

              Output: u.collection, u.cluster_number

              ->  Aggregate  (cost=226.62..226.63 rows=1 width=32) (actual time=6.954..6.955 rows=1 loops=1)

                    Output: st_clusterintersecting(my_shapes.geom)

                    ->  Seq Scan on public.my_shapes  (cost=0.00..76.00 rows=1200 width=378) (actual time=0.005..0.144 rows=1200 loops=1)

                          Output: my_shapes.id, my_shapes.initial_seed, my_shapes.geom

              ->  Function Scan on pg_catalog.unnest u  (cost=0.00..0.10 rows=10 width=40) (actual time=0.146..0.354 rows=789 loops=1)

                    Output: u.collection, u.cluster_number

                    Function Call: unnest((st_clusterintersecting(my_shapes.geom)))

Planning Time: 0.247 ms

Execution Time: 16.364 ms



EXPLAIN




cluster_number
st_area
count


1
836.248837776196
2

2
651.4061064388445
1

3
53.56050595625381
1

4
269.4408305746047
1

5
1596.5821681225993
4

6
1262.1100035153077
3

7
2008.7632476757103
4

8
66.289777695502
1



SELECT 8


--PostGIS 3.4.0
explain analyze verbose 
create table my_shapes_clustered2 as
select 1+ST_ClusterIntersectingWin(geom)over() as cluster_number, *
from my_shapes;

select cluster_number,st_area(st_union(geom)),count(*),array_agg(id)
from my_shapes_clustered2 
group by cluster_number 
order by cluster_number 
limit 8;


QUERY PLAN


WindowAgg  (cost=0.00..15091.00 rows=1200 width=391) (actual time=5.639..5.948 rows=1200 loops=1)

  Output: (1 + st_clusterintersectingwin(geom) OVER (?)), id, initial_seed, geom

  ->  Seq Scan on public.my_shapes  (cost=0.00..76.00 rows=1200 width=387) (actual time=0.006..0.215 rows=1200 loops=1)

        Output: geom, id, initial_seed

Planning Time: 0.063 ms

Execution Time: 8.455 ms



EXPLAIN




cluster_number
st_area
count
array_agg


1
836.248837776196
2
{798,1}

2
651.4061064388445
1
{2}

3
53.56050595625381
1
{3}

4
269.4408305746047
1
{4}

5
1596.5821681225993
4
{507,5,676,72}

6
1262.1100035153077
3
{186,296,6}

7
2008.7632476757103
4
{474,891,7,1121}

8
66.289777695502
1
{8}



SELECT 8


--PostGIS 2.3.0
explain analyze verbose 
create table my_shapes_clustered3 as
select 1+ST_ClusterDBSCAN(geom,0,1)over() as cluster_number, *
from my_shapes;

select cluster_number,st_area(st_union(geom)),count(*),array_agg(id)
from my_shapes_clustered3 
group by cluster_number 
order by cluster_number 
limit 8;


QUERY PLAN


WindowAgg  (cost=0.00..15091.00 rows=1200 width=391) (actual time=17.688..18.018 rows=1200 loops=1)

  Output: (1 + st_clusterdbscan(geom, '0'::double precision, 1) OVER (?)), id, initial_seed, geom

  ->  Seq Scan on public.my_shapes  (cost=0.00..76.00 rows=1200 width=387) (actual time=0.005..0.218 rows=1200 loops=1)

        Output: geom, id, initial_seed

Planning Time: 0.041 ms

Execution Time: 21.007 ms



EXPLAIN




cluster_number
st_area
count
array_agg


1
836.248837776196
2
{798,1}

2
651.4061064388445
1
{2}

3
53.56050595625381
1
{3}

4
269.4408305746047
1
{4}

5
1596.5821681225993
4
{507,5,676,72}

6
1262.1100035153077
3
{186,296,6}

7
2008.7632476757103
4
{474,891,7,1121}

8
66.289777695502
1
{8}



SELECT 8

cluster_number	st_area	count
1	836.248837776196	2
2	651.4061064388445	1
3	53.56050595625381	1
4	269.4408305746047	1
5	1596.5821681225993	4
6	1262.1100035153077	3
7	2008.7632476757103	4
8	66.289777695502	1

cluster_number	st_area	count	array_agg
1	836.248837776196	2	{798,1}
2	651.4061064388445	1	{2}
3	53.56050595625381	1	{3}
4	269.4408305746047	1	{4}
5	1596.5821681225993	4	{507,5,676,72}
6	1262.1100035153077	3	{186,296,6}
7	2008.7632476757103	4	{474,891,7,1121}
8	66.289777695502	1	{8}