explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kh6t

Settings
# exclusive inclusive rows x rows loops node
1. 5.760 175,714.450 ↓ 78.6 2,436 1

Group (cost=18,233.10..18,235.27 rows=31 width=956) (actual time=175,708.568..175,714.450 rows=2,436 loops=1)

  • Group Key: maintenance_summary_data_counts_sql.property_name, maintenance_summary_data_counts_sql.maintenance_status_type, maintenance_summary_data_counts_sql.lookup_code, (sum(maintenance_summary_data_counts_sql.mon_count)), (sum(maintenance_summary_data_counts_sql.tue_count)), (sum(maintenance_summary_data_counts_sql.wed_count)), (sum(maintenance_summary_data_counts_sql.thu_count)), (sum(maintenance_summary_data_counts_sql.fri_count)), (sum(maintenance_summary_data_counts_sql.sat_count)), (sum(maintenance_summary_data_counts_sql.sun_count)), (COALESCE(sum(maintenance_summary_data_counts_sql.count), '0'::numeric)), (COALESCE(sum(round(((maintenance_summary_data_counts_sql.total_days / (maintenance_summary_data_counts_sql.count_temp)::double precision))::numeric, 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.mon_count / maintenance_summary_data_counts_sql.mon_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.tue_count / maintenance_summary_data_counts_sql.tue_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.wed_count / maintenance_summary_data_counts_sql.wed_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.thu_count / maintenance_summary_data_counts_sql.thu_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.fri_count / maintenance_summary_data_counts_sql.fri_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.sat_count / maintenance_summary_data_counts_sql.sat_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.sun_count / maintenance_summary_data_counts_sql.sun_total_days), 2)), '0'::numeric))
2.          

CTE property_info

3. 78.827 4,225.033 ↓ 2,442.0 2,442 1

GroupAggregate (cost=24.93..24.97 rows=1 width=188) (actual time=4,130.144..4,225.033 rows=2,442 loops=1)

  • Group Key: p_1.id, o.owner_name, (COALESCE(ppn.phone_number, '-'::character varying)), pms.maintenance_status_id, (COALESCE(ms.name, '-'::character varying)), p_1.cid
4. 1,499.352 4,146.206 ↓ 152,591.0 152,591 1

Sort (cost=24.93..24.93 rows=1 width=119) (actual time=4,130.000..4,146.206 rows=152,591 loops=1)

  • Sort Key: p_1.id, o.owner_name, (COALESCE(ppn.phone_number, '-'::character varying)), pms.maintenance_status_id, (COALESCE(ms.name, '-'::character varying))
  • Sort Method: quicksort Memory: 28968kB
5. 91.240 2,646.854 ↓ 152,591.0 152,591 1

Nested Loop Left Join (cost=2.39..24.92 rows=1 width=119) (actual time=28.613..2,646.854 rows=152,591 loops=1)

6. 187.532 1,945.250 ↓ 152,591.0 152,591 1

Nested Loop (cost=2.10..23.38 rows=1 width=64) (actual time=28.546..1,945.250 rows=152,591 loops=1)

7. 61.304 1,452.536 ↓ 152,591.0 152,591 1

Nested Loop (cost=1.82..22.46 rows=1 width=54) (actual time=28.517..1,452.536 rows=152,591 loops=1)

8. 196.882 933.459 ↓ 152,591.0 152,591 1

Nested Loop (cost=1.54..22.14 rows=1 width=49) (actual time=28.475..933.459 rows=152,591 loops=1)

  • Join Filter: (lp.property_id = p_1.id)
9. 52.313 278.804 ↓ 152,591.0 152,591 1

Nested Loop (cost=1.25..21.74 rows=1 width=36) (actual time=28.456..278.804 rows=152,591 loops=1)

  • Join Filter: (lp.property_id = pu.property_id)
10. 2.400 54.651 ↓ 5,728.0 5,728 1

Nested Loop (cost=0.83..11.57 rows=1 width=24) (actual time=28.412..54.651 rows=5,728 loops=1)

  • Join Filter: (lp.property_id = pms.property_id)
