Postgresql gist index ignored for st_dwithin

Question

I have a database of points (10 million) in postgresql 12 which has the postgis extension. I have created a gist index on the points column and I need to filter the points based on distance from a given point. It seems that the index is not used. I run the following explain:

EXPLAIN
SELECT actual_location
FROM geometries
WHERE ST_DWithin(ST_SetSRID(ST_MakePoint(30,30), 4326), actual_location, 100000, true);

which yields:

so it seems it only does a parallel seq scan. Am I getting something wrong here? Should I be using a different index type? When the database was populated with 1 million points, it returned results in about 1.3 seconds. With 10 million it goes to about 11-13 which is not acceptable for a user of my application to wait that much time.

Looks like you are using the geography version of st_dwithin, so it is casting your geometry to geography - your index is on geometry so can't be used . I guess you are doing that to enter a distance in metres. You could convert you geometry column to geography, or create your index on the actual_location::geography . — mlinth
– mlinth, Commented Oct 2, 2020 at 12:02
Thanks for taking the time to answer, I have already answered that, it works. Cheers! — Theo Stefou
– Theo Stefou, Commented Oct 2, 2020 at 12:27

Laurenz Albe · Accepted Answer · 2020-10-02 15:07:21Z

1

Turns out I should have created the index like so:

CREATE INDEX example_idx ON geometries USING GIST (geography(actual_location));

edited Oct 2, 2020 at 15:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Oct 2, 2020 at 12:21

Theo Stefou

6851 gold badge6 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql gist index ignored for st_dwithin

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related