In PostgreSQL using PostGIS, I want to find every point which are close enough of some other points but which also are far enough from another points series.

So here is my query :

SELECT pod.id, st_AsText(point) 
    (SELECT poi.geom FROM poi WHERE poi.sector_id IN ('39')) AS restric, 
    (SELECT poi.geom FROM poi WHERE poi.sector_id IN ('25')) AS exclu
WHERE ST_DWithin(restric.geom, pod.point, 10)
AND NOT ST_DWithin(exclu.geom, pod.point, 5)
GROUP BY pod.id

At so point my query will return me all the point I am interested in but it takes multiple minutes to answer. Whereas when I request only one of the two clauses of my query, PostgreSQL answer in no time (less than a second).

Is there a reason for that ? And is there a way to keep the execution time reasonably low ?

Here is the EXPLAIN (ANALYZE, BUFFERS) result :

Group  (cost=5061.73..5830.65 rows=409 width=37) (actual time=7726.053..7820.974 rows=596 loops=1)
  Group Key: pod.id
  Buffers: shared hit=1892516, temp read=1799 written=1799
  ->  Sort  (cost=5061.73..5062.75 rows=409 width=37) (actual time=7726.032..7794.860 rows=328949 loops=1)
        Sort Key: pod.id
        Sort Method: external merge  Disk: 14376kB
        Buffers: shared hit=1892516, temp read=1799 written=1799
        ->  Nested Loop  (cost=2.72..5043.99 rows=409 width=37) (actual time=0.773..7616.931 rows=328949 loops=1)
              Join Filter: ((NOT (poi.geom && st_expand(pod.point, '0.130650640188137'::double precision))) OR (NOT (pod.point && st_expand(poi.geom, '0.130650640188137'::double precision))) OR (NOT _st_dwithin(poi.geom, pod.point, '0.130650640188137'::double precision)))
              Rows Removed by Join Filter: 43
              Buffers: shared hit=1892516
              ->  Nested Loop  (cost=2.72..11.15 rows=1 width=37) (actual time=0.585..3.602 rows=596 loops=1)
                    Buffers: shared hit=216
                    ->  Index Scan using "POS_pkey" on pos  (cost=0.42..4.44 rows=1 width=32) (actual time=0.026..0.028 rows=1 loops=1)
                          Index Cond: ((id)::text = '6'::text)
                          Buffers: shared hit=4
                    ->  Bitmap Heap Scan on pod  (cost=2.30..6.70 rows=1 width=37) (actual time=0.552..3.399 rows=596 loops=1)
                          Recheck Cond: (point && st_expand(pos.geom, '0.653253200940685'::double precision))
                           Filter: ((pos.geom && st_expand(point, '0.653253200940685'::double precision)) AND _st_dwithin(pos.geom, point, '0.653253200940685'::double precision))
                          Rows Removed by Filter: 268
                          Heap Blocks: exact=201
                          Buffers: shared hit=212
                          ->  Bitmap Index Scan on pod_geom_gist  (cost=0.00..2.29 rows=2 width=0) (actual time=0.404..0.404 rows=864 loops=1)
                                Index Cond: (point && st_expand(pos.geom, '0.653253200940685'::double precision))
                                Buffers: shared hit=11
              ->  Seq Scan on poi  (cost=0.00..4922.41 rows=409 width=32) (actual time=0.047..12.350 rows=552 loops=596)
                    Filter: ((sector_id)::text = '25'::text)
                    Rows Removed by Filter: 139241
                    Buffers: shared hit=1892300
Planning time: 0.650 ms
Execution time: 7823.665 ms 
  • 3
    Please post the EXPLAIN (ANALYZE, BUFFERS) output – JGH Sep 12 '18 at 13:22
  • I added the EXPLAIN (ANALYZE, BUFFERS) outuput to the original post – Louis Sep 13 '18 at 8:29

The plan indicates

Sort Key: pod.id
Sort Method: external merge  Disk: 14376kB

The second line indicates that there is not enough memory (work_mem) available, so the sorting cannot be done in memory and must be written to disk, which is slow (unless you are using an SSD, but then you wouldn't have this issue...)

The typical solution would be to tune your configuration to set work_mem to at least 14376kB, and eventually to add more physical memory.

BUT, before doing that, let's reconsider why Postgres is doing this sort: because the query has a group by clause. Why?

The original query is

SELECT pod.id, st_AsText(point) 
GROUP BY pod.id

which is invalid, as ST_AsText() is not an aggregating function..

Since you are just filtering the table content, you should already get distinct results, so you could completely remove the group by clause and the query would be much faster.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.