explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 9E7

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.462 8,008.257 ↑ 1.7 30 1

Sort (cost=5,274,064.93..5,274,064.96 rows=52 width=348) (actual time=8,008.253..8,008.257 rows=30 loops=1)

  • Sort Key: cc_shelterbed_bedlistroom_bedlistfloor.name, cc_shelterbed_bedlistroom.name, cc_shelterbed.name
  • Sort Method: quicksort Memory: 29kB
2. 0.010 8,007.795 ↑ 1.7 30 1

Append (cost=2,624,356.84..5,274,064.64 rows=52 width=348) (actual time=6,906.102..8,007.795 rows=30 loops=1)

3. 13.157 7,975.366 ↑ 1.2 30 1

Hash Left Join (cost=2,624,356.84..2,642,814.85 rows=37 width=348) (actual time=6,906.102..7,975.366 rows=30 loops=1)

  • Hash Cond: (cc_shelterbed.bedlist_room_id = cc_shelterbed_bedlistroom.bedlist_room_id)
  • Filter: ((ns.shelter_bedlist_id = 439) OR ((cc_shelterbed_bedlistroom_bedlistfloor.bedlist_id = 439) AND cc_shelterbed.active AND cc_shelterbed_bedlistroom.active AND cc_shelterbed_bedlistroom_bedlistfloor.active))
4. 11.061 7,920.449 ↓ 1.0 21,887 1

Merge Right Join (cost=2,624,291.60..2,630,674.81 rows=21,886 width=336) (actual time=6,509.422..7,920.449 rows=21,887 loops=1)

  • Merge Cond: (ns.shelter_bed_id = cc_shelterbed.bedlist_bed_id)
5. 616.964 7,895.906 ↓ 6.6 3,200 1

Nested Loop Left Join (cost=2,624,291.60..2,846,977.72 rows=484 width=324) (actual time=6,509.394..7,895.906 rows=3,200 loops=1)

  • Join Filter: (ns.client_id = rc.client_id)
6. 3.508 491.742 ↓ 6.6 3,200 1

Nested Loop Left Join (cost=0.05..222,024.06 rows=484 width=90) (actual time=18.871..491.742 rows=3,200 loops=1)

7. 3.564 328.234 ↓ 6.6 3,200 1

Nested Loop (cost=0.00..214,610.98 rows=484 width=74) (actual time=0.273..328.234 rows=3,200 loops=1)

8. 3.092 302.249 ↓ 6.4 3,203 1

Nested Loop (cost=0.00..212,794.50 rows=500 width=78) (actual time=0.258..302.249 rows=3,203 loops=1)

9. 270.150 270.150 ↓ 6.2 3,223 1

Index Scan using needservice_bedlistbedid on sp_need_service ns (cost=0.00..211,270.94 rows=521 width=78) (actual time=0.191..270.150 rows=3,223 loops=1)

  • Filter: (shelter_item AND (shelter_bedlist_id IS NOT NULL) AND active AND (provide_start_date IS NOT NULL) AND ((provide_end_date IS NULL) OR (provide_end_date >= '2012-05-18 11:56:04.988-05'::timestamp with time zone)))
10. 29.007 29.007 ↑ 1.0 1 3,223

Index Scan using idx_sp_client_client_id on sp_client c (cost=0.00..2.92 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=3,223)

  • Index Cond: (c.client_id = ns.client_id)
11. 22.421 22.421 ↑ 1.0 1 3,203

Index Scan using sp_need_pkey on sp_need n (cost=0.00..3.63 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=3,203)

  • Index Cond: (n.need_id = ns.need_id)
  • Filter: n.active
12. 29.783 160.000 ↑ 1.0 1 3,200

Index Scan using sp_client_pkey on sp_client c (cost=0.05..15.31 rows=1 width=20) (actual time=0.050..0.050 rows=1 loops=3,200)

  • Index Cond: (ns.client_id = c.client_id)
  • Filter: ((hashed SubPlan 44) OR ((alternatives: SubPlan 45 or hashed SubPlan 46) AND (NOT (alternatives: SubPlan 47 or hashed SubPlan 48))))
13.          

SubPlan (forIndex Scan)

14. 18.492 18.492 ↓ 4.0 4 1

Result (cost=0.00..0.05 rows=1 width=0) (actual time=18.470..18.492 rows=4 loops=1)

15. 0.000 95.970 ↑ 5.0 1 3,199

Nested Loop (cost=0.00..30.04 rows=5 width=0) (actual time=0.030..0.030 rows=1 loops=3,199)

16. 19.194 19.194 ↑ 1.0 6 3,199

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=0.003..0.006 rows=6 loops=3,199)

  • Index Cond: (provider_id = 4906)
