Обсуждение: Question about query planner

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

Emil Briggs <emil@baymountain.com> writes:
> 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?

Exactly.

> 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;

The system has to fetch all the rows of a, because any of them might
join to a row of i matching the i.contact_num condition, and conversely
it has to fetch every row of i because any of them might join to a row
of a matching one of the phone conditions.  It is therefore necessary
to effectively form the entire join of a and i; until you've done that
there is no way to eliminate any rows.

I'm a bit surprised that it's using the indexes at all --- a hash join
with seqscan inputs would probably run faster.  Try increasing work_mem
a bit.

            regards, tom lane