6

I am doing a local statistic, similar to the the one described in an earlier question / answer:

SELECT a.tree_id, a.species, avg(b.age) as age_avg, count(*) as samples, a.geom
FROM trees a LEFT JOIN trees b
ON ST_DWithin(a.geom, b.geom, 100) AND a.species = b.species
WHERE a.age IS NULL
GROUP BY a.tree_id, a.species, a.geom;

This finds all trees of the same species in a radius of 100 meters. This works quite nicely for small datasets with few missing data points (WHERE a.age IS NULL).

However, when I run the query for a larger dataset with more missing data, it gets very slow (i.e. several hours / days). In this case 6000 of of a total of 200000 points have no value (a.age).

Do you see a way to increase the speed of the query? Maybe an alternative function to st_dwithin is helpful?

UPDATE - EXPLAIN ANALYZE says:

HashAggregate (cost=2721694211.11..2721694280.52 rows=6941 width=26) (actual time=12571.761..12571.774 rows=8 loops=1)

Group Key: a.id, a.age, a.species

-> Nested Loop Left Join (cost=0.00..2721694141.70 rows=6941 width=26) (actual time=655.504..12570.495 rows=167 loops=1) Join Filter: ((a.geom && st_expand(b.geom, 300::double precision)) AND (b.geom && st_expand(a.geom, 300::double precision)) AND _st_dwithin(a.geom, b.geom, 300::double precision)) Rows Removed by Join Filter: 11210316

-> Seq Scan on trees a (cost=0.00..251349.76 rows=6941 width=54) (actual time=213.037..1006.055 rows=8 loops=1) Filter: ((age IS NULL)) Rows Removed by Filter: 1401302

-> Materialize (cost=0.00..254946.52 rows=1438701 width=36) (actual time=0.004..326.335 rows=1401310 loops=8)

-> Seq Scan on trees b (cost=0.00..247753.01 rows=1438701 width=36) (actual time=0.011..1490.796 rows=1401310 loops=1)

Planning time: 0.186 ms Execution time: 12597.034 ms

3
  • 1
    Can you add the output from explain analyse?
    – mlinth
    Dec 2, 2015 at 10:56
  • Couple of thoughts. Why are you using a LEFT JOIN? Won't INNER JOIN work? You're also counting each tree as being within 100m of itself... Finally, what happens when b.age is null - do you want to exclude them as well?
    – mlinth
    Dec 2, 2015 at 11:01
  • @mlinth Thanks for your thoughts! INNER JOIN and excluding b.age IS NULL yield no speed increase :(
    – n1000
    Dec 2, 2015 at 16:09

1 Answer 1

9

The explain doesn't show an index coming into play, which could be for two reasons:

  • You don't have one. So make one with CREATE INDEX tree_gix ON trees USING GIST (geom)
  • Your data is in geographic coordinates, so your spatial join isn't really doing anything selective (it's joining every tree to all other trees, every time). In that case, either (a) change to using the geography type or (b) move your data to an appropriate planar projection (I recommend (b)).

Finally, yeah, the LEFT JOIN is not doing anything useful for you unless there are lonely trees with no partners in the radius you need to keep in the result set. I'd remove the geometric and species GROUP BY as well, since you already have a unique id in there, the other variables are just noise.

SELECT 
  a.tree_id, a.species, avg(b.age) as age_avg, 
  count(*) as samples, a.geom
FROM trees a 
JOIN trees b
ON ST_DWithin(a.geom, b.geom, 100) AND a.species = b.species
WHERE a.age IS NULL
GROUP BY a.tree_id;

Other than the first two possible errors above, the query itself looks pretty neat and clean.

5
  • 1
    OMG! I almost have tears in my eyes... Your first hint brought my query time from >20 hours to 22 seconds!! Obviously I have to learn more about spatial indices! I always assumed PostGIS would take care of them automatically...?
    – n1000
    Dec 2, 2015 at 16:30
  • 2
    No more than PostgreSQL takes care of non-spatial indexes automatically. There's a cost to indexing (disk space, insert/update performance, locking) and it's up to the DB user to decide when the trade-off makes sense. Glad it worked for you! :) Dec 5, 2015 at 14:07
  • WOW, thanks for this answer! The index was incredible!
    – Sultanen
    Jan 31, 2021 at 21:56
  • @PaulRamsey I am also experiencing a rather slow query time using ST_DWithin. I have an index set though! My geometry is in EPSG:3857. Would you recommend an other projection? I amkind of a gis noob, so would be great if you could tell me a epsg number more suitable for that kind of query.
    – Georg
    May 30 at 15:18
  • @PaulRamsey edit to my previous reply. What I need to figure out is how many line-strings are within a certain distance (around 10m) to my point. My query currently takes around 120ms (while having around 400.000 linestrings). But I need to make a LOT of those queries... so bringing query time down would be very much needed.
    – Georg
    May 30 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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