explain.depesz.com

A tool for finding a real cause for slow queries.

Result: yVoN : Big, slow after analyze

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 82.200 588,362.061 ↑ 1.0 1 1

Aggregate (cost=136,514.51..20,056,635.44 rows=1 width=104) (actual time=588,362.061..588,362.061 rows=1 loops=1)

2.          

Initplan (forAggregate)

3. 0.001 0.445 ↑ 1.0 1 1

Aggregate (cost=1,312.03..1,312.03 rows=1 width=10) (actual time=0.445..0.445 rows=1 loops=1)

4. 0.070 0.444 ↓ 0.0 0 1

Hash Join (cost=1,306.34..1,312.02 rows=3 width=10) (actual time=0.444..0.444 rows=0 loops=1)

  • Hash Cond: ("outer".service_id = "inner".service_id)
5. 0.024 0.024 ↑ 1.0 69 1

Seq Scan on service serv (cost=0.00..4.95 rows=70 width=4) (actual time=0.004..0.024 rows=69 loops=1)

  • Filter: (code_id <> 12)
6. 0.000 0.350 ↓ 0.0 0 1

Hash (cost=1,306.33..1,306.33 rows=3 width=14) (actual time=0.350..0.350 rows=0 loops=1)

7. 0.350 0.350 ↓ 0.0 0 1

Index Scan using logs_services_log_dttm on logs_services (cost=0.00..1,306.33 rows=3 width=14) (actual time=0.350..0.350 rows=0 loops=1)

  • Index Cond: ((log_dttm >= '2012-06-01 00:00:00'::timestamp without time zone) AND (log_dttm <= '2012-06-30 00:00:00'::timestamp without time zone))
  • Filter: (((service_id = 29) OR (service_id = 30)) AND (facility_id = 50) AND (flag = 1::numeric))
8. 0.004 0.267 ↑ 1.0 1 1

Aggregate (cost=1,309.82..1,309.82 rows=1 width=10) (actual time=0.267..0.267 rows=1 loops=1)

9. 0.002 0.263 ↑ 2.0 1 1

Nested Loop (cost=0.00..1,309.81 rows=2 width=10) (actual time=0.064..0.263 rows=1 loops=1)

10. 0.003 0.003 ↑ 1.0 1 1

Index Scan using service_pkey on service serv (cost=0.00..5.04 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (31 = service_id)
  • Filter: (code_id <> 12)
11. 0.258 0.258 ↑ 2.0 1 1

Index Scan using logs_services_log_dttm on logs_services (cost=0.00..1,304.75 rows=2 width=14) (actual time=0.059..0.258 rows=1 loops=1)

  • Index Cond: ((log_dttm >= '2012-06-01 00:00:00'::timestamp without time zone) AND (log_dttm <= '2012-06-30 00:00:00'::timestamp without time zone))
  • Filter: ((service_id = 31) AND (facility_id = 50) AND (flag = 1::numeric))
12. 4.564 344.963 ↓ 988.0 988 1

Nested Loop Left Join (cost=0.00..133,892.13 rows=1 width=104) (actual time=0.633..344.963 rows=988 loops=1)

13. 4.243 261.359 ↓ 988.0 988 1

Nested Loop Left Join (cost=0.00..133,684.18 rows=1 width=76) (actual time=0.555..261.359 rows=988 loops=1)

14. 3.127 122.748 ↓ 988.0 988 1

Nested Loop Left Join (cost=0.00..133,660.14 rows=1 width=48) (actual time=0.414..122.748 rows=988 loops=1)

15. 1.103 101.009 ↓ 423.0 423 1

Nested Loop Left Join (cost=0.00..133,600.60 rows=1 width=40) (actual time=0.376..101.009 rows=423 loops=1)

16. 2.061 97.791 ↓ 423.0 423 1

Nested Loop Left Join (cost=0.00..133,595.54 rows=1 width=36) (actual time=0.353..97.791 rows=423 loops=1)

  • Join Filter: (("inner".facility_id = "outer".facility_id) AND ("inner".effdt = (subplan)))
17. 3.163 81.915 ↓ 423.0 423 1

Nested Loop (cost=0.00..133,562.42 rows=1 width=40) (actual time=0.249..81.915 rows=423 loops=1)

  • Join Filter: ("inner".effdt = (subplan))
18. 2.423 29.495 ↓ 4.1 423 1

Index Scan using logs_services_log_dttm on logs_services ls (cost=0.00..130,676.77 rows=102 width=32) (actual time=0.104..29.495 rows=423 loops=1)

  • Index Cond: ((log_dttm >= '2012-06-01 00:00:00'::timestamp without time zone) AND (log_dttm <= '2012-06-30 00:00:00'::timestamp without time zone))
  • Filter: ((facility_id = 50) AND (flag = 1::numeric) AND (effseq = (subplan)))
19.          

SubPlan (forIndex Scan)

20. 0.846 27.072 ↑ 1.0 1 423

Aggregate (cost=204.22..204.22 rows=1 width=4) (actual time=0.064..0.064 rows=1 loops=423)

21. 26.226 26.226 ↓ 2.0 2 423

Index Scan using logs_services_log_dttm on logs_services lsi (cost=0.00..204.21 rows=1 width=4) (actual time=0.035..0.062 rows=2 loops=423)

  • Index Cond: (log_dttm = $18)
  • Filter: (((client_id)::text = ($15)::text) AND (facility_id = $13))
22. 4.437 12.267 ↓ 3.0 3 423

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..16.28 rows=1 width=28) (actual time=0.016..0.029 rows=3 loops=423)

  • Index Cond: (((cd.client_id)::text = ("outer".client_id)::text) AND (50 = cd.facility_id))
  • Filter: (effseq = (subplan))
