To: emil@baymountain.com
cc: pgsql-performance@postgresql.org
Subject: Re: Question about query planner 
In-reply-to: <200602190858.13081.emil@baymountain.com> 
References: <200602190858.13081.emil@baymountain.com>
Comments: In-reply-to Emil Briggs <emil@baymountain.com>
	message dated "Sun, 19 Feb 2006 08:58:12 -0500"
Date: Sun, 19 Feb 2006 13:15:07 -0500
Message-ID: <11959.1140372907@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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