Hello.
I'm trying to figure out why a query I'm doing is incredibly slow (~10
minutes.) The incredibly slow query is something like:
SELECT count(*) from registration LEFT JOIN person USING (person_id)
WHERE x_program(registration.x_type_code) = 'blah';
The person view is quite big (~69000 rows). I don't actually want to
return the count, but I've been using that for testing purposes as it
has a similar response time to returning the fields needed.
Queries related to this, like:
A) SELECT count(*) from registration LEFT JOIN person USING (person_id);
and
B) SELECT count(*) from registration WHERE
x_program(registration.x_type_code) = 'blah';
are nearly instantaneous. I've run EXPLAIN, EXPLAIN VERBOSE, and EXPLAIN
ANALYZE on the query, and that hasn't helped me.
According to EXPLAIN ANALYZE, the slow query takes 709704 ms to execute,
and query A takes 1554 ms, but looking at the execution plans, query A
is predicted to take longer than the slow query.
The hold-up seems to be in a 'Nested Loop Left Join', which is only in
the plan for the slow query.
Here are the first two lines of EXPLAIN ANALYZE on the slow query:
Aggregate (cost=8969.16..8969.17 rows=1 width=0) (actual
time=709703.985..709703.987 rows=1 loops=1)
-> Nested Loop Left Join (cost=755.61..8968.29 rows=346 width=0)
(actual time=147.667..709700.553 rows=684 loops=1)
Does anybody have ideas why this is so slow?
Thank you,
Jessi
--
Jessi Berkelhammer
Downtown Emergency Service Center
Computer Programming Specialist
