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.

My query:

DROP TABLE IF EXISTS tmp;
CREATE TEMP TABLE tmp AS SELECT *, ST_BUFFER(the_geom::GEOGRAPHY, 3000)::GEOMETRY AS buffer FROM af_modis_master LIMIT 20000;
CREATE INDEX idx_tmp_the_geom ON tmp USING gist(buffer); 
EXPLAIN SELECT (DUMP(ST_UNION(buffer))).path[1], (DUMP(ST_UNION(buffer))).geom FROM tmp;

Output from EXPLAIN:

Aggregate  (cost=1705.52..1705.54 rows=1 width=32)
  ->  Seq Scan on tmp  (cost=0.00..1625.01 rows=16101 width=32)

Seq Scan means it is not using the index, right? Why not?

UPDATE: Even adding a where clause that filters based on the buffer causes a Seq Scan:

ANALYZE tmp;
EXPLAIN SELECT (DUMP(ST_UNION(buffer))).path[1], (DUMP(ST_UNION(buffer))).geom FROM tmp WHERE ST_XMIN(buffer) = 0.0;

(This question was also posted here. Apologies for cross-posting but the community there is a bit more active (maybe just on PostGIS query topics?), so perhaps wil provide an answer quicker.)

share|improve this question
    
don't cross post: stackoverflow.com/questions/14875901/… –  Mike T Feb 16 '13 at 23:05
    
Yeah, sorry, I did apologize in the StackOverflow question. It seemed that this community is less active than the StackOverflow community, maybe just on PostGIS query topics? –  Ries Feb 18 '13 at 8:10
1  
No biggie. Generally, PostGIS questions do well at gis.SE, while pure SQL questions are better put to SE. This one fits in between. –  Mike T Feb 18 '13 at 12:35
add comment

1 Answer

An index is used to filter a query using the "WHERE" part of the statement. A GiST index is not used by ST_Union. Without a "WHERE" part, then no filtering (or index) is required to return the result, and the query just chugs through all the rows in the query.

As described in the manual, not all functions make use of indicies, for example ST_Distance and ST_XMin don't benefit from a spatial index. Other spatial functions make use of a GiST index if the query would benefit from it. For example, with my test data:

EXPLAIN SELECT (ST_Dump(ST_Union(buffer))).path[1], (ST_Dump(ST_Union(buffer))).geom FROM tmp
WHERE ST_DWithin(buffer,  ST_MakeEnvelope(90900, 190900, 100100, 200100), 100);

Aggregate  (cost=8.53..8.55 rows=1 width=2272)
  ->  Index Scan using idx_tmp_the_geom on tmp  (cost=0.00..8.52 rows=1 width=2272)
        Index Cond: (buffer && '0103000000010000000500000000000000002BF64000000000804A074100000000002BF6400000000040700841000000008076F8400000000040700841000000008076F84000000000804A074100000000002BF64000000000804A0741'::geometry)
        Filter: (('01030000000100000005000000000000004031F64000000000A04D0741000000004031F64000000000206D0841000000004070F84000000000206D0841000000004070F84000000000A04D0741000000004031F64000000000A04D0741'::geometry && st_expand(buffer, 100::double precision)) AND _st_dwithin(buffer, '01030000000100000005000000000000004031F64000000000A04D0741000000004031F64000000000206D0841000000004070F84000000000206D0841000000004070F84000000000A04D0741000000004031F64000000000A04D0741'::geometry, 100::double precision))
share|improve this answer
    
Please see my update. Btw, if you want I can close/delete this question? I wonder if StackExchange has considered a way to post a question in multiple "communities" ie sites so that questions that are relevant to multiple communities do not have to be cross-posted in order to get wider attention? –  Ries Feb 18 '13 at 8:29
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.