PostGIS – Window Function Not Picking Up Index

index-tuningpostgispostgresqlwindow functions

I am executing this query ST_ClusterDBSCAN with PostGIS extension and PostgreSQL. I have indexed point_data but an EXPLAIN ANALYZE shows me that the index is not being picked up. How can I rearrange my query so that it is indeed picked up?

Index:

CREATE INDEX index_point_data ON point_data USING GiST(geom)

Query: 

SELECT id, ST_ClusterDBSCAN(geom, eps := 350, minPoints := 3)
OVER () AS cluster_id, geom
FROM point_data

Plan: 

WindowAgg  (cost=0.00..148.64 rows=3984 width=44) (actual time=195.029..197.227 rows=3984 loops=1)
  ->  Seq Scan on point_data  (cost=0.00..98.84 rows=3984 width=40) (actual time=0.022..0.578 rows=3984 loops=1)
Planning time: 0.136 ms
Execution time: 197.593 ms

Best Answer

A query reading the whole table will not profit from the index. It's cheaper to read the whole table sequentially.

The spatial GiST index you display can be useful for other types of queries filtering a small percentage from the table, like:

SELECT * FROM point_data WHERE ST_DWithin($input_geometry, geom, 5000);

Understanding the decisions of the Postgres query planner requires intimate knowledge of Postgres. Start by reading this chapter of the manual and follow links from there:

And more particularly, about spatial indexes in the PostGIS manual:

Related Question