explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Nou

Settings
# exclusive inclusive rows x rows loops node
1. 0.191 2,024.805 ↓ 10.0 10 1

Nested Loop Left Join (cost=6,642.91..6,757.46 rows=1 width=419) (actual time=1,718.074..2,024.805 rows=10 loops=1)

2. 0.050 1,992.564 ↓ 10.0 10 1

Nested Loop Left Join (cost=6,642.62..6,738.43 rows=1 width=131) (actual time=1,713.872..1,992.564 rows=10 loops=1)

3. 0.021 69.474 ↓ 10.0 10 1

Nested Loop Left Join (cost=25.62..120.20 rows=1 width=107) (actual time=13.594..69.474 rows=10 loops=1)

4. 0.022 47.783 ↓ 10.0 10 1

Nested Loop (cost=7.16..101.65 rows=1 width=83) (actual time=11.755..47.783 rows=10 loops=1)

5. 0.102 46.701 ↓ 10.0 10 1

Merge Join (cost=7.02..101.32 rows=1 width=91) (actual time=10.752..46.701 rows=10 loops=1)

  • Merge Cond: (ut.id = us.unit_type_id)
  • Join Filter: (cl.unit_space_id = us.id)
  • Rows Removed by Join Filter: 491
6. 0.035 46.504 ↓ 6.8 81 1

Nested Loop (cost=0.71..139.09 rows=12 width=87) (actual time=3.569..46.504 rows=81 loops=1)

7. 0.020 0.020 ↑ 1.3 9 1

Index Scan using idx_unit_types_id on unit_types ut (cost=0.14..6.58 rows=12 width=18) (actual time=0.011..0.020 rows=9 loops=1)

  • Filter: (cid = 16813)
8. 0.029 46.449 ↓ 9.0 9 9

Materialize (cost=0.57..132.12 rows=1 width=69) (actual time=0.395..5.161 rows=9 loops=9)

9. 0.028 46.420 ↓ 10.0 10 1

Nested Loop (cost=0.57..132.12 rows=1 width=69) (actual time=3.553..46.420 rows=10 loops=1)

  • Join Filter: (cal.lease_interval_type_id = lit.id)
  • Rows Removed by Join Filter: 10
10. 0.027 46.372 ↓ 10.0 10 1

Nested Loop (cost=0.57..130.80 rows=1 width=61) (actual time=3.549..46.372 rows=10 loops=1)

11. 42.945 42.945 ↓ 10.0 10 1

Index Scan using idx_cached_application_logs_cid_lease_interval_id on cached_application_logs cal (cost=0.29..126.48 rows=1 width=53) (actual time=2.030..42.945 rows=10 loops=1)

  • Index Cond: ((cid = 16813) AND (lease_interval_id = ANY ('{21528073,21657649,21660749,21705618,21712656,21712824,21717597,21719353,21720311,21725854}'::integer[])))
  • Filter: ((lease_approved_on IS NOT NULL) AND (cancelled_on IS NULL) AND (NOT is_post_date_ignored) AND (NOT is_post_month_ignored) AND (NOT is_deleted) AND (property_id = 1004340) AND (post_month = '2019-03-01'::date) AND (lease_interval_type_id = ANY ('{1,3,5}'::integer[])))
  • Rows Removed by Filter: 295
12. 3.400 3.400 ↑ 1.0 1 10

Index Scan using pk_cached_leases on cached_leases cl (cost=0.28..4.32 rows=1 width=16) (actual time=0.340..0.340 rows=1 loops=10)

  • Index Cond: ((cid = 16813) AND (id = cal.lease_id))
13. 0.020 0.020 ↑ 2.5 2 10

Seq Scan on lease_interval_types lit (cost=0.00..1.15 rows=5 width=16) (actual time=0.002..0.002 rows=2 loops=10)

14. 0.068 0.095 ↓ 9.1 494 1

Sort (cost=6.31..6.44 rows=54 width=16) (actual time=0.056..0.095 rows=494 loops=1)

  • Sort Key: us.unit_type_id
  • Sort Method: quicksort Memory: 27kB
15. 0.027 0.027 ↑ 1.0 54 1

Seq Scan on unit_spaces us (cost=0.00..4.75 rows=54 width=16) (actual time=0.003..0.027 rows=54 loops=1)

  • Filter: (cid = 16813)
16. 1.060 1.060 ↑ 1.0 1 10

Index Only Scan using pk_property_units on property_units pu (cost=0.14..0.33 rows=1 width=8) (actual time=0.106..0.106 rows=1 loops=10)

  • Index Cond: ((cid = 16813) AND (id = us.property_unit_id))
  • Heap Fetches: 10
17. 0.070 21.670 ↑ 1.0 1 10

Aggregate (cost=18.46..18.49 rows=1 width=32) (actual time=2.167..2.167 rows=1 loops=10)

18. 21.600 21.600 ↓ 2.0 2 10

Index Scan using idx_scheduled_charges_lease_interval_id on scheduled_charges (cost=0.29..18.45 rows=1 width=5) (actual time=0.813..2.160 rows=2 loops=10)

  • Index Cond: (lease_interval_id = cal.lease_interval_id)
  • Filter: ((deleted_on IS NULL) AND (NOT is_unselected_quote) AND (ar_trigger_id = 307) AND (ar_code_type_id = 2) AND (cid = cal.cid) AND (property_id = cal.property_id))
  • Rows Removed by Filter: 8
