Why is this postgresql query so slow?

Question

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?

Answer 1

Did you forget something from your actual query? It's missing ANSI-89 joins between the three tables, giving you a cartesian product but only pulling out the POINTS records.

Answer 2

You're joining three tables, p, a, and c, but you aren't specifying how to attach them together. What you're getting is a full Cartesian join between all of the rows in all of the tables that match the criteria, then everything in areas.

You probably want to attach something in points to something in areas. And something in contacts with ... well, I don't know what your schema looks like.

Try sticking an "EXPLAIN" at the beginning for information on what's happening.

Answer 3

Probably you are missing the joins. Joining the table would be something like this.

SELECT DISTINCT p.* 
  FROM points p
  JOIN areas a p ON  a.FkPoint = p.id
  JOIN contacts c ON c.FkArea = a.id
 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

For better indexes on coordinates use Quadtree or R-Tree index implementation.

If you intentionally did not miss the joins, try a subquery like this.

select DISTINCT thePoints.*
(   
    SELECT DISTINCT p.* 
    FROM points p
    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' 
) as thePoints
, areas, contacts
WHERE  c.user_id = 6

Answer 4

You need a rtree index and use the @ operator, normal index won't work.

R-Tree http://www.postgresql.org/docs/8.1/static/indexes-types.html

@ operator http://www.postgresql.org/docs/8.1/static/functions-geometry.html