explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GXn1

Settings
# exclusive inclusive rows x rows loops node
1. 64.789 58,556.625 ↑ 9.7 276 1

GroupAggregate (cost=2,487,262.58..2,490,331.47 rows=2,673 width=56) (actual time=58,457.252..58,556.625 rows=276 loops=1)

  • Group Key: l.cid, l.property_id, l.property_week_id
2. 144.823 58,491.836 ↓ 4.1 51,450 1

Sort (cost=2,487,262.58..2,487,293.62 rows=12,417 width=815) (actual time=58,456.003..58,491.836 rows=51,450 loops=1)

  • Sort Key: l.property_id, l.property_week_id
  • Sort Method: quicksort Memory: 51265kB
3. 147.502 58,347.013 ↓ 4.1 51,450 1

Nested Loop Left Join (cost=170,666.05..2,486,418.22 rows=12,417 width=815) (actual time=2,046.937..58,347.013 rows=51,450 loops=1)

4. 191.797 30,159.261 ↓ 4.1 51,450 1

Nested Loop (cost=170,614.27..1,843,186.56 rows=12,417 width=811) (actual time=2,046.675..30,159.261 rows=51,450 loops=1)

5. 3,130.964 23,073.164 ↓ 4.1 51,450 1

Hash Join (cost=170,568.38..1,272,997.92 rows=12,417 width=775) (actual time=2,046.595..23,073.164 rows=51,450 loops=1)

  • Hash Cond: (l.property_week_id = cpw.id)
  • Join Filter: (((cpw.start_date - '1 day'::interval) >= cal_prior.reporting_post_date) AND ((cpw.start_date - '1 day'::interval) <= cal_prior.apply_through_post_date))
  • Rows Removed by Join Filter: 3207979
6. 6,378.182 19,941.696 ↓ 29.2 3,259,429 1

Merge Join (cost=170,556.70..1,270,332.10 rows=111,753 width=775) (actual time=2,045.608..19,941.696 rows=3,259,429 loops=1)

  • Merge Cond: (cal_prior.application_id = l.application_id)
  • Join Filter: ((cal_prior.id <> l.id) AND (l.property_id = cal_prior.property_id))
  • Rows Removed by Join Filter: 368203
7. 10,502.289 10,502.289 ↑ 1.0 1,936,920 1

Index Scan using idx_cached_application_logs_application_id on cached_application_logs cal_prior (cost=0.43..1,008,937.92 rows=1,944,676 width=735) (actual time=0.014..10,502.289 rows=1,936,920 loops=1)

  • Filter: ((NOT is_post_date_ignored) AND (cid = 15077))
  • Rows Removed by Filter: 2997272
8. 2,748.102 3,061.225 ↓ 9.9 3,627,631 1

Sort (cost=44,737.17..45,657.67 rows=368,203 width=60) (actual time=627.313..3,061.225 rows=3,627,631 loops=1)

  • Sort Key: l.application_id
  • Sort Method: quicksort Memory: 52555kB
9. 313.123 313.123 ↑ 1.0 368,203 1

Seq Scan on leads l (cost=0.00..10,696.54 rows=368,203 width=60) (actual time=0.006..313.123 rows=368,203 loops=1)

  • Filter: (cid = 15077)
10. 0.240 0.504 ↑ 1.0 297 1

Hash (cost=7.97..7.97 rows=297 width=16) (actual time=0.503..0.504 rows=297 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
11. 0.264 0.264 ↑ 1.0 297 1

Seq Scan on property_weeks cpw (cost=0.00..7.97 rows=297 width=16) (actual time=0.007..0.264 rows=297 loops=1)

12. 2,366.700 6,894.300 ↑ 1.0 1 51,450

Aggregate (cost=45.89..45.90 rows=1 width=84) (actual time=0.134..0.134 rows=1 loops=51,450)

13. 4,527.600 4,527.600 ↓ 48.0 48 51,450

Index Scan using idx_cached_application_logs_cid_app_opening_log on cached_application_logs cal_inner (cost=0.43..45.86 rows=1 width=12) (actual time=0.006..0.088 rows=48 loops=51,450)

  • Index Cond: ((cid = 15077) AND (application_id = l.application_id))
  • Filter: ((post_date <= cpw.end_date) AND (property_id = l.property_id))
  • Rows Removed by Filter: 23
14. 154.350 28,040.250 ↑ 1.0 1 51,450

Aggregate (cost=51.77..51.78 rows=1 width=8) (actual time=0.544..0.545 rows=1 loops=51,450)

15. 705.380 27,885.900 ↓ 0.0 0 51,450

Nested Loop (cost=1.00..51.76 rows=1 width=16) (actual time=0.539..0.542 rows=0 loops=51,450)

  • Join Filter: ((ass.id <> ass_prior.id) AND ((ass_prior.id = ANY ('{13,14}'::integer[])) OR (ass.id = ANY ('{13,14}'::integer[]))))
  • Rows Removed by Join Filter: 5
16. 10,890.940 25,879.350 ↓ 5.0 5 51,450

Nested Loop (cost=0.86..51.29 rows=1 width=20) (actual time=0.055..0.503 rows=5 loops=51,450)

  • Join Filter: ((cal_reopened.application_stage_id = ass.application_stage_id) AND (cal_reopened.application_status_id = ass.application_status_id) AND (cal_reopened.lease_interval_type_id = ass.lease_interval_type_id))
  • Rows Removed by Join Filter: 288
17. 814.248 4,579.050 ↓ 5.0 5 51,450

Nested Loop (cost=0.86..48.69 rows=1 width=28) (actual time=0.036..0.089 rows=5 loops=51,450)

18. 2,984.100 2,984.100 ↓ 5.0 5 51,450

Index Scan using idx_cached_application_logs_cid_app_opening_log on cached_application_logs cal_reopened (cost=0.43..46.04 rows=1 width=24) (actual time=0.029..0.058 rows=5 loops=51,450)

  • Index Cond: ((cid = l.cid) AND (application_id = l.application_id))
  • Filter: ((post_date >= cpw.start_date) AND (post_date < (cpw.end_date + '1 day'::interval)))
  • Rows Removed by Filter: 65
19. 780.702 780.702 ↑ 1.0 1 260,234

Index Scan using pk_cached_application_logs on cached_application_logs cal_canceled_or_archived (cost=0.43..2.65 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=260,234)

  • Index Cond: ((cid = l.cid) AND (id = cal_reopened.prior_cached_application_log_id))
20. 10,409.360 10,409.360 ↑ 1.0 58 260,234

Seq Scan on application_stage_statuses ass (cost=0.00..1.58 rows=58 width=16) (actual time=0.002..0.040 rows=58 loops=260,234)

21. 1,301.170 1,301.170 ↑ 1.0 1 260,234

Index Scan using idx_application_stage_status_lease_interval_type_id on application_stage_statuses ass_prior (cost=0.14..0.46 rows=1 width=16) (actual time=0.003..0.005 rows=1 loops=260,234)

  • Index Cond: (lease_interval_type_id = cal_canceled_or_archived.lease_interval_type_id)
  • Filter: ((cal_canceled_or_archived.application_stage_id = application_stage_id) AND (cal_canceled_or_archived.application_status_id = application_status_id))
  • Rows Removed by Filter: 17