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@xxxxxxxxxxx
<mailto: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 <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
