Обсуждение: Postgresql-12 taking more time to execute the query
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
Regards,
Vishwa S Kalyankar
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 oftenthan 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=#
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/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
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