3

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?

  • 3
    I would suggest trying ST_DWithin and you may want to store your points as geography rather than geometry. That would mean that you wouldn't need the ST_Transforms? – MickyT Apr 10 '15 at 2:49
  • 1
    Your index isn't being used because you aren't generating a query that can use it, even without the ST_Transform to slow things down. – Vince Apr 10 '15 at 3:05
  • You can put an index on result of ST_Transform(point,xxx) and then an index will be used. – Jakub Kania Apr 10 '15 at 9:17
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.