From: Tom Lane <tgl@sss.pgh.pa.us>
To: emil@baymountain.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Question about query planner
Date: Sun, 19 Feb 2006 13:15:07 -0500
Message-ID: <11959.1140372907@sss.pgh.pa.us> (raw)
In-Reply-To: <200602190858.13081.emil@baymountain.com>
References: <200602190858.13081.emil@baymountain.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, emil@baymountain.com
Subject: Re: Question about query planner
In-Reply-To: <11959.1140372907@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox