explain.depesz.com

PostgreSQL's explain analyze made readable

Result: APkCF

Settings
# exclusive inclusive rows x rows loops node
1. 0.135 1,832.030 ↓ 10.0 10 1

Nested Loop Left Join (cost=6,632.62..6,747.14 rows=1 width=419) (actual time=1,579.200..1,832.030 rows=10 loops=1)

2. 0.030 1,801.955 ↓ 10.0 10 1

Nested Loop Left Join (cost=6,632.33..6,728.14 rows=1 width=127) (actual time=1,575.481..1,801.955 rows=10 loops=1)

3. 0.018 61.935 ↓ 10.0 10 1

Nested Loop Left Join (cost=25.59..120.18 rows=1 width=103) (actual time=12.563..61.935 rows=10 loops=1)

4. 0.011 44.407 ↓ 10.0 10 1

Nested Loop (cost=7.16..101.65 rows=1 width=75) (actual time=10.729..44.407 rows=10 loops=1)

5. 0.078 43.546 ↓ 10.0 10 1

Merge Join (cost=7.02..101.32 rows=1 width=83) (actual time=9.917..43.546 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.028 43.387 ↓ 6.8 81 1

Nested Loop (cost=0.71..139.09 rows=12 width=79) (actual time=3.453..43.387 rows=81 loops=1)

7. 0.015 0.015 ↑ 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.007..0.015 rows=9 loops=1)

  • Filter: (cid = 16813)
8. 0.017 43.344 ↓ 9.0 9 9

Materialize (cost=0.57..132.12 rows=1 width=61) (actual time=0.383..4.816 rows=9 loops=9)

9. 0.014 43.327 ↓ 10.0 10 1

Nested Loop (cost=0.57..132.12 rows=1 width=61) (actual time=3.442..43.327 rows=10 loops=1)

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

Nested Loop (cost=0.57..130.80 rows=1 width=53) (actual time=3.438..43.303 rows=10 loops=1)

11. 40.205 40.205 ↓ 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=49) (actual time=2.070..40.205 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.080 3.080 ↑ 1.0 1 10

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

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

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

14. 0.058 0.081 ↓ 9.1 494 1

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

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

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

  • Filter: (cid = 16813)
16. 0.850 0.850 ↑ 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.085..0.085 rows=1 loops=10)

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

Aggregate (cost=18.43..18.46 rows=1 width=32) (actual time=1.751..1.751 rows=1 loops=10)

18. 17.470 17.470 ↓ 2.0 2 10

Index Scan using idx_scheduled_charges_lease_interval_id on scheduled_charges (cost=0.29..18.43 rows=1 width=5) (actual time=0.753..1.747 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))
  • Rows Removed by Filter: 8
19. 0.120 1,739.990 ↑ 1.0 1 10

Hash Right Join (cost=6,606.73..6,607.93 rows=1 width=40) (actual time=173.995..173.999 rows=1 loops=10)

  • Hash Cond: (litp.id = pcal.lease_interval_type_id)
20. 0.020 0.020 ↑ 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.002 rows=5 loops=10)

21. 0.030 1,739.850 ↑ 1.0 1 10

Hash (cost=6,606.70..6,606.70 rows=1 width=32) (actual time=173.985..173.985 rows=1 loops=10)

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

Subquery Scan on pcal (cost=6,606.55..6,606.70 rows=1 width=32) (actual time=173.960..173.982 rows=1 loops=10)

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

WindowAgg (cost=6,606.55..6,606.64 rows=2 width=5,052) (actual time=173.959..173.980 rows=15 loops=10)

24. 0.880 1,739.540 ↓ 7.5 15 10

Sort (cost=6,606.55..6,606.56 rows=2 width=36) (actual time=173.953..173.954 rows=15 loops=10)

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

Bitmap Heap Scan on cached_application_logs s (cost=1,401.49..6,606.54 rows=2 width=36) (actual time=33.867..173.866 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 (unit_space_id = cl.unit_space_id))
  • Rows Removed by Filter: 9175
  • Heap Blocks: exact=36450
26. 6.810 223.160 ↓ 0.0 0 10

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

27. 97.620 97.620 ↓ 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=9.762..9.762 rows=72,217 loops=10)

  • Index Cond: (cal.post_month >= reporting_post_month)
28. 43.530 43.530 ↑ 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=4.353..4.353 rows=24,350 loops=10)

  • Index Cond: (cal.post_month <= apply_through_post_month)
29. 75.200 75.200 ↓ 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=7.520..7.520 rows=37,332 loops=10)

  • Index Cond: (is_post_date_ignored = false)
30. 0.050 29.940 ↑ 1.0 1 10

GroupAggregate (cost=0.29..18.66 rows=1 width=40) (actual time=2.994..2.994 rows=1 loops=10)

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

Index Scan using idx_scheduled_charges_lease_interval_id on scheduled_charges scheduled_charges_1 (cost=0.29..18.62 rows=1 width=13) (actual time=1.355..2.989 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 (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 : 128.283 ms