23.          

SubPlan (forIndex Scan)

24. 1.566 7.830 ↑ 1.0 1 1,566

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,566)

25. 6.264 6.264 ↑ 1.0 1 1,566

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1,566)

  • Index Cond: (((client_id)::text = ($19)::text) AND (effdt = $21))
  • Filter: (facility_id = $20)
26.          

SubPlan (forNested Loop)

27. 2.740 36.990 ↑ 1.0 1 1,370

Aggregate (cost=11.99..11.99 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1,370)

28. 8.974 34.250 ↓ 4.0 4 1,370

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..11.99 rows=1 width=8) (actual time=0.008..0.025 rows=4 loops=1,370)

  • Index Cond: ((($19)::text = (client_id)::text) AND ($20 = facility_id) AND (effdt <= $18))
  • Filter: (effseq = (subplan))
29.          

SubPlan (forIndex Scan)

30. 6.319 25.276 ↑ 1.0 1 6,319

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=6,319)

31. 18.957 18.957 ↑ 1.0 1 6,319

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=6,319)

  • Index Cond: (((client_id)::text = ($22)::text) AND (effdt = $24))
  • Filter: (facility_id = $23)
32. 3.597 6.345 ↑ 1.0 1 423

Index Scan using client_id_hohid_index on client_hoh hoh (cost=0.00..21.19 rows=1 width=24) (actual time=0.013..0.015 rows=1 loops=423)

  • Index Cond: ((hoh.client_id)::text = ("outer".client_id)::text)
  • Filter: ((effstat = 'A'::bpchar) AND (effseq = (subplan)))
33.          

SubPlan (forIndex Scan)

34. 0.458 2.748 ↑ 1.0 1 458

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=458)

35. 2.290 2.290 ↑ 1.0 1 458

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=458)

  • Index Cond: ((facility_id = $26) AND ((client_id)::text = ($25)::text) AND (effdt = $27))
36.          

SubPlan (forNested Loop Left Join)

37. 0.830 7.470 ↑ 1.0 1 415

Aggregate (cost=11.91..11.91 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=415)

38. 3.985 6.640 ↑ 1.0 1 415

Index Scan using ix_client_hoh_01 on client_hoh hoh (cost=0.00..11.91 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=415)

  • Index Cond: ((effstat = 'A'::bpchar) AND (($15)::text = (client_id)::text) AND (effdt <= $18))
  • Filter: (($13 = facility_id) AND (effseq = (subplan)))
39.          

SubPlan (forIndex Scan)

40. 1.062 2.655 ↑ 1.0 1 531

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=531)

41. 1.593 1.593 ↑ 1.0 1 531

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=531)

  • Index Cond: ((facility_id = $23) AND ((client_id)::text = ($22)::text) AND (effdt = $24))
42. 2.115 2.115 ↑ 1.0 1 423

Index Scan using service_pkey on service serv (cost=0.00..5.04 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=423)

  • Index Cond: (serv.service_id = "outer".service_id)
  • Filter: (active = 1)
43. 8.382 18.612 ↓ 2.0 2 423

Index Scan using ix_client_hoh_02 on client_hoh hoh (cost=0.00..59.53 rows=1 width=12) (actual time=0.018..0.044 rows=2 loops=423)

  • Index Cond: ((hoh.effstat = 'A'::bpchar) AND (hoh.hoh_id = "outer".hoh_id))
  • Filter: ((effseq = (subplan)) AND (((effdt >= '2012-06-01 00:00:00'::timestamp without time zone) AND (effdt <= '2012-06-30 00:00:00'::timestamp without time zone)) OR ((effdt < '2012-06-01 00:00:00'::timestamp without time zone) AND (NOT (subplan)))))
