Postgresql-12 taking more time to execute the query

Vishwa Kalyankar schrieb am 10.07.2020 um 08:50:

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.

Most of the slowdowns I have seen when upgrading to Postgres 12 were caused by JITting, which seems to kick in more often than not on queries that don't profit from it.

Try to disable jit (set jit=off) and re-run your query

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@aklaver.com

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.klaver@aklaver.com>
wrote:

On 7/10/20 7:48 AM, Vishwa Kalyankar wrote:

Hi,

  Below is the output of the query explain and analyze result.

   Note : port 5434 is postgresql12  and 5433 is postgresql10

Well that is not enlightening. What's happening inside the function is
not being shown. You might have to ask this on PostGIS list:

https://lists.osgeo.org/mailman/listinfo/postgis-users

Somewhere there may have a better idea of what goes on in the function
and whether that changed from PostGIS 2.5.3 to 3.0.1.

-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.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sat, 11 Jul 2020 at 02:48, Vishwa Kalyankar
<vishwakalyankar8@gmail.com> wrote:

Below is the output of the query explain and analyze result.

You may find better help here if you follow the advice given in
https://wiki.postgresql.org/wiki/Slow_Query_Questions

David