<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">I always run vacuum before benchmarking so statistics should be up to date.<div class=""><br class=""></div><div class="">Here are plans for two different queries I use. The one with _ST_DWithin is always faster.</div><div class=""><br class=""></div><div class="">Straight ST_DWithin:</div><div class=""><br class=""></div><div class=""><font face="Courier New" class="">explain analyze select count(*) from assets where ST_DWithin(geometry, 'SRID=4326;POINT (59 32)'::geometry, 10.664129861033185)</font></div><div class=""><font face="Courier New" class=""><br class=""></font></div><div class=""><div class=""><font face="Courier New" class="">Aggregate (cost=18204.79..18204.80 rows=1 width=8) (actual time=45.987..45.987 rows=1 loops=1)</font></div><div class=""><font face="Courier New" class=""> -> Bitmap Heap Scan on assets (cost=708.70..18201.50 rows=1316 width=0) (actual time=3.298..42.899 rows=16437 loops=1)</font></div><div class=""><font face="Courier New" class=""> Recheck Cond: (geometry && '0103000020E610000001000000050000006048EFCAFD2A4840C190DE95FB5535406048EFCAFD2A4840A0B7103502554540D05B881A816A5140A0B7103502554540D05B881A81</font></div><div class=""><font face="Courier New" class="">6A5140C190DE95FB5535406048EFCAFD2A4840C190DE95FB553540'::geometry)</font></div><div class=""><font face="Courier New" class=""> Filter: (('0101000020E61000000000000000804D400000000000004040'::geometry && st_expand(geometry, '10.6641298610332'::double precision)) AND _st_dwithin(geometry, '010100</font></div><div class=""><font face="Courier New" class="">0020E61000000000000000804D400000000000004040'::geometry, '10.6641298610332'::double precision))</font></div><div class=""><font face="Courier New" class=""> Rows Removed by Filter: 3146</font></div><div class=""><font face="Courier New" class=""> Heap Blocks: exact=5484</font></div><div class=""><font face="Courier New" class=""> -> Bitmap Index Scan on assets_geometry_idx (cost=0.00..708.38 rows=19746 width=0) (actual time=2.640..2.640 rows=19583 loops=1)</font></div><div class=""><font face="Courier New" class=""> Index Cond: (geometry && '0103000020E610000001000000050000006048EFCAFD2A4840C190DE95FB5535406048EFCAFD2A4840A0B7103502554540D05B881A816A5140A0B7103502554540D05B88</font></div><div class=""><font face="Courier New" class="">1A816A5140C190DE95FB5535406048EFCAFD2A4840C190DE95FB553540'::geometry)</font></div><div class=""><font face="Courier New" class="">Planning time: 1.572 ms</font></div><div class=""><font face="Courier New" class="">Execution time: 46.203 ms</font></div></div><div class=""><br class=""></div><div class="">_ST_DWithin with one check:</div><div class=""><br class=""></div><div class=""><div class=""><font face="Courier New" class="">explain analyze select count(*) from assets where geometry && ST_Expand('SRID=4326;POINT (59 32)'::geometry, 10.664129861033185) AND _ST_DWithin(geometry, 'SRID=4326;POINT (59 32)'::geometry, 10.6641298610331);</font></div><div class=""><font face="Courier New" class=""><br class=""></font></div><div class=""><font face="Courier New" class="">Aggregate (cost=18120.54..18120.55 rows=1 width=8) (actual time=41.745..41.746 rows=1 loops=1)</font></div><div class=""><font face="Courier New" class=""> -> Bitmap Heap Scan on assets (cost=710.02..18104.09 rows=6582 width=0) (actual time=8.279..36.987 rows=16437 loops=1)</font></div><div class=""><font face="Courier New" class=""> Recheck Cond: (geometry && '0103000020E610000001000000050000006048EFCAFD2A4840C190DE95FB5535406048EFCAFD2A4840A0B7103502554540D05B881A816A5140A0B7103502554540D05B881A81</font></div><div class=""><font face="Courier New" class="">6A5140C190DE95FB5535406048EFCAFD2A4840C190DE95FB553540'::geometry)</font></div><div class=""><font face="Courier New" class=""> Filter: _st_dwithin(geometry, '0101000020E61000000000000000804D400000000000004040'::geometry, '10.6641298610331'::double precision)</font></div><div class=""><font face="Courier New" class=""> Rows Removed by Filter: 3146</font></div><div class=""><font face="Courier New" class=""> Heap Blocks: exact=5484</font></div><div class=""><font face="Courier New" class=""> -> Bitmap Index Scan on assets_geometry_idx (cost=0.00..708.38 rows=19746 width=0) (actual time=6.104..6.104 rows=19583 loops=1)</font></div><div class=""><font face="Courier New" class=""> Index Cond: (geometry && '0103000020E610000001000000050000006048EFCAFD2A4840C190DE95FB5535406048EFCAFD2A4840A0B7103502554540D05B881A816A5140A0B7103502554540D05B88</font></div><div class=""><font face="Courier New" class="">1A816A5140C190DE95FB5535406048EFCAFD2A4840C190DE95FB553540'::geometry)</font></div><div class=""><font face="Courier New" class="">Planning time: 0.633 ms</font></div><div class=""><font face="Courier New" class="">Execution time: 41.833 ms</font></div></div><div class=""><br class=""></div><div class="">This is a tiny test table with about 100k rows. As you can see, it takes long time to get a count. Our geometry columns always contain mixed types (points, line-string, polygon, multi-polygon). Wonder if that makes it slow.</div><div class=""><br class=""></div><div class="">Thanks for your help!</div><div class=""><br class=""></div><div class="">Zhihong</div><div class=""><br class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Jan 6, 2018, at 11:13 AM, Darafei Komяpa Praliaskouski <<a href="mailto:me@komzpa.net" class="">me@komzpa.net</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class="">Hello,<div class=""><br class=""></div><div class="">Would you please post the query plan please?<br class="">It feels like "all geometry columns are indexed" may switch plan to Bitmap And, and that indeed may be slower. <br class="">Is statistics up-to-date?</div></div><br class=""><div class="gmail_quote"><div dir="ltr" class="">сб, 6 янв. 2018 г. в 19:10, Zhihong Zhang <<a href="mailto:zhihong@gmail.com" class="">zhihong@gmail.com</a>>:<br class=""></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word;line-break:after-white-space" class="">Thanks for the explanation. I understand the reason for the 2nd check now.<div class=""><br class=""></div><div class="">All our geometry columns are indexed so the 2nd check is redundant, which causes the performance degradation.</div></div><div style="word-wrap:break-word;line-break:after-white-space" class=""><div class=""><br class=""></div><div class="">Zhihong</div></div><div style="word-wrap:break-word;line-break:after-white-space" class=""><div class=""><div class=""><br class=""><blockquote type="cite" class=""><div class="">On Jan 6, 2018, at 11:00 AM, Nicklas Avén <<a href="mailto:nicklas.aven@jordogskog.no" target="_blank" class="">nicklas.aven@jordogskog.no</a>> wrote:</div><br class="m_-2702818378071579770Apple-interchange-newline"><div class=""><div class=""><div class="">Hello</div><div class=""><br class=""></div><div class="">If you get a difference as large as 20% I guess you are not using spatial indexes.</div><div class=""><br class=""></div><div class="">The reaason for that implementation is that it gives the query planner the possibility to choose if it shall iterate table1 and use the index of table 2 to find overlaps, or if it shall iterate table 2 instead and use the index of table 1 to find overlap.</div><div class=""><br class=""></div><div class="">The index cannot be used on an expanded geometry (snce the bbox in the index is for the original geometry) so without this double check the planner wouldn't have the choice.</div><div class=""><br class=""></div><div class="">That would be very bad if only 1 of the tables have an index. It is also a bad thing if the table that gets the sequential scan is the one with millions of rows and the index is used on the table with just a few rows.</div><div class=""><br class=""></div><div class="">/Nicklas</div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">On Sat, 2018-01-06 at 10:14 -0500, Zhihong Zhang wrote:</div><blockquote type="cite" class="">ST_DWithin is implemented like this,<div class=""><br class=""></div><div class=""></div><div class=""><font color="#24292e" face="Courier New" class=""><span style="font-size:14px" class="">CREATE OR REPLACE FUNCTION ST_DWithin(geom1 geometry, geom2 geometry, float8 RETURNS boolean</span></font><table class="m_-2702818378071579770highlight m_-2702818378071579770tab-size m_-2702818378071579770js-file-line-container" style="box-sizing:border-box;border-spacing:0px;border-collapse:collapse;background-color:rgb(255,255,255)"></table><font color="#24292e" face="Courier New" class=""><span style="font-size:14px" class="">AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($2,$3) AND <b class="">$2 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($1,$3)</b> AND @extschema@._ST_DWithin($1, $2, $3)'</span></font><table class="m_-2702818378071579770highlight m_-2702818378071579770tab-size m_-2702818378071579770js-file-line-container" style="box-sizing:border-box;border-spacing:0px;border-collapse:collapse;background-color:rgb(255,255,255)"></table><font color="#24292e" face="Courier New" class=""><span style="font-size:14px" class="">LANGUAGE 'sql' IMMUTABLE _PARALLEL;</span></font><table class="m_-2702818378071579770highlight m_-2702818378071579770tab-size m_-2702818378071579770js-file-line-container" style="box-sizing:border-box;border-spacing:0px;border-collapse:collapse;background-color:rgb(255,255,255)"></table><div class=""><br class=""></div></div><div class="">What’s the purpose of the second bounding box check? </div><div class=""><br class=""></div><div class="">I did many tests. It’s always faster (about 20%) with one check. So we ended up using our own version of ST_DWithin.</div><div class=""><br class=""></div><div class="">Thanks!</div><div class=""><br class=""></div><div class="">Zhihong</div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div><pre class="">_______________________________________________
postgis-devel mailing list
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" class="">postgis-devel@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></pre></blockquote></div>_______________________________________________<br class="">postgis-devel mailing list<br class=""><a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" class="">postgis-devel@lists.osgeo.org</a><br class=""><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></div></blockquote></div><br class=""></div></div>_______________________________________________<br class="">
postgis-devel mailing list<br class="">
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" class="">postgis-devel@lists.osgeo.org</a><br class="">
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div>
_______________________________________________<br class="">postgis-devel mailing list<br class=""><a href="mailto:postgis-devel@lists.osgeo.org" class="">postgis-devel@lists.osgeo.org</a><br class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</div></blockquote></div><br class=""></div></body></html>