11. 1.292 37.835 ↓ 848.0 848 1

Nested Loop (cost=0.54..9.31 rows=1 width=12) (actual time=28.388..37.835 rows=848 loops=1)

12. 29.143 29.143 ↓ 925.0 925 1

Function Scan on load_properties lp (cost=0.25..1.00 rows=1 width=4) (actual time=28.326..29.143 rows=925 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
  • Rows Removed by Filter: 248
13. 7.400 7.400 ↑ 1.0 1 925

Index Only Scan using uk_property_details_company_property_id on property_details pd (cost=0.29..8.31 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=925)

  • Index Cond: ((cid = 235) AND (property_id = lp.property_id))
  • Heap Fetches: 848
14. 14.416 14.416 ↓ 7.0 7 848

Index Scan using idx_property_maintenance_statuses_property_id on property_maintenance_statuses pms (cost=0.29..2.24 rows=1 width=12) (actual time=0.009..0.017 rows=7 loops=848)

  • Index Cond: (property_id = pd.property_id)
  • Filter: ((cid = 235) AND (is_published = 1))
  • Rows Removed by Filter: 0
15. 171.840 171.840 ↓ 3.9 27 5,728

Index Scan using idx_property_units_property_id on property_units pu (cost=0.42..10.08 rows=7 width=12) (actual time=0.005..0.030 rows=27 loops=5,728)

  • Index Cond: (property_id = pms.property_id)
  • Filter: (((deleted_on IS NULL) OR (deleted_on > '2019-01-31 00:00:00-07'::timestamp with time zone)) AND (cid = 235))
  • Rows Removed by Filter: 1
16. 457.773 457.773 ↑ 1.0 1 152,591

Index Scan using pk_properties on properties p_1 (cost=0.29..0.39 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=152,591)

  • Index Cond: ((cid = 235) AND (id = pu.property_id))
  • Filter: (disabled_on IS NULL)
17. 457.773 457.773 ↑ 1.0 1 152,591

Index Scan using idx_maintenance_statuses_id on maintenance_statuses ms (cost=0.28..0.32 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=152,591)

  • Index Cond: (id = pms.maintenance_status_id)
  • Filter: ((cid = 235) AND (is_published = 1))
  • Rows Removed by Filter: 0
18. 305.182 305.182 ↑ 1.0 1 152,591

Index Scan using idx_owners_id on owners o (cost=0.29..0.92 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=152,591)

  • Index Cond: (id = p_1.owner_id)
  • Filter: (cid = 235)
19. 610.364 610.364 ↑ 1.0 1 152,591

Index Scan using idx_property_phone_numbers_property_id on property_phone_numbers ppn (cost=0.29..1.53 rows=1 width=19) (actual time=0.003..0.004 rows=1 loops=152,591)

  • Index Cond: (property_id = p_1.id)
  • Filter: ((cid = 235) AND (phone_number_type_id = 3) AND (cid = p_1.cid))
  • Rows Removed by Filter: 2
20. 2.853 175,708.690 ↓ 78.6 2,436 1

Sort (cost=18,208.13..18,208.21 rows=31 width=668) (actual time=175,708.531..175,708.690 rows=2,436 loops=1)

  • Sort Key: maintenance_summary_data_counts_sql.property_name, maintenance_summary_data_counts_sql.maintenance_status_type, maintenance_summary_data_counts_sql.lookup_code, (sum(maintenance_summary_data_counts_sql.mon_count)), (sum(maintenance_summary_data_counts_sql.tue_count)), (sum(maintenance_summary_data_counts_sql.wed_count)), (sum(maintenance_summary_data_counts_sql.thu_count)), (sum(maintenance_summary_data_counts_sql.fri_count)), (sum(maintenance_summary_data_counts_sql.sat_count)), (sum(maintenance_summary_data_counts_sql.sun_count)), (COALESCE(sum(maintenance_summary_data_counts_sql.count), '0'::numeric)), (COALESCE(sum(round(((maintenance_summary_data_counts_sql.total_days / (maintenance_summary_data_counts_sql.count_temp)::double precision))::numeric, 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.mon_count / maintenance_summary_data_counts_sql.mon_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.tue_count / maintenance_summary_data_counts_sql.tue_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.wed_count / maintenance_summary_data_counts_sql.wed_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.thu_count / maintenance_summary_data_counts_sql.thu_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.fri_count / maintenance_summary_data_counts_sql.fri_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.sat_count / maintenance_summary_data_counts_sql.sat_total_days), 2)), '0'::numeric)), (COALESCE(sum(round((maintenance_summary_data_counts_sql.sun_count / maintenance_summary_data_counts_sql.sun_total_days), 2)), '0'::numeric))
  • Sort Method: quicksort Memory: 439kB
