Public feed
#604491
Hash Left Join (cost=1409464.72..1409466.86 rows=47 width=888) (actual time=3332367.093..3332477.306 rows=557 loops=1)
Hash Cond: (choice_scoring_results.result_id = xx.result_id)
-> Merge Left Join (cost=1012612.48..1012612.74 rows=47 width=856) (actual time=1744794.873..1744797.480 rows=557 loops=1)
Merge Cond: (choice_scoring_results.result_id = v.result_id)
-> Sort (cost=613196.74..613196.86 rows=47 width=824) (actual time=27316.392..27316.841 rows=557 loops=1)
Sort Key: choice_scoring_results.result_id
-> Subquery Scan choice_scoring_results (cost=613190.97..613195.43 rows=47 width=824) (actual time=27308.970..27313.378 rows=557 loops=1)
-> Unique (cost=613190.97..613194.96 rows=47 width=882) (actual time=27308.956..27311.869 rows=557 loops=1)
-> Sort (cost=613190.97..613191.09 rows=47 width=882) (actual time=27308.947..27309.530 rows=557 loops=1)
Sort Key: ccs.result_id, ccs.sub_id, ccs.result_severity, sum(ccs.na_sub_check), ccs.guestrooms_inspected, ccs.dept, ccs.sub_dept, ccs.type1, ccs.
group1, ccs.results_status, ccs.insp_id, ccs.str_insp_id, ccs.set_id, ccs.effective_date, ccs.stan_id, ccs.orderby, ccs.comp_num, ccs.lra_num, NULL::character varying, ccs.descrip
tion, ccs.dateactive, ccs.dateexpire, max(((ccs.capital_issue)::integer)::text), ccs.trigger1, ccs.trigger2, ccs.other, ccs.standards_notes, ccs.brand_id, ccs.brand, ccs.firm, ccs
.sub_description, CASE WHEN ((inspections.announce_date)::date <= '2007-06-19'::date) THEN CASE WHEN ((ccs.guestrooms_inspected)::numeric = sum(ccs.na_sub_check)) THEN 0::numeric
WHEN ((((ccs.group1)::text = 'Cleanliness'::text) OR ((ccs.group1)::text = 'Condition'::text)) AND (((ccs.dept)::text = 'Guest Room'::text) OR ((ccs.dept)::text = 'Guest Bathroom'
::text))) THEN (max(ccs.points) * (ccs.guestrooms_inspected)::numeric) WHEN (((ccs.dept)::text <> 'Guest Room'::text) AND ((ccs.dept)::text <> 'Guest Bathroom'::text) AND (concat_
agg((ccs.name)::text) <> ''::text)) THEN (max(ccs.points) * (count(ccs.sub_id))::numeric) ELSE max(ccs.points) END ELSE CASE WHEN ((max(view_total_sub_features.sub_feature_count))
::numeric = sum(ccs.na_sub_check)) THEN 0::numeric WHEN ((((ccs.group1)::text = 'Cleanliness'::text) OR ((ccs.group1)::text = 'Condition'::text)) AND (((ccs.dept)::text = 'Guest R
oom'::text) OR ((ccs.dept)::text = 'Guest Bathroom'::text))) THEN (max(ccs.points) * (max(view_total_sub_features.sub_feature_count))::numeric) WHEN (((ccs.group1)::text <> 'Brand
Standards'::text) AND ((ccs.dept)::text <> 'Guest Room'::text) AND ((ccs.dept)::text <> 'Guest Bathroom'::text) AND (concat_agg((ccs.name)::text) <> ''::text)) THEN (max(ccs.points) * (count(ccs.sub_id))::numeric) ELSE max(ccs.points) END END, CASE WHEN ((inspections.announce_date)::date <= '2007-06-19'::date) THEN CASE WHEN (((sum(ccs.points))::text = '0'::text) OR (ccs.result_id = 0) OR NULL::boolean) THEN 0::numeric WHEN ((sum((ccs.sub_results_passfail)::numeric) >= 2::numeric) AND ((ccs.group1)::text <> 'Brand Standards'::text) AND (((ccs.dept)::text = 'Guest Room'::text) OR ((ccs.dept)::text = 'Guest Bathroom'::text))) THEN (to_char(sum(ccs.lost), '999'::text))::numeric WHEN (((ccs.group1)::text <> 'Brand Standards'::text) AND (((ccs.dept)::text <> 'Guest Room'::text) OR ((ccs.dept)::text <> 'Guest Bathroom'::text))) THEN (to_char(sum(ccs.lost), '999'::text))::numeric WHEN ((sum((ccs.sub_results_passfail)::numeric) >= 2::numeric) AND ((ccs.group1)::text = 'Brand Standards'::text) AND (((ccs.dept)::text = 'Guest Room'::text) OR ((ccs.dept)::text = 'Guest Bathroom'::text))) THEN max(ccs.lost) WHEN (((ccs.group1)::text = 'Brand Standards'::text) AND (((ccs.dept)::text <> 'Guest Room'::text) OR ((ccs.dept)::text <> 'Guest Bathroom'::text))) THEN max(ccs.lost) ELSE 0::numeric END ELSE CASE WHEN (((sum(ccs.points))::text = '0'::text) OR (ccs.result_id = 0) OR NULL::boolean) THEN 0::numeric WHEN (((ccs.group1)::text <> 'Brand Standards'::text) AND (((ccs.dept)::text = 'Guest Room'::text) OR ((ccs.dept)::text = 'Guest Bathroom'::text))) THEN (to_char(sum(ccs.lost), '999'::text))::numeric WHEN (((ccs.group1)::text <> 'Brand Standards'::text) AND ((ccs.dept)::text <> 'Guest Room'::text) AND ((ccs.dept)::text <> 'Guest Bathroom'::text)) THEN (to_char(sum(ccs.lost), '999'::text))::numeric WHEN ((sum((ccs.sub_results_passfail)::numeric) >= 2::numeric) AND ((ccs.group1)::text = 'Brand Standards'::text) AND (((ccs.dept)::text = 'Guest Room'::text) OR ((ccs.dept)::text = 'Guest Bathroom'::text))) THEN max(ccs.lost) WHEN (((sum((ccs.sub_results_passfail)::numeric) >= 1::numeric) OR (sum((ccs.results_passfail)::numeric) >= 1::numeric)) AND ((ccs.group1)::text = 'Brand Standards'::text) AND ((ccs.dept)::text <> 'Guest Room'::text) AND ((ccs.dept)::text <> 'Guest Bathroom'::text)) THEN max(ccs.lost) ELSE 0::numeric END END
-> GroupAggregate (cost=611690.49..613189.66 rows=47 width=882) (actual time=27275.118..27305.677 rows=557 loops=1)
-> Sort (cost=611690.49..611714.07 rows=9432 width=882) (actual time=27274.978..27276.019 rows=959 loops=1)
Sort Key: ccs.result_id, ccs.sub_id, ccs.result_severity, ccs.sub_dept, ccs.dept, ccs.group1, ccs.insp_id, ccs.str_insp_id, ccs.set_id, ccs.results_status, ccs.results_passfail, ccs.effective_date, ccs.stan_id, ccs.type1, ccs.orderby, ccs.comp_num, ccs.lra_num, ccs.description, ccs.dateactive, ccs.dateexpire, ccs.trigger1, ccs.trigger2, ccs.other, ccs.standards_notes, ccs.brand, ccs.brand_id, ccs.original_points, ccs.firm, ccs.sub_description, ccs.guestrooms_inspected, inspections.announce_date
-> Merge Left Join (cost=606945.13..607389.32 rows=9432 width=882) (actual time=27201.150..27205.854 rows=959 loops=1)
Merge Cond: (property_features.pi_id = view_total_sub_features.pi_id)
-> Sort (cost=57144.93..57146.31 rows=552 width=882) (actual time=1705.185..1705.985 rows=959 loops=1)
Sort Key: property_features.pi_id
-> Hash Left Join (cost=56851.12..57119.79 rows=552 width=882) (actual time=1678.765..1699.850 rows=959 loops=1)
Hash Cond: ((inspections.prop_id = property_features.prop_id) AND ((ccs.sub_dept)::text = (property_features.title)::text))
-> Nested Loop (cost=56708.54..56938.35 rows=552 width=878) (actual time=1661.699..1679.366 rows=959 loops=1)
-> Index Scan using inspections_prop_id_idx on inspections (cost=0.00..218.77 rows=8 width=25) (actual time=13.311..14.623 rows=1 loops=1)
Filter: (1893::numeric = (insp_id)::numeric)
-> Materialize (cost=56708.54..56709.23 rows=69 width=861) (actual time=1648.351..1661.661 rows=959 loops=1)
-> Subquery Scan ccs (cost=56707.61..56708.48 rows=69 width=861) (actual time=1648.312..1652.831 rows=959 loops=1)
-> Sort (cost=56707.61..56707.79 rows=69 width=742) (actual time=1648.294..1649.280 rows=959 loops=1)
Sort Key: inspections.insp_id, sub_standards.sub_id, results.result_id, public.sub_results.sub_result_id
-> Nested Loop Left Join (cost=6167.84..56705.51 rows=69 width=742) (actual time=1307.705..1637.742 rows=959 loops=1)
-> Nested Loop Left Join (cost=6167.84..56652.07 rows=69 width=714) (actual time=1307.595..1606.962 rows=959 loops=1)
-> Nested Loop Left Join (cost=6167.84..56472.74 rows=69 width=694) (actual time=1286.998..1571.813 rows=959 loops=1)
-> Nested Loop Left Join (cost=6167.84..56070.09 rows=69 width=686) (actual time=1286.986..1563.076 rows=959 loops=1)
-> Nested Loop Left Join (cost=6167.84..55667.44 rows=69 width=678) (actual time=1286.972..1553.945 rows=959 loops=1)
-> Nested Loop Left Join (cost=6167.84..55264.79 rows=69 width=670) (actual time=1286.956..1544.639 rows=959 loops=1)
Join Filter: (sub_standards.group_id = standards_groups.group_id)
-> Nested Loop Left Join (cost=6166.72..55246.59 rows=69 width=642) (actual time=1286.898..1519.628 rows=959 loops=1)
-> Nested Loop Left Join (cost=6166.72..55226.74 rows=69 width=614) (actual time=1286.877..1508.545 rows=959 loops=1)
-> Nested Loop (cost=6166.72..55206.89 rows=69 width=586) (actual time=1286.853..1496.386 rows=959 loops=1)
Join Filter: (standards.brand_id = brands.brand_id)
-> Seq Scan on brands (cost=0.00..1.29 rows=1 width=72) (actual time=0.097..0.150 rows=10 loops=1)
Filter: ((firm)::text = 'Choice'::text)
-> Nested Loop Left Join (cost=6166.72..55185.76 rows=1588 width=518) (actual time=51.083..148.366 rows=959 loops=10)
-> Nested Loop Left Join (cost=6166.72..49194.78 rows=1588 width=510) (actual time=51.076..139.945 rows=959 loops=10)
-> Nested Loop Left Join (cost=6166.72..43203.81 rows=1588 width=502) (actual time=51.069..127.924 rows=959 loops=10)
-> Nested Loop Left Join (cost=6166.72..15297.32 rows=899 width=411) (actual time=51.060..118.252 rows=557 loops=10)
-> Merge Join (cost=6160.66..6235.89 rows=899 width=328) (actual time=49.017..56.690 rows=557 loops=10)
Merge Cond: (sub_standards.stan_id = standards.stan_id)
-> Sort (cost=3827.93..3830.18 rows=899 width=163) (actual time=22.743..23.258 rows=557 loops=10)
Sort Key: sub_standards.stan_id
-> Merge Join (cost=3683.05..3783.82 rows=899 width=163) (actual time=219.387..226.378 rows=557 loops=1)
Merge Cond: (standards_sets.sub_id = sub_standards.sub_id)
-> Sort (cost=606.15..608.40 rows=899 width=33) (actual time=76.571..77.003 rows=557 loops=1)
Sort Key: standards_sets.sub_id
-> Hash Join (cost=72.19..562.04 rows=899 width=33) (actual time=65.218..75.588 rows=557 loops=1)
Hash Cond: (standards_sets.set_id = inspections.set_id)
-> Seq Scan on standards_sets (cost=0.00..393.63 rows=23263 width=8) (actual time=0.056..30.976 rows=23263 loops=1)
-> Hash (cost=72.17..72.17 rows=1 width=29) (actual time=10.277..10.277 rows=1 loops=1)
-> Seq Scan on inspections (cost=0.00..72.17 rows=1 width=29) (actual time=0.507..10.226 rows=1 loops=1)
Filter: (approv AND approv AND (insp_completed = 1) AND ((insp_id)::numeric = 1893::numeric))
-> Sort (cost=3076.90..3120.55 rows=17461 width=134) (actual time=117.527..135.630 rows=14809 loops=1)
Sort Key: sub_standards.sub_id
-> Seq Scan on sub_standards (cost=0.00..621.61 rows=17461 width=134) (actual time=0.009..23.997 rows=17461 loops=1)
-> Sort (cost=2332.73..2363.61 rows=12349 width=169) (actual time=8.219..21.983 rows=9738 loops=10)
Sort Key: standards.stan_id
-> Seq Scan on standards (cost=0.00..436.49 rows=12349 width=169) (actual time=0.019..18.445 rows=12349 loops=1)
-> Bitmap Heap Scan on results (cost=6.05..10.06 rows=1 width=91) (actual time=0.102..0.103 rows=0 loops=5570)
Recheck Cond: ((results.sub_id = sub_standards.sub_id) AND (results.insp_id = inspections.insp_id))
-> BitmapAnd (cost=6.05..6.05 rows=1 width=0) (actual time=0.090..0.090 rows=0 loops=5570)
-> Bitmap Index Scan on results_sub_id_idx (cost=0.00..2.38 rows=55 width=0) (actual time=0.019..0.019 rows=20 loops=5570)
Index Cond: (results.sub_id = sub_standards.sub_id)
-> Bitmap Index Scan on results_insp_id_idx (cost=0.00..3.36 rows=186 width=0) (actual time=0.068..0.068 rows=267 loops=5190)
Index Cond: (results.insp_id = inspections.insp_id)
-> Index Scan using sub_results_result_id_idx on sub_results (cost=0.00..30.75 rows=23 width=95) (actual time=0.010..0.012 rows=1 loops=5570)
Index Cond: (public.sub_results.result_id = results.result_id)
-> Index Scan using sub_results_pkey on sub_results (cost=0.00..3.76 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=9590)
Index Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
Filter: ((status)::text = 'Waiver'::text)
-> Index Scan using sub_results_pkey on sub_results (cost=0.00..3.76 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=9590)
Index Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
Filter: ((status)::text = 'Actionable'::text)
-> Index Scan using department_pkey on departments (cost=0.00..0.28 rows=1 width=40) (actual time=0.006..0.008 rows=1 loops=959)
Index Cond: (sub_standards.dept_id = departments.dept_id)
-> Index Scan using standards_subgroups_pkey on standards_subgroups (cost=0.00..0.28 rows=1 width=40) (actual time=0.005..0.007 rows=1 loops=959)
Index Cond: (sub_standards.sub_group_id = standards_subgroups.sub_group_id)
-> Materialize (cost=1.12..1.23 rows=11 width=40) (actual time=0.001..0.011 rows=11 loops=959)
-> Seq Scan on standards_groups (cost=0.00..1.11 rows=11 width=40) (actual time=0.009..0.022 rows=11 loops=1)
-> Index Scan using sub_results_pkey on sub_results (cost=0.00..5.82 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=959)
Index Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
Filter: ((status)::text = 'AES'::text)
-> Index Scan using sub_results_pkey on sub_results (cost=0.00..5.82 rows=1 width=8) (actual time=0.005..0.006 rows=0 loops=959)
Index Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
Filter: ((status)::text = 'Not Available/Applicable'::text)
-> Index Scan using sub_results_pkey on sub_results (cost=0.00..5.82 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=959)
Index Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
Filter: ((status)::text = 'Future Standard'::text)
-> Index Scan using standard_types_pkey on standards_types (cost=0.00..2.59 rows=1 width=32) (actual time=0.030..0.032 rows=1 loops=959)
Index Cond: (standards_types.stype_id = sub_standards.stype_id)
-> Index Scan using sub_departments_pkey on sub_departments (cost=0.00..0.50 rows=1
width=40) (actual time=0.006..0.008 rows=1 loops=959)
Index Cond: (sub_standards.subdept_id = sub_departments.subdept_id)
-> Hash (cost=81.63..81.63 rows=4063 width=31) (actual time=17.016..17.016 rows=4063 loops=1)
-> Seq Scan on property_features (cost=0.00..81.63 rows=4063 width=31) (actual time=3.231..10.213 rows=4063 loops=1)
-> Sort (cost=549800.20..549973.76 rows=69422 width=12) (actual time=24438.236..25032.206 rows=551363 loops=1)
Sort Key: view_total_sub_features.pi_id
-> GroupAggregate (cost=519309.98..542336.88 rows=69422 width=12) (actual time=14724.417..22815.644 rows=551057 loops=1)
-> Sort (cost=519309.98..526696.35 rows=2954550 width=12) (actual time=14720.244..18845.272 rows=2950833 loops=1)
Sort Key: sub_property_features.pi_id
-> Seq Scan on sub_property_features (cost=0.00..50293.50 rows=2954550 width=12) (actual time=0.255..5592.217 rows=2950833 loops=1)
-> Sort (cost=399415.74..399415.75 rows=1 width=72) (actual time=1717478.456..1717478.692 rows=267 loops=1)
Sort Key: v.result_id
-> Subquery Scan v (cost=399383.18..399415.73 rows=1 width=72) (actual time=1716968.027..1717478.080 rows=267 loops=1)
Filter: (insp_id = 1893::numeric)
-> Unique (cost=399383.18..399413.23 rows=200 width=80) (actual time=1715250.075..1717354.309 rows=105561 loops=1)
-> GroupAggregate (cost=399383.18..399412.73 rows=200 width=80) (actual time=1715250.070..1717108.061 rows=105561 loops=1)
-> Sort (cost=399383.18..399387.27 rows=1637 width=80) (actual time=1715249.989..1715735.056 rows=280182 loops=1)
Sort Key: p.result_id
-> Unique (cost=398935.82..399279.42 rows=1637 width=120) (actual time=1701824.913..1713789.030 rows=280182 loops=1)
-> GroupAggregate (cost=398935.82..399263.05 rows=1637 width=120) (actual time=1701824.908..1712922.522 rows=280182 loops=1)
-> Sort (cost=398935.82..398976.72 rows=16360 width=120) (actual time=1701824.800..1707167.053 rows=411159 loops=1)
Sort Key: choice_scoring."comment", choice_scoring.sub_id, choice_scoring.result_id, choice_scoring.insp_id
-> Sort (cost=396550.30..396591.20 rows=16360 width=742) (actual time=1623057.435..1644222.274 rows=411159 loops=1)
Sort Key: inspections.insp_id, sub_standards.sub_id, results.result_id, public.sub_results.sub_result_id
-> Nested Loop Left Join (cost=375471.02..384611.27 rows=16360 width=742) (actual time=1540860.550..1566475.127 rows=411159 loops=1)
-> Merge Right Join (cost=375471.02..375718.66 rows=16360 width=714) (actual time=1540595.947..1551121.043 rows=411159 loops=1)
Merge Cond: (standards_types.stype_id = sub_standards.stype_id)
-> Sort (cost=28.27..29.39 rows=449 width=32) (actual time=7.649..8.178 rows=383 loops=1)
Sort Key: standards_types.stype_id
-> Seq Scan on standards_types (cost=0.00..8.49 rows=449 width=32) (actual time=0.063..6.839 rows=449 loops=1)
-> Sort (cost=375442.75..375483.65 rows=16360 width=694) (actual time=1540587.124..1549046.230 rows=411159 loops=1)
Sort Key: sub_standards.stype_id
-> Hash Left Join (cost=282816.84..369236.72 rows=16360 width=694) (actual time=211033.651..1507870.901 rows=411159 loops=1)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Hash Left Join (cost=275075.28..361348.82 rows=16360 width=686) (actual time=210872.008..1506730.550 rows=411159 loops=1)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Nested Loop Left Join (cost=267333.72..353460.92 rows=16360 width=678) (actual time=210676.590..1505158.578 rows=411159 loops=1)
Join Filter: (sub_standards.group_id = standards_groups.group_id)
-> Hash Left Join (cost=267332.59..349410.70 rows=16360 width=650) (actual time=210667.040..1495856.737 rows=411159 loops=1)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Nested Loop Left Join (cost=259591.03..341522.80 rows=16360 width=642) (actual time=210517.060..1494738.113 rows=411159 loops=1)
-> Nested Loop Left Join (cost=259591.03..336940.17 rows=16360 width=614) (actual time=210502.181..1490309.031 rows=411159 loops=1)
-> Nested Loop (cost=259591.03..332357.53 rows=16360 width=586) (actual time=210475.828..1484899.342 rows=411159 loops=1)
Join Filter: (standards.brand_id = brands.brand_id)
-> Seq Scan on brands (cost=0.00..1.29 rows=1 width=72) (actual time=8.962..9.030 rows=10 loops=1)
Filter: ((firm)::text = 'Choice'::text)
-> Hash Left Join (cost=259591.03..327652.62 rows=376290 width=518) (actual time=2044.195..147623.426 rows=913357 loops=10)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Merge Left Join (cost=251849.47..316545.15 rows=376290 width=510) (actual time=2024.053..145539.100 rows=913357 loops=10)
Merge Cond: (sub_standards.sub_id = results.sub_id)
Join Filter: (results.insp_id = inspections.insp_id)
-> Merge Join (cost=43384.89..46669.49 rows=213154 width=328) (actual time=612.624..3642.055 rows=722770 loops=10)
Merge Cond: (sub_standards.sub_id = standards_sets.sub_id)
-> Sort (cost=9413.58..9457.23 rows=17461 width=299) (actual time=40.715..75.372 rows=15022 loops=10)
Sort Key: sub_standards.sub_id
-> Merge Join (cost=5409.63..5733.29 rows=17461 width=299) (actual time=203.438..307.082 rows=17461 loops=1)
Merge Cond: (standards.stan_id = sub_standards.stan_id)
-> Sort (cost=2332.73..2363.61 rows=12349 width=169) (actual time=85.493..104.707 rows=12349 loops=1)
Sort Key: standards.stan_id
-> Seq Scan on standards (cost=0.00..436.49 rows=12349 width=169) (actual time=0.033..24.941 rows=12349 loops=1)
-> Sort (cost=3076.90..3120.55 rows=17461 width=134) (actual time=117.925..143.790 rows=17461 loops=1)
Sort Key: sub_standards.stan_id
-> Seq Scan on sub_standards (cost=0.00..621.61 rows=17461 width=134) (actual time=0.052..29.094 rows=17461 loops=1)
-> Sort (cost=33971.32..34504.20 rows=213154 width=33) (actual time=571.410..1789.834 rows=777020 loops=10)
Sort Key: standards_sets.sub_id
-> Hash Join (cost=684.42..3443.55 rows=213154 width=33) (actual time=67.571..1051.592 rows=777020 loops=1)
Hash Cond: (inspections.set_id = standards_sets.set_id)
-> Seq Scan on inspections (cost=0.00..64.12 rows=237 width=29) (actual time=2.474..10.856 rows=833 loops=1)
Filter: (approv AND approv AND (insp_completed = 1))
-> Hash (cost=393.63..393.63 rows=23263 width=8) (actual time=65.054..65.054 rows=23263 loops=1)
-> Seq Scan on standards_sets (cost=0.00..393.63 rows=23263 width=8) (actual time=6.570..39.715 rows=23263 loops=1)
-> Sort (cost=208464.58..209185.24 rows=288264 width=190) (actual time=1410.594..75621.963 rows=50568366 loops=10)
Sort Key: results.sub_id
-> Merge Left Join (cost=122988.89..128129.31 rows=288264 width=190) (actual time=7672.020..9636.805 rows=369019 loops=1)
Merge Cond: (results.result_id = public.sub_results.result_id)
-> Sort (cost=35119.53..35527.76 rows=163291 width=91) (actual time=2245.656..2770.155 rows=163291 loops=1)
Sort Key: results.result_id
-> Seq Scan on results (cost=0.00..4236.91 rows=163291 width=91) (actual time=9.716..1285.464 rows=163291 loops=1)
-> Sort (cost=87869.36..88590.02 rows=288264 width=103) (actual time=5342.584..5818.097 rows=287117 loops=1)
Sort Key: public.sub_results.result_id
-> Hash Left Join (cost=9280.59..29213.10 rows=288264 width=103) (actual time=771.876..2991.820 rows=287117 loops=1)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Seq Scan on sub_results (cost=0.00..6950.64 rows=288264 width=95) (actual time=8.538..664.023 rows=287117 loops=1)
-> Hash (cost=7671.30..7671.30 rows=95223 width=8) (actual time=762.954..762.954 rows=97944 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=95223 width=8) (actual time=18.911..581.152 rows=97944 loops=1)
Filter: ((status)::text = 'Actionable'::text)
-> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=201.351..201.351 rows=4516 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=16.040..195.305 rows=4516 loops=1)
Filter: ((status)::text = 'Waiver'::text)
-> Index Scan using department_pkey on departments (cost=0.00..0.27 rows=1 width=40) (actual time=0.007..0.009 rows=1 loops=411159)
Index Cond: (sub_standards.dept_id = departments.dept_id)
-> Index Scan using standards_subgroups_pkey on standards_subgroups (cost=0.00..0.27 rows=1 width=40) (actual time=0.005..0.007 rows=1 loops=411159)
Index Cond: (sub_standards.sub_group_id = standards_subgroups.sub_group_id)
-> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=149.937..149.937 rows=26 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=27.305..149.879 rows=26 loops=1)
Filter: ((status)::text = 'AES'::text)
-> Materialize (cost=1.12..1.23 rows=11 width=40) (actual time=0.001..0.009 rows=11 loops=411159)
-> Seq Scan on standards_groups (cost=0.00..1.11 rows=11 width=40) (actual time=9.501..9.517 rows=11 loops=1)
-> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=195.377..195.377 rows=16395 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=0.514..175.136 rows=16395 loops=1)
Filter: ((status)::text = 'Not Available/Applicable'::text)
-> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=161.600..161.600 rows=2719 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=5.652..157.807 rows=2719 loops=1)
Filter: ((status)::text = 'Future Standard'::text)
-> Index Scan using sub_departments_pkey on sub_departments (cost=0.00..0.27 rows=1 width=40) (actual time=0.009..0.012 rows=1 loops=411159)
Index Cond: (sub_standards.subdept_id = sub_departments.subdept_id)
-> Hash (cost=396849.74..396849.74 rows=200 width=40) (actual time=1587568.639..1587568.639 rows=105561 loops=1)
-> Subquery Scan xx (cost=396846.74..396849.74 rows=200 width=40) (actual time=1586573.141..1587307.920 rows=105561 loops=1)
-> Unique (cost=396846.74..396847.74 rows=200 width=40) (actual time=1586573.136..1587083.047 rows=105561 loops=1)
-> Sort (cost=396846.74..396847.24 rows=200 width=40) (actual time=1586573.129..1586813.999 rows=105561 loops=1)
Sort Key: choice_scoring.result_id
-> HashAggregate (cost=396836.60..396839.10 rows=200 width=40) (actual time=1585699.581..1585902.262 rows=105561 loops=1)
-> Sort (cost=396550.30..396591.20 rows=16360 width=742) (actual time=1577807.867..1583091.197 rows=411159 loops=1)
Sort Key: inspections.insp_id, sub_standards.sub_id, results.result_id, public.sub_results.sub_result_id
-> Nested Loop Left Join (cost=375471.02..384611.27 rows=16360 width=742) (actual time=1506497.966..1527811.843 rows=411159 loops=1)
-> Merge Right Join (cost=375471.02..375718.66 rows=16360 width=714) (actual time=1506317.890..1514595.258 rows=411159 loops=1)
Merge Cond: (standards_types.stype_id = sub_standards.stype_id)
-> Sort (cost=28.27..29.39 rows=449 width=32) (actual time=13.294..13.653 rows=383 loops=1)
Sort Key: standards_types.stype_id
-> Seq Scan on standards_types (cost=0.00..8.49 rows=449 width=32) (actual time=11.697..12.563 rows=449 loops=1)
-> Sort (cost=375442.75..375483.65 rows=16360 width=694) (actual time=1506304.435..1512851.678 rows=411159 loops=1)
Sort Key: sub_standards.stype_id
Hash Left Join (cost=282816.84..369236.72 rows=16360 width=694) (actual time=205072.751..1479912.217 rows=411159 loops=1)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Hash Left Join (cost=275075.28..361348.82 rows=16360 width=686) (actual time=204914.419..1478779.894 rows=411159 loops=1)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Nested Loop Left Join (cost=267333.72..353460.92 rows=16360 width=678) (actual time=204708.058..1477129.400 rows=411159 loops=1)
Join Filter: (sub_standards.group_id = standards_groups.group_id)
-> Hash Left Join (cost=267332.59..349410.70 rows=16360 width=650) (actual time=204700.377..1467894.641 rows=411159 loops=1)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Nested Loop Left Join (cost=259591.03..341522.80 rows=16360 width=642) (actual time=204548.526..1466790.696 rows=411159 loops=1)
-> Nested Loop Left Join (cost=259591.03..336940.17 rows=16360 width=614) (actual time=204525.347..1462292.828 rows=411159 loops=1)
-> Nested Loop (cost=259591.03..332357.53 rows=16360 width=586) (actual time=204492.732..1457066.155 rows=411159 loops=1)
Join Filter: (standards.brand_id = brands.brand_id)
-> Seq Scan on brands (cost=0.00..1.29 rows=1 width=72) (actual time=0.023..0.091 rows=10 loops=1)
Filter: ((firm)::text = 'Choice'::text)
-> Hash Left Join (cost=259591.03..327652.62 rows=376290 width=518) (actual time=2037.143..144851.488 rows=913357 loops=10)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Merge Left Join (cost=251849.47..316545.15 rows=376290 width=510) (actual time=2020.806..142751.746 rows=913357 loops=10)
Merge Cond: (sub_standards.sub_id = results.sub_id)
Join Filter: (results.insp_id = inspections.insp_id)
-> Merge Join (cost=43384.89..46669.49 rows=213154 width=328) (actual time=779.327..3874.929 rows=722770 loops=10)
Merge Cond: (sub_standards.sub_id = standards_sets.sub_id)
-> Sort (cost=9413.58..9457.23 rows=17461 width=299) (actual time=87.035..121.074 rows=15022 loops=10)
Sort Key: sub_standards.sub_id
-> Merge Join (cost=5409.63..5733.29 rows=17461 width=299) (actual time=551.854..659.852 rows=17461 loops=1)
Merge Cond: (standards.stan_id = sub_standards.stan_id)
-> Sort (cost=2332.73..2363.61 rows=12349 width=169) (actual time=150.507..169.926 rows=12349 loops=1)
Sort Key: standards.stan_id
-> Seq Scan on standards (cost=0.00..436.49 rows=12349 width=169) (actual time=4.314..40.392 rows=12349 loops=1)
-> Sort (cost=3076.90..3120.55 rows=17461 width=134) (actual time=401.328..428.311 rows=17461 loops=1)
Sort Key: sub_standards.stan_id
-> Seq Scan on sub_standards (cost=0.00..621.61 rows=17461 width=134) (actual time=39.533..217.565 rows=17461 loops=1)
-> Sort (cost=33971.32..34504.20 rows=213154 width=33) (actual time=691.830..1938.839 rows=777020 loops=10)
Sort Key: standards_sets.sub_id
-> Hash Join (cost=684.42..3443.55 rows=213154 width=33) (actual time=83.377..1398.187 rows=777020 loops=1)
Hash Cond: (inspections.set_id = standards_sets.set_id)
-> Seq Scan on inspections (cost=0.00..64.12 rows=237 width=29) (actual time=9.498..229.383 rows=833 loops=1)
Filter: (approv AND approv AND (insp_completed = 1))
-> Hash (cost=393.63..393.63 rows=23263 width=8) (actual time=73.824..73.824 rows=23263 loops=1)
-> Seq Scan on standards_sets (cost=0.00..393.63 rows=23263 width=8) (actual time=6.519..48.643 rows=23263 loops=1)
-> Sort (cost=208464.58..209185.24 rows=288264 width=190) (actual time=1240.652..72918.603 rows=50568366 loops=10)
Sort Key: results.sub_id
-> Merge Left Join (cost=122988.89..128129.31 rows=288264 width=190) (actual time=7512.379..9175.116 rows=369019 loops=1)
Merge Cond: (results.result_id = public.sub_results.result_id)
-> Sort (cost=35119.53..35527.76 rows=163291 width=91) (actual time=1442.429..1807.192 rows=163291 loops=1)
Sort Key: results.result_id
-> Seq Scan on results (cost=0.00..4236.91 rows=163291 width=91) (actual time=11.791..501.378 rows=163291 loops=1)
-> Sort (cost=87869.36..88590.02 rows=288264 width=103) (actual time=5978.040..6415.634 rows=287117 loops=1)
Sort Key: public.sub_results.result_id
-> Hash Left Join (cost=9280.59..29213.10 rows=288264 width=103) (actual time=565.703..3496.806 rows=287117 loops=1)
Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id)
-> Seq Scan on sub_results (cost=0.00..6950.64 rows=288264 width=95) (actual time=14.292..857.605 rows=287117 loops=1)
-> Hash (cost=7671.30..7671.30 rows=95223 width=8) (actual time=551.149..551.149 rows=97944 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=95223 width=8) (actual time=18.932..423.436 rows=97944 loops=1)
Filter: ((status)::text = 'Actionable'::text)
-> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=163.301..163.301 rows=4516 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=0.640..157.395 rows=4516 loops=1)
Filter: ((status)::text = 'Waiver'::text)
-> Index Scan using department_pkey on departments (cost=0.00..0.27 rows=1 width=40) (actual time=0.007..0.008 rows=1 loops=411159)
Index Cond: (sub_standards.dept_id = departments.dept_id)
-> Index Scan using standards_subgroups_pkey on standards_subgroups (cost=0.00..0.27 rows=1 width=40) (actual time=0.005..0.007 rows=1 loops=411159)
Index Cond: (sub_standards.sub_group_id = standards_subgroups.sub_group_id)
-> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=151.808..151.808 rows=26 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=28.178..151.744 rows=26 loops=1)
Filter: ((status)::text = 'AES'::text)
-> Materialize (cost=1.12..1.23 rows=11 width=40) (actual time=0.001..0.009 rows=11 loops=411159)
-> Seq Scan on standards_groups (cost=0.00..1.11 rows=11 width=40) (actual time=7.634..7.649 rows=11 loops=1)
-> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=206.319..206.319 rows=16395 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=0.491..184.483 rows=16395 loops=1)
Filter: ((status)::text = 'Not Available/Applicable'::text)
-> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=158.288..158.288 rows=2719 loops=1)
-> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=5.754..154.597 rows=2719 loops=1)
Filter: ((status)::text = 'Future Standard'::text)
-> Index Scan using sub_departments_pkey on sub_departments (cost=0.00..0.27 rows=1 width=40) (actual time=0.008..0.010 rows=1 loops=411159)
Index Cond: (sub_standards.subdept_id = sub_departments.subdept_id)
Total runtime: 3334810.951 ms
(292 rows)