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;
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?