44.          

SubPlan (forIndex Scan)

45. 4.985 4.985 ↓ 0.0 0 997

Index Scan using client_id_hohid_index on client_hoh hohi (cost=0.00..5.92 rows=1 width=84) (actual time=0.005..0.005 rows=0 loops=997)

  • Index Cond: (((client_id)::text = ($28)::text) AND (hoh_id = $31))
  • Filter: ((effdt >= $30) AND (effdt <= '2012-06-01 00:00:00'::timestamp without time zone) AND (effstat <> 'A'::bpchar))
46. 2.098 5.245 ↑ 1.0 1 1,049

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,049)

47. 3.147 3.147 ↑ 1.0 1 1,049

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,049)

  • Index Cond: ((facility_id = $29) AND ((client_id)::text = ($28)::text) AND (effdt = $30))
48. 21.176 134.368 ↑ 1.0 1 988

Index Scan using client_names_ix01 on client_names cn (cost=0.00..24.03 rows=1 width=32) (actual time=0.135..0.136 rows=1 loops=988)

  • Index Cond: (((cn.client_id)::text = ("outer".client_id)::text) AND (cn.facility_id = "outer".facility_id))
  • Filter: ((effseq = (subplan)) AND (effdt = (subplan)))
49.          

SubPlan (forIndex Scan)

50. 6.514 91.196 ↑ 1.0 1 3,257

Aggregate (cost=12.02..12.02 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=3,257)

51. 24.606 84.682 ↓ 4.0 4 3,257

Index Scan using client_names_ix01 on client_names cn (cost=0.00..12.01 rows=1 width=8) (actual time=0.008..0.026 rows=4 loops=3,257)

  • Index Cond: ((($32)::text = (client_id)::text) AND ($33 = facility_id) AND (effdt < '2012-06-30 00:00:00'::timestamp without time zone))
  • Filter: (effseq = (subplan))
52.          

SubPlan (forIndex Scan)

53. 15.019 60.076 ↑ 1.0 1 15,019

Aggregate (cost=6.01..6.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=15,019)

54. 45.057 45.057 ↑ 1.0 1 15,019

Index Scan using client_names_pkey on client_names cni (cost=0.00..6.00 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=15,019)

  • Index Cond: (((client_id)::text = ($22)::text) AND (effdt = $24))
  • Filter: (facility_id = $23)
55. 3.666 21.996 ↑ 1.0 1 3,666

Aggregate (cost=6.01..6.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3,666)

56. 18.330 18.330 ↑ 1.0 1 3,666

Index Scan using client_names_pkey on client_names cni (cost=0.00..6.00 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=3,666)

  • Index Cond: (((client_id)::text = ($32)::text) AND (effdt = $34))
  • Filter: (facility_id = $33)
57. 9.568 79.040 ↑ 1.0 1 988

Index Scan using client_names_pkey on client_names cn (cost=0.00..207.94 rows=1 width=28) (actual time=0.079..0.080 rows=1 loops=988)

  • Index Cond: ((cn.client_id)::text = ("outer".client_id)::text)
  • Filter: ((effseq = (subplan)) AND (effdt = (subplan)))
58.          

SubPlan (forIndex Scan)

59. 4.854 58.248 ↑ 1.0 1 2,427

Aggregate (cost=40.83..40.83 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=2,427)

60. 13.834 53.394 ↓ 4.0 4 2,427

Index Scan using client_names_pkey on client_names cn (cost=0.00..40.83 rows=1 width=8) (actual time=0.008..0.022 rows=4 loops=2,427)

  • Index Cond: ((($35)::text = (client_id)::text) AND (effdt < '2012-06-30 00:00:00'::timestamp without time zone))
  • Filter: (effseq = (subplan))
61.          

SubPlan (forIndex Scan)

62. 9.890 39.560 ↑ 1.0 1 9,890

Aggregate (cost=6.01..6.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=9,890)

63. 29.670 29.670 ↑ 1.0 1 9,890

Index Scan using client_names_pkey on client_names cni (cost=0.00..6.00 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=9,890)

  • Index Cond: (((client_id)::text = ($22)::text) AND (effdt = $24))
  • Filter: (facility_id = $23)
64. 2.806 11.224 ↑ 1.0 1 2,806

Aggregate (cost=6.01..6.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,806)

65. 8.418 8.418 ↑ 1.0 1 2,806

