2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / 2ndQuadrant3 / Researching PostGIS slowness (2019 edition)
Researching PostGIS slowness (2019 edition)
Álvaro Herrera

Researching PostGIS slowness (2019 edition)

February 27, 2020/2 Comments/in 2ndQuadrant, Alvaro's PlanetPostgreSQL, PostgreSQL /by Álvaro Herrera

Just as last year was ending, one of our long-time customers came to us because one of their long-existing PostgreSQL queries involving PostGIS geometry calculations was much slower for specific values.  We researched the problem and found out how to solve it; read on! What we found as the cause of the problem will surprise you!

The starting observation, reported by our customer, was that running a query involving ST_DistanceSpheroid took about 7 milliseconds when asked to return the distance to POINT(33.681953 23.155994) on a specific spheroid, but if that point was moved to POINT(33.681953 23.1559941) (a difference of just 0.0000001) then it took 0.13 millisecond.  60 times faster! What on Earth (another spheroid!) could be going on?

Initially, we were unable to reproduce the slowness in our test environments. In our hands, both queries would perform equally quickly, with no slowdown. We dug down to the specific versions of software in use thinking that an update might be needed. We used the versions reported by customer: PostgreSQL 10.11, PostGIS 2.4.4, libproj 4.93. We returned to cave ages by downgrading to those precise versions, with no success.

Eventually we became aware that the customer was using Ubuntu 18.04, so we tried that … and lo and behold, the problem did reproduce there. Suffices to say that we jumped at the opportunity of profiling the query in that machine.  We got this:

Samples: 224K of event 'cpu-clock', Event count (approx.): 56043500000
  Children      Self  Command   Shared Object           Symbol
+   84.86%     0.00%  postgres  [unknown]               [.] 0xffffffffffffffff
+   84.59%     0.00%  postgres  postgres                [.] DirectFunctionCall4Coll
+   84.58%     0.00%  postgres  postgis-2.5.so          [.] geometry_distance_spheroid
+   84.56%     0.00%  postgres  liblwgeom-2.5.so.0.0.0  [.] lwgeom_distance_spheroid
+   84.31%     0.19%  postgres  libm-2.27.so            [.] __sincos
+   84.18%     0.00%  postgres  libm-2.27.so            [.] __cos_local (inlined)
+   84.13%     0.00%  postgres  libm-2.27.so            [.] cslow2 (inlined)
+   84.05%     0.01%  postgres  libm-2.27.so            [.] __mpcos
+   83.95%     0.32%  postgres  libm-2.27.so            [.] __c32
+   83.87%     0.00%  postgres  postgres                [.] ExecInterpExpr
+   83.75%     0.00%  postgres  postgres                [.] standard_ExecutorRun
+   83.75%     0.00%  postgres  postgres                [.] ExecutePlan (inlined)
+   83.73%     0.00%  postgres  postgres                [.] ExecProcNode (inlined)
+   83.73%     0.00%  postgres  postgres                [.] ExecScan
+   83.67%     0.00%  postgres  postgres                [.] ExecProject (inlined)
+   83.67%     0.00%  postgres  postgres                [.] ExecEvalExprSwitchContext (inlined)
+   83.65%     0.00%  postgres  postgres                [.] _SPI_execute_plan
+   83.60%     0.00%  postgres  postgres                [.] _SPI_pquery (inlined)
+   83.49%     0.01%  postgres  plpgsql.so              [.] exec_stmts
+   83.49%     0.00%  postgres  plpgsql.so              [.] exec_stmt (inlined)
+   83.49%     0.00%  postgres  plpgsql.so              [.] exec_stmt_fori (inlined)
+   83.48%     0.00%  postgres  plpgsql.so              [.] exec_stmt_perform (inlined)
+   83.48%     0.00%  postgres  plpgsql.so              [.] exec_run_select
+   83.47%     0.00%  postgres  postgres                [.] SPI_execute_plan_with_paramlist
+   81.67%     0.01%  postgres  liblwgeom-2.5.so.0.0.0  [.] edge_distance_to_point
+   81.67%     0.00%  postgres  liblwgeom-2.5.so.0.0.0  [.] 0x00007f2ce1c2c0e6
+   61.85%    60.82%  postgres  libm-2.27.so            [.] __mul
+   54.83%     0.01%  postgres  liblwgeom-2.5.so.0.0.0  [.] sphere_distance
+   27.14%     0.00%  postgres  plpgsql.so              [.] exec_stmt_block
+   26.67%     0.01%  postgres  liblwgeom-2.5.so.0.0.0  [.] geog2cart
+   19.24%     0.00%  postgres  libm-2.27.so            [.] ss32 (inlined)
+   18.28%     0.00%  postgres  libm-2.27.so            [.] cc32 (inlined)
+   12.55%     0.76%  postgres  libm-2.27.so            [.] __sub
+   11.46%    11.40%  postgres  libm-2.27.so            [.] sub_magnitudes
+    8.15%     4.89%  postgres  libm-2.27.so            [.] __add
+    4.94%     0.00%  postgres  libm-2.27.so            [.] add_magnitudes (inlined)
+    3.18%     3.16%  postgres  libm-2.27.so            [.] __acr
+    2.66%     0.00%  postgres  libm-2.27.so            [.] mcr (inlined)
+    1.44%     0.00%  postgres  liblwgeom-2.5.so.0.0.0  [.] lwgeom_calculate_gbox_geodetic
+    1.44%     0.00%  postgres  liblwgeom-2.5.so.0.0.0  [.] ptarray_calculate_gbox_geodetic

