I'm trying to find the nearest locations to a certain point using 2 tables.
One contains the address and zipcodes of the locations and is about 2000
rows, the other contains zipcodes and lat, lon values and has about 1.4M
rows.
I've got indexes on the zip column of both tables and I just need to pull
the lat and lon out of the zips table for each zipcode that happens to be in
the ATMs table.
There are only about 2000 rows in ATMs, and since both the atms.zip and
zips.zip are indexed, I'm not sure why a seq scan is being performed.
The select is taking anywhere from 30secs to 1min. it's running on a linux
box w/2 pIII/700s and a raid ..so the machine shouldn't be slowing me down.
I think it's the seq scan but I can't seem to get rid of it.
=> explain select ( point(32.85, -94.55) <@> point(y.lat, y.lon) ) as
distance, x.zip, y.zip, y.lat, y.lon from atms x, zips y where x.zip = y.zip
order by 1 limit 3;
NOTICE: QUERY PLAN:
Sort (cost=39164156.66..39164156.66 rows=32338349 width=40) -> Nested Loop (cost=0.00..30401394.25 rows=32338349
width=40) -> Seq Scan on zips y (cost=0.00..29558.49 rows=1401749 width=28) -> Index Scan using atms_zip
onatms x (cost=0.00..21.38 rows=23
width=12)
Any idea on how to speed this up?
Thanks,
Bryan