Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.net/docs/using_postgis_dbmanagement.html#id558306
  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
share|improve this question

migrated from stackoverflow.com Aug 30 '11 at 13:15

This question came from our site for professional and enthusiast programmers.

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 '11 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. –  hekevintran Aug 30 '11 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 '11 at 12:47
add comment

1 Answer 1

up vote 5 down vote accepted

Hekevintran,

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.

e.g.


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

share|improve this answer
    
I dropped the geometry constraints, converted the geometry columns to geography columns, and everything is working great. Thanks. –  hekevintran Aug 31 '11 at 13:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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