1

I need to select all entries from a table, that are within a distance from a point. Each entry is a point geometry (I set a gist index on geom).

Current runtime is anywhere between 3-15 seconds, depends on how many entries are in a given area (I think), but I'm trying to improve that to under 3sec.

I've seen a few posts on different ways to speed this up, including using ST_DWithin but it does not seem to improve performance, neither does casting to geography.

Any ideas how to proceed?

Here's a sample query:

SELECT  * 
FROM    T1 
WHERE   entry_date >= current_date - interval '1' month
  AND   ST_DistanceSphere(geom, ST_MakePoint(-74.00072343, 40.7209235)) <= 1000;
Improve this question
5
  • The distance functions are not index-optimized. Instead, use ST_DWithin with a covering index (see gis.stackexchange.com/questions/247113/…) – Vince Jan 22 '20 at 16:33
  • In fact, I've tried using ST_DWithin but it's taking longer (although geog is indexed). ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(-74.00072343, 40.7209235), 4326)::geography, 1000). Could it be because I'm "making point" for each iteration? Should this point be created once, and accessed after that? – guyts Jan 22 '20 at 17:05
  • 1
    I'd reverse the order of parameters 1 & 2 in the DWithin. I'd also look into reversing the WHERE constraint order (spatial then date). You should always include the query plan from EXPLAIN with optimization questions, along with the indexes reported by pgAdmin for the table. – Vince Jan 22 '20 at 17:54
  • The problem is more likely to be that the entry_date >= current_date - interval '1' month is not using an index, due to the minus interval part. You can fix this by using an expression index. Please, post the EXPLAIN output from this query, which will determine what the query parser plan looks like. – John Powell Jan 23 '20 at 15:00
  • You other issue is that if you construct a geometry, using say ST_SetSRID, before passing to ST_DWithin, the index will also not be used. You should run ST_SetSRID first, as it is generally considered back practice not to set a SRID explicitly. – John Powell Jan 23 '20 at 15:03
-1

Here are two things that will probably improve the performance:

1: Store The point you're using in a new Table like so:

CREATE TABLE temp AS 
SELECT ST_MakePoint(-74.00072343, 40.7209235) geom;

2: Use ST_DWithin:

SELECT T1.*
T1, temp
WHERE
entry_date >= current_date - interval '1' month AND 
ST_DWithin(T1.geom, temp.geom, 1);

Also note that since you're using geographic coordinates, the distance has to be specified in degrees.

Improve this answer
2
  • You should always use ST_SetSRID with ST_MakePoint; the distance can be specified in meters if the geometry is cast to geography and an index on the cast is added. – Vince Jan 22 '20 at 16:43
  • 1
    This is quite categorically not the answer. It unnecessarily wastes space. Updating the geometry SRID is much cleaner and more space efficient. The other issue is that the time function has a subtraction in, another no no, as far as the query planner is concerned. – John Powell Jan 23 '20 at 15:04

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.