Why is this postgresql query so slow?
- by user315975
I'm no database expert, but I have enough knowledge to get myself into trouble, as is the case here. This query
SELECT DISTINCT p.*
FROM points p, areas a, contacts c
WHERE ( p.latitude > 43.6511659465
AND p.latitude < 43.6711659465
AND p.longitude > -79.4677941889
AND p.longitude < -79.4477941889)
AND p.resource_type = 'Contact'
AND c.user_id = 6
is extremely slow. The points table has fewer than 2000 records, but it takes about 8 seconds to execute. There are indexes on the latitude and longitude columns. Removing the clause concering the resource_type and user_id make no difference.
The latitude and longitude fields are both formatted as number(15,10) -- I need the precision for some calculations.
There are many, many other queries in this project where points are compared, but no execution time problems. What's going on?
