[postgis-tickets] [PostGIS] #4720: Memory leak when using Geography

[PostGIS]

#4720: Memory leak when using Geography
----------------------+------------------------------
  Reporter:  kthujvu  |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  medium   |  Milestone:
 Component:  postgis  |    Version:  3.0.x
Resolution:           |   Keywords:  geography memory
----------------------+------------------------------

Comment (by kthujvu):

 I also cannot reproduce it with the 50m dataset.

 Here is the full "test suite" I used, uses docker (sorry):


 {{{
 docker run --name "postgis" -p 25432:5432 -d -t kartoza/postgis:12.1
 docker exec -it postgis bash
 # then in the container itself:
 apt update
 apt install postgis  # for shp2pgsql, update is needed first for the
 package to be discoverable
 apt install unzip
 wget
 https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/110m/cultural/ne_110m_admin_0_countries.zip
 wget
 https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/50m/cultural/ne_50m_admin_0_countries.zip
 unzip ne_50m_admin_0_countries.zip
 shp2pgsql -c -D -i -I -G ne_50m_admin_0_countries.shp | PGPASSWORD=docker
 psql -h localhost -U docker gis
 unzip ne_110m_admin_0_countries.zip
 shp2pgsql -c -D -i -I -G ne_110m_admin_0_countries.shp | PGPASSWORD=docker
 psql -h localhost -U docker gis
 }}}


 Then in
 {{{
 PGPASSWORD=docker psql -h localhost -U docker gis
 }}}
 I tested the following query:



 {{{
 WITH latitudes AS (
         SELECT generate_series AS latitude
         FROM generate_series(-90, 90, 0.1)
 ), longitudes AS (
         SELECT generate_series AS longitude
         FROM generate_series(-180, 180, 0.1)
 ), points AS (
         SELECT ST_SetSRID(ST_Point(longitude, latitude), 4326)::geography
 AS geog
         FROM latitudes
         CROSS JOIN longitudes
 )
 SELECT
         geog,
         (
                 SELECT name
                 FROM ne_50m_admin_0_countries AS ne
                 ORDER BY p.geog <-> ne.geog
                 LIMIT 1
         )
 FROM points AS p
 ;
 }}}



 That query on the ne_50m_admin_0_countries table: 250MB RAM (RES) usage
 after 30 minutes

 Same query with ne_110m_admin_0_countries: > 2G RAM (RES) and growing
 ~8MB/s after 15 minutes

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4720#comment:2>
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.