explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fg2l

Settings
# exclusive inclusive rows x rows loops node
1. 25,675.931 29,386.922 ↓ 1.4 157,994 1

WindowAgg (cost=538,472.10..910,726.18 rows=111,872 width=2,444) (actual time=3,598.496..29,386.922 rows=157,994 loops=1)

2. 627.970 3,710.991 ↓ 1.4 157,994 1

Sort (cost=538,472.10..538,751.78 rows=111,872 width=3,933) (actual time=3,598.071..3,710.991 rows=157,994 loops=1)

  • Sort Key: clu.cid, clu.lease_id, clu.unit_space_id, charges.rent_charge_order, charges.ledger_order, charges.charge_name
  • Sort Method: quicksort Memory: 309295kB
3. 309.170 3,083.021 ↓ 1.4 157,994 1

Nested Loop Left Join (cost=7,575.07..529,090.80 rows=111,872 width=3,933) (actual time=175.046..3,083.021 rows=157,994 loops=1)

4. 112.587 2,030.091 ↓ 4.0 92,970 1

Hash Left Join (cost=7,574.16..411,844.37 rows=23,180 width=3,239) (actual time=174.999..2,030.091 rows=92,970 loops=1)

  • Hash Cond: (usl.occupancy_type_id = ot_unit.id)
5. 127.590 1,917.485 ↓ 4.0 92,970 1

Hash Left Join (cost=7,572.93..411,781.45 rows=23,180 width=3,230) (actual time=174.972..1,917.485 rows=92,970 loops=1)

  • Hash Cond: ((cusp.cid = ut.cid) AND (cusp.unit_type_id = ut.id))
6. 120.914 1,788.826 ↓ 4.0 92,970 1

Hash Left Join (cost=7,528.55..411,615.09 rows=23,180 width=3,025) (actual time=173.895..1,788.826 rows=92,970 loops=1)

  • Hash Cond: ((cusp.cid = pf.cid) AND (cusp.property_floorplan_id = pf.id))
7. 190.041 1,666.655 ↓ 4.0 92,970 1

Hash Left Join (cost=7,469.67..411,434.28 rows=23,180 width=3,006) (actual time=172.610..1,666.655 rows=92,970 loops=1)

  • Hash Cond: ((cusp.cid = clu.cid) AND (cusp.unit_space_id = clu.unit_space_id))
  • Filter: (((cusp.unit_space_id > 0) OR (clu.lease_id IS NOT NULL)) AND CASE WHEN (clu.lease_status_type_id = 3) THEN true ELSE ((usl.unit_space_id IS NOT NULL) OR (cusp.rent <> '0'::numeric)) END)
  • Rows Removed by Filter: 3933
8. 120.660 1,345.219 ↓ 2.1 81,829 1

Hash Left Join (cost=2,756.18..405,141.04 rows=38,405 width=1,641) (actual time=41.041..1,345.219 rows=81,829 loops=1)

  • Hash Cond: ((cusp.cid = pu.cid) AND (cusp.property_unit_id = pu.id))
9. 92.700 1,197.245 ↓ 2.1 81,829 1

Hash Left Join (cost=1,479.78..403,663.01 rows=38,405 width=1,401) (actual time=13.680..1,197.245 rows=81,829 loops=1)

  • Hash Cond: (usl.property_unit_id = property_unit_details.property_unit_id)
10. 94.324 1,103.420 ↓ 2.1 81,829 1

Hash Left Join (cost=1,443.88..403,459.95 rows=38,405 width=1,397) (actual time=12.549..1,103.420 rows=81,829 loops=1)

  • Hash Cond: (usl.unit_space_status_type_id = usst.id)
11. 87.340 1,009.082 ↓ 2.1 81,829 1

Hash Left Join (cost=1,442.73..403,296.95 rows=38,405 width=884) (actual time=12.529..1,009.082 rows=81,829 loops=1)

  • Hash Cond: ((usl.cid = use.cid) AND (usl.property_id = use.property_id) AND (usl.unit_exclusion_reason_type_id = use.unit_exclusion_reason_type_id))
12. 93.994 921.673 ↓ 2.1 81,829 1

Hash Left Join (cost=1,439.99..402,991.69 rows=38,405 width=714) (actual time=12.454..921.673 rows=81,829 loops=1)

  • Hash Cond: (usl.unit_exclusion_reason_type_id = usert.id)
13. 177.059 827.662 ↓ 2.1 81,829 1

Nested Loop Left Join (cost=1,438.81..402,836.28 rows=38,405 width=288) (actual time=12.430..827.662 rows=81,829 loops=1)

14. 97.577 323.287 ↓ 2.1 81,829 1

Hash Join (cost=1,438.38..211,233.97 rows=38,405 width=73) (actual time=12.416..323.287 rows=81,829 loops=1)

  • Hash Cond: (cusp.property_id = load_prop.property_id)
15. 218.084 225.499 ↓ 2.1 81,829 1

Bitmap Heap Scan on cached_unit_space_periods cusp (cost=1,430.10..210,697.63 rows=38,405 width=34) (actual time=12.197..225.499 rows=81,829 loops=1)

  • Recheck Cond: (post_month = '2020-05-01'::date)
  • Filter: ((NOT COALESCE(is_deleted, false)) AND (cid = 13531))
  • Rows Removed by Filter: 426
  • Heap Blocks: exact=28106
16. 7.415 7.415 ↓ 1.3 99,470 1

Bitmap Index Scan on idx_cached_unit_space_periods_post_month (cost=0.00..1,420.50 rows=76,809 width=0) (actual time=7.414..7.415 rows=99,470 loops=1)

  • Index Cond: (post_month = '2020-05-01'::date)