19. 0.160 1,923.040 ↑ 1.0 1 10

Hash Right Join (cost=6,617.00..6,618.20 rows=1 width=44) (actual time=192.298..192.304 rows=1 loops=10)

  • Hash Cond: (litp.id = pcal.lease_interval_type_id)
20. 0.030 0.030 ↑ 1.0 5 10

Seq Scan on lease_interval_types litp (cost=0.00..1.15 rows=5 width=16) (actual time=0.002..0.003 rows=5 loops=10)

21. 0.030 1,922.850 ↑ 1.0 1 10

Hash (cost=6,616.97..6,616.97 rows=1 width=36) (actual time=192.285..192.285 rows=1 loops=10)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.030 1,922.820 ↑ 1.0 1 10

Subquery Scan on pcal (cost=6,616.82..6,616.97 rows=1 width=36) (actual time=192.253..192.282 rows=1 loops=10)

  • Filter: (pcal.rank = 1)
  • Rows Removed by Filter: 14
23. 0.360 1,922.790 ↓ 7.5 15 10

WindowAgg (cost=6,616.82..6,616.90 rows=2 width=5,052) (actual time=192.251..192.279 rows=15 loops=10)

24. 1.290 1,922.430 ↓ 7.5 15 10

Sort (cost=6,616.82..6,616.82 rows=2 width=40) (actual time=192.241..192.243 rows=15 loops=10)

  • Sort Key: s.lease_end_date DESC
  • Sort Method: quicksort Memory: 25kB
25. 1,662.720 1,921.140 ↓ 7.5 15 10

Bitmap Heap Scan on cached_application_logs s (cost=1,401.49..6,616.81 rows=2 width=40) (actual time=38.147..192.114 rows=15 loops=10)

  • Recheck Cond: ((cal.post_month >= reporting_post_month) AND (cal.post_month <= apply_through_post_month))
  • Filter: ((lease_approved_on IS NOT NULL) AND (cancelled_on IS NULL) AND (NOT is_post_date_ignored) AND (NOT is_deleted) AND (lease_interval_type_id <> 2) AND (lease_interval_id <> cal.lease_interval_id) AND (lease_start_date < cal.lease_start_date) AND (cid = cl.cid) AND (property_id = cl.property_id) AND (unit_space_id = cl.unit_space_id))
  • Rows Removed by Filter: 9175
  • Heap Blocks: exact=36450
26. 7.490 258.420 ↓ 0.0 0 10

BitmapAnd (cost=1,401.49..1,401.49 rows=4,107 width=0) (actual time=25.842..25.842 rows=0 loops=10)

27. 110.770 110.770 ↓ 2.5 72,217 10

Bitmap Index Scan on idx_cached_application_logs_reporting_post_month (cost=0.00..430.74 rows=29,126 width=0) (actual time=11.077..11.077 rows=72,217 loops=10)

  • Index Cond: (cal.post_month >= reporting_post_month)
28. 50.170 50.170 ↑ 1.2 24,350 10

Bitmap Index Scan on idx_cached_application_logs_apply_through_post_month (cost=0.00..432.74 rows=29,126 width=0) (actual time=5.017..5.017 rows=24,350 loops=10)

  • Index Cond: (cal.post_month <= apply_through_post_month)
29. 89.990 89.990 ↓ 1.0 37,332 10

Bitmap Index Scan on idx_cached_application_logs_is_post_date_ignored (cost=0.00..537.51 rows=36,963 width=0) (actual time=8.999..8.999 rows=37,332 loops=10)

  • Index Cond: (is_post_date_ignored = false)
30. 0.070 32.050 ↑ 1.0 1 10

GroupAggregate (cost=0.29..18.69 rows=1 width=40) (actual time=3.205..3.205 rows=1 loops=10)

  • Group Key: scheduled_charges_1.cid, scheduled_charges_1.lease_interval_id
31. 31.980 31.980 ↑ 1.0 1 10

Index Scan using idx_scheduled_charges_lease_interval_id on scheduled_charges scheduled_charges_1 (cost=0.29..18.65 rows=1 width=13) (actual time=1.540..3.198 rows=1 loops=10)

  • Index Cond: (lease_interval_id = pcal.lease_interval_id)
  • Filter: ((deleted_on IS NULL) AND (NOT is_unselected_quote) AND (ar_trigger_id = 307) AND (ar_code_type_id = 2) AND (cid = pcal.cid) AND (property_id = pcal.property_id) AND (COALESCE((pcal.lease_start_date)::timestamp with time zone, pcal.lease_approved_on) >= charge_start_date) AND (COALESCE((pcal.lease_start_date)::timestamp with time zone, pcal.lease_approved_on) <= COALESCE((charge_end_date)::timestamp with time zone, (posted_through_date)::timestamp with time zone, COALESCE((pcal.lease_start_date)::timestamp with time zone, pcal.lease_approved_on))))
  • Rows Removed by Filter: 19
Planning time : 12.049 ms