Index Scan using client_names_pkey on client_names cni (cost=0.00..6.00 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,806)

  • Index Cond: (((client_id)::text = ($35)::text) AND (effdt = $37))
  • Filter: (facility_id = $36)
66.          

SubPlan (forAggregate)

67. 17.713 4,239.508 ↑ 1.0 1 988

Nested Loop Left Join (cost=0.00..4,979,091.32 rows=1 width=118) (actual time=4.291..4.291 rows=1 loops=988)

  • Join Filter: (("inner".facility_id = "outer".facility_id) AND ("inner".effdt = (subplan)))
68. 8.656 4,175.288 ↑ 1.0 1 988

Nested Loop (cost=0.00..4,979,044.19 rows=1 width=114) (actual time=4.226..4.226 rows=1 loops=988)

  • Join Filter: ("inner".effdt = (subplan))
69. 4,038.862 4,098.224 ↑ 1.0 1 988

Index Scan using logs_services_log_dttm on logs_services ls (cost=0.00..4,979,020.20 rows=1 width=106) (actual time=4.148..4.148 rows=1 loops=988)

  • Index Cond: ((log_dttm >= '2011-10-01 00:00:00'::timestamp without time zone) AND (log_dttm < '2012-06-01 00:00:00'::timestamp without time zone))
  • Filter: ((facility_id = $13) AND ((client_id)::text = ($15)::text) AND (effseq = (subplan)))
70.          

SubPlan (forIndex Scan)

71. 2.658 59.362 ↑ 1.0 1 886

Aggregate (cost=204.22..204.22 rows=1 width=4) (actual time=0.067..0.067 rows=1 loops=886)

72. 56.704 56.704 ↓ 2.0 2 886

Index Scan using logs_services_log_dttm on logs_services lsi (cost=0.00..204.21 rows=1 width=4) (actual time=0.027..0.064 rows=2 loops=886)

  • Index Cond: (log_dttm = $3)
  • Filter: (((client_id)::text = ($5)::text) AND (facility_id = $6))
73. 5.955 17.720 ↓ 2.0 2 886

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..11.99 rows=1 width=28) (actual time=0.013..0.020 rows=2 loops=886)

  • Index Cond: (((cd.client_id)::text = ("outer".client_id)::text) AND ($13 = cd.facility_id))
  • Filter: (effseq = (subplan))
74.          

SubPlan (forIndex Scan)

75. 4.706 11.765 ↑ 1.0 1 2,353

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=2,353)

76. 7.059 7.059 ↑ 1.0 1 2,353

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,353)

  • Index Cond: (((client_id)::text = ($7)::text) AND (effdt = $9))
  • Filter: (facility_id = $8)
77.          

SubPlan (forNested Loop)

78. 4.224 50.688 ↑ 1.0 1 2,112

Aggregate (cost=11.99..11.99 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=2,112)

79. 14.368 46.464 ↓ 3.0 3 2,112

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..11.99 rows=1 width=8) (actual time=0.008..0.022 rows=3 loops=2,112)

  • Index Cond: ((($7)::text = (client_id)::text) AND ($8 = facility_id) AND (effdt <= $3))
  • Filter: (effseq = (subplan))
80.          

SubPlan (forIndex Scan)

81. 8.024 32.096 ↑ 1.0 1 8,024

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8,024)

82. 24.072 24.072 ↑ 1.0 1 8,024

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=8,024)

  • Index Cond: (((client_id)::text = ($0)::text) AND (effdt = $2))
  • Filter: (facility_id = $1)
83. 5.016 10.632 ↑ 1.0 1 886

Index Scan using client_id_hohid_index on client_hoh hoh (cost=0.00..21.19 rows=1 width=24) (actual time=0.011..0.012 rows=1 loops=886)

  • Index Cond: ((hoh.client_id)::text = ("outer".client_id)::text)
  • Filter: ((effstat = 'A'::bpchar) AND (effseq = (subplan)))
84.          

SubPlan (forIndex Scan)

85. 1.872 5.616 ↑ 1.0 1 936

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=936)

86. 3.744 3.744 ↑ 1.0 1 936

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=936)

  • Index Cond: ((facility_id = $11) AND ((client_id)::text = ($10)::text) AND (effdt = $12))
87.          

SubPlan (forNested Loop Left Join)

88. 1.830 13.725 ↑ 1.0 1 915

Aggregate (cost=11.91..11.91 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=915)

89. 6.295 11.895 ↑ 1.0 1 915

Index Scan using ix_client_hoh_01 on client_hoh hoh (cost=0.00..11.91 rows=1 width=8) (actual time=0.011..0.013 rows=1 loops=915)

  • Index Cond: ((effstat = 'A'::bpchar) AND (($5)::text = (client_id)::text) AND (effdt <= $3))
  • Filter: (($6 = facility_id) AND (effseq = (subplan)))
90.          

SubPlan (forIndex Scan)

91. 2.240 5.600 ↑ 1.0 1 1,120

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,120)

92. 3.360 3.360 ↑ 1.0 1 1,120

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,120)

  • Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
93. 2.658 22.150 ↑ 1.0 1 886

Aggregate (cost=13.97..13.97 rows=1 width=0) (actual time=0.025..0.025 rows=1 loops=886)

94. 9.260 19.492 ↓ 3.0 3 886

Index Scan using ix_client_hoh_02 on client_hoh hoh (cost=0.00..13.97 rows=1 width=0) (actual time=0.011..0.022 rows=3 loops=886)

  • Index Cond: ((effstat = 'A'::bpchar) AND (hoh_id = $4) AND (effdt <= $3))
  • Filter: (((client_id)::text <> ($5)::text) AND (effseq = (subplan)))
95.          

SubPlan (forIndex Scan)

96. 2.558 10.232 ↑ 1.0 1 2,558

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,558)

97. 7.674 7.674 ↑ 1.0 1 2,558

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,558)

  • Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
98. 16.448 4,057.716 ↑ 1.0 1 988

Nested Loop Left Join (cost=0.00..4,979,091.32 rows=1 width=118) (actual time=4.107..4.107 rows=1 loops=988)

  • Join Filter: (("inner".facility_id = "outer".facility_id) AND ("inner".effdt = (subplan)))
99. 7.702 3,997.448 ↑ 1.0 1 988

Nested Loop (cost=0.00..4,979,044.19 rows=1 width=114) (actual time=4.046..4.046 rows=1 loops=988)

  • Join Filter: ("inner".effdt = (subplan))
100. 3,867.632 3,924.336 ↑ 1.0 1 988

Index Scan using logs_services_log_dttm on logs_services ls (cost=0.00..4,979,020.20 rows=1 width=106) (actual time=3.972..3.972 rows=1 loops=988)

  • Index Cond: ((log_dttm >= '2011-10-01 00:00:00'::timestamp without time zone) AND (log_dttm < '2012-06-01 00:00:00'::timestamp without time zone))
  • Filter: ((facility_id = $13) AND ((client_id)::text = ($15)::text) AND (effseq = (subplan)))
101.          

SubPlan (forIndex Scan)

102. 1.772 56.704 ↑ 1.0 1 886

Aggregate (cost=204.22..204.22 rows=1 width=4) (actual time=0.064..0.064 rows=1 loops=886)

103. 54.932 54.932 ↓ 2.0 2 886

Index Scan using logs_services_log_dttm on logs_services lsi (cost=0.00..204.21 rows=1 width=4) (actual time=0.027..0.062 rows=2 loops=886)

  • Index Cond: (log_dttm = $3)
  • Filter: (((client_id)::text = ($5)::text) AND (facility_id = $6))
104. 7.422 16.834 ↓ 2.0 2 886

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..11.99 rows=1 width=28) (actual time=0.011..0.019 rows=2 loops=886)

  • Index Cond: (((cd.client_id)::text = ("outer".client_id)::text) AND ($13 = cd.facility_id))
  • Filter: (effseq = (subplan))
105.          

SubPlan (forIndex Scan)

106. 2.353 9.412 ↑ 1.0 1 2,353

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,353)

107. 7.059 7.059 ↑ 1.0 1 2,353

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,353)

  • Index Cond: (((client_id)::text = ($7)::text) AND (effdt = $9))
  • Filter: (facility_id = $8)
108.          

SubPlan (forNested Loop)

109. 4.224 48.576 ↑ 1.0 1 2,112

Aggregate (cost=11.99..11.99 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=2,112)

110. 12.256 44.352 ↓ 3.0 3 2,112

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..11.99 rows=1 width=8) (actual time=0.008..0.021 rows=3 loops=2,112)

  • Index Cond: ((($7)::text = (client_id)::text) AND ($8 = facility_id) AND (effdt <= $3))
  • Filter: (effseq = (subplan))
111.          

SubPlan (forIndex Scan)

112. 8.024 32.096 ↑ 1.0 1 8,024

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8,024)

113. 24.072 24.072 ↑ 1.0 1 8,024

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=8,024)

  • Index Cond: (((client_id)::text = ($0)::text) AND (effdt = $2))
  • Filter: (facility_id = $1)