17. 76.776 76.776 ↓ 0.0 0 19,194

Index Scan using clientvisibility_clientid_sharinggroupid_allow on sp_clientvisibility v (cost=0.00..3.32 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=19,194)

  • Index Cond: ((v.client_id = $32) AND (v.visibility_group_id = t.visibility_group_id))
18. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..18,209.03 rows=798,836 width=4) (actual time=.. rows= loops=0)

19. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using spclientvisibility_sharinggroupid on sp_clientvisibility v (cost=0.00..3,026.79 rows=2,543 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
  • Filter: v.visible
21. 2.692 15.755 ↓ 0.0 0 3,151

Nested Loop (cost=0.00..6.13 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=3,151)

22. 12.604 12.604 ↓ 0.0 0 3,151

Index Scan using clientvisibility_clientid_sharinggroupid_deny on sp_clientvisibility v (cost=0.00..3.08 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=3,151)

  • Index Cond: (client_id = $32)
23. 0.459 0.459 ↓ 0.0 0 153

Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_visibility_group_provider_tree t (cost=0.00..3.06 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=153)

  • Index Cond: ((t.visibility_group_id = v.visibility_group_id) AND (t.provider_id = 4906))
24. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..360.44 rows=4,639 width=4) (actual time=.. rows= loops=0)

25. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using clientvisibility_clientid_sharinggroupid_deny on sp_clientvisibility v (cost=0.00..58.35 rows=15 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
27. 297.323 6,787.200 ↓ 4.1 1,242 3,200

Materialize (cost=2,624,291.55..2,624,292.15 rows=304 width=238) (actual time=2.028..2.121 rows=1,242 loops=3,200)

28. 0.214 6,489.877 ↓ 4.1 1,242 1

Subquery Scan rc (cost=2,624,290.27..2,624,291.48 rows=304 width=238) (actual time=6,488.791..6,489.877 rows=1,242 loops=1)

29. 11.988 6,489.663 ↓ 4.1 1,242 1

HashAggregate (cost=2,624,290.27..2,624,290.88 rows=304 width=57) (actual time=6,488.789..6,489.663 rows=1,242 loops=1)

30. 34.348 6,477.675 ↓ 13.2 4,008 1

Nested Loop Anti Join (cost=0.05..2,624,286.77 rows=304 width=57) (actual time=21.865..6,477.675 rows=4,008 loops=1)

31. 271.846 1,215.857 ↓ 1.0 193,610 1

Nested Loop (cost=0.00..182,712.23 rows=192,214 width=61) (actual time=0.046..1,215.857 rows=193,610 loops=1)

32. 362.461 362.461 ↑ 1.0 193,850 1

Index Scan using idx_sp_need_service_client_id2 on sp_need_service ns (cost=0.00..107,419.55 rows=200,330 width=4) (actual time=0.019..362.461 rows=193,850 loops=1)

33. 581.550 581.550 ↑ 1.0 1 193,850

Index Scan using idx_sp_client_client_id on sp_client c (cost=0.00..0.37 rows=1 width=57) (actual time=0.003..0.003 rows=1 loops=193,850)

  • Index Cond: (c.client_id = ns.client_id)
34. 814.804 5,227.470 ↑ 1.0 1 193,610

Index Scan using sp_client_pkey on sp_client c (cost=0.05..12.70 rows=1 width=4) (actual time=0.027..0.027 rows=1 loops=193,610)

  • Index Cond: (c.client_id = ns.client_id)
  • Filter: ((hashed SubPlan 49) OR ((alternatives: SubPlan 50 or hashed SubPlan 51) AND (NOT (alternatives: SubPlan 52 or hashed SubPlan 53))))
35.          

SubPlan (forIndex Scan)

36. 18.321 18.321 ↓ 4.0 4 1

Result (cost=0.00..0.05 rows=1 width=0) (actual time=18.305..18.321 rows=4 loops=1)

37. 765.660 4,019.547 ↑ 5.0 1 191,407

Nested Loop (cost=0.00..30.04 rows=5 width=0) (actual time=0.021..0.021 rows=1 loops=191,407)

38. 957.035 957.035 ↑ 1.0 6 191,407

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=0.002..0.005 rows=6 loops=191,407)

  • Index Cond: (provider_id = 4906)
39. 2,296.852 2,296.852 ↓ 0.0 0 1,148,426

Index Scan using clientvisibility_clientid_sharinggroupid_allow on sp_clientvisibility v (cost=0.00..3.32 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,148,426)

  • Index Cond: ((v.client_id = $19) AND (v.visibility_group_id = t.visibility_group_id))
40. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..18,209.03 rows=798,836 width=4) (actual time=.. rows= loops=0)

41. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using spclientvisibility_sharinggroupid on sp_clientvisibility v (cost=0.00..3,026.79 rows=2,543 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
  • Filter: v.visible
43. 0.000 374.798 ↓ 0.0 0 187,399

Nested Loop (cost=0.00..6.13 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=187,399)

44. 374.798 374.798 ↓ 0.0 0 187,399

Index Scan using clientvisibility_clientid_sharinggroupid_deny on sp_clientvisibility v (cost=0.00..3.08 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=187,399)

  • Index Cond: (client_id = $19)
45. 16.518 16.518 ↓ 0.0 0 8,259

Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_visibility_group_provider_tree t (cost=0.00..3.06 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=8,259)

  • Index Cond: ((t.visibility_group_id = v.visibility_group_id) AND (t.provider_id = 4906))
46. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..360.44 rows=4,639 width=4) (actual time=.. rows= loops=0)

47. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using clientvisibility_clientid_sharinggroupid_deny on sp_clientvisibility v (cost=0.00..58.35 rows=15 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
49. 13.482 13.482 ↓ 1.0 21,887 1

Index Scan using sp_bedlist_bed_pkey on sp_bedlist_bed cc_shelterbed (cost=0.00..391.00 rows=21,886 width=16) (actual time=0.022..13.482 rows=21,887 loops=1)

50. 1.136 3.690 ↑ 1.0 2,780 1

Hash (cost=58.29..58.29 rows=2,780 width=27) (actual time=3.690..3.690 rows=2,780 loops=1)

51. 1.551 2.554 ↑ 1.0 2,780 1

Hash Left Join (cost=17.09..58.29 rows=2,780 width=27) (actual time=0.653..2.554 rows=2,780 loops=1)

  • Hash Cond: (cc_shelterbed_bedlistroom.bedlist_floor_id = cc_shelterbed_bedlistroom_bedlistfloor.bedlist_floor_id)
52. 0.365 0.365 ↑ 1.0 2,780 1

Seq Scan on sp_bedlist_room cc_shelterbed_bedlistroom (cost=0.00..33.56 rows=2,780 width=16) (actual time=0.004..0.365 rows=2,780 loops=1)

53. 0.362 0.638 ↑ 1.0 1,131 1

Hash (cost=14.26..14.26 rows=1,131 width=19) (actual time=0.638..0.638 rows=1,131 loops=1)

54. 0.276 0.276 ↑ 1.0 1,131 1

Seq Scan on sp_bedlist_floor cc_shelterbed_bedlistroom_bedlistfloor (cost=0.00..14.26 rows=1,131 width=19) (actual time=0.004..0.276 rows=1,131 loops=1)

55.          

SubPlan (forHash Left Join)

56. 0.000 19.200 ↓ 0.0 0 30

Nested Loop (cost=3.11..163.44 rows=1 width=21) (actual time=0.633..0.640 rows=0 loops=30)

  • Join Filter: (a.date_effective = (max(a.date_effective)))
57.          

Initplan (forNested Loop)

58. 0.027 0.027 ↑ 1.0 1 1

Index Scan using da_question_virt_field_name_key on da_question daquestion (cost=0.00..3.06 rows=1 width=4) (actual time=0.024..0.027 rows=1 loops=1)

  • Index Cond: ((virt_field_name)::text = 'SVPPROFGENDER'::text)
59. 0.042 0.180 ↓ 0.0 0 30

Nested Loop (cost=0.00..9.04 rows=1 width=37) (actual time=0.005..0.006 rows=0 loops=30)

60. 0.090 0.090 ↓ 0.0 0 30

Index Scan using daanswer_clientid_questionid on da_answer a (cost=0.00..5.98 rows=1 width=20) (actual time=0.002..0.003 rows=0 loops=30)

  • Index Cond: ((client_id = $23) AND (question_id = $22))
  • Filter: active
61. 0.048 0.048 ↑ 1.0 1 8

Index Scan using sp_picklist_value_pkey on sp_picklist_value pv (cost=0.00..3.05 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=8)

  • Index Cond: (pv.picklist_value_id = a.val_int)
62. 0.016 19.008 ↑ 1.0 1 8

GroupAggregate (cost=0.05..151.35 rows=1 width=16) (actual time=2.376..2.376 rows=1 loops=8)

63. 0.124 18.992 ↑ 1.0 1 8

Index Scan using daanswer_clientid_questionid on da_answer a (cost=0.05..151.34 rows=1 width=16) (actual time=2.330..2.374 rows=1 loops=8)

  • Index Cond: ((client_id = $23) AND (question_id = $22))
  • Filter: (active AND (date_effective <= '2012-05-18 11:56:04.988-05'::timestamp with time zone) AND ((hashed SubPlan 31) OR ((alternatives: SubPlan 32 or hashed SubPlan 33) AND (NOT (alternatives: SubPlan 34 or hashed SubPlan 35)) AND covered_by_roi)))
64.          

SubPlan (forIndex Scan)

65. 18.558 18.558 ↓ 4.0 4 1

Result (cost=0.00..0.05 rows=1 width=0) (actual time=18.542..18.558 rows=4 loops=1)

66. 0.000 0.310 ↓ 0.0 0 10

Nested Loop (cost=0.00..134.02 rows=1 width=0) (actual time=0.031..0.031 rows=0 loops=10)

67. 0.070 0.070 ↑ 1.0 6 10

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=0.003..0.007 rows=6 loops=10)

  • Index Cond: (provider_id = 4906)
