Re: Search speed issues

"Charley L.Tiggs" <ctiggs(at)xpressdocs(dot)com> writes:
> SELECT (first_name || ' ' || last_name) AS name,
> (house_number || ' ' || fract::varchar || ' ' ||
> street_prefix_dir::varchar || ' ' || street_name || ' ' ||
> street_suffix::varchar || ' ' || street_post_dir::varchar) AS address,
> city,
> state,
> zip
> FROM address
> WHERE latitude >= $minLat AND
> latitude <= $maxLat AND
> longitude >= $minLong AND
> longitude <= $maxLong
> ORDER BY ((abs($property_long - longitude)) * 1000) +
> ((abs($property_lat - latitude)) * 1000)
> LIMIT 100

Seems like a two-column index on (latitude, longitude) is what you need.

You could get fancy and try mucking about with box overlap operators and
rtree indexes, but I suspect the 2-column btree will work well enough.

regards, tom lane