0

I have around 2M rows on my PostgreSQL table with GIST index on geo_point field but while I try to use ST_Distance or ST_DWithin it is not using the index instead it is doing Parallel Seq

Indexed Field:

CREATE INDEX rental_properties_geo_point_idx ON rental_properties USING gist(geo_point);

Initial Query: with ST_Distance

EXPLAIN SELECT id
FROM rental_properties
WHERE ST_Distance(ST_Transform(rental_properties.geo_point, 26986),
ST_Transform(ST_SetSRID(ST_MakePoint(-115.74181,46.394012), 4326), 26986)) / 1000 < 5
AND rental_properties.published = true 
AND rental_properties.usd_price IS NOT NULL 
AND rental_properties.usd_price != 0 
ORDER BY distance ASC, 
rental_properties.review_score DESC, rental_properties.number_of_review DESC
LIMIT 12

Explain:

->  Parallel Seq Scan on rental_properties  (cost=0.00..23188911.68 rows=197196 width=1016)
      Filter: (published AND (usd_price IS NOT NULL) AND (usd_price <> '0'::numeric) AND ((st_distance(st_transform(geo_point, 26986), '01010000206A6900004204EC0D715A47C1D41445CDFFE54041'::geometry) / '1000'::double precision) < '5'::double precision))

Then I converted it to ST_DWithin as per the suggestion here like below but still, it is not using the Spatial Index

Converted Query: with ST_DWithin

EXPLAIN SELECT id
FROM rental_properties
WHERE 
ST_DWithin(ST_Transform(rental_properties.geo_point, 26986), 
           ST_Transform(ST_SetSRID(ST_MakePoint(-80.08713,26.237396), 4326), 26986), 5000::double precision)
AND rental_properties.published = true 
AND rental_properties.usd_price IS NOT NULL 
AND rental_properties.usd_price != 0 
ORDER BY ST_DWithin(ST_Transform(rental_properties.geo_point, 26986), 
           ST_Transform(ST_SetSRID(ST_MakePoint(-80.08713,26.237396), 4326), 26986), 5000::double precision) ASC, 
rental_properties.review_score DESC, rental_properties.number_of_review DESC
LIMIT 12

Explain:

->  Parallel Seq Scan on rental_properties  (cost=0.00..17763977.78 rows=59 width=1009)
      Filter: (published AND (usd_price IS NOT NULL) AND (usd_price <> '0'::numeric) AND st_dwithin(st_transform(geo_point, 26986), '01010000206A690000899A6DDC3A0025C1AE1388EB44622AC1'::geometry, '5000'::double precision))

Another one suggested here to convert it from geometry to geography. So my basic question is how to do a radius search to utilize the index instead of Seq scan.

2
  • Start from the simplest possible. Does select ... from ... where ... ST_Distance(ST_Transform(rental_properties.geo_point, 26986), ST_Transform(ST_SetSRID(ST_MakePoint(-115.74181,46.394012), 4326), 26986)) / 1000 < 5 utilize index? If it does not, would it use index without ST_Transforms? Wild guess is that index is used until order by and limit are added.
    – user30184
    Jan 18 at 9:18
  • I've tried with simple one but not used index, let me try without order and limt Jan 18 at 9:20