[postgis-tickets] [PostGIS] #4754: Performance difference with versions of Postgres/Postgis

[PostGIS]

#4754: Performance difference with versions of Postgres/Postgis
----------------------+--------------------------------
 Reporter:  fractalf  |      Owner:  pramsey
     Type:  defect    |     Status:  new
 Priority:  medium    |  Milestone:  PostGIS PostgreSQL
Component:  postgis   |    Version:  3.0.x
 Keywords:            |
----------------------+--------------------------------
 Hi community!

 I'd like to register an issue with Postgis regarding performance in
 different versions of Postgres/Postgis.

 I'm testing this out before we build a full solution and got some strange
 different response times running a query on different versions.

 I test this in docker using 3 setups:

 **1.**

 {{{
 FROM postgres:11.9
 ENV POSTGIS_MAJOR 3
 ENV POSTGIS_VERSION 3.0.2+dfsg-2.pgdg90+1

 }}}

 => This gives me a response time of about **0.05s**

 **2.**

 {{{
 FROM postgres:12
 ENV POSTGIS_MAJOR 3
 ENV POSTGIS_VERSION 3.0.2+dfsg-2.pgdg100+1

 }}}

 => This gives me a response time of about **0.5s**

 **3.**

 {{{
 FROM postgres:12
 ENV POSTGIS_MAJOR 2.5
 ENV POSTGIS_VERSION 2.5.5+dfsg-1.pgdg100+1

 }}}

 =>  This gives me a response time of about **0.05s**

 I found this huge increase (almost 10x) in response time very strange.

 This is more or less my test data/query:


 {{{
 CREATE TABLE "zones" (
   "id" serial NOT NULL,
   "name" character varying NOT NULL,
   "geo" geometry NOT NULL
 );
 CREATE TABLE "points" (
   "id" serial NOT NULL,
   "name" character varying NOT NULL,
   "geo" geometry NOT NULL
 );
 INSERT INTO zones (name, geo) VALUES
     ('Foo', 'POLYGON(
         (
             63.43277965483068 10.354270935058594,
             63.43009239981209 10.353584289550781,
             63.42533150079501 10.367660522460936,
             63.421337878804195 10.386199951171873,
             63.40474303024033 10.385856628417969,
             63.394904513399645 10.387916564941406,
             63.4155000456553 10.458297729492186,
             63.428403139082626 10.472373962402344,
             63.43945831122457 10.46945571899414,
             63.44759342069812 10.462932586669922,
             63.45035576238835 10.45074462890625,
             63.44797709520402 10.42276382446289,
             63.44053289210481 10.398731231689453,
             63.434008030326964 10.362682342529297,
             63.43277965483068 10.354270935058594
         )
     )');
 # Insert 10000 random points from a generated sql file


 select count(*)
 from points as p
 join zones as z
 on ST_WITHIN(p.geo, z.geo)
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4754>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.