LIMIT 1 == EXISTS optimization?

I was just troubleshooting a slow query<br /><br />SELECT * FROM da_answer a<br /> WHERE<br />    a.provider_id IN (
SELECTvisibility_bypass_providers( 0, 0 ) ) OR  -- ownership<br />    (<br />      EXISTS (  -- Visibility grant<br />
       SELECT v.client_answer_id FROM sp_client_answervisibility v<br />          JOIN sp_sharing_group_provider_tree t
ONv.sharing_group_id = t.sharing_group_id AND t.provider_id = 0<br />        WHERE<br />            v.client_answer_id
=a.answer_id AND v.visible = TRUE<br />       ) AND NOT EXISTS ( -- Visibility deny<br />        SELECT
v.client_answer_idFROM sp_client_answervisibility v<br />          JOIN sp_sharing_group_provider_tree t ON
v.sharing_group_id= t.sharing_group_id AND t.provider_id = 0<br />         WHERE<br />            v.client_answer_id =
a.answer_idAND v.visible = FALSE<br />      ) AND --ROI goes here<br />        a.covered_by_roi = TRUE<br />    )<br
/><br/>The subplan 3 in the explain seemed to be looping through 3 million rows which explained the slowdown....<br
/><br/>                                                                                             QUERY
PLAN                                                                                            <br
/>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> Bitmap Heap Scan on da_answer a  (cost=222.43..946804.85 rows=22309 width=70) (actual time=15.717..5141.001
rows=34810loops=1)<br />   Recheck Cond: (question_id = 18)<br />   Filter: ((hashed SubPlan 1) OR ((alternatives:
SubPlan2 or hashed SubPlan 3) AND (NOT (alternatives: SubPlan 4 or hashed SubPlan 5)) AND covered_by_roi))<br />   
-> Bitmap Index Scan on daanswer_questionid  (cost=0.00..221.26 rows=35695 width=0) (actual time=6.438..6.438
rows=35060loops=1)<br />         Index Cond: (question_id = 18)<br />   SubPlan 1<br />     ->  Result 
(cost=0.00..0.05rows=1 width=0) (actual time=3.683..4.621 rows=1728 loops=1)<br />    SubPlan 2<br />     ->  Merge
Join (cost=9.04..17.43 rows=1 width=0) (never executed)<br />           Merge Cond: (v.sharing_group_id =
t.sharing_group_id)<br/>           ->  Index Scan using clientanswervisibility_answerid_sharinggroupid_allow on
sp_client_answervisibilityv  (cost=0.00..8.38 rows=3 width=4) (never executed)<br />                  Index Cond:
(client_answer_id= $1)<br />           ->  Sort  (cost=9.04..9.04 rows=4 width=4) (never executed)<br
/>                Sort Key: t.sharing_group_id<br />                 ->  Bitmap Heap Scan on
sp_sharing_group_provider_treet  (cost=2.05..9.03 rows=4 width=4) (never executed)<br />                        Recheck
Cond:(provider_id = 0)<br />                       ->  Bitmap Index Scan on sharinggroupprovidertree_providerid 
(cost=0.00..2.05rows=4 width=0) (never executed)<br />                             Index Cond: (provider_id = 0)<br />
  SubPlan 3<br />     ->  Nested Loop  (cost=0.00..52203.49 rows=2316644 width=4) (actual time=0.053..2827.799
rows=3321883loops=1)<br />           ->  Index Scan using sharinggroupprovidertree_providerid on
sp_sharing_group_provider_treet  (cost=0.00..10.03 rows=4 width=4) (actual time=0.024..0.030 rows=3 loops=1)<br />
                Index Cond: (provider_id = 0)<br />           ->  Index Scan using
