[postgis-users] Why is my PostGIS query not using a spatial index?

[Kevin Tran]

I am having an issue with a spatial query that is not making use of indexes.

My schema is:

    CREATE TABLE geoplanet_place
    (
      woeid integer NOT NULL,
      "name" character varying(300) NOT NULL,
      admin_1 character varying(300),
      coords geometry NOT NULL,
      bbox geometry NOT NULL,
      CONSTRAINT geoplanet_place_pkey PRIMARY KEY (woeid),
      CONSTRAINT enforce_dims_bbox CHECK (st_ndims(bbox) = 2),
      CONSTRAINT enforce_dims_coords CHECK (st_ndims(coords) = 2),
      CONSTRAINT enforce_geotype_bbox CHECK (geometrytype(bbox) = 'POLYGON'::text OR bbox IS NULL),
      CONSTRAINT enforce_geotype_coords CHECK (geometrytype(coords) = 'POINT'::text OR coords IS NULL),
      CONSTRAINT enforce_srid_bbox CHECK (st_srid(bbox) = 4326),
      CONSTRAINT enforce_srid_coords CHECK (st_srid(coords) = 4326)
    )
    WITH (
      OIDS=FALSE
    );

My indexes are:

    CREATE INDEX geoplanet_place_bbox_id
      ON geoplanet_place
      USING gist
      (bbox);

    CREATE INDEX geoplanet_place_coords_id
      ON geoplanet_place
      USING gist
      (coords);

Query A:

    SELECT "geoplanet_place"."woeid", "geoplanet_place"."name", "geoplanet_place"."admin_1"
    FROM "geoplanet_place"
    WHERE "geoplanet_place"."bbox" && ST_Expand(ST_GeomFromText('POINT (-71.4119869999999963 41.8238720000000015)', 4326), 16093.44)
        AND ST_distance_sphere("geoplanet_place"."coords", ST_GeomFromText('POINT (-71.4119869999999963 41.8238720000000015)', 4326)) <= 16093.44
    ORDER BY "geoplanet_place"."woeid" ASC
    LIMIT 5;

Running EXPLAIN ANALYZE on Query A yields:

     Limit  (cost=0.00..56.00 rows=5 width=24) (actual time=1440.482..1451.604 rows=5 loops=1)
       ->  Index Scan using geoplanet_place_pkey on geoplanet_place  (cost=0.00..20018585.21 rows=1787420 width=24) (actual time=1440.477..1451.583 rows=5 loops=1)
             Filter: ((bbox && '0103000020E61000000100000005000000CFF6E80D6D92CFC0134548DDCE59CFC0CFF6E80D6D92CFC02BC58EC6A183CF406F13EE95034BCF402BC58EC6A183CF406F13EE95034BCF40134548DDCE59CFC0CFF6E80D6D92CFC0134548DDCE59CFC0'::geometry) AND (st_distance_sphere(coords, '0101000020E610000023D8B8FE5DDA51C0890B40A374E94440'::geometry) <= 16093.44::double precision))
     Total runtime: 1451.657 ms
    (4 rows)

I guess the plan is using the index because the query completes quickly, but it doesn't mention the index anywhere.

---

Query B is Query A rewritten to use ST_DWithin(). This function call is supposed to be equivalent to Query A's WHERE clause.  
Query B:

    SELECT "geoplanet_place"."woeid", "geoplanet_place"."name", "geoplanet_place"."admin_1"
    FROM "geoplanet_place"
    WHERE ST_DWithin("geoplanet_place"."bbox", ST_GeomFromText('POINT (-71.4119869999999963 41.8238720000000015)', 4326), 16093.44)
    ORDER BY "geoplanet_place"."woeid" ASC
    LIMIT 5;

Running EXPLAIN on Query B yields:

     Limit  (cost=1884535.96..1884535.97 rows=5 width=24)
       ->  Sort  (cost=1884535.96..1884535.98 rows=9 width=24)
             Sort Key: woeid
             ->  Seq Scan on geoplanet_place  (cost=0.00..1884535.82 rows=9 width=24)
                   Filter: ((bbox && '0103000020E61000000100000005000000CFF6E80D6D92CFC0134548DDCE59CFC0CFF6E80D6D92CFC02BC58EC6A183CF406F13EE95034BCF402BC58EC6A183CF406F13EE95034BCF40134548DDCE59CFC0CFF6E80D6D92CFC0134548DDCE59CFC0'::geometry) AND ('0101000020E610000023D8B8FE5DDA51C0890B40A374E94440'::geometry && st_expand(bbox, 16093.44::double precision)) AND _st_dwithin(bbox, '0101000020E610000023D8B8FE5DDA51C0890B40A374E94440'::geometry, 16093.44::double precision))
    (5 rows)

I didn't allow EXPLAIN ANALYZE on Query B to finish because it was taking a long time.

I ran ANALYZE to see if it would help, but nothing changed:

    ANALYZE VERBOSE geoplanet_place (bbox, coords);

Questions:

 1. Why does the result of EXPLAIN on Query A not say anything about indexes?
 2. Why does Query B result in a different query plan when it should be equivalent to Query A?
 3. What must I do so that Query B will be fast?
 4. Is there a problem with my indexes? Will changing the statistics on the geometry columns change anything?

Links:

 1. Building Indexes for PostGIS: http://postgis.refractions.net/documentation/manual-svn/using_postgis_dbmanagement.html#id2757356
 2. ST_DWithin: http://postgis.refractions.net/documentation/manual-svn/ST_DWithin.html
 3. Statistics Used by the Planner: http://www.postgresql.org/docs/8.4/interactive/planner-stats.html