17. 0.106 0.211 ↑ 1.0 190 1

Hash (cost=5.90..5.90 rows=190 width=43) (actual time=0.211..0.211 rows=190 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
18. 0.105 0.105 ↑ 1.0 190 1

Seq Scan on load_prop (cost=0.00..5.90 rows=190 width=43) (actual time=0.007..0.105 rows=190 loops=1)

19. 327.316 327.316 ↑ 1.0 1 81,829

Index Scan using idx_unit_space_logs_id on unit_space_logs usl (cost=0.43..4.98 rows=1 width=223) (actual time=0.004..0.004 rows=1 loops=81,829)

  • Index Cond: (id = cusp.unit_space_log_id)
  • Filter: ((cid = 13531) AND (cid = cusp.cid))
20. 0.008 0.017 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=430) (actual time=0.017..0.017 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
21. 0.009 0.009 ↑ 1.0 8 1

Seq Scan on unit_exclusion_reason_types usert (cost=0.00..1.08 rows=8 width=430) (actual time=0.004..0.009 rows=8 loops=1)

22. 0.034 0.069 ↑ 1.0 58 1

Hash (cost=1.73..1.73 rows=58 width=190) (actual time=0.069..0.069 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
23. 0.035 0.035 ↑ 1.0 58 1

Seq Scan on unit_space_exclusions use (cost=0.00..1.73 rows=58 width=190) (actual time=0.005..0.035 rows=58 loops=1)

  • Filter: (cid = 13531)
24. 0.007 0.014 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=517) (actual time=0.014..0.014 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
25. 0.007 0.007 ↑ 1.0 7 1

Seq Scan on unit_space_status_types usst (cost=0.00..1.07 rows=7 width=517) (actual time=0.003..0.007 rows=7 loops=1)

26. 0.545 1.125 ↑ 1.0 1,156 1

Hash (cost=21.45..21.45 rows=1,156 width=12) (actual time=1.125..1.125 rows=1,156 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 66kB
27. 0.580 0.580 ↑ 1.0 1,156 1

Seq Scan on property_unit_details (cost=0.00..21.45 rows=1,156 width=12) (actual time=0.012..0.580 rows=1,156 loops=1)

  • Filter: (cid = 13531)
28. 12.860 27.314 ↑ 1.0 23,469 1

Hash (cost=924.36..924.36 rows=23,469 width=248) (actual time=27.314..27.314 rows=23,469 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 3427kB
29. 14.454 14.454 ↑ 1.0 23,469 1

Seq Scan on property_units pu (cost=0.00..924.36 rows=23,469 width=248) (actual time=0.005..14.454 rows=23,469 loops=1)

  • Filter: (cid = 13531)
30. 65.901 131.395 ↑ 1.0 84,745 1

Hash (cost=3,442.31..3,442.31 rows=84,745 width=1,372) (actual time=131.395..131.395 rows=84,745 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 16476kB
31. 65.494 65.494 ↑ 1.0 84,745 1

Seq Scan on cached_leased_units_temp clu (cost=0.00..3,442.31 rows=84,745 width=1,372) (actual time=0.013..65.494 rows=84,745 loops=1)

  • Filter: (cid = 13531)
32. 0.580 1.257 ↑ 1.0 1,123 1

Hash (cost=42.04..42.04 rows=1,123 width=27) (actual time=1.257..1.257 rows=1,123 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
33. 0.677 0.677 ↑ 1.0 1,123 1

Seq Scan on property_floorplans pf (cost=0.00..42.04 rows=1,123 width=27) (actual time=0.030..0.677 rows=1,123 loops=1)

  • Filter: (cid = 13531)
34. 0.492 1.069 ↑ 1.0 923 1

Hash (cost=30.54..30.54 rows=923 width=217) (actual time=1.068..1.069 rows=923 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 78kB
35. 0.577 0.577 ↑ 1.0 923 1

Seq Scan on unit_types ut (cost=0.00..30.54 rows=923 width=217) (actual time=0.008..0.577 rows=923 loops=1)

  • Filter: (cid = 13531)
36. 0.008 0.019 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=13) (actual time=0.018..0.019 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.011 0.011 ↑ 1.0 10 1

Seq Scan on occupancy_types ot_unit (cost=0.00..1.10 rows=10 width=13) (actual time=0.006..0.011 rows=10 loops=1)

38. 278.910 743.760 ↑ 5.0 1 92,970

Bitmap Heap Scan on scheduled_charges_temp charges (cost=0.91..5.01 rows=5 width=706) (actual time=0.007..0.008 rows=1 loops=92,970)

  • Recheck Cond: (((cid = 13531) AND (lease_id = clu.lease_id)) OR ((cid = 13531) AND (lease_id = clu.prior_lease_id)))
  • Filter: (cid = clu.cid)
  • Heap Blocks: exact=56815
39. 92.970 464.850 ↓ 0.0 0 92,970

BitmapOr (cost=0.91..0.91 rows=5 width=0) (actual time=0.005..0.005 rows=0 loops=92,970)

40. 278.910 278.910 ↑ 2.0 1 92,970

Bitmap Index Scan on idx_schedule_charge_temp (cost=0.00..0.45 rows=2 width=0) (actual time=0.003..0.003 rows=1 loops=92,970)

  • Index Cond: ((cid = 13531) AND (lease_id = clu.lease_id))
41. 92.970 92.970 ↓ 0.0 0 92,970

Bitmap Index Scan on idx_schedule_charge_temp (cost=0.00..0.45 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=92,970)

  • Index Cond: ((cid = 13531) AND (lease_id = clu.prior_lease_id))