114. 5.066 9.746 ↑ 1.0 1 886

Index Scan using client_id_hohid_index on client_hoh hoh (cost=0.00..21.19 rows=1 width=24) (actual time=0.010..0.011 rows=1 loops=886)

  • Index Cond: ((hoh.client_id)::text = ("outer".client_id)::text)
  • Filter: ((effstat = 'A'::bpchar) AND (effseq = (subplan)))
115.          

SubPlan (forIndex Scan)

116. 1.872 4.680 ↑ 1.0 1 936

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=936)

117. 2.808 2.808 ↑ 1.0 1 936

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=936)

  • Index Cond: ((facility_id = $11) AND ((client_id)::text = ($10)::text) AND (effdt = $12))
118.          

SubPlan (forNested Loop Left Join)

119. 1.830 12.810 ↑ 1.0 1 915

Aggregate (cost=11.91..11.91 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=915)

120. 5.380 10.980 ↑ 1.0 1 915

Index Scan using ix_client_hoh_01 on client_hoh hoh (cost=0.00..11.91 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=915)

  • Index Cond: ((effstat = 'A'::bpchar) AND (($5)::text = (client_id)::text) AND (effdt <= $3))
  • Filter: (($6 = facility_id) AND (effseq = (subplan)))
121.          

SubPlan (forIndex Scan)

122. 2.240 5.600 ↑ 1.0 1 1,120

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,120)

123. 3.360 3.360 ↑ 1.0 1 1,120

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,120)

  • Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
124. 2.658 21.264 ↑ 1.0 1 886

Aggregate (cost=13.97..13.97 rows=1 width=0) (actual time=0.024..0.024 rows=1 loops=886)

125. 8.374 18.606 ↓ 3.0 3 886

Index Scan using ix_client_hoh_02 on client_hoh hoh (cost=0.00..13.97 rows=1 width=0) (actual time=0.010..0.021 rows=3 loops=886)

  • Index Cond: ((effstat = 'A'::bpchar) AND (hoh_id = $4) AND (effdt <= $3))
  • Filter: (((client_id)::text <> ($5)::text) AND (effseq = (subplan)))
126.          

SubPlan (forIndex Scan)

127. 2.558 10.232 ↑ 1.0 1 2,558

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,558)

128. 7.674 7.674 ↑ 1.0 1 2,558

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,558)

  • Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
129. 2.386 834.782 ↑ 1.0 1 194

Nested Loop Left Join (cost=0.00..4,979,091.32 rows=1 width=118) (actual time=4.303..4.303 rows=1 loops=194)

  • Join Filter: (("inner".facility_id = "outer".facility_id) AND ("inner".effdt = (subplan)))
130. 1.182 828.380 ↑ 1.0 1 194

Nested Loop (cost=0.00..4,979,044.19 rows=1 width=114) (actual time=4.270..4.270 rows=1 loops=194)

  • Join Filter: ("inner".effdt = (subplan))
131. 805.354 815.770 ↑ 1.0 1 194

Index Scan using logs_services_log_dttm on logs_services ls (cost=0.00..4,979,020.20 rows=1 width=106) (actual time=4.205..4.205 rows=1 loops=194)

  • Index Cond: ((log_dttm >= '2011-10-01 00:00:00'::timestamp without time zone) AND (log_dttm < '2012-06-01 00:00:00'::timestamp without time zone))
  • Filter: ((facility_id = $13) AND ((client_id)::text = ($15)::text) AND (effseq = (subplan)))
132.          

SubPlan (forIndex Scan)

133. 0.336 10.416 ↑ 1.0 1 168

Aggregate (cost=204.22..204.22 rows=1 width=4) (actual time=0.062..0.062 rows=1 loops=168)

134. 10.080 10.080 ↓ 2.0 2 168

Index Scan using logs_services_log_dttm on logs_services lsi (cost=0.00..204.21 rows=1 width=4) (actual time=0.016..0.060 rows=2 loops=168)

  • Index Cond: (log_dttm = $3)
  • Filter: (((client_id)::text = ($5)::text) AND (facility_id = $6))
135. 1.232 3.024 ↓ 2.0 2 168

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..11.99 rows=1 width=28) (actual time=0.010..0.018 rows=2 loops=168)

  • Index Cond: (((cd.client_id)::text = ("outer".client_id)::text) AND ($13 = cd.facility_id))
  • Filter: (effseq = (subplan))
136.          

SubPlan (forIndex Scan)

137. 0.448 1.792 ↑ 1.0 1 448

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=448)

