One of the features of the Python/Django/PostgreSQL web application I'm building is that a user will be able to see the 100 other users who are nearest to them, sorted by distance. I'm using PostGIS 2.1 and its "<->" operator for doing the distance ordering and nearest neighbor limits. However, I'm wondering if I can make my query any faster since this will probably be the most often-run query in my application.
Here's the table:
CREATE TABLE user_account(
id SERIAL PRIMARY KEY,
name VARCHAR(64),
address VARCHAR(64),
city VARCHAR(24),
region VARCHAR(18),
postal_code VARCHAR(10),
country VARCHAR(2),
lon DOUBLE PRECISION NOT NULL CHECK(lon > -180 and lon <= 180),
lat DOUBLE PRECISION NOT NULL CHECK(lat > -90 and lat <= 90),
location GEOMETRY(POINT, 4326) -- PostGIS geom field with SRID 4326
);
I've also created an index on the location geometry field:
CREATE INDEX user_account_idx ON user_account USING GIST(location);
VACUUM ANALYZE user_account (location);
Here's the query:
EXPLAIN ANALYZE
SELECT name, city,
ST_Distance(location::geography, 'SRID=4326;POINT(-118.240584 34.072918)'::geography)/1000 as dist_km
FROM user_account
ORDER BY location <-> 'SRID=4326;POINT(-118.240584 34.072918)' limit 100;
Here's the explanation:
Limit (cost=55.55..55.80 rows=100 width=244) (actual time=0.351..0.380 rows=7 loops=1)
-> Sort (cost=55.55..55.92 rows=150 width=244) (actual time=0.347..0.356 rows=7 loops=1)
Sort Key: ((location <-> '0101000020E610000037FC6EBA658F5DC016A5846055094140'::geometry))
Sort Method: quicksort Memory: 25kB
-> Seq Scan on user_account (cost=0.00..50.12 rows=150 width=244) (actual time=0.235..0.313 rows=7 loops=1)
Total runtime: 0.430 ms
(6 rows)
The reason I'm concerned about speed is that when I ran EXPLAIN ANALYZE on this query with only seven rows in my table, it took a little over .400 milliseconds. It would appear that having to cast the result to the "geography" type is having a big impact because the time drops to around .185 milliseconds if I omit the cast and get the results in degrees. But degrees aren't useful when you need to calculate a distance. Can I make this query any faster while still calculating a distance? I've read similar questions here and didn't see anything useful.
Thanks for your advice!