spclientanswervisibility_sharinggroupidon sp_client_answervisibility v  (cost=0.00..13011.17 rows=14877 width=8)
(actualtime=0.014..512.286 rows=1107294 loops=3)<br />                  Index Cond: (v.sharing_group_id =
t.sharing_group_id)<br/>                 Filter: v.visible<br />   SubPlan 4<br />     ->  Nested Loop 
(cost=0.00..8.19rows=1 width=0) (never executed)<br />           ->  Index Scan using
clientanswervisibility_answerid_sharinggroupid_denyon sp_client_answervisibility v  (cost=0.00..4.13 rows=1 width=4)
(neverexecuted)<br />                  Index Cond: (client_answer_id = $1)<br />           ->  Index Scan using
sp_sharing_group_provider_tree_sharing_group_id_keyon sp_sharing_group_provider_tree t  (cost=0.00..4.05 rows=1
width=4)(never executed)<br />                  Index Cond: ((t.sharing_group_id = v.sharing_group_id) AND
(t.provider_id= 0))<br />   SubPlan 5<br />     ->  Nested Loop  (cost=2993.74..35065.77 rows=542897 width=4)
(actualtime=105.162..105.162 rows=0 loops=1)<br />            ->  Bitmap Heap Scan on sp_sharing_group_provider_tree
t (cost=2.05..9.03 rows=4 width=4) (actual time=0.037..0.047 rows=3 loops=1)<br />                 Recheck Cond:
(provider_id= 0)<br />                 ->  Bitmap Index Scan on sharinggroupprovidertree_providerid 
(cost=0.00..2.05rows=4 width=0) (actual time=0.027..0.027 rows=3 loops=1)<br />                        Index Cond:
(provider_id= 0)<br />           ->  Bitmap Heap Scan on sp_client_answervisibility v  (cost=2991.69..8755.47
rows=3486width=8) (actual time=35.030..35.030 rows=0 loops=3)<br />                 Recheck Cond: ((v.sharing_group_id
=t.sharing_group_id) AND (NOT v.visible))<br />                  ->  Bitmap Index Scan on
clientanswervisibility_answerid_sharinggroupid_deny (cost=0.00..2991.51 rows=3486 width=0) (actual time=35.027..35.027
rows=0loops=3)<br />                       Index Cond: (v.sharing_group_id = t.sharing_group_id)<br />  Total runtime:
5170.291ms<br />(42 rows)<br /><br /><br />So on a whim I tossed a LIMIT 1 into both exists clauses:<br /><br />SELECT
*FROM da_answer a<br />  WHERE<br />     a.provider_id IN ( SELECT visibility_bypass_providers( 0, 0 ) ) OR  --
ownership<br/>     (<br />       EXISTS (  -- Visibility grant<br />         SELECT v.client_answer_id FROM
sp_client_answervisibilityv<br />           JOIN sp_sharing_group_provider_tree t ON v.sharing_group_id =
t.sharing_group_idAND t.provider_id = 0<br />         WHERE<br />             v.client_answer_id = a.answer_id AND
v.visible= TRUE<br />        LIMIT 1<br />       ) AND NOT EXISTS ( -- Visibility deny<br />         SELECT
v.client_answer_idFROM sp_client_answervisibility v<br />           JOIN sp_sharing_group_provider_tree t ON
v.sharing_group_id= t.sharing_group_id AND t.provider_id = 0<br />         WHERE<br />             v.client_answer_id =
a.answer_idAND v.visible = FALSE<br />        LIMIT 1<br />       ) AND --ROI goes here<br />         a.covered_by_roi
=TRUE<br />     )<br /><br />And it went from 5000+ ms to 90ms...  <br /><br
/>                                                                                               QUERY
PLAN                                                                                                <br />
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> BitmapHeap Scan on da_answer a  (cost=222.43..946804.85 rows=22309 width=70) (actual time=15.850..84.705 rows=34810
loops=1)<br/>    Recheck Cond: (question_id = 18)<br />   Filter: ((hashed SubPlan 1) OR ((SubPlan 2) AND (NOT (SubPlan
3))AND covered_by_roi))<br />   ->  Bitmap Index Scan on daanswer_questionid  (cost=0.00..221.26 rows=35695 width=0)
(actualtime=6.319..6.319 rows=35060 loops=1)<br />          Index Cond: (question_id = 18)<br />   SubPlan 1<br />    
-> Result  (cost=0.00..0.05 rows=1 width=0) (actual time=3.798..4.707 rows=1728 loops=1)<br />   SubPlan 2<br />    
-> Limit  (cost=9.04..17.43 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1994)<br />            -> 
MergeJoin  (cost=9.04..17.43 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1994)<br />                 Merge
Cond:(v.sharing_group_id = t.sharing_group_id)<br />                 ->  Index Scan using
clientanswervisibility_answerid_sharinggroupid_allowon sp_client_answervisibility v  (cost=0.00..8.38 rows=3 width=8)
(actualtime=0.005..0.005 rows=1 loops=1994)<br />                        Index Cond: (client_answer_id = $1)<br
/>                ->  Sort  (cost=9.04..9.04 rows=4 width=4) (actual time=0.000..0.000 rows=1 loops=1856)<br
/>                      Sort Key: t.sharing_group_id<br />                        Sort Method:  quicksort  Memory:
25kB<br/>                       ->  Bitmap Heap Scan on sp_sharing_group_provider_tree t  (cost=2.05..9.03 rows=4
width=4)(actual time=0.011..0.014 rows=3 loops=1)<br />                              Recheck Cond: (provider_id = 0)<br
/>                            ->  Bitmap Index Scan on sharinggroupprovidertree_providerid  (cost=0.00..2.05 rows=4
width=0)(actual time=0.008..0.008 rows=3 loops=1)<br />                                    Index Cond: (provider_id =
0)<br/>   SubPlan 3<br />     ->  Limit  (cost=0.00..8.19 rows=1 width=4) (actual time=0.003..0.003 rows=0
loops=1744)<br/>           ->  Nested Loop  (cost=0.00..8.19 rows=1 width=4) (actual time=0.003..0.003 rows=0
loops=1744)<br/>                  ->  Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on
sp_client_answervisibilityv  (cost=0.00..4.13 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1744)<br
/>                      Index Cond: (client_answer_id = $1)<br />                  ->  Index Scan using
sp_sharing_group_provider_tree_sharing_group_id_keyon sp_sharing_group_provider_tree t  (cost=0.00..4.05 rows=1
width=4)(actual time=0.003..0.003 rows=0 loops=22)<br />                       Index Cond: ((t.sharing_group_id =
v.sharing_group_id)AND (t.provider_id = 0))<br />  Total runtime: 91.263 ms<br />(28 rows)<br /><br />I'm no backend
guru,so I was hoping someone could explain what the original query-plan was doing.  If all you need to know is if a row
exists,why loop over all 3M rows?  It seems very simplistic to assume the a LIMIT 1 clause on the end of all EXISTS
subquerieswould be a general case optimization... Right?<br clear="all" /><br />-- <br />"An eye for eye only ends up
makingthe whole world blind." -- Mohandas Gandhi<br />