138. 1.344 1.344 ↑ 1.0 1 448

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=448)

  • Index Cond: (((client_id)::text = ($7)::text) AND (effdt = $9))
  • Filter: (facility_id = $8)
139.          

SubPlan (forNested Loop)

140. 0.764 8.404 ↑ 1.0 1 382

Aggregate (cost=11.99..11.99 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=382)

141. 2.124 7.640 ↓ 3.0 3 382

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..11.99 rows=1 width=8) (actual time=0.008..0.020 rows=3 loops=382)

  • Index Cond: ((($7)::text = (client_id)::text) AND ($8 = facility_id) AND (effdt <= $3))
  • Filter: (effseq = (subplan))
142.          

SubPlan (forIndex Scan)

143. 1.379 5.516 ↑ 1.0 1 1,379

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1,379)

144. 4.137 4.137 ↑ 1.0 1 1,379

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1,379)

  • Index Cond: (((client_id)::text = ($0)::text) AND (effdt = $2))
  • Filter: (facility_id = $1)
145. 0.659 1.344 ↑ 1.0 1 168

Index Scan using client_id_hohid_index on client_hoh hoh (cost=0.00..21.19 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=168)

  • Index Cond: ((hoh.client_id)::text = ("outer".client_id)::text)
  • Filter: ((effstat = 'A'::bpchar) AND (effseq = (subplan)))
146.          

SubPlan (forIndex Scan)

147. 0.274 0.685 ↑ 1.0 1 137

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=137)

148. 0.411 0.411 ↑ 1.0 1 137

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=137)

  • Index Cond: ((facility_id = $11) AND ((client_id)::text = ($10)::text) AND (effdt = $12))
149.          

SubPlan (forNested Loop Left Join)

150. 0.256 1.664 ↑ 1.0 1 128

Aggregate (cost=11.91..11.91 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=128)

151. 0.836 1.408 ↑ 1.0 1 128

Index Scan using ix_client_hoh_01 on client_hoh hoh (cost=0.00..11.91 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=128)

  • Index Cond: ((effstat = 'A'::bpchar) AND (($5)::text = (client_id)::text) AND (effdt <= $3))
  • Filter: (($6 = facility_id) AND (effseq = (subplan)))
152.          

SubPlan (forIndex Scan)

153. 0.143 0.572 ↑ 1.0 1 143

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=143)

154. 0.429 0.429 ↑ 1.0 1 143

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=143)

  • Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
155. 0.336 1.008 ↑ 1.0 1 168

Aggregate (cost=13.97..13.97 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=168)

156. 0.557 0.672 ↓ 0.0 0 168

Index Scan using ix_client_hoh_02 on client_hoh hoh (cost=0.00..13.97 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=168)

  • Index Cond: ((effstat = 'A'::bpchar) AND (hoh_id = $4) AND (effdt <= $3))
  • Filter: (((client_id)::text <> ($5)::text) AND (effseq = (subplan)))
157.          

SubPlan (forIndex Scan)

158. 0.046 0.115 ↑ 1.0 1 23

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=23)

159. 0.069 0.069 ↑ 1.0 1 23

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=23)

  • Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
160. 20,992.598 578,802.180 ↑ 1.0 1 794

Nested Loop (cost=0.00..4,982,845.82 rows=1 width=118) (actual time=728.970..728.970 rows=1 loops=794)

  • Join Filter: ((household_or_person_id("inner".hoh_id, "outer".client_id) = household_or_person_id($14, $15)) AND ("inner".effdt = (subplan)))
161. 11,030.284 524,047.146 ↓ 4,246.0 4,246 794

Nested Loop (cost=0.00..4,982,798.68 rows=1 width=122) (actual time=0.306..660.009 rows=4,246 loops=794)

  • Join Filter: ("inner".effdt = (subplan))
162. 6,034.874 225,588.104 ↓ 26.5 4,247 794

Index Scan using logs_services_log_dttm on logs_services ls (cost=0.00..4,978,959.53 rows=160 width=106) (actual time=0.060..284.116 rows=4,247 loops=794)

  • Index Cond: ((log_dttm >= '2011-10-01 00:00:00'::timestamp without time zone) AND (log_dttm < '2012-06-01 00:00:00'::timestamp without time zone))
  • Filter: ((facility_id = $13) AND (effseq = (subplan)))
163.          

SubPlan (forIndex Scan)

164. 3,377.742 219,553.230 ↑ 1.0 1 3,377,742

Aggregate (cost=204.22..204.22 rows=1 width=4) (actual time=0.065..0.065 rows=1 loops=3,377,742)

165. 216,175.488 216,175.488 ↓ 2.0 2 3,377,742

