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
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 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.kla...@aklaver.com >
Same query taking less time in low configuration machine
Hi, I have two machines - one with 8GB RAM & 4core CPU and the other with 64GB Ram & 24 core CPU. Both machines have the same DB (Postgres 12 + Postgis 2.5.3). Same query is taking less time in low end machine whereas more time in high end machine. Any thoughts on where to look? I have tuned the db in both machines according to https://pgtune.leopard.in.ua/#/ Below I am pasting the output of query explain in both the machines. -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 * from kseb_geometry_trace_with_barrier_partition(5,'kottarakara_version',437,'htline',2) ; QUERY PLAN - Function Scan on kseb_geometry_trace_with_barrier_partition (cost=0.25..10.25 rows=1000 width=169) (actual time=11626.548..11626.568 rows=254 loops=1) Planning Time: 0.212 ms Execution Time: *11628.590 ms* -bash-4.2$ psql -p 5422 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 * from kseb_geometry_trace_with_barrier_partition(5,'kottarakara_version',437,'htline',2) ; QUERY PLAN - Function Scan on kseb_geometry_trace_with_barrier_partition (cost=0.25..10.25 rows=1000 width=169) (actual time=22304.425..22304.448 rows=254 loops=1) Planning Time: 0.219 ms Execution Time: *22352.219 ms* (3 rows)
Re: Same query taking less time in low configuration machine
HI, OS cache is updated and I had run the query few times with almost the same result each time. Regards, Vishwa On Tue, Jul 14, 2020 at 6:16 PM Philip Semanchuk < phi...@americanefficient.com> wrote: > > > > On Jul 14, 2020, at 5:27 AM, Vishwa Kalyankar < > vishwakalyank...@gmail.com> wrote: > > > > Hi, > > > > I have two machines - one with 8GB RAM & 4core CPU and the other with > 64GB Ram & 24 core CPU. Both machines have the same DB (Postgres 12 + > Postgis 2.5.3). Same query is taking less time in low end machine whereas > more time in high end machine. Any thoughts on where to look? I have > tuned the db in both machines according to https://pgtune.leopard.in.ua/#/ > > > > > > Below I am pasting the output of query explain in both the machines. > > > > -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 * from > kseb_geometry_trace_with_barrier_partition(5,'kottarakara_version',437,'htline',2) > ; > > > QUERY PLAN > > > - > > Function Scan on kseb_geometry_trace_with_barrier_partition > (cost=0.25..10.25 rows=1000 width=169) (actual time=11626.548..11626.568 > rows=254 loops=1) > > Planning Time: 0.212 ms > > Execution Time: 11628.590 ms > > > > > > -bash-4.2$ psql -p 5422 > > 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 * from > kseb_geometry_trace_with_barrier_partition(5,'kottarakara_version',437,'htline',2) > ; > > > QUERY PLAN > > > - > > Function Scan on kseb_geometry_trace_with_barrier_partition > (cost=0.25..10.25 rows=1000 width=169) (actual time=22304.425..22304.448 > rows=254 loops=1) > > Planning Time: 0.219 ms > > Execution Time: 22352.219 ms > > (3 rows) > > > > Hi Vishwa, > Is it possible that your data is in the cache on the low end machine but > not on the high end machine? There’s both the Postgres cache and the OS > disk cache to consider. You can see what’s in the Postgres cache with an > extension like pg_buffercache. I don’t know of a way to see what’s in the > OS cache; maybe others do. > > Cheers > Philip > > > > > > >
Re: Same query taking less time in low configuration machine
Hi, I am pasting the output of both server cpu speed and memory speed, and we have same os (centos) on both the machines and i have downloaded the postgres rpms from https://www.postgresql.org/ 1) High end machine [root@localhost ~]# dmidecode -t processor | grep Speed Max Speed: 3600 MHz Current Speed: 2666 MHz Max Speed: 3600 MHz Current Speed: 2666 MHz Ram slots 16x4=64 GB [root@localhost ~]# dmidecode --type 17 # dmidecode 3.1 Getting SMBIOS data from sysfs. SMBIOS 2.6 present. Handle 0x1100, DMI type 17, 28 bytes Memory Device Array Handle: 0x1000 Error Information Handle: Not Provided Total Width: 72 bits Data Width: 64 bits Size: 16384 MB Form Factor: DIMM Set: 1 Locator: DIMM_A1 Bank Locator: Not Specified Type: DDR3 Type Detail: Synchronous Registered (Buffered) Speed: 1333 MT/s Manufacturer: 00CE00B380CE Serial Number: 35E9FC94 Asset Tag: 02131263 Part Number: M393B2G70BH0-YH9 Rank: 2 cache size : 12288 KB 2) Low End machine [root@localhost ~]# dmidecode -t processor | grep Speed Max Speed: 3800 MHz Current Speed: 3200 MHz Ram slots 4x2=8GB [root@localhost ~]# dmidecode --type 17 # dmidecode 3.0 Getting SMBIOS data from sysfs. SMBIOS 2.7 present. Handle 0x0038, DMI type 17, 34 bytes Memory Device Array Handle: 0x0037 Error Information Handle: Not Provided Total Width: 64 bits Data Width: 64 bits Size: 4096 MB Form Factor: DIMM Set: None Locator: DIMM1 Bank Locator: Not Specified Type: DDR3 Type Detail: Synchronous Speed: 1600 MHz Manufacturer: Hynix/Hyundai Serial Number: 0BB0390C003C Asset Tag: 9876543210 Part Number: HMT351U6EFR8C-PB Rank: 2 Configured Clock Speed: 1600 MHz cache size : 6144 KB Any other details required? kindly let me know how to obtain those, i will share you the same. Regards, Vishwa S Kalyankar On Tue, Jul 14, 2020 at 10:12 PM Kenneth Marshall wrote: > On Tue, Jul 14, 2020 at 09:27:56PM +0530, Vishwa Kalyankar wrote: > > HI, > > > > OS cache is updated and I had run the query few times with almost the > same > > result each time. > > > > Regards, > > Vishwa > > Hi Vishwa, > > What are the CPU speeds, memory bandwidth, I/O bandwidth? Often the > lower core count CPUs have a faster clock speed. What is the CPU cache > size for both? Are you running the same OS and PostgreSQL build binaries > on both? > > Regards, > Ken >
