[postgis-tickets] [PostGIS] #4732: long planning time

[PostGIS]

#4732: long planning time
--------------------------+---------------------------
  Reporter:  michal       |      Owner:  pramsey
      Type:  enhancement  |     Status:  new
  Priority:  low          |  Milestone:  PostGIS 3.1.0
 Component:  postgis      |    Version:  3.0.x
Resolution:               |   Keywords:
--------------------------+---------------------------

Comment (by michal):

 steps to reproduce (create some data, two cities with a lot of points plus
 some points in between):
 {{{
 drop table if exists tmp_within;
 create table tmp_within (name text, way geography);
 insert into tmp_within select left(md5(i::text), 8) as desc,
 geography(st_makepoint(48+random(), 17+random())) from
 generate_series(1,25000) as i;
 insert into tmp_within select left(md5(i::text), 8) as desc,
 geography(st_makepoint(48+random(), 19+random())) from
 generate_series(25000,50000) as i;
 insert into tmp_within select left(md5(i::text), 8) as desc,
 geography(st_makepoint(48+random(), 17+3*random())) from
 generate_series(50000,75000) as i;
 create index on tmp_within using gist(way) ;
 analyze tmp_within;
 }}}
 and then select which points are near our point (48.9,17.1) - within
 roughly 1km (I generally don't care about the difference between square
 and round buffer, as long as it is fast).

 selecting rows within (square) buffer:
 {{{
 explain analyze select name from tmp_within where way &&
 st_buffer(geography(st_makepoint(48.9, 17.1)), 1000);

 Index Scan using tmp_within_way_idx on tmp_within  (cost=0.28..3.90 rows=1
 width=9) (actual time=0.096..0.116 rows=13 loops=1)
    Index Cond: (way &&
 '0103000020E610000001000000210000002BB56A00677448405C3505F89F19314030735BBB617448406A20C2592C193140EC5A10D6507448401DCD6BEEBC18314004EFD4F63474484098061EFE551831407F8DEB2F0F74484007137F7DFB173140FC1901F5E07348404E74D7E6B0173140ED65E30CAC734840EA3BDE1779173140C70F078072734840B5FB8935561731401CC18884367348400A1DFB964917314032496E68FA72484083974EB8531731406822FD7AC07248402E4DDA357417314065DB04F68A72484086B501D0A9173140624DFCE75B72484073207D77F2173140BF7BC81F3572484010EF9A614B1831408BBBF31A18724840B7D0B323B118314058EF03F705724840BD5CC4D41F19314023208066FF714840D8F6E13393193140E4AB11AA047248401D1E11D2061A31403A01058E1572484078ECE13D761A314029F3426C317248403DF1252FDD1A314091BAAF3257724840446911B1371B3140076CB16D8572484039332949821B314044AC7656BA724840BA307E19BA1B3140E55A71E4F37248400FDEE4FCDC1B3140633659E12F734840740A139CE91B31408864F1FE6B734840A77DD37ADF1B31403F4EBAEDA5734840F04CCDFCBE1B31403D0FB073DB73484057A6AF61891B31405B3735820A744840F2B6E7B8401B31400ACB514A3174484018BD59CDE71A31400DFF7E4E4E7448405616E509821A3140C872507160744840301DC257131A31402BB56A00677448405C3505F89F193140'::geography)
  Planning Time: 15.122 ms
  Execution Time: 0.135 ms
 }}}

 selecting rows with st_dwithin (which is round):
 {{{
 explain analyze select name from tmp_within where
 st_dwithin(way,geography(st_makepoint(48.9, 17.1)), 1000);

 Index Scan using tmp_within_way_idx on tmp_within  (cost=0.53..29.15
 rows=8 width=9) (actual time=0.697..0.793 rows=12 loops=1)
    Index Cond: (way &&
 _st_expand('0101000020E610000033333333337348409A99999999193140'::geography,
 '1000'::double precision))
    Filter: st_dwithin(way,
 '0101000020E610000033333333337348409A99999999193140'::geography,
 '1000'::double precision, true)
    Rows Removed by Filter: 5
  Planning Time: 0.300 ms
  Execution Time: 0.838 ms

 }}}

 The first approach, which is far less accurate, spends long time in
 planning (and less time in execution, which is expectable, since it is far
 less accurate).


 the fastest approach should be (I am not sure, why the _ in front of
 _st_expand is neccessary):
 {{{
 explain analyze select name from tmp_within where way &&
 _st_expand(geography(st_makepoint(48.9, 17.1)), 1000);

 Index Scan using tmp_within_way_idx on tmp_within  (cost=0.28..3.90 rows=1
 width=9) (actual time=0.182..0.280 rows=17 loops=1)
    Index Cond: (way &&
 '0101000020E610000033333333337348409A99999999193140'::geography)
  Planning Time: 0.419 ms
  Execution Time: 0.313 ms


 }}}

 Why is there such huge difference in planning time between the approaches?

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