21. 17.921 175,705.837 ↓ 78.6 2,436 1

GroupAggregate (cost=18,202.56..18,207.05 rows=31 width=668) (actual time=175,687.736..175,705.837 rows=2,436 loops=1)

  • Group Key: maintenance_summary_data_counts_sql.property_name, maintenance_summary_data_counts_sql.maintenance_status_type, maintenance_summary_data_counts_sql.lookup_code
22. 2.607 175,687.916 ↓ 78.6 2,436 1

Sort (cost=18,202.56..18,202.64 rows=31 width=676) (actual time=175,687.668..175,687.916 rows=2,436 loops=1)

  • Sort Key: maintenance_summary_data_counts_sql.property_name, maintenance_summary_data_counts_sql.maintenance_status_type, maintenance_summary_data_counts_sql.lookup_code
  • Sort Method: quicksort Memory: 527kB
23. 0.466 175,685.309 ↓ 78.6 2,436 1

Subquery Scan on maintenance_summary_data_counts_sql (cost=18,196.44..18,201.79 rows=31 width=676) (actual time=175,676.504..175,685.309 rows=2,436 loops=1)

24. 8.209 175,684.843 ↓ 78.6 2,436 1

GroupAggregate (cost=18,196.44..18,201.48 rows=31 width=676) (actual time=175,676.501..175,684.843 rows=2,436 loops=1)

  • Group Key: maintenance_summary_data_inner_sql.property_name, maintenance_summary_data_inner_sql.lookup_code, maintenance_summary_data_inner_sql.maintenance_status_type
25. 6.156 175,676.634 ↓ 78.8 2,444 1

Sort (cost=18,196.44..18,196.52 rows=31 width=284) (actual time=175,676.447..175,676.634 rows=2,444 loops=1)

  • Sort Key: maintenance_summary_data_inner_sql.property_name, maintenance_summary_data_inner_sql.lookup_code, maintenance_summary_data_inner_sql.maintenance_status_type
  • Sort Method: quicksort Memory: 746kB
26. 0.714 175,670.478 ↓ 78.8 2,444 1

Subquery Scan on maintenance_summary_data_inner_sql (cost=18,190.33..18,195.68 rows=31 width=284) (actual time=175,451.334..175,670.478 rows=2,444 loops=1)

27. 215.654 175,669.764 ↓ 78.8 2,444 1

GroupAggregate (cost=18,190.33..18,195.37 rows=31 width=420) (actual time=175,451.332..175,669.764 rows=2,444 loops=1)

  • Group Key: pi.new_property_id, pi.company_id, pi.property_name, p.lookup_code, pi.year_built, pi.total_units, pi.owner_name, pi.office_phone, mrd.actual_start_datetime, mrd.completed_datetime, pi.maintenance_status_type
28. 234.547 175,454.110 ↓ 2,442.0 75,702 1

Sort (cost=18,190.33..18,190.41 rows=31 width=272) (actual time=175,447.963..175,454.110 rows=75,702 loops=1)

  • Sort Key: pi.new_property_id, pi.property_name, p.lookup_code, pi.year_built, pi.total_units, pi.owner_name, pi.office_phone, mrd.actual_start_datetime, mrd.completed_datetime, pi.maintenance_status_type
  • Sort Method: quicksort Memory: 13970kB
