Postgresql gist index ignored for st_dwithin

1
votes

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:

enter image description here


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.

1
postgresqlindexingpostgis
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
Thanks for taking the time to answer, I have already answered that, it works. Cheers! – Theo Stefou

1 Answers

1
votes

Turns out I should have created the index like so:

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