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)

My indexes are:

CREATE INDEX geoplanet_place_bbox_id
  ON geoplanet_place
  USING gist

CREATE INDEX geoplanet_place_coords_id
  ON geoplanet_place
  USING gist

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

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

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


  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?


  1. Building Indexes for PostGIS: http://postgis.net/docs/using_postgis_dbmanagement.html#idm2459
  2. ST_DWithin: http://postgis.net/docs/ST_DWithin.html
  3. Statistics Used by the Planner: http://www.postgresql.org/docs/8.4/interactive/planner-stats.html
  • 1
    How big is your table? How much RAM do you have to work with? If it's faster to load the whole table into RAM than to do multiple reads off disk from the index, the optimizer will skip the index.
    – Sean
    Aug 30, 2011 at 15:16
  • The table size is 3517MB and the estimated row count is 5,367,530. shared_buffers is set to 512MB. The table cannot be loaded completely into RAM. Aug 30, 2011 at 17:03
  • Try not to cross post to the postgis list and here. Give some time to get an answer from one before posting to the other.
    – Sean
    Aug 31, 2011 at 12:47

2 Answers 2


Your first query is using an index just not the spatial one. See the Index Scan using geoplanet_place_pkey. So it's more efficient for it to use the id key since you are doing an ORDER by the column and your spatial filter covers the whole table.

The spatial index is not used because your ST_Expand is too big. You have a geometry but its in long lat. The expand units are therefore in long lat. My god I can't fathom how big 16093.44 degrees is. that would cover your whole table and then some so therefore the index is useless and Postgres is smart enough to realize that.

If you are going to do this use geography data type and then your 16093.44 would be in meters and valid and you wouldn't need that ST_Spheriod call.

since the ST_Dwithin geograpy call would take care of everything.


CREATE TABLE geoplanet_place
  woeid integer NOT NULL,
  "name" character varying(300) NOT NULL,
  admin_1 character varying(300),
  coords geography(POINT,4326) NOT NULL,
  bbox geography(POLYGON,4326) NOT NULL

and then instead of ST_GeomFromText use ST_GeogFromText

  • I dropped the geometry constraints, converted the geometry columns to geography columns, and everything is working great. Thanks. Aug 31, 2011 at 13:44
  • Since 16000 is much greater than 360, I think we would have to assume it encompasses the entire Earth (if the behavior is even defined for a degree that large).
    – jpmc26
    Nov 5, 2014 at 2:14
  • Thank you. My index was only used by changing the type to geography and this reduced the request time by factor 5. Jan 26 at 15:07

I had a similar problem, but with a different solution: I simply needed to ANALYZE the table in question. This was following a fresh restore of the table on a new database, so the query planner did not have statistics and so it didn't know it should use the index and defaulted to a sequential scan instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.