Hi,
Below is the output of the query explain and analyze result.
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=#
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=#
On Fri, Jul 10, 2020 at 7:23 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 7/9/20 11:50 PM, Vishwa Kalyankar wrote:
> Hi Team,
>
> I Need help or any suggestion on below mentioned issue.
>
> Previously we are running postgresql-10with postgis 2.5.3 and now we are
> updated to postgresql-12 and postgis-3.0.1, and in postgresql-10 one
> query is taking 20 sec and same query is taking upto 80 sec. thanks in
> advance
The actual query and EXPLAIN ANALYZE for both runs of the query would be
useful.
>
> Regards,
>
> Vishwa S Kalyankar
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
