Why doesn't my spatial index work?

Question

I have a table with 50 million test points. The points are stored as latitude / longitude with SRID = 4326. The create script for the table:

CREATE TABLE points
(
  name character varying,
  point geometry(Geometry,4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE points
  OWNER TO postgres;

CREATE INDEX idxpointspoint
  ON points
  USING gist
  (point);

When I run the SQL included below, the resultset includes 500 rows and takes 47 seconds. The time is consistent whether or not I have an index on the geometry column.

SELECT name, point FROM points WHERE ST_Distance(ST_Transform(ST_GeomFromText('POINT(LONG LAT)',4326),XXXX), ST_Transform(point,XXXX)) <= 1000.0;

I ran VACUUM ANALYZE points; but no results were returned and it took 1400 ms.

When I prefix the SQL query with EXPLAIN, I get the following:

"Seq Scan on points  (cost=0.00..15020113.70 rows=17596460 width=56)"
"  Filter: (st_distance('0101000020FF0B0000B47EAC032D6621412086FD5737A81141'::geometry, st_transform(point, XXXX)) <= 1000::double precision)"

How do I troubleshoot this and get the spatial index to work?

Answer 1

If you have global data, use a geography type:

CREATE TABLE points
(
  gid serial primary key,
  name character varying,
  point geography(Point,4326)
);
CREATE INDEX points_point_idx ON points USING gist (point);

Then use a function that can use the spatial index, using a metric distance (see ST_DWithin)

SELECT name, point
FROM points, (SELECT ST_MakePoint(LONG, LAT)::geography AS poi) AS poi
WHERE ST_DWithin(point, poi, 1000.0);

Nested Loop  (cost=0.14..8.45 rows=1 width=64)
  ->  Result  (cost=0.00..0.01 rows=1 width=0)
  ->  Index Scan using points_point_idx on points  (cost=0.14..8.42 rows=1 width=64)
  ...

If you don't have global data (i.e., it is all within one region), then use a geometry type with a local projection system for that region.