The following query runs much slower than I would have expected. I ran it
through EXPLAIN ANALYZE (results included after) and I don't understand why
the planner is doing what it is. All of the columns from the WHERE part of
the query are indexed and the indexes are being used. The number of rows
being reported is equal to the size of the table though so it's really no
better than just doing a sequential scan. This is running on Postgres 8.0.7
and the system has been freshly vaccumed with the statistics target set to
800. Does any know why the query behaves like this? Does it have anything to
do with the OR statements in the where clause spanning two different tables?
I tried an experiment where I split this into queries two queries using UNION
and it ran in less than 1 ms. Which is a solution but I'm still curious why
the original was so slow.
SELECT DISTINCT a.account_id, l.username, a.status, a.company, a.fax_num,
a.primary_phone, a.responsible_first, a.responsible_last FROM
accounts a, logins l, supplemental_info i
WHERE l.account_id=a.account_id and
i.account_id=a.account_id and
((a.primary_phone = 'xxx-xxx-xxxx') OR (a.alternate_phone = 'xxx-xxx-xxxx')
OR (i.contact_num = 'xxx-xxx-xxxx'))
ORDER BY a.status, a.primary_phone, a.account_id;
EXPLAIN ANALYZE results
Unique (cost=47837.93..47838.02 rows=4 width=92) (actual
time=850.250..850.252 rows=1 loops=1)
-> Sort (cost=47837.93..47837.94 rows=4 width=92) (actual
time=850.248..850.248 rows=1 loops=1)
Sort Key: a.status, a.primary_phone, a.account_id, l.username,
a.company, a.fax_num, a.responsible_first, a.responsible_last
-> Nested Loop (cost=0.00..47837.89 rows=4 width=92) (actual
time=610.641..850.222 rows=1 loops=1)
-> Merge Join (cost=0.00..47818.70 rows=4 width=88) (actual
time=610.602..850.179 rows=1 loops=1)
Merge Cond: ("outer".account_id = "inner".account_id)
Join Filter: ((("outer".primary_phone)::text =
'xxx-xxx-xxxx'::text) OR (("outer".alternate_phone)::text =
'xxx-xxx-xxxx'::text) OR (("inner".contact_num)::text =
'xxx-xxx-xxxx'::text))
-> Index Scan using accounts_pkey on accounts a
(cost=0.00..18423.73 rows=124781 width=95) (actual time=0.019..173.523
rows=124783 loops=1)
-> Index Scan using supplemental_info_account_id_idx on
supplemental_info i (cost=0.00..15393.35 rows=124562 width=24) (actual
time=0.014..145.757 rows=124643 loops=1)
-> Index Scan using logins_account_id_idx on logins l
(cost=0.00..4.59 rows=2 width=20) (actual time=0.022..0.023rows=1 loops=1)
Index Cond: ("outer".account_id = l.account_id)
Total runtime: 850.429 ms
