I have pretty large dataset (>10M records) with a geography points (id, latitude and longitude), and need to find closest points from this set. Data itself is not updated very often but I need quick response. And I'm looking for something fast. Unfortunately I'm not experienced GIS user.
I tried to use PostGIS with this setup. Made this schema:
CREATE TABLE place (
id int,
name varchar(100),
latitude float,
longitude float,
gpoint geometry
);
SELECT AddGeometryColumn ('public','place','gpoint',4326,'POINT',2);
UPDATE place SET geometry = ST_PointFromText('POINT(' || longitude || ' ' || latitude || ')', 4326);
CREATE INDEX idx_geo ON place USING gist(gpoint);
And tried to call data from database using this query:
SELECT * FROM place
ORDER BY gpoint <-> ST_GeogFromText('POINT(44.509648 40.184822)')
LIMIT 1;
But speed is not "close to realtime", about 30 seconds on my work laptop.
I've tried to make some optimizations: VACUUM ANALYZE, updated to latest Postgres and Postgis, use clustered table, may be something else (my stack already overloaded with all this tries). Last what I did I moved to another server with much more memory and space, but it increased speed only 2 times. Didn't tried yet to put everything in memory, but honestly I don't know yet how to make it right. Have other databases with a lot of data inside on this sever.
After all I'm not sure that I'm moving right direction. Do I use right storage and database. For some reasons I think that it is should be something trivial. But after few days searching I didn't found solution in the internet.
I want something fast. Under 1 second response time. Is it possible to get this numbers using Postgris or can you recommend to switch to something else? Since this is my pet project, so I want to use something with cheap cost of ownership.
