[postgis-users] Optimization of ST_ContainsProperly and ST_Intersects functions

[Light]

  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>