Re: Question about query planner

Emil Briggs <emil(at)baymountain(dot)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