Re: Postgresql-12 taking more time to execute the query

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=#

On Fri, Jul 10, 2020 at 7:23 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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(dot)klaver(at)aklaver(dot)com
>