[postgis-users] Postgresql-12 taking more time to execute the query

[Srinivasa T N]

Hi All,
   Previously we were using Postgres 10 + Postgis 2.5.3.  Now we are
planning to upgrade to Postgres 12 + Postgis 3.0.1.  Previously the query
was taking 20 sec but now it is taking 45 sec.  I have pasted the query and
output of explain below:

Note : port 5434 is postgresql12  and 5433 is postgresql10

*-bash-4.2$ psql -p 5434*
psql (12.3)
Type "help" for help.

postgres=# \c IPDS_KSEB
You are now connected to database "IPDS_KSEB" as user "postgres".
IPDS_KSEB=# explain analyze select object_name, objectid, ST_AsText(shape)
as geom, gisid from kseb_geometry_trace_with_barrier_v1(453, 'htline', 2,
null, null, null, false, true);
                                                                     QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on kseb_geometry_trace_with_barrier_v1  (cost=0.25..2510.25
rows=1000 width=100) (actual time=44246.596..44247.349 rows=252 loops=1)
 Planning Time: 0.254 ms
 Execution Time: 44308.083 ms
(3 rows)

IPDS_KSEB=# \q

*-bash-4.2$ psql -p 5433*
psql (12.3, server 10.11)
Type "help" for help.

postgres=# \c IPDS_KSEB
psql (12.3, server 10.11)
You are now connected to database "IPDS_KSEB" as user "postgres".
IPDS_KSEB=# explain analyze select object_name, objectid, ST_AsText(shape)
as geom, gisid from kseb_geometry_trace_with_barrier_v1(453, 'htline', 2,
null, null, null, false, true);
                                                                     QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on kseb_geometry_trace_with_barrier_v1  (cost=0.25..1885.25
rows=1000 width=100) (actual time=19901.708..19902.453 rows=252 loops=1)
 Planning time: 0.154 ms
 Execution time: 19951.016 ms
(3 rows)

IPDS_KSEB=#


   Any suggestions on why it is taking lot of time and what has to be done
to improve?

Regards,
Seenu.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200713/b3229ba1/attachment.html>