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 />