----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------- Sort (cost=381813.40..381854.30 rows=16360 width=729) (actual time=1598450.492..1599757.629 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=368096.52..375355.37 rows=16360 width=729) (actual time=1527931.452..1545963.578 rows=411159 loops=1) -> Merge Right Join (cost=368096.52..368344.16 rows=16360 width=701) (actual time=1527903.005..1534490.831 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=14.903..15.296 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=5.623..9.655 rows=449 loops=1) -> Sort (cost=368068.25..368109.15 rows=16360 width=681) (actual time=1527887.667..1532429.433 rows=411159 loops=1) Sort Key: sub_standards.stype_id -> Hash Left Join (cost=275526.34..361946.22 rows=16360 width=681) (actual time=210519.508..1498459.758 rows=411159 loops=1) Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id) -> Nested Loop Left Join (cost=267784.78..354058.32 rows=16360 width=673) (actual time=210358.768..1497299.177 rows=411159 loops=1) Join Filter: (sub_standards.group_id = standards_groups.group_id) -> Hash Left Join (cost=267783.66..350008.10 rows=16360 width=645) (actual time=210354.461..1488215.255 rows=411159 loops=1) Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id) -> Hash Left Join (cost=260042.09..342120.20 rows=16360 width=637) (actual time=210150.701..1486718.691 rows=411159 loops=1) Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id) -> Nested Loop Left Join (cost=252300.53..334232.30 rows=16360 width=629) (actual time=209957.699..1485580.604 rows=411159 loops=1) -> Nested Loop Left Join (cost=252300.53..329649.67 rows=16360 width=601) (actual time=209934.495..1481231.797 rows=411159 loo ps=1) -> Nested Loop (cost=252300.53..325067.03 rows=16360 width=573) (actual time=209900.920..1476283.199 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.974..9.045 rows=10 loops=1) Filter: ((firm)::text = 'Choice'::text) -> Hash Left Join (cost=252300.53..320362.12 rows=376290 width=505) (actual time=2021.535..146749.357 rows=913357 loops=10) Hash Cond: (public.sub_results.sub_result_id = public.sub_results.sub_result_id) -> Merge Left Join (cost=244558.97..309254.65 rows=376290 width=497) (actual time=1999.651..144673.802 rows= 913357 loops=10) Merge Cond: (sub_standards.sub_id = results.sub_id) Join Filter: (results.insp_id = inspections.insp_id) -> Merge Join (cost=36094.39..39378.99 rows=213154 width=315) (actual time=629.766..3695.984 rows=7227 70 loops=10) Merge Cond: (sub_standards.sub_id = standards_sets.sub_id) -> Sort (cost=9413.58..9457.23 rows=17461 width=299) (actual time=47.962..84.981 rows=15022 loop s=10) Sort Key: sub_standards.sub_id -> Merge Join (cost=5409.63..5733.29 rows=17461 width=299) (actual time=264.650..373.040 r ows=17461 loops=1) Merge Cond: (standards.stan_id = sub_standards.stan_id) -> Sort (cost=2332.73..2363.61 rows=12349 width=169) (actual time=129.011..148.148 r ows=12349 loops=1) Sort Key: standards.stan_id -> Seq Scan on standards (cost=0.00..436.49 rows=12349 width=169) (actual time =0.263..49.117 rows=12349 loops=1) -> Sort (cost=3076.90..3120.55 rows=17461 width=134) (actual time=135.609..163.125 r ows=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.031..26.913 rows=17461 loops=1) -> Sort (cost=26680.82..27213.70 rows=213154 width=20) (actual time=581.329..1742.930 rows=777020 loops=10) Sort Key: standards_sets.sub_id -> Hash Join (cost=684.42..3443.55 rows=213154 width=20) (actual time=73.696..1067.449 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=16) (actual time=0.045..7.049 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.609..73.609 rows=23263 loops=1) -> Seq Scan on standards_sets (cost=0.00..393.63 rows=23263 width=8) (actual time=10.216..44.854 rows=23263 loops=1) -> Sort (cost=208464.58..209185.24 rows=288264 width=190) (actual time=1369.000..74395.492 rows=50568366 loops=10) Sort Key: results.sub_id -> Merge Left Join (cost=122988.89..128129.31 rows=288264 width=190) (actual time=6434.425..9081.032 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=1410.068..2465.057 rows=163291 loops=1) Sort Key: results.result_id -> Seq Scan on results (cost=0.00..4236.91 rows=163291 width=91) (actual time=0.077..405.515 rows=163291 loops=1) -> Sort (cost=87869.36..88590.02 rows=288264 width=103) (actual time=4908.537..5459.073 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=437.754..2618.262 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=0.063..357.325 rows=287117 loops=1) -> Hash (cost=7671.30..7671.30 rows=95223 width=8) (actual time=437.347..437.347 rows=97944 loops=1) -> Seq Scan on sub_results (cost=0.00..7671.30 rows=95223 width=8) (actual time=0.163..304.347 rows=97944 loops=1) Filter: ((status)::text = 'Actionable'::text) -> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=218.758..218.758 rows=4516 loops=1) -> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=0.658..211.554 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.006..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.006 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=192.954..192.954 rows=26 loops=1) -> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=33.915..192.888 rows=26 loops=1) Filter: ((status)::text = 'AES'::text) -> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=203.704..203.704 rows=16395 loops=1) -> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=0.609..183.067 rows=16395 loops=1) Filter: ((status)::text = 'Not Available/Applicable'::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=4.248..4.263 rows=11 loops=1) -> Hash (cost=7671.30..7671.30 rows=5621 width=8) (actual time=160.689..160.689 rows=2719 loops=1) -> Seq Scan on sub_results (cost=0.00..7671.30 rows=5621 width=8) (actual time=5.800..156.982 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: 1603250.918 ms (85 rows)