My query:

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:

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

  • don't cross post: stackoverflow.com/questions/14875901/…
    – Mike T
    Commented Feb 16, 2013 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
    Commented Feb 18, 2013 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
    Commented Feb 18, 2013 at 12:35

1 Answer 1


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))
  • 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
    Commented Feb 18, 2013 at 8:29

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.