29. 77.388 175,219.563 ↓ 2,442.0 75,702 1

Nested Loop Left Join (cost=457.61..18,189.56 rows=31 width=272) (actual time=4,138.431..175,219.563 rows=75,702 loops=1)

30. 52.547 175,142.175 ↓ 2,442.0 75,702 1

Nested Loop Left Join (cost=457.33..18,179.67 rows=31 width=274) (actual time=4,138.400..175,142.175 rows=75,702 loops=1)

  • Join Filter: (date_trunc('day'::text, mrd.actual_start_datetime) = d.date)
  • Rows Removed by Join Filter: 60
31. 19.872 4,305.916 ↓ 2,442.0 75,702 1

Nested Loop (cost=0.29..9.24 rows=31 width=250) (actual time=4,130.301..4,305.916 rows=75,702 loops=1)

32. 4,229.878 4,229.878 ↓ 2,442.0 2,442 1

CTE Scan on property_info pi (cost=0.00..0.02 rows=1 width=242) (actual time=4,130.181..4,229.878 rows=2,442 loops=1)

  • Filter: (company_id = 235)
33. 56.166 56.166 ↑ 1.0 31 2,442

Index Only Scan using uk_days_date on days d (cost=0.29..8.91 rows=31 width=8) (actual time=0.005..0.023 rows=31 loops=2,442)

  • Index Cond: ((date >= '2019-01-01 00:00:00'::timestamp without time zone) AND (date <= '2019-01-31 00:00:00'::timestamp without time zone))
  • Heap Fetches: 75702
34. 49.476 170,783.712 ↓ 0.0 0 75,702

Nested Loop (cost=457.04..586.13 rows=1 width=28) (actual time=2.255..2.256 rows=0 loops=75,702)

35. 11,885.214 170,405.202 ↑ 1.0 1 75,702

Bitmap Heap Scan on maintenance_requests mr (cost=456.60..583.19 rows=1 width=16) (actual time=2.110..2.251 rows=1 loops=75,702)

  • Recheck Cond: ((property_id = pi.new_property_id) AND (pi.maintenance_status_id = maintenance_status_id))
  • Filter: ((parent_maintenance_request_id IS NULL) AND (deleted_by IS NULL) AND (cid = 235) AND (maintenance_request_type_id = 2))
  • Rows Removed by Filter: 358
  • Heap Blocks: exact=1199142
36. 890.289 158,519.988 ↓ 0.0 0 75,702

BitmapAnd (cost=456.60..456.60 rows=32 width=0) (actual time=2.094..2.094 rows=0 loops=75,702)

37. 31,189.224 31,189.224 ↑ 1.4 6,730 75,702

Bitmap Index Scan on idx_maintenance_requests_property_id (cost=0.00..180.43 rows=9,599 width=0) (actual time=0.412..0.412 rows=6,730 loops=75,702)

  • Index Cond: (property_id = pi.new_property_id)
38. 126,440.475 126,440.475 ↓ 7.8 116,340 18,445

Bitmap Index Scan on idx_maintenance_requests_maintenance_status_id (cost=0.00..275.93 rows=14,866 width=0) (actual time=6.855..6.855 rows=116,340 loops=18,445)

  • Index Cond: (pi.maintenance_status_id = maintenance_status_id)
39. 329.034 329.034 ↓ 0.0 0 54,839

Index Scan using idx_maintenance_request_details_maintenance_request_id on maintenance_request_details mrd (cost=0.43..2.93 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=54,839)

  • Index Cond: (maintenance_request_id = mr.id)
  • Filter: ((cid = 235) AND (date(actual_start_datetime) >= '2019-01-01'::date) AND (date(actual_start_datetime) <= '2019-01-31'::date))
  • Rows Removed by Filter: 1
40. 0.000 0.000 ↓ 0.0 0 75,702

Index Scan using pk_properties on properties p (cost=0.29..0.32 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=75,702)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.property_id))