postgresql - ST_DWithin 有时不使用索引

Question

我将 PostGIS 与 Postgresql 结合使用，以便能够通过存储在位置列 Geometry/Point SRID: 4326 中的坐标定位某个半径内的条目。这是我正在试验的两个查询:

第一个以米为单位的距离和 use_spheroid=true

EXPLAIN ANALYZE SELECT count(*) FROM "cars" WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(20, -30), 4326), 105000, true) LIMIT 1000;
                                                                                                                                                                                             QUERY PLAN                                                                                                                                                                                              
--------------
 Limit  (cost=11884.28..11884.30 rows=1 width=8) (actual time=18.843..18.844 rows=1 loops=1)
   ->  Aggregate  (cost=11884.28..11884.30 rows=1 width=8) (actual time=18.842..18.843 rows=1 loops=1)
         ->  Seq Scan on cars  (cost=0.00..11883.33 rows=381 width=0) (actual time=0.486..18.827 rows=38 loops=1)
               Filter: (((location)::geography && '0101000020E610000000000000000034400000000000003EC0'::geography) AND ('0101000020E610000000000000000034400000000000003EC0'::geography && _st_expand((location)::geography, '105000'::double precision)) AND _st_dwithin((location)::geography, '0101000020E610000000000000000034400000000000003EC0'::geography, '105000'::double precision, true))
               Rows Removed by Filter: 28549
 Planning time: 0.166 ms
 Execution time: 18.878 ms
(7 rows)

其次，我假设接受以度为单位的距离，默认情况下 use_spheroid 为 false。更正:原来这仍然使用 use_spheroid=true，但与此调用匹配的函数签名需要几何和 SRID 单位，即 4326 的度数。

EXPLAIN ANALYZE SELECT count(*) FROM "cars" WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(20, -30), 4326), 1) LIMIT 1000;
                                                                                                                          QUERY PLAN                                                                                                                          
-----------------------------
 Limit  (cost=145.30..145.31 rows=1 width=8) (actual time=0.154..0.155 rows=1 loops=1)
   ->  Aggregate  (cost=145.30..145.31 rows=1 width=8) (actual time=0.154..0.154 rows=1 loops=1)
         ->  Bitmap Heap Scan on cars  (cost=4.59..145.29 rows=3 width=0) (actual time=0.050..0.147 rows=37 loops=1)
               Recheck Cond: (location && '0103000020E6100000010000000500000000000000000033400000000000003FC000000000000033400000000000003DC000000000000035400000000000003DC000000000000035400000000000003FC000000000000033400000000000003FC0'::geometry)
               Filter: (('0101000020E610000000000000000034400000000000003EC0'::geometry && st_expand(location, '1'::double precision)) AND _st_dwithin(location, '0101000020E610000000000000000034400000000000003EC0'::geometry, '1'::double precision))
               Rows Removed by Filter: 11
               Heap Blocks: exact=47
               ->  Bitmap Index Scan on cars_location_index  (cost=0.00..4.59 rows=42 width=0) (actual time=0.037..0.037 rows=48 loops=1)
                     Index Cond: (location && '0103000020E6100000010000000500000000000000000033400000000000003FC000000000000033400000000000003DC000000000000035400000000000003DC000000000000035400000000000003FC000000000000033400000000000003FC0'::geometry)
 Planning time: 0.280 ms
 Execution time: 0.188 ms
(11 rows)

两个查询返回相似的结果(+/- 因为精度)。但是第一个运行速度慢 100 倍。此外，将 use_spheroid 设置为 false 并不能保证使用索引，当距离太小 (<0.4) 或太大 (>45) 时，它会退回到 Seq Scan。这是应该的样子还是我做错了什么？

添加:经过更多实验后，我将列类型更改为 Geography.Point，现在它始终使用索引。问题似乎已解决，但我仍然对我使用 Geometry 类型观察到的行为感到困惑。

Answer 1

ST_DWithin文档指出第一个函数签名接受地理类型而不是几何类型:

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

因为 (location,ST_SetSRID(ST_MakePoint(20, -30), 4326)) 都是几何形状，所以函数的执行很困惑是有道理的。而且我认为你的第二个函数工作正常，因为你正在执行的是这个签名:

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

正如您所说，将列类型切换为 Geography 而不是 Geometry 将解决问题，因为这将使您正确执行:

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

希望对您有所帮助。

编辑:

在 documentation 中找到此部分表示在输入数据时

Standard geometry type data will autocast to geography if it is of SRID 4326

这可以解释为什么 Postgres 接受您对 ST_DWithin() 的第一次调用，因为 postgis 显然会将其转换为地理，并且这也解释了为什么执行需要更长的时间并忽略索引，因为每次转换都会产生一个新对象未在您的原始列中编入索引。