EXPLAIN (ANALYZE, BUFFERS) SELECT DISTINCT locations.id FROM pois, locations WHERE pois.poi_kind_id = 3 AND ST_DWithin(pois.coordinates, locations.coordinates, 500, FALSE);

gistfile1.txt

 Unique  (cost=2407390.71..2407390.72 rows=2 width=4) (actual time=3338.080..3338.252 rows=918 loops=1)
   Buffers: shared hit=559
   ->  Sort  (cost=2407390.71..2407390.72 rows=2 width=4) (actual time=3338.079..3338.145 rows=963 loops=1)
         Sort Key: locations.id
         Sort Method: quicksort  Memory: 70kB
         Buffers: shared hit=559
         ->  Nested Loop  (cost=0.00..2407390.71 rows=2 width=4) (actual time=2.466..3337.835 rows=963 loops=1)
               Join Filter: (((pois.coordinates)::geography && _st_expand((locations.coordinates)::geography, 500::double precision)) AND ((locations.coordinates)::geography && _st_expand((pois.coordinates)::geography, 500::double precision)) AND _st_dwithin((pois.coordinates)::geography, (locations.coordinates)::geography, 500::double precision, false))
               Rows Removed by Join Filter: 4531356
               Buffers: shared hit=559
               ->  Seq Scan on locations  (cost=0.00..791.68 rows=24168 width=36) (actual time=0.005..3.100 rows=24237 loops=1)
                     Buffers: shared hit=550
               ->  Materialize  (cost=0.00..10.47 rows=187 width=32) (actual time=0.000..0.009 rows=187 loops=24237)
                     Buffers: shared hit=6
                     ->  Seq Scan on pois  (cost=0.00..9.54 rows=187 width=32) (actual time=0.015..0.053 rows=187 loops=1)
                           Filter: (poi_kind_id = 3)
                           Rows Removed by Filter: 96
                           Buffers: shared hit=6
 Planning time: 0.184 ms
 Execution time: 3338.304 ms
(20 rows)