[postgis-users] Optimization of ST_ContainsProperly and ST_Intersects functions
Light
wangdapeng20191008 at gmail.com
Fri Aug 11 07:22:06 PDT 2023
Hi, everyone,
I have 2 issues and need to improve their speed.
There are 3 tables in total, they use the ST_ContainsProperly function and
ST_Intersects.
1 table name gisdata containing 3 billion rows of data, fields gid and geom
and others.
There is also 1 test_line table, gid and geom, with 1 row of data.
There is also 1 test_polygon table, gid and geom, with 1 row of data.
Created SPGIST (geom).
--ST_ContainsProperly Execution Time: 143759.746 ms
select count(*) from gisdata_30 g join test_polygon po on
ST_ContainsProperly(po.geom,g.geom)='t';
Aggregate (cost=85375554.05..85375554.06 rows=1 width=8) (actual
time=143758.524..143758.526 rows=1 loops=1)
-> Nested Loop (cost=84983.55..85368054.05 rows=3000000 width=0)
(actual time=940.048..143633.927 rows=1148761 loops=1)
-> Seq Scan on bs c (cost=0.00..1.01 rows=1 width=32) (actual
time=0.002..0.004 rows=1 loops=1)
-> Bitmap Heap Scan on gisdata_30 sl (cost=84983.55..85365053.04
rows=300000 width=157) (actual time=940.042..143458.484 rows=
1148761 loops=1)
Filter: st_containsproperly(c.geom, geom)
Rows Removed by Filter: 564339
Heap Blocks: exact=1107489
-> Bitmap Index Scan on gisdata_30_geom
(cost=0.00..84908.55 rows=3000000 width=0) (actual time=551.154..551.154
rows=17
13100 loops=1)
Index Cond: (geom @ c.geom)
Planning Time: 32.232 ms
Execution Time: 143759.746 ms
--ST_Intersects 1445741.458 ms
select count(*) from gisdata_30 g join test_line li on
ST_Intersects(li.geom,g.geom)='t';
Aggregate (cost=8720103.56..8720103.57 rows=1 width=8) (actual
time=1445741.385..1445741.386 rows=1 loops=1)
-> Nested Loop (cost=0.56..8712603.56 rows=3000000 width=0) (actual
time=526.359..1445730.137 rows=76332 loops=1)
-> Seq Scan on bs_line li (cost=0.00..1.01 rows=1 width=32)
(actual time=0.354..0.357 rows=1 loops=1)
-> Index Scan using gisdata_30_geom on gisdata_30 sl
(cost=0.56..8709602.55 rows=300000 width=157) (actual time=526.000..14457
15.941 rows=76332 loops=1)
Index Cond: (geom && li.geom)
Filter: st_intersects(li.geom, geom)
Rows Removed by Filter: 7907303
Planning Time: 0.783 ms
Execution Time: 1445741.458 ms
Is there any way to make them faster?
Wangdapeng.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230811/054b73af/attachment.htm>
More information about the postgis-users
mailing list