4

I am using the Postgis ST_DWithin function like this:

SELECT *,
    ST_X(geometry) as longitude, 
    ST_Y(geometry) as latitude,
    ST_Distance_Sphere(
        ST_SetSRID(
            ST_MakePoint(-118.478255,34.303046999999999),
            4326),
        geometry) / 1609.34 as DISTANCE 
FROM  poi 
WHERE ( category =10980400 ) 
    AND ST_DWithin(geometry::geography,
        (ST_SetSRID(
            ST_MakePoint(-118.478255,34.303046999999999),
            4326)::geography
        ),
        80467.2,
        false)
ORDER BY DISTANCE
LIMIT 10

I am using it for finding nearest points (like ATMs, restaurants) within a radius.

I have indexes available for geometry, geography and a btree index for category. When I "explain analyze" the query I can see that the query only uses category index. Not sure if it happens always or is it a runtime decision.

The problem is that there are cases where the query performs better if I remove category index and the query uses spatial index.

Similarly, there are cases when I have all indexes and the query is better by using category index. But then the cases above degrade.

Why can't postgis decide the optimum way ?? I have run VACUUM ANALYZE on the table after having all the indexes.


SELECT version() : "PostgreSQL 9.3.14"
SELECT postgis_version() : "2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"

Here are the indexes I have:

CREATE INDEX world_poi_idx4
  ON public.poi
  USING gist
  ((geometry::geography));

CREATE INDEX world_poi_new_idx3
  ON public.poi
  USING gist
  (geometry);

 CREATE INDEX world_poi_idx1
  ON public.poi
  USING btree
  (category);

Here are the results of explain analyze (example of just one case, results are different in other cases, this is just to provide an idea):

When category index is present: https://explain.depesz.com/s/iZWj

When category index is not present: https://explain.depesz.com/s/0WH

  • 2
    I would make a try by converting geometries into geographies physically instead of using casts. – user30184 Jan 12 '17 at 18:20
  • PostgreSQL does not support hinting, so if you have a better idea than the optimizer, you should structure the query so it can't make an unwise choice. – Vince Jan 12 '17 at 23:58
  • could you paste the explain analyze if you're going to reference it and ask performance advice.. and also paste the indexes on the table. – Evan Carroll Jan 13 '17 at 6:43
  • What version of postgis are you using? – Evan Carroll Jan 13 '17 at 6:54
  • @Vince, the issue is that some cases perform better by category index and some by geography. By the way, what kind of structuring will help me. Can you please provide an example ? – Rahul Bansal Jan 13 '17 at 9:28
1

Assuming you don't have to reproject, try altering the geometry into geography,

    ALTER TABLE poi ALTER COLUMN geometry RENAME TO geog;
    ALTER TABLE poi ALTER COLUMN geog TYPE geography;

Now that I can see what you're doing.

  1. Let's optimize the ST_SetSRID(ST_MakePoint()) and see if that helps the planner
  2. Let's switch up ST_Distance_Sphere to ST_Distance. You already have an index on the cast anyway .. try both

Try this..

SELECT ST_X(geometry) as long2,
  ST_Y(geometry) AS lat2,
  ST_Distance( geometry::geography, point.p, false ) AS distance,
FROM ( VALUES
  (ST_SetSRID(
    ST_MakePoint(-118.478255,34.303046999999999),
    4326
  )::geography)
) AS point(p)
CROSS JOIN poi
WHERE category = 10980400
  AND ST_DWithin( point.p, geometry, 80467.2, false )
ORDER BY distance
LIMIT 10;

Further optimization with <->

Also, from the docs on <-> you can see this method suggested.

9.5+ with KNN <->

You're only doing ST_Distance() on 10 points with this method. I certainly would calculate it on the sphereoid and not the sphere.

SELECT ST_X(geometry) as long2,
  ST_Y(geometry) AS lat2,
  ST_Distance( geometry::geography, point.p, false ) AS distance,
FROM ( VALUES
  (ST_SetSRID(
    ST_MakePoint(-118.478255,34.303046999999999),
    4326
  )::geography)
) AS point(p)
CROSS JOIN poi
WHERE category = 10980400
  AND ST_DWithin( point.p, geometry, 80467.2, false )
ORDER BY geometry::geography <-> point.p
LIMIT 10;

Pre-9.5 hybrid hack optimization

You're only doing ST_Distance() on 100 points with this method. I certainly would calculate it on the spheroid and not the sphere.

WITH t AS (
  SELECT ST_X(geometry) as long2,
    ST_Y(geometry) AS lat2,
    ST_Distance( geometry::geography, point.p, false ) AS distance,
  FROM ( VALUES
    (ST_SetSRID(
      ST_MakePoint(-118.478255,34.303046999999999),
      4326
    )::geography)
  ) AS point(p)
  CROSS JOIN poi
  WHERE category = 10980400
    AND ST_DWithin( point.p, geometry, 80467.2, false )
  ORDER BY geometry::geography <-> point.p
  LIMIT 100
)
SELECT * FROM t
ORDER BY distance
LIMIT 10;
  • 1
    Answers should not have questions in them. Sometimes you can caveat a question as an assumption, but this is a litany of questions without an answer, and therefore should be one or more comments. – Vince Jan 13 '17 at 11:47
  • The admins here don't close any questions. It's a problem with them. On a normal stack exchange, the question would be closed until details would be provided and then reopened. – Evan Carroll Jan 13 '17 at 17:14
  • @EvanCarroll, KNN has an issue. See my question: gis.stackexchange.com/questions/224404/… . Also, any thoughts on why Postgis sometimes/always choose to go via category index when the spatial route can perform faster in certain cases ? – Rahul Bansal Jan 13 '17 at 20:21
  • Yes. the query planner thinks there are 21 million rows, and you're returning only 6 in poi_nr_world_poi. Try ANALYZE. If that doesn't work, try increasing analytics to 1000 and try ANALYZE again. If that doesn't work come back. But, to be honest you probably need paid consultation at some point and access to the box. – Evan Carroll Jan 13 '17 at 20:34

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.