ST_DWithin taking a long time (more than 6000ms)

Question

I have a table (underground_route) with 1756678 records which defines different ug routes. I want to find details within a particular radius.

I have created a GiST index underground_route_the_geom_idx for the_geom field, like this:

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)

My query to find all details within 500 meters:

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;

This query takes about 5995.856 ms to provide details, which is taking too long.

         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

Why is it still using a sequential scan, even after creating the index? What should I do to speed up the query? Do I have to tweak any performance parameters to get an index scan instead of a sequential scan?

Answer 1

Your problem is the fourth parameter of 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)

The version of ST_DWithin that has the fourth boolean parameter (use_spheroid) takes geography as arguments.

That means that your geometry gets cast to geograhy when the function is called. You can see that in the execution plan:

(underground_route.the_geom)::geography

But the index is not defined on that expression, so it cannot be used.

You would have to create the index like this:

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