Index Scan using logs_services_log_dttm on logs_services lsi (cost=0.00..204.21 rows=1 width=4) (actual time=0.025..0.064 rows=2 loops=3,377,742)

  • Index Cond: (log_dttm = $3)
  • Filter: (((client_id)::text = ($5)::text) AND (facility_id = $6))
166. 29,141.728 74,194.296 ↓ 3.0 3 3,372,468

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..11.99 rows=1 width=28) (actual time=0.010..0.022 rows=3 loops=3,372,468)

  • Index Cond: (((cd.client_id)::text = ("outer".client_id)::text) AND ($13 = cd.facility_id))
  • Filter: (effseq = (subplan))
167.          

SubPlan (forIndex Scan)

168. 11,263.142 45,052.568 ↑ 1.0 1 11,263,142

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=11,263,142)

169. 33,789.426 33,789.426 ↑ 1.0 1 11,263,142

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=11,263,142)

  • Index Cond: (((client_id)::text = ($7)::text) AND (effdt = $9))
  • Filter: (facility_id = $8)
170.          

SubPlan (forNested Loop)

171. 20,308.044 213,234.462 ↑ 1.0 1 10,154,022

Aggregate (cost=11.99..11.99 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=10,154,022)

172. 51,141.658 192,926.418 ↓ 3.0 3 10,154,022

Index Scan using client_detail_ix01 on client_detail cd (cost=0.00..11.99 rows=1 width=8) (actual time=0.007..0.019 rows=3 loops=10,154,022)

  • Index Cond: ((($7)::text = (client_id)::text) AND ($8 = facility_id) AND (effdt <= $3))
  • Filter: (effseq = (subplan))
173.          

SubPlan (forIndex Scan)

174. 35,446.190 141,784.760 ↑ 1.0 1 35,446,190

Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=35,446,190)

175. 106,338.570 106,338.570 ↑ 1.0 1 35,446,190

Index Scan using client_detail_ix02 on client_detail cdi (cost=0.00..5.98 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=35,446,190)

  • Index Cond: (((client_id)::text = ($0)::text) AND (effdt = $2))
  • Filter: (facility_id = $1)
176. 17,663.735 33,714.960 ↑ 1.0 1 3,371,496

Index Scan using client_id_hohid_index on client_hoh hoh (cost=0.00..21.19 rows=1 width=24) (actual time=0.009..0.010 rows=1 loops=3,371,496)

  • Index Cond: ((hoh.client_id)::text = ("outer".client_id)::text)
  • Filter: ((effstat = 'A'::bpchar) AND ($13 = facility_id) AND (effseq = (subplan)))
177.          

SubPlan (forIndex Scan)

178. 3,210.245 16,051.225 ↑ 1.0 1 3,210,245

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=3,210,245)

179. 12,840.980 12,840.980 ↑ 1.0 1 3,210,245

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=3,210,245)

  • Index Cond: ((facility_id = $11) AND ((client_id)::text = ($10)::text) AND (effdt = $12))
180.          

SubPlan (forNested Loop)

181. 2.586 20.688 ↑ 1.0 1 862

Aggregate (cost=11.91..11.91 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=862)

182. 13.157 18.102 ↑ 1.0 1 862

Index Scan using ix_client_hoh_01 on client_hoh hoh (cost=0.00..11.91 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=862)

  • Index Cond: ((effstat = 'A'::bpchar) AND (($5)::text = (client_id)::text) AND (effdt <= $3))
  • Filter: (($6 = facility_id) AND (effseq = (subplan)))
183.          

SubPlan (forIndex Scan)

184. 1.978 4.945 ↑ 1.0 1 989

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=989)

185. 2.967 2.967 ↑ 1.0 1 989

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=989)

  • Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
186. 2.172 26.788 ↑ 1.0 1 724

Aggregate (cost=13.97..13.97 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=724)

187. 14.396 24.616 ↓ 4.0 4 724

Index Scan using ix_client_hoh_02 on client_hoh hoh (cost=0.00..13.97 rows=1 width=0) (actual time=0.019..0.034 rows=4 loops=724)

  • Index Cond: ((effstat = 'A'::bpchar) AND (hoh_id = $4) AND (effdt <= $3))
  • Filter: (((client_id)::text <> ($5)::text) AND (effseq = (subplan)))
188.          

SubPlan (forIndex Scan)

189. 2.555 10.220 ↑ 1.0 1 2,555

Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,555)

190. 7.665 7.665 ↑ 1.0 1 2,555

Index Scan using client_hoh_ix01 on client_hoh hohi (cost=0.00..5.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,555)

  • Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))