68. 0.240 0.240 ↓ 0.0 0 60

Index Scan using clientanswervisibility_answerid_sharinggroupid_allow on sp_client_answervisibility v (cost=0.00..20.65 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=60)

  • Index Cond: ((v.client_answer_id = $3) AND (v.visibility_group_id = t.visibility_group_id))
69. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=801.01..1,314,109.39 rows=77,277 width=4) (actual time=.. rows= loops=0)

70. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
71. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on sp_client_answervisibility v (cost=801.01..218,622.70 rows=157,536 width=8) (actual time=.. rows= loops=0)

  • Recheck Cond: (v.visibility_group_id = t.visibility_group_id)
  • Filter: v.visible
72. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on spclientanswervisibility_sharinggroupid (cost=0.00..793.13 rows=157,536 width=0) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
73. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..11.29 rows=1 width=0) (actual time=.. rows= loops=0)

74. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on sp_client_answervisibility v (cost=0.00..8.23 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (client_answer_id = $3)
75. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_visibility_group_provider_tree t (cost=0.00..3.06 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((t.visibility_group_id = v.visibility_group_id) AND (t.provider_id = 4906))
76. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..296.62 rows=2 width=4) (actual time=.. rows= loops=0)

77. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
78. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on sp_client_answervisibility v (cost=0.00..47.74 rows=4 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
79. 0.032 18.870 ↓ 0.0 0 30

Nested Loop (cost=3.11..160.39 rows=1 width=8) (actual time=0.623..0.629 rows=0 loops=30)

  • Join Filter: (a.date_effective = (max(a.date_effective)))
80.          

Initplan (forNested Loop)

81. 0.027 0.027 ↑ 1.0 1 1

Index Scan using da_question_virt_field_name_key on da_question daquestion (cost=0.00..3.06 rows=1 width=4) (actual time=0.024..0.027 rows=1 loops=1)

  • Index Cond: ((virt_field_name)::text = 'SVPPROFDOB'::text)
82. 0.030 0.030 ↓ 0.0 0 30

Index Scan using daanswer_clientid_questionid on da_answer a (cost=0.00..5.98 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=30)

  • Index Cond: ((client_id = $23) AND (question_id = $27))
  • Filter: active
83. 0.014 18.781 ↑ 1.0 1 7

GroupAggregate (cost=0.05..151.35 rows=1 width=16) (actual time=2.683..2.683 rows=1 loops=7)

84. 0.093 18.767 ↑ 1.0 1 7

Index Scan using daanswer_clientid_questionid on da_answer a (cost=0.05..151.34 rows=1 width=16) (actual time=2.645..2.681 rows=1 loops=7)

  • Index Cond: ((client_id = $23) AND (question_id = $27))
  • Filter: (active AND (date_effective <= '2012-05-18 11:56:04.988-05'::timestamp with time zone) AND ((hashed SubPlan 38) OR ((alternatives: SubPlan 39 or hashed SubPlan 40) AND (NOT (alternatives: SubPlan 41 or hashed SubPlan 42)) AND covered_by_roi)))
85.          

SubPlan (forIndex Scan)

86. 18.422 18.422 ↓ 4.0 4 1

Result (cost=0.00..0.05 rows=1 width=0) (actual time=18.405..18.422 rows=4 loops=1)

87. 0.027 0.252 ↓ 0.0 0 9

Nested Loop (cost=0.00..134.02 rows=1 width=0) (actual time=0.028..0.028 rows=0 loops=9)

88. 0.063 0.063 ↑ 1.0 6 9

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=0.003..0.007 rows=6 loops=9)

  • Index Cond: (provider_id = 4906)
89. 0.162 0.162 ↓ 0.0 0 54

Index Scan using clientanswervisibility_answerid_sharinggroupid_allow on sp_client_answervisibility v (cost=0.00..20.65 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=54)

  • Index Cond: ((v.client_answer_id = $3) AND (v.visibility_group_id = t.visibility_group_id))
90. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=801.01..1,314,109.39 rows=77,277 width=4) (actual time=.. rows= loops=0)

91. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
92. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on sp_client_answervisibility v (cost=801.01..218,622.70 rows=157,536 width=8) (actual time=.. rows= loops=0)

  • Recheck Cond: (v.visibility_group_id = t.visibility_group_id)
  • Filter: v.visible
93. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on spclientanswervisibility_sharinggroupid (cost=0.00..793.13 rows=157,536 width=0) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
94. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..11.29 rows=1 width=0) (actual time=.. rows= loops=0)

95. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on sp_client_answervisibility v (cost=0.00..8.23 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (client_answer_id = $3)
96. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_visibility_group_provider_tree t (cost=0.00..3.06 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((t.visibility_group_id = v.visibility_group_id) AND (t.provider_id = 4906))
97. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..296.62 rows=2 width=4) (actual time=.. rows= loops=0)

98. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
99. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on sp_client_answervisibility v (cost=0.00..47.74 rows=4 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
100. 0.001 32.419 ↓ 0.0 0 1

Hash Left Join (cost=2,625,148.96..2,631,249.68 rows=15 width=348) (actual time=32.419..32.419 rows=0 loops=1)

  • Hash Cond: (ns.client_id = rc.client_id)
101. 0.002 32.418 ↓ 0.0 0 1

Nested Loop Left Join (cost=856.71..2,099.84 rows=15 width=114) (actual time=32.418..32.418 rows=0 loops=1)

102. 0.000 32.416 ↓ 0.0 0 1

Nested Loop Left Join (cost=856.71..2,098.87 rows=15 width=104) (actual time=32.416..32.416 rows=0 loops=1)

103. 0.002 32.416 ↓ 0.0 0 1

Nested Loop Left Join (cost=856.71..2,098.01 rows=15 width=97) (actual time=32.416..32.416 rows=0 loops=1)

104. 0.000 32.414 ↓ 0.0 0 1

Nested Loop Left Join (cost=856.71..2,053.49 rows=15 width=86) (actual time=32.414..32.414 rows=0 loops=1)

  • Filter: (c.active OR (c.active IS NULL))
105. 0.002 32.414 ↓ 0.0 0 1

Nested Loop Left Join (cost=856.66..1,414.45 rows=16 width=90) (actual time=32.414..32.414 rows=0 loops=1)

  • Filter: (c.active OR (c.active IS NULL))
106. 0.438 32.412 ↓ 0.0 0 1

Bitmap Heap Scan on sp_need_service ns (cost=856.61..1,153.15 rows=17 width=74) (actual time=32.412..32.412 rows=0 loops=1)

  • Recheck Cond: ((shelter_bedlist_id = 439) AND (shelter_bed_id IS NULL) AND shelter_item AND active)
  • Filter: ((provide_start_date <= '2012-05-18 11:56:03.971-05'::timestamp with time zone) AND ((provide_end_date IS NULL) OR (provide_end_date >= '2012-05-18 11:56:03.971-05'::timestamp with time zone)))
107. 0.034 31.974 ↓ 0.0 0 1

BitmapAnd (cost=856.61..856.61 rows=200 width=0) (actual time=31.974..31.974 rows=0 loops=1)

108. 0.056 0.056 ↑ 32.3 205 1

Bitmap Index Scan on needservice_bedlistid_bedlistbedid (cost=0.00..36.11 rows=6,618 width=0) (actual time=0.056..0.056 rows=205 loops=1)

  • Index Cond: ((shelter_bedlist_id = 439) AND (shelter_bed_id IS NULL))
109. 31.884 31.884 ↑ 1.0 193,851 1

Bitmap Index Scan on idx_sp_need_service_client_id2 (cost=0.00..820.44 rows=200,330 width=0) (actual time=31.884..31.884 rows=193,851 loops=1)

110. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_client_pkey on sp_client c (cost=0.05..15.37 rows=1 width=21) (actual time=.. rows= loops=0)

  • Index Cond: (ns.client_id = c.client_id)
  • Filter: ((hashed SubPlan 15) OR ((alternatives: SubPlan 16 or hashed SubPlan 17) AND (NOT (alternatives: SubPlan 18 or hashed SubPlan 19))))
111.          

SubPlan (forIndex Scan)

112. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.05 rows=1 width=0) (actual time=.. rows= loops=0)

113. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..30.04 rows=5 width=0) (actual time=.. rows= loops=0)

114. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
115. 0.000 0.000 ↓ 0.0 0

Index Scan using clientvisibility_clientid_sharinggroupid_allow on sp_clientvisibility v (cost=0.00..3.32 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((v.client_id = $11) AND (v.visibility_group_id = t.visibility_group_id))
116. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..18,209.03 rows=798,836 width=4) (actual time=.. rows= loops=0)

117. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
118. 0.000 0.000 ↓ 0.0 0

Index Scan using spclientvisibility_sharinggroupid on sp_clientvisibility v (cost=0.00..3,026.79 rows=2,543 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
  • Filter: v.visible
119. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..6.13 rows=1 width=0) (actual time=.. rows= loops=0)

120. 0.000 0.000 ↓ 0.0 0

Index Scan using clientvisibility_clientid_sharinggroupid_deny on sp_clientvisibility v (cost=0.00..3.08 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (client_id = $11)
121. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_visibility_group_provider_tree t (cost=0.00..3.06 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((t.visibility_group_id = v.visibility_group_id) AND (t.provider_id = 4906))
122. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..360.44 rows=4,639 width=4) (actual time=.. rows= loops=0)

123. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
124. 0.000 0.000 ↓ 0.0 0

Index Scan using clientvisibility_clientid_sharinggroupid_deny on sp_clientvisibility v (cost=0.00..58.35 rows=15 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
125. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_need_pkey on sp_need c (cost=0.05..39.94 rows=1 width=5) (actual time=.. rows= loops=0)

  • Index Cond: (ns.need_id = c.need_id)
  • Filter: ((hashed SubPlan 20) OR ((alternatives: SubPlan 21 or hashed SubPlan 22) AND (NOT (alternatives: SubPlan 23 or hashed SubPlan 24)) AND c.covered_by_roi))
126.          

SubPlan (forIndex Scan)

127. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.05 rows=1 width=0) (actual time=.. rows= loops=0)

128. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=10.14..28.42 rows=1 width=0) (actual time=.. rows= loops=0)

  • Hash Cond: (v.visibility_group_id = t.visibility_group_id)
129. 0.000 0.000 ↓ 0.0 0

Index Scan using needvisibility_needid on sp_needvisibility v (cost=0.00..17.55 rows=364 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (need_id = $15)
  • Filter: visible
130. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.12..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

131. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
132. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=424.34..635,050.85 rows=31,679 width=4) (actual time=.. rows= loops=0)

133. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
134. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on sp_needvisibility v (cost=424.34..105,631.80 rows=83,327 width=8) (actual time=.. rows= loops=0)

  • Recheck Cond: (v.visibility_group_id = t.visibility_group_id)
  • Filter: v.visible
135. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on spneedvisibility_sharinggroupid (cost=0.00..420.17 rows=83,327 width=0) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
136. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..7.83 rows=1 width=0) (actual time=.. rows= loops=0)

137. 0.000 0.000 ↓ 0.0 0

Index Scan using needvisibility_needid_sharinggroupid_deny on sp_needvisibility v (cost=0.00..4.77 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (need_id = $15)
138. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_visibility_group_provider_tree t (cost=0.00..3.06 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((t.visibility_group_id = v.visibility_group_id) AND (t.provider_id = 4906))
139. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..199.69 rows=2 width=4) (actual time=.. rows= loops=0)

140. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
141. 0.000 0.000 ↓ 0.0 0

Index Scan using needvisibility_needid_sharinggroupid_deny on sp_needvisibility v (cost=0.00..31.58 rows=6 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
142. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_bedlist_bed_pkey on sp_bedlist_bed ns_shelterbed (cost=0.00..2.97 rows=1 width=15) (actual time=.. rows= loops=0)

  • Index Cond: (ns.shelter_bed_id = ns_shelterbed.bedlist_bed_id)
143. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_bedlist_room_pkey on sp_bedlist_room ns_shelterbed_bedlistroom (cost=0.00..0.06 rows=1 width=15) (actual time=.. rows= loops=0)

  • Index Cond: (ns_shelterbed.bedlist_room_id = ns_shelterbed_bedlistroom.bedlist_room_id)
144. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_bedlist_floor_pkey on sp_bedlist_floor ns_shelterbed_bedlistroom_bedlistfloor (cost=0.00..0.06 rows=1 width=18) (actual time=.. rows= loops=0)

  • Index Cond: (ns_shelterbed_bedlistroom.bedlist_floor_id = ns_shelterbed_bedlistroom_bedlistfloor.bedlist_floor_id)
145. 0.000 0.000 ↓ 0.0 0

Hash (cost=2,624,291.48..2,624,291.48 rows=304 width=238) (actual time=.. rows= loops=0)

146. 0.000 0.000 ↓ 0.0 0

Subquery Scan rc (cost=2,624,290.27..2,624,291.48 rows=304 width=238) (actual time=.. rows= loops=0)

147. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=2,624,290.27..2,624,290.88 rows=304 width=57) (actual time=.. rows= loops=0)

148. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=0.05..2,624,286.77 rows=304 width=57) (actual time=.. rows= loops=0)

149. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..182,712.23 rows=192,214 width=61) (actual time=.. rows= loops=0)

150. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_sp_need_service_client_id2 on sp_need_service ns (cost=0.00..107,419.55 rows=200,330 width=4) (actual time=.. rows= loops=0)

151. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_sp_client_client_id on sp_client c (cost=0.00..0.37 rows=1 width=57) (actual time=.. rows= loops=0)

  • Index Cond: (c.client_id = ns.client_id)
152. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_client_pkey on sp_client c (cost=0.05..12.70 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (c.client_id = ns.client_id)
  • Filter: ((hashed SubPlan 25) OR ((alternatives: SubPlan 26 or hashed SubPlan 27) AND (NOT (alternatives: SubPlan 28 or hashed SubPlan 29))))
153.          

SubPlan (forIndex Scan)

154. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.05 rows=1 width=0) (actual time=.. rows= loops=0)

155. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..30.04 rows=5 width=0) (actual time=.. rows= loops=0)

156. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
157. 0.000 0.000 ↓ 0.0 0

Index Scan using clientvisibility_clientid_sharinggroupid_allow on sp_clientvisibility v (cost=0.00..3.32 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((v.client_id = $19) AND (v.visibility_group_id = t.visibility_group_id))
158. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..18,209.03 rows=798,836 width=4) (actual time=.. rows= loops=0)

159. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
160. 0.000 0.000 ↓ 0.0 0

Index Scan using spclientvisibility_sharinggroupid on sp_clientvisibility v (cost=0.00..3,026.79 rows=2,543 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
  • Filter: v.visible
161. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..6.13 rows=1 width=0) (actual time=.. rows= loops=0)

162. 0.000 0.000 ↓ 0.0 0

Index Scan using clientvisibility_clientid_sharinggroupid_deny on sp_clientvisibility v (cost=0.00..3.08 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (client_id = $19)
163. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_visibility_group_provider_tree t (cost=0.00..3.06 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((t.visibility_group_id = v.visibility_group_id) AND (t.provider_id = 4906))
164. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..360.44 rows=4,639 width=4) (actual time=.. rows= loops=0)

165. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
166. 0.000 0.000 ↓ 0.0 0

Index Scan using clientvisibility_clientid_sharinggroupid_deny on sp_clientvisibility v (cost=0.00..58.35 rows=15 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
167.          

SubPlan (forHash Left Join)

168. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.11..163.44 rows=1 width=21) (actual time=.. rows= loops=0)

  • Join Filter: (a.date_effective = (max(a.date_effective)))
169.          

Initplan (forNested Loop)

170. 0.000 0.000 ↓ 0.0 0

Index Scan using da_question_virt_field_name_key on da_question daquestion (cost=0.00..3.06 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((virt_field_name)::text = 'SVPPROFGENDER'::text)
171. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..9.04 rows=1 width=37) (actual time=.. rows= loops=0)

172. 0.000 0.000 ↓ 0.0 0

Index Scan using daanswer_clientid_questionid on da_answer a (cost=0.00..5.98 rows=1 width=20) (actual time=.. rows= loops=0)

  • Index Cond: ((client_id = $1) AND (question_id = $0))
  • Filter: active
173. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_picklist_value_pkey on sp_picklist_value pv (cost=0.00..3.05 rows=1 width=25) (actual time=.. rows= loops=0)

  • Index Cond: (pv.picklist_value_id = a.val_int)
174. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.05..151.35 rows=1 width=16) (actual time=.. rows= loops=0)

175. 0.000 0.000 ↓ 0.0 0

Index Scan using daanswer_clientid_questionid on da_answer a (cost=0.05..151.34 rows=1 width=16) (actual time=.. rows= loops=0)

  • Index Cond: ((client_id = $1) AND (question_id = $0))
  • Filter: (active AND (date_effective <= '2012-05-18 11:56:04.988-05'::timestamp with time zone) AND ((hashed SubPlan 2) OR ((alternatives: SubPlan 3 or hashed SubPlan 4) AND (NOT (alternatives: SubPlan 5 or hashed SubPlan 6)) AND covered_by_roi)))
176.          

SubPlan (forIndex Scan)

177. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.05 rows=1 width=0) (actual time=.. rows= loops=0)

178. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..134.02 rows=1 width=0) (actual time=.. rows= loops=0)

179. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
180. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_allow on sp_client_answervisibility v (cost=0.00..20.65 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((v.client_answer_id = $3) AND (v.visibility_group_id = t.visibility_group_id))
181. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=801.01..1,314,109.39 rows=77,277 width=4) (actual time=.. rows= loops=0)

182. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
183. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on sp_client_answervisibility v (cost=801.01..218,622.70 rows=157,536 width=8) (actual time=.. rows= loops=0)

  • Recheck Cond: (v.visibility_group_id = t.visibility_group_id)
  • Filter: v.visible
184. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on spclientanswervisibility_sharinggroupid (cost=0.00..793.13 rows=157,536 width=0) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
185. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..11.29 rows=1 width=0) (actual time=.. rows= loops=0)

186. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on sp_client_answervisibility v (cost=0.00..8.23 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (client_answer_id = $3)
187. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_visibility_group_provider_tree t (cost=0.00..3.06 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((t.visibility_group_id = v.visibility_group_id) AND (t.provider_id = 4906))
188. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..296.62 rows=2 width=4) (actual time=.. rows= loops=0)

189. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
190. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on sp_client_answervisibility v (cost=0.00..47.74 rows=4 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
191. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.11..160.39 rows=1 width=8) (actual time=.. rows= loops=0)

  • Join Filter: (a.date_effective = (max(a.date_effective)))
192.          

Initplan (forNested Loop)

193. 0.000 0.000 ↓ 0.0 0

Index Scan using da_question_virt_field_name_key on da_question daquestion (cost=0.00..3.06 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((virt_field_name)::text = 'SVPPROFDOB'::text)
194. 0.000 0.000 ↓ 0.0 0

Index Scan using daanswer_clientid_questionid on da_answer a (cost=0.00..5.98 rows=1 width=24) (actual time=.. rows= loops=0)

  • Index Cond: ((client_id = $1) AND (question_id = $6))
  • Filter: active
195. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.05..151.35 rows=1 width=16) (actual time=.. rows= loops=0)

196. 0.000 0.000 ↓ 0.0 0

Index Scan using daanswer_clientid_questionid on da_answer a (cost=0.05..151.34 rows=1 width=16) (actual time=.. rows= loops=0)

  • Index Cond: ((client_id = $1) AND (question_id = $6))
  • Filter: (active AND (date_effective <= '2012-05-18 11:56:04.988-05'::timestamp with time zone) AND ((hashed SubPlan 9) OR ((alternatives: SubPlan 10 or hashed SubPlan 11) AND (NOT (alternatives: SubPlan 12 or hashed SubPlan 13)) AND covered_by_roi)))
197.          

SubPlan (forIndex Scan)

198. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.05 rows=1 width=0) (actual time=.. rows= loops=0)

199. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..134.02 rows=1 width=0) (actual time=.. rows= loops=0)

200. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
201. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_allow on sp_client_answervisibility v (cost=0.00..20.65 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((v.client_answer_id = $3) AND (v.visibility_group_id = t.visibility_group_id))
202. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=801.01..1,314,109.39 rows=77,277 width=4) (actual time=.. rows= loops=0)

203. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
204. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on sp_client_answervisibility v (cost=801.01..218,622.70 rows=157,536 width=8) (actual time=.. rows= loops=0)

  • Recheck Cond: (v.visibility_group_id = t.visibility_group_id)
  • Filter: v.visible
205. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on spclientanswervisibility_sharinggroupid (cost=0.00..793.13 rows=157,536 width=0) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)
206. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..11.29 rows=1 width=0) (actual time=.. rows= loops=0)

207. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on sp_client_answervisibility v (cost=0.00..8.23 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (client_answer_id = $3)
208. 0.000 0.000 ↓ 0.0 0

Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_visibility_group_provider_tree t (cost=0.00..3.06 rows=1 width=4) (actual time=.. rows= loops=0)

  • Index Cond: ((t.visibility_group_id = v.visibility_group_id) AND (t.provider_id = 4906))
209. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..296.62 rows=2 width=4) (actual time=.. rows= loops=0)

210. 0.000 0.000 ↓ 0.0 0

Index Scan using sharinggroupprovidertree_providerid on sp_visibility_group_provider_tree t (cost=0.00..10.12 rows=6 width=4) (actual time=.. rows= loops=0)

  • Index Cond: (provider_id = 4906)
211. 0.000 0.000 ↓ 0.0 0

Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on sp_client_answervisibility v (cost=0.00..47.74 rows=4 width=8) (actual time=.. rows= loops=0)

  • Index Cond: (v.visibility_group_id = t.visibility_group_id)