Postgis can't find the optimum way, seems to be always using Btree index rather than spatial index

Question

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

Answer 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;