#	exclusive	inclusive	rows x	rows	loops	node
1.	82.200	588362.061	↑ 1.0	1	1	→ Aggregate (cost=136514.51..20056635.44 rows=1 width=104) (actual time=588362.061..588362.061 rows=1 loops=1)
2.						Initplan (forAggregate)
3.	0.001	0.445	↑ 1.0	1	1	→ Aggregate (cost=1312.03..1312.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=1306.34..1312.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=1306.33..1306.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..1306.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=1309.82..1309.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..1309.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..1304.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..133892.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..133684.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..133660.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..133600.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..133595.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..133562.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..130676.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	1566	→ Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1566)
25.	6.264	6.264	↑ 1.0	1	1566	→ 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=1566) 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	1370	→ Aggregate (cost=11.99..11.99 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1370)
28.	8.974	34.250	↓ 4.0	4	1370	→ 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=1370) 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	6319	→ Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=6319)
31.	18.957	18.957	↑ 1.0	1	6319	→ 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=6319) 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	1049	→ Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1049)
47.	3.147	3.147	↑ 1.0	1	1049	→ 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=1049) 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	3257	→ Aggregate (cost=12.02..12.02 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=3257)
51.	24.606	84.682	↓ 4.0	4	3257	→ 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=3257) 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	15019	→ Aggregate (cost=6.01..6.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=15019)
54.	45.057	45.057	↑ 1.0	1	15019	→ 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=15019) Index Cond: (((client_id)::text = ($22)::text) AND (effdt = $24)) Filter: (facility_id = $23)
55.	3.666	21.996	↑ 1.0	1	3666	→ Aggregate (cost=6.01..6.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3666)
56.	18.330	18.330	↑ 1.0	1	3666	→ 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=3666) 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	2427	→ Aggregate (cost=40.83..40.83 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=2427)
60.	13.834	53.394	↓ 4.0	4	2427	→ 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=2427) 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	9890	→ Aggregate (cost=6.01..6.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=9890)
63.	29.670	29.670	↑ 1.0	1	9890	→ 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=9890) Index Cond: (((client_id)::text = ($22)::text) AND (effdt = $24)) Filter: (facility_id = $23)
64.	2.806	11.224	↑ 1.0	1	2806	→ Aggregate (cost=6.01..6.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2806)
65.	8.418	8.418	↑ 1.0	1	2806	→ 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=2806) Index Cond: (((client_id)::text = ($35)::text) AND (effdt = $37)) Filter: (facility_id = $36)
66.						SubPlan (forAggregate)
67.	17.713	4239.508	↑ 1.0	1	988	→ Nested Loop Left Join (cost=0.00..4979091.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	4175.288	↑ 1.0	1	988	→ Nested Loop (cost=0.00..4979044.19 rows=1 width=114) (actual time=4.226..4.226 rows=1 loops=988) Join Filter: ("inner".effdt = (subplan))
69.	4038.862	4098.224	↑ 1.0	1	988	→ Index Scan using logs_services_log_dttm on logs_services ls (cost=0.00..4979020.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	2353	→ Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=2353)
76.	7.059	7.059	↑ 1.0	1	2353	→ 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=2353) 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	2112	→ Aggregate (cost=11.99..11.99 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=2112)
79.	14.368	46.464	↓ 3.0	3	2112	→ 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=2112) 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	8024	→ Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8024)
82.	24.072	24.072	↑ 1.0	1	8024	→ 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=8024) 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	1120	→ Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1120)
92.	3.360	3.360	↑ 1.0	1	1120	→ 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=1120) 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	2558	→ Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2558)
97.	7.674	7.674	↑ 1.0	1	2558	→ 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=2558) Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
98.	16.448	4057.716	↑ 1.0	1	988	→ Nested Loop Left Join (cost=0.00..4979091.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	3997.448	↑ 1.0	1	988	→ Nested Loop (cost=0.00..4979044.19 rows=1 width=114) (actual time=4.046..4.046 rows=1 loops=988) Join Filter: ("inner".effdt = (subplan))
100.	3867.632	3924.336	↑ 1.0	1	988	→ Index Scan using logs_services_log_dttm on logs_services ls (cost=0.00..4979020.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	2353	→ Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2353)
107.	7.059	7.059	↑ 1.0	1	2353	→ 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=2353) 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	2112	→ Aggregate (cost=11.99..11.99 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=2112)
110.	12.256	44.352	↓ 3.0	3	2112	→ 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=2112) 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	8024	→ Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8024)
113.	24.072	24.072	↑ 1.0	1	8024	→ 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=8024) 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	1120	→ Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1120)
123.	3.360	3.360	↑ 1.0	1	1120	→ 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=1120) 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	2558	→ Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2558)
128.	7.674	7.674	↑ 1.0	1	2558	→ 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=2558) 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..4979091.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..4979044.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..4979020.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	1379	→ Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1379)
144.	4.137	4.137	↑ 1.0	1	1379	→ 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=1379) 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.	20992.598	578802.180	↑ 1.0	1	794	→ Nested Loop (cost=0.00..4982845.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.	11030.284	524047.146	↓ 4246.0	4246	794	→ Nested Loop (cost=0.00..4982798.68 rows=1 width=122) (actual time=0.306..660.009 rows=4246 loops=794) Join Filter: ("inner".effdt = (subplan))
162.	6034.874	225588.104	↓ 26.5	4247	794	→ Index Scan using logs_services_log_dttm on logs_services ls (cost=0.00..4978959.53 rows=160 width=106) (actual time=0.060..284.116 rows=4247 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.	3377.742	219553.230	↑ 1.0	1	3377742	→ Aggregate (cost=204.22..204.22 rows=1 width=4) (actual time=0.065..0.065 rows=1 loops=3377742)
165.	216175.488	216175.488	↓ 2.0	2	3377742	→ 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=3377742) Index Cond: (log_dttm = $3) Filter: (((client_id)::text = ($5)::text) AND (facility_id = $6))
166.	29141.728	74194.296	↓ 3.0	3	3372468	→ 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=3372468) Index Cond: (((cd.client_id)::text = ("outer".client_id)::text) AND ($13 = cd.facility_id)) Filter: (effseq = (subplan))
167.						SubPlan (forIndex Scan)
168.	11263.142	45052.568	↑ 1.0	1	11263142	→ Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=11263142)
169.	33789.426	33789.426	↑ 1.0	1	11263142	→ 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=11263142) Index Cond: (((client_id)::text = ($7)::text) AND (effdt = $9)) Filter: (facility_id = $8)
170.						SubPlan (forNested Loop)
171.	20308.044	213234.462	↑ 1.0	1	10154022	→ Aggregate (cost=11.99..11.99 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=10154022)
172.	51141.658	192926.418	↓ 3.0	3	10154022	→ 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=10154022) Index Cond: ((($7)::text = (client_id)::text) AND ($8 = facility_id) AND (effdt <= $3)) Filter: (effseq = (subplan))
173.						SubPlan (forIndex Scan)
174.	35446.190	141784.760	↑ 1.0	1	35446190	→ Aggregate (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=35446190)
175.	106338.570	106338.570	↑ 1.0	1	35446190	→ 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=35446190) Index Cond: (((client_id)::text = ($0)::text) AND (effdt = $2)) Filter: (facility_id = $1)
176.	17663.735	33714.960	↑ 1.0	1	3371496	→ 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=3371496) 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.	3210.245	16051.225	↑ 1.0	1	3210245	→ Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=3210245)
179.	12840.980	12840.980	↑ 1.0	1	3210245	→ 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=3210245) 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	2555	→ Aggregate (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2555)
190.	7.665	7.665	↑ 1.0	1	2555	→ 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=2555) Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))

                                                                                                                                       QUERY PLAN                                                                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=136514.51..20056635.44 rows=1 width=104) (actual time=588362.061..588362.061 rows=1 loops=1)
   InitPlan
     ->  Aggregate  (cost=1312.03..1312.03 rows=1 width=10) (actual time=0.445..0.445 rows=1 loops=1)
           ->  Hash Join  (cost=1306.34..1312.02 rows=3 width=10) (actual time=0.444..0.444 rows=0 loops=1)
                 Hash Cond: ("outer".service_id = "inner".service_id)
                 ->  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)
                 ->  Hash  (cost=1306.33..1306.33 rows=3 width=14) (actual time=0.350..0.350 rows=0 loops=1)
                       ->  Index Scan using logs_services_log_dttm on logs_services  (cost=0.00..1306.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))
     ->  Aggregate  (cost=1309.82..1309.82 rows=1 width=10) (actual time=0.267..0.267 rows=1 loops=1)
           ->  Nested Loop  (cost=0.00..1309.81 rows=2 width=10) (actual time=0.064..0.263 rows=1 loops=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)
                 ->  Index Scan using logs_services_log_dttm on logs_services  (cost=0.00..1304.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))
   ->  Nested Loop Left Join  (cost=0.00..133892.13 rows=1 width=104) (actual time=0.633..344.963 rows=988 loops=1)
         ->  Nested Loop Left Join  (cost=0.00..133684.18 rows=1 width=76) (actual time=0.555..261.359 rows=988 loops=1)
               ->  Nested Loop Left Join  (cost=0.00..133660.14 rows=1 width=48) (actual time=0.414..122.748 rows=988 loops=1)
                     ->  Nested Loop Left Join  (cost=0.00..133600.60 rows=1 width=40) (actual time=0.376..101.009 rows=423 loops=1)
                           ->  Nested Loop Left Join  (cost=0.00..133595.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)))
                                 ->  Nested Loop  (cost=0.00..133562.42 rows=1 width=40) (actual time=0.249..81.915 rows=423 loops=1)
                                       Join Filter: ("inner".effdt = (subplan))
                                       ->  Index Scan using logs_services_log_dttm on logs_services ls  (cost=0.00..130676.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)))
                                             SubPlan
                                               ->  Aggregate  (cost=204.22..204.22 rows=1 width=4) (actual time=0.064..0.064 rows=1 loops=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))
                                       ->  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))
                                             SubPlan
                                               ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1566)
                                                     ->  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=1566)
                                                           Index Cond: (((client_id)::text = ($19)::text) AND (effdt = $21))
                                                           Filter: (facility_id = $20)
                                       SubPlan
                                         ->  Aggregate  (cost=11.99..11.99 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1370)
                                               ->  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=1370)
                                                     Index Cond: ((($19)::text = (client_id)::text) AND ($20 = facility_id) AND (effdt <= $18))
                                                     Filter: (effseq = (subplan))
                                                     SubPlan
                                                       ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=6319)
                                                             ->  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=6319)
                                                                   Index Cond: (((client_id)::text = ($22)::text) AND (effdt = $24))
                                                                   Filter: (facility_id = $23)
                                 ->  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)))
                                       SubPlan
                                         ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=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))
                                 SubPlan
                                   ->  Aggregate  (cost=11.91..11.91 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=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)))
                                               SubPlan
                                                 ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=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))
                           ->  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)
                     ->  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)))))
                           SubPlan
                             ->  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))
                             ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1049)
                                   ->  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=1049)
                                         Index Cond: ((facility_id = $29) AND ((client_id)::text = ($28)::text) AND (effdt = $30))
               ->  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)))
                     SubPlan
                       ->  Aggregate  (cost=12.02..12.02 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=3257)
                             ->  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=3257)
                                   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))
                                   SubPlan
                                     ->  Aggregate  (cost=6.01..6.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=15019)
                                           ->  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=15019)
                                                 Index Cond: (((client_id)::text = ($22)::text) AND (effdt = $24))
                                                 Filter: (facility_id = $23)
                       ->  Aggregate  (cost=6.01..6.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3666)
                             ->  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=3666)
                                   Index Cond: (((client_id)::text = ($32)::text) AND (effdt = $34))
                                   Filter: (facility_id = $33)
         ->  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)))
               SubPlan
                 ->  Aggregate  (cost=40.83..40.83 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=2427)
                       ->  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=2427)
                             Index Cond: ((($35)::text = (client_id)::text) AND (effdt < '2012-06-30 00:00:00'::timestamp without time zone))
                             Filter: (effseq = (subplan))
                             SubPlan
                               ->  Aggregate  (cost=6.01..6.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=9890)
                                     ->  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=9890)
                                           Index Cond: (((client_id)::text = ($22)::text) AND (effdt = $24))
                                           Filter: (facility_id = $23)
                 ->  Aggregate  (cost=6.01..6.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2806)
                       ->  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=2806)
                             Index Cond: (((client_id)::text = ($35)::text) AND (effdt = $37))
                             Filter: (facility_id = $36)
   SubPlan
     ->  Nested Loop Left Join  (cost=0.00..4979091.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)))
           ->  Nested Loop  (cost=0.00..4979044.19 rows=1 width=114) (actual time=4.226..4.226 rows=1 loops=988)
                 Join Filter: ("inner".effdt = (subplan))
                 ->  Index Scan using logs_services_log_dttm on logs_services ls  (cost=0.00..4979020.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)))
                       SubPlan
                         ->  Aggregate  (cost=204.22..204.22 rows=1 width=4) (actual time=0.067..0.067 rows=1 loops=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))
                 ->  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))
                       SubPlan
                         ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=2353)
                               ->  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=2353)
                                     Index Cond: (((client_id)::text = ($7)::text) AND (effdt = $9))
                                     Filter: (facility_id = $8)
                 SubPlan
                   ->  Aggregate  (cost=11.99..11.99 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=2112)
                         ->  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=2112)
                               Index Cond: ((($7)::text = (client_id)::text) AND ($8 = facility_id) AND (effdt <= $3))
                               Filter: (effseq = (subplan))
                               SubPlan
                                 ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8024)
                                       ->  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=8024)
                                             Index Cond: (((client_id)::text = ($0)::text) AND (effdt = $2))
                                             Filter: (facility_id = $1)
           ->  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)))
                 SubPlan
                   ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=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))
           SubPlan
             ->  Aggregate  (cost=11.91..11.91 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=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)))
                         SubPlan
                           ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1120)
                                 ->  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=1120)
                                       Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
             ->  Aggregate  (cost=13.97..13.97 rows=1 width=0) (actual time=0.025..0.025 rows=1 loops=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)))
                         SubPlan
                           ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2558)
                                 ->  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=2558)
                                       Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
     ->  Nested Loop Left Join  (cost=0.00..4979091.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)))
           ->  Nested Loop  (cost=0.00..4979044.19 rows=1 width=114) (actual time=4.046..4.046 rows=1 loops=988)
                 Join Filter: ("inner".effdt = (subplan))
                 ->  Index Scan using logs_services_log_dttm on logs_services ls  (cost=0.00..4979020.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)))
                       SubPlan
                         ->  Aggregate  (cost=204.22..204.22 rows=1 width=4) (actual time=0.064..0.064 rows=1 loops=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))
                 ->  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))
                       SubPlan
                         ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2353)
                               ->  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=2353)
                                     Index Cond: (((client_id)::text = ($7)::text) AND (effdt = $9))
                                     Filter: (facility_id = $8)
                 SubPlan
                   ->  Aggregate  (cost=11.99..11.99 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=2112)
                         ->  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=2112)
                               Index Cond: ((($7)::text = (client_id)::text) AND ($8 = facility_id) AND (effdt <= $3))
                               Filter: (effseq = (subplan))
                               SubPlan
                                 ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8024)
                                       ->  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=8024)
                                             Index Cond: (((client_id)::text = ($0)::text) AND (effdt = $2))
                                             Filter: (facility_id = $1)
           ->  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)))
                 SubPlan
                   ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=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))
           SubPlan
             ->  Aggregate  (cost=11.91..11.91 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=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)))
                         SubPlan
                           ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1120)
                                 ->  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=1120)
                                       Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
             ->  Aggregate  (cost=13.97..13.97 rows=1 width=0) (actual time=0.024..0.024 rows=1 loops=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)))
                         SubPlan
                           ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2558)
                                 ->  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=2558)
                                       Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
     ->  Nested Loop Left Join  (cost=0.00..4979091.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)))
           ->  Nested Loop  (cost=0.00..4979044.19 rows=1 width=114) (actual time=4.270..4.270 rows=1 loops=194)
                 Join Filter: ("inner".effdt = (subplan))
                 ->  Index Scan using logs_services_log_dttm on logs_services ls  (cost=0.00..4979020.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)))
                       SubPlan
                         ->  Aggregate  (cost=204.22..204.22 rows=1 width=4) (actual time=0.062..0.062 rows=1 loops=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))
                 ->  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))
                       SubPlan
                         ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=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)
                 SubPlan
                   ->  Aggregate  (cost=11.99..11.99 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=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))
                               SubPlan
                                 ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1379)
                                       ->  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=1379)
                                             Index Cond: (((client_id)::text = ($0)::text) AND (effdt = $2))
                                             Filter: (facility_id = $1)
           ->  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)))
                 SubPlan
                   ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=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))
           SubPlan
             ->  Aggregate  (cost=11.91..11.91 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=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)))
                         SubPlan
                           ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=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))
             ->  Aggregate  (cost=13.97..13.97 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=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)))
                         SubPlan
                           ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=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))
     ->  Nested Loop  (cost=0.00..4982845.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)))
           ->  Nested Loop  (cost=0.00..4982798.68 rows=1 width=122) (actual time=0.306..660.009 rows=4246 loops=794)
                 Join Filter: ("inner".effdt = (subplan))
                 ->  Index Scan using logs_services_log_dttm on logs_services ls  (cost=0.00..4978959.53 rows=160 width=106) (actual time=0.060..284.116 rows=4247 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)))
                       SubPlan
                         ->  Aggregate  (cost=204.22..204.22 rows=1 width=4) (actual time=0.065..0.065 rows=1 loops=3377742)
                               ->  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=3377742)
                                     Index Cond: (log_dttm = $3)
                                     Filter: (((client_id)::text = ($5)::text) AND (facility_id = $6))
                 ->  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=3372468)
                       Index Cond: (((cd.client_id)::text = ("outer".client_id)::text) AND ($13 = cd.facility_id))
                       Filter: (effseq = (subplan))
                       SubPlan
                         ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=11263142)
                               ->  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=11263142)
                                     Index Cond: (((client_id)::text = ($7)::text) AND (effdt = $9))
                                     Filter: (facility_id = $8)
                 SubPlan
                   ->  Aggregate  (cost=11.99..11.99 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=10154022)
                         ->  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=10154022)
                               Index Cond: ((($7)::text = (client_id)::text) AND ($8 = facility_id) AND (effdt <= $3))
                               Filter: (effseq = (subplan))
                               SubPlan
                                 ->  Aggregate  (cost=5.99..5.99 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=35446190)
                                       ->  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=35446190)
                                             Index Cond: (((client_id)::text = ($0)::text) AND (effdt = $2))
                                             Filter: (facility_id = $1)
           ->  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=3371496)
                 Index Cond: ((hoh.client_id)::text = ("outer".client_id)::text)
                 Filter: ((effstat = 'A'::bpchar) AND ($13 = facility_id) AND (effseq = (subplan)))
                 SubPlan
                   ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=3210245)
                         ->  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=3210245)
                               Index Cond: ((facility_id = $11) AND ((client_id)::text = ($10)::text) AND (effdt = $12))
           SubPlan
             ->  Aggregate  (cost=11.91..11.91 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=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)))
                         SubPlan
                           ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=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))
             ->  Aggregate  (cost=13.97..13.97 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=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)))
                         SubPlan
                           ->  Aggregate  (cost=5.97..5.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2555)
                                 ->  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=2555)
                                       Index Cond: ((facility_id = $1) AND ((client_id)::text = ($0)::text) AND (effdt = $2))
 Total runtime: 588364.603 ms
(334 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	53	73817.810 ms	12.5 %
Hash	1	0.000 ms	0.0 %
Hash Join	1	0.070 ms	0.0 %
Index Scan	73	482448.925 ms	82.0 %
Nested Loop	7	32043.587 ms	5.4 %
Nested Loop Left Join	8	51.645 ms	0.0 %
Seq Scan	1	0.024 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
client_detail	20	220561.114 ms	37.5 %
Index Scan	20	220561.114 ms	100.0 %
client_hoh	31	30641.851 ms	5.2 %
Index Scan	31	30641.851 ms	100.0 %
client_names	8	170.659 ms	0.0 %
Index Scan	8	170.659 ms	100.0 %
logs_services	12	231073.183 ms	39.3 %
Index Scan	12	231073.183 ms	100.0 %
service	3	2.142 ms	0.0 %
Index Scan	2	2.118 ms	98.9 %
Seq Scan	1	0.024 ms	1.1 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: yVoN : Big, slow after analyze

Colorize:

Columns visibility:

Per node type stats

Per table stats