[postgis-tickets] [PostGIS] #5186: PostGIS performance regressions on identical queries

[PostGIS]

#5186: PostGIS performance regressions on identical queries
----------------------+---------------------------
  Reporter:  dracos   |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  medium   |  Milestone:  PostGIS 3.2.2
 Component:  postgis  |    Version:  3.1.x
Resolution:           |   Keywords:
----------------------+---------------------------
Comment (by robe):

 Curious what happens if you flip the order, so write it like this:


 {{{
 SELECT * FROM "mapit_postcode"
 WHERE (
 location && ST_Transform((select ST_Collect(polygon) from mapit_geometry
 where area_id=2577 group by area_id), 4326) AND
 ST_CoveredBy(location, ST_Transform((select ST_Collect(polygon) from
 mapit_geometry where area_id=2577 group by area_id), 4326))
 )
 LIMIT 1;
 }}}

 OR just leave out the first, it seems redundant, though you might have had
 it there because it made things faster.  I'd be interested to see how the
 below fairs on your old (if you still have it around) and your new.  If my
 suspicions are right, I would expect the 9.6 version to be come just as
 slow as your 13. If not well that is an added piece of information

 {{{
 SELECT * FROM "mapit_postcode"
 WHERE (

 ST_CoveredBy(location, ST_Transform((select ST_Collect(polygon) from
 mapit_geometry where area_id=2577 group by area_id), 4326))
 )
 LIMIT 1;
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5186#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.