Gibberish, you say.  However, there’s something mighty curious about this profile … and you have to ignore the first 26 lines and focus on the __mul line there.  See that 60.82% of "self" time? (I can hear the sound of realization your mind just made).  So why does it take so much time for certain points on the spheroid and not others?  And also why does it take long in Ubuntu 18.04 but not in other machines? Why does upgrading PostGIS not fix the problem?

The answer was suggested to me once I realized what was obvious: PostGIS does a lot of trigonometry (sine, cosine, tangent etc) by calling libm functions.  Looking at the glibc changelogs we found some optimizations in trigonometry functions: for certain inputs, trigonometry calculations take shortcuts that cannot be taken for other inputs; and such shortcuts have been optimized over time. Indeed, the glibc announces for both 2.27 and 2.29 both mention optimizations in sine/cosine/etc functions. Apparently, once upon a time there were some optimizations by Intel that were supposed to provide very accurate results, but then somebody realized that the accuracy claim was incorrect, so glibc disabled the use of those optimizations; later on, that stuff was reimplemented in a different but again fast way.  Or something like that — for outsiders like me it’s hard to figure out the exact details.

We suspected that upgrading to a newer version of glibc would fix the problem, leaving everything else the same. Our customer tried that, and indeed it was true, and they were happy. We’re not really certain which of these glibc changes were responsible, but one thing is clear: it’s always a good idea to run stuff on up-to-date software.

Do keep in mind that bleeding edges are sharp … so be careful out there.

Tags: performance, postgis
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
2 replies
  1. Mariyappan
    Mariyappan says:
    March 5, 2020 at 10:17 am

    I’m using Hibernate @ JPA to connect PostgreSQL.

    I have an entity and i configure the PK as LONG and in PostgreSQL DB i configured the datatype for the PK as “bigint”. When i try to connect system throws me the error like

    Caused by: org.postgresql.util.PSQLException: Bad value for type long :

    at org.postgresql.jdbc.PgResultSet.toLong([PgResultSet.java:2876](https://PgResultSet.java:2876)) \[postgresql.jar:42.2.10\]

    at org.postgresql.jdbc.PgResultSet.getLong([PgResultSet.java:2083](https://PgResultSet.java:2083)) \[postgresql.jar:42.2.10\]

    at org.postgresql.jdbc.PgResultSet.getBlob([PgResultSet.java:416](https://PgResultSet.java:416)) \[postgresql.jar:42.2.10\]

    at org.postgresql.jdbc.PgResultSet.getBlob([PgResultSet.java:403](https://PgResultSet.java:403)) \[postgresql.jar:42.2.10\]

    at org.jboss.jca.adapters.jdbc.WrappedResultSet.getBlob([WrappedResultSet.java:573](https://WrappedResultSet.java:573))

    at org.hibernate.type.descriptor.sql.BlobTypeDescriptor$1.doExtract([BlobTypeDescriptor.java:65](https://BlobTypeDescriptor.java:65)) \[hibernate-core-4.3.7.Final.jar:4.3.7.Final\]

    at org.hibernate.type.descriptor.sql.BasicExtractor.extract([BasicExtractor.java:64](https://BasicExtractor.java:64)) \[hibernate-core-4.3.7.Final.jar:4.3.7.Final\]

    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet([AbstractStandardBasicType.java:267](https://AbstractStandardBasicType.java:267)) \[hibernate-core-4.3.7.Final.jar:4.3.7.Final\]

    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet([AbstractStandardBasicType.java:263](https://AbstractStandardBasicType.java:263)) \[hibernate-core-4.3.7.Final.jar:4.3.7.Final\]

    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet([AbstractStandardBasicType.java:253](https://AbstractStandardBasicType.java:253)) \[hibernate-core-4.3.7.Final.jar:4.3.7.Final\]

    at org.hibernate.type.AbstractStandardBasicType.hydrate([AbstractStandardBasicType.java:338](https://AbstractStandardBasicType.java:338)) \[hibernate-core-4.3.7.Final.jar:4.3.7.Final\]

    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate([AbstractEntityPersister.java:2969](https://AbstractEntityPersister.java:2969)) \[hibernate-core-4.3.7.Final.jar:4.3.7.Final\]

    at org.hibernate.loader.plan.exec.process.internal.EntityReferenceInitializerImpl.loadFromResultSet([EntityReferenceInitializerImpl.java:324](https://EntityReferenceInitializerImpl.java:324)) \[hibernate-core-4.3.7.Final.jar:4.3.7.Final\]

    … 337 more

    Any idea about this issue?
    Thanks

    Reply
    • Álvaro Herrera
      Álvaro Herrera says:
      March 12, 2020 at 12:05 pm

      Apparently, you’re trying to use an empty string as a number, which might work in other database systems, but in PostgreSQL it doesn’t.
      This question is not relevant to my blog article; I suggest you take it up some support forum.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Webinar: All you need to know about CREATE STATISTICS [Follow Up] Developing PostgreSQL for Windows, Part 2 Developing PostgreSQL for Windows, Part 2
Scroll to top
×