Why doesn't PostGIS use gist index when doing a ST_DUMP(ST_UNION)?

Question

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

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