[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:  PostGIS 3.0.3
 Component:  postgis  |    Version:  3.0.x
Resolution:           |   Keywords:  geography memory
----------------------+------------------------------

Comment (by Algunenano):

 I also see this behaviour if I load the column as a geometry and have an
 index over that geometry, even If I remove geography out of the query
 completely:

 {{{
 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)::geometry AS geog
 FROM latitudes
 CROSS JOIN longitudes
 )
 SELECT
 geog,
 (
 SELECT name
 FROM ne_110m_admin_0_countries AS ne
 ORDER BY p.geog <-> ne.the_geom
 LIMIT 1
 )
 FROM points AS p
 ;
 }}}

 Plan:
 {{{
  Nested Loop  (cost=0.01..3203541.08 rows=1000000 width=64)
    ->  Function Scan on generate_series  (cost=0.00..10.00 rows=1000
 width=32)
    ->  Function Scan on generate_series generate_series_1
 (cost=0.00..10.00 rows=1000 width=32)
    SubPlan 1
      ->  Limit  (cost=0.27..3.05 rows=1 width=17)
            ->  Index Scan using ne_110m_admin_0_countries_the_geom_idx on
 ne_110m_admin_0_countries ne  (cost=0.27..492.07 ro
 ws=177 width=17)
                  Order By: (the_geom <->
 st_setsrid(st_point((generate_series_1.generate_series)::double precision,
 (generate
 _series.generate_series)::double precision), 4326))
 (7 rows)
 }}}


 OTOH, if I use ST_Distance instead of `<->`, which doesn't use indexes,
 then the memory is really stable. Same if I use `<->` but remove
 `indexscan` as a possible subplan.

 So it looks like an issue with the index subplans, where either Postgis or
 Postgres, or both :D,  leaking memory or caching way more things.

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