Re: Postgresql-12 taking more time to execute the query - Mailing list pgsql-general

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