Re: Search speed issues

"Charley L.Tiggs" <ctiggs@xpressdocs.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