From: Emil Briggs <emil@baymountain.com>
Reply-To: emil@baymountain.com
Organization: Baymountain, Inc.
To: pgsql-performance@postgresql.org
Subject: Question about query planner
Date: Sun, 19 Feb 2006 08:58:12 -0500
User-Agent: KMail/1.7.1
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <200602190858.13081.emil@baymountain.com>


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