postgresql - ST_DWithin 耗时较长(超过6000ms)

Question

我有一个包含 1756678 条记录的表 (underground_route)，它定义了不同的 ug 路线。我想查找特定半径内的详细信息。

我为 the_geom 字段创建了一个 GiST 索引 underground_route_the_geom_idx，如下所示:

select * from pg_indexes where indexname='underground_route_the_geom_idx';
 schemaname |     tablename     |           indexname            | tablespace |                                          indexdef
------------+-------------------+--------------------------------+------------+--------------------------------------------------------------------------------------------
 icw        | underground_route | underground_route_the_geom_idx |            | CREATE INDEX underground_route_the_geom_idx ON icw.underground_route USING gist (the_geom)
(1 row)

我查询查找 500 米内的所有详细信息:

select unique_id, st_astext(the_geom) as Geom, 
  construction_status, 
  unique_id as name, 
  u_id11 as surface_material, 
  u_id12 as surrounding_material, 
  sw_uid22 as undergound_type 
from icw.underground_route 
where 
  ST_DWithin(the_geom,ST_GeomFromText('POINT('||28.51104||' '|| 77.09295399999996||')'),500, false) order by unique_id;

此查询需要大约 5995.856 毫秒来提供详细信息，这花费的时间太长了。

         QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=827467.77..827526.33 rows=23422 width=182) (actual time=5995.826..5995.826 rows=0 loops=1)
   Output: unique_id, (st_astext(the_geom)), construction_status, unique_id, u_id11, u_id12, sw_uid22
   Sort Key: underground_route.unique_id
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on icw.underground_route  (cost=0.00..825767.86 rows=23422 width=182) (actual time=5995.818..5995.818 rows=0 loops=1)
         Output: unique_id, st_astext(the_geom), construction_status, unique_id, u_id11, u_id12, sw_uid22
         Filter: (((underground_route.the_geom)::geography && '0101000020E6100000A6F27684D3823C40108255F5F2455340'::geography) AND ('0101000020E6100000A6F27684D3823C40108255F5F2455340'::geography && _st_expand((underground_route.the_geom)
::geography, '500'::double precision)) AND _st_dwithin((underground_route.the_geom)::geography, '0101000020E6100000A6F27684D3823C40108255F5F2455340'::geography, '500'::double precision, false))
         Rows Removed by Filter: 1756678
 Planning time: 0.319 ms
 Execution time: 5995.856 ms

 select * from pg_stat_user_tables where relname='underground_route';
-[ RECORD 1 ]-------+---------------------------------
relid               | 1121827
schemaname          | icw
relname             | underground_route
seq_scan            | 3075
seq_tup_read        | 5389469844
idx_scan            | 34272
idx_tup_fetch       | 275507552
n_tup_ins           | 21
n_tup_upd           | 508
n_tup_del           | 5
n_tup_hot_upd       | 63
n_live_tup          | 1757407
n_dead_tup          | 394
n_mod_since_analyze | 20
last_vacuum         | 2019-05-01 15:51:05.254495+05:30
last_autovacuum     |
last_analyze        | 2019-05-06 13:23:09.343957+05:30
last_autoanalyze    |
vacuum_count        | 3
autovacuum_count    | 0
analyze_count       | 6
autoanalyze_count   | 0

为什么创建索引后仍然使用顺序扫描？我应该怎么做才能加快查询速度？我是否必须调整任何性能参数才能获得索引扫描而不是顺序扫描？

Answer 1

你的问题是ST_DWithin的第四个参数:

\df st_dwithin
                                                           List of functions
 Schema |    Name    | Result data type |                                  Argument data types                                  | Type 
--------+------------+------------------+---------------------------------------------------------------------------------------+------
 public | st_dwithin | boolean          | geography, geography, double precision                                                | func
 public | st_dwithin | boolean          | geography, geography, double precision, boolean                                       | func
 public | st_dwithin | boolean          | geom1 geometry, geom2 geometry, double precision                                      | func
 public | st_dwithin | boolean          | rast1 raster, nband1 integer, rast2 raster, nband2 integer, distance double precision | func
 public | st_dwithin | boolean          | rast1 raster, rast2 raster, distance double precision                                 | func
 public | st_dwithin | boolean          | text, text, double precision                                                          | func
(6 rows)

ST_DWithin 的版本具有第四个boolean 参数(use_spheroid)将geography 作为参数。

这意味着当函数被调用时，您的geometry 被转换为geograhy。你可以在执行计划中看到:

(underground_route.the_geom)::geography

但是索引没有在那个表达式上定义，所以不能使用。

您必须像这样创建索引:

CREATE INDEX ON gis USING gist((g::geography));