[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    ST_Slope query is slow.
From:       Dapeng Wang <wangdapeng20191008 () gmail ! com>
Date:       2024-04-09 6:42:04
Message-ID: CAKx0Yhzv87UXCYDmku26ToyyWVOzdtO-BVV30Wbgos5gU6mhCA () mail ! gmail ! com
[Download RAW message or body]

Hello,

-The computation of slope (in degrees) is relatively slow, taking about 20
seconds for analysis using ArcGIS Pro.

version:pg12+postgis340

--postgis_full_version
POSTGIS="3.4.0 0" [EXTENSION] PGSQL="120" GEOS="3.12.0-CAPI-1.18.0"
SFCGAL="SFCGAL 1.4.1, CGAL 5.4.5, BOOST 1.72.0" PRO
J="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
USER_WRITABLE_DIRECTORY=/home/postgres/.local/share/proj
DATABASE_PATH=/usr/local/proj-8.2.1/share/proj/proj.db" GDAL="GDAL 3.6.2,
released 2023/01/02" LIBXML="2.9.11" LIBJSON="
0.15" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER

raster2pgsql -I -s 4326 -t 32x32 "/opt/raster_data/changjiang12.tif" | psql
-h localhost -p5432 -Upostgres -dpostgis340
-After importing, the TIFF file is 14MB and 12198 rows.

postgis340=# \d changjiang12
                             Table "public.changjiang12"
 Column |  Type   | Collation | Nullable |                  Default
--------+---------+-----------+----------+-------------------------------------------
 rid    | integer |           | not null |
nextval('changjiang12_rid_seq'::regclass)
 rast   | raster  |           |          |
Indexes:
    "changjiang12_pkey" PRIMARY KEY, btree (rid)
    "changjiang12_st_convexhull_idx" gist (st_convexhull(rast))

postgis340=# EXPLAIN ANALYSE select ST_Slope(rast) as slope from
changjiang12;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
  Seq Scan on changjiang12  (cost=0.00..4894.48 rows=12198 width=32) (actual
time=22.371..142759.655 rows=12198 loops=1)
 Planning Time: 0.836 ms
 Execution Time: 142764.987 ms
(3 rows)
Time: 142770.199 ms (02:22.770)

-Where can improvements be made?

Thanks.


[Attachment #3 (text/html)]

<div dir="ltr"><div>Hello,</div><div><br></div><div>-The computation of slope (in \
degrees) is relatively slow, taking about 20 seconds for analysis using ArcGIS \
Pro.<br></div><div><br></div>version:pg12+postgis340<br><br>--postgis_full_version<br>POSTGIS=&quot;3.4.0 \
0&quot; [EXTENSION] PGSQL=&quot;120&quot; GEOS=&quot;3.12.0-CAPI-1.18.0&quot; \
SFCGAL=&quot;SFCGAL 1.4.1, CGAL 5.4.5, BOOST 1.72.0&quot; PRO<br>J=&quot;8.2.1 \
NETWORK_ENABLED=OFF URL_ENDPOINT=<a \
href="https://cdn.proj.org">https://cdn.proj.org</a> \
USER_WRITABLE_DIRECTORY=/home/postgres/.local/share/proj<br>DATABASE_PATH=/usr/local/proj-8.2.1/share/proj/proj.db&quot; \
GDAL=&quot;GDAL 3.6.2, released 2023/01/02&quot; LIBXML=&quot;2.9.11&quot; \
LIBJSON=&quot;<br>0.15&quot; LIBPROTOBUF=&quot;1.4.1&quot; WAGYU=&quot;0.5.0 \
(Internal)&quot; TOPOLOGY RASTER<br><br>raster2pgsql -I -s 4326 -t 32x32 \
&quot;/opt/raster_data/changjiang12.tif&quot; | psql -h localhost -p5432 -Upostgres \
-dpostgis340<br>-After importing, the TIFF file is 14MB and 12198 \
rows.<br><br>postgis340=# \d changjiang12<br>                                         \
Table &quot;public.changjiang12&quot;<br>  Column |   Type    | Collation | Nullable \
|                           \
Default<br>--------+---------+-----------+----------+-------------------------------------------<br> \
rid      | integer |                | not null | \
nextval(&#39;changjiang12_rid_seq&#39;::regclass)<br>  rast    | raster   |           \
|               |<br>Indexes:<br>      &quot;changjiang12_pkey&quot; PRIMARY KEY, \
btree (rid)<br>      &quot;changjiang12_st_convexhull_idx&quot; gist \
(st_convexhull(rast))<br><br>postgis340=# EXPLAIN ANALYSE select ST_Slope(rast) as \
slope from changjiang12;<br>                                                          \
QUERY PLAN<br>------------------------------------------------------------------------------------------------------------------------<br> \
Seq Scan on changjiang12   (cost=0.00..4894.48 rows=12198 width=32) (actual \
time=22.371..142759.655 rows=12198 loops=1)<br>  Planning Time: 0.836 ms<br>  \
Execution Time: 142764.987 ms<br>(3 rows)<br>Time: 142770.199 ms \
(02:22.770)<br><br>-Where can improvements be \
made?<br><br>Thanks.<br><br><br><br><br><br></div>



[prev in list] [next in list] [prev in thread] [next in thread]