explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qJgg

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 1,573.018 ↓ 30.0 30 1

Limit (cost=51,163.98..51,163.98 rows=1 width=524) (actual time=1,572.939..1,573.018 rows=30 loops=1)

2. 0.287 1,572.949 ↓ 30.0 30 1

Sort (cost=51,163.98..51,163.98 rows=1 width=524) (actual time=1,572.937..1,572.949 rows=30 loops=1)

  • Sort Key: apps.priority DESC NULLS LAST, apps.application_completed_on NULLS FIRST
  • Sort Method: top-N heapsort Memory: 38kB
3. 0.248 1,572.662 ↓ 242.0 242 1

Subquery Scan on apps (cost=39,322.04..51,163.97 rows=1 width=524) (actual time=881.936..1,572.662 rows=242 loops=1)

  • Filter: (apps.cid = 2603)
4. 0.365 1,572.414 ↓ 60.5 242 1

Unique (cost=39,322.04..51,163.92 rows=4 width=524) (actual time=881.934..1,572.414 rows=242 loops=1)

5. 2.826 1,572.049 ↓ 61.0 244 1

Nested Loop Left Join (cost=39,322.04..51,163.91 rows=4 width=524) (actual time=881.931..1,572.049 rows=244 loops=1)

  • Join Filter: (appt.id = sar.applicant_id)
6. 0.587 1,568.735 ↓ 61.0 244 1

Nested Loop Left Join (cost=39,321.75..51,142.68 rows=4 width=507) (actual time=881.877..1,568.735 rows=244 loops=1)

  • Join Filter: (sacs.screening_application_request_id = sapr.id)
  • Filter: CASE WHEN (ptv.id IS NULL) THEN ((ptv.id IS NULL) AND (ca.lease_interval_type_id = 1)) WHEN ((ptv.id IS NOT NULL) AND ((CASE WHEN (ctv.transmission_vendor_id = 21) THEN 1 ELSE 0 END) <> 1) AND (pp_4.id IS NULL)) THEN ((aat.applicant_application_id IS NOT NULL) AND (ca.lease_interval_type_id = 1)) WHEN ((ptv.id IS NOT NULL) AND ((CASE WHEN (ctv.transmission_vendor_id = 21) THEN 1 ELSE 0 END) = 1) AND (sapr.screening_vendor_id = 2) AND (pp_4.id IS NULL)) THEN ((sapr.id IS NOT NULL) AND (ca.lease_interval_type_id = ANY ('{1,4}'::integer[])) AND CASE WHEN ((sacs.id IS NOT NULL) AND (sacs.is_active = 1)) THEN ((sacs.satisfied_on IS NOT NULL) AND (sapr.screening_decision_type_id = 2)) ELSE NULL::boolean END) WHEN ((ptv.id IS NOT NULL) AND ((CASE WHEN (ctv.transmission_vendor_id = 21) THEN 1 ELSE 0 END) <> 1) AND (pp_4.id IS NOT NULL)) THEN ((aat.applicant_application_id IS NULL) AND (ca.lease_interval_type_id = 1)) WHEN ((ptv.id IS NOT NULL) AND ((CASE WHEN (ctv.transmission_vendor_id = 21) THEN 1 ELSE 0 END) = 1) AND (pp_4.id IS NOT NULL)) THEN ((sapr.id IS NULL) AND (ca.lease_interval_type_id = ANY ('{1,4}'::integer[]))) ELSE NULL::boolean END
  • Rows Removed by Filter: 111
7. 0.666 1,567.438 ↓ 50.7 355 1

Nested Loop Left Join (cost=39,321.47..51,139.98 rows=7 width=523) (actual time=881.865..1,567.438 rows=355 loops=1)

8. 0.880 1,566.062 ↓ 50.7 355 1

Nested Loop Left Join (cost=39,321.18..51,134.53 rows=7 width=507) (actual time=881.848..1,566.062 rows=355 loops=1)

9. 0.743 1,562.697 ↓ 50.7 355 1

Nested Loop Left Join (cost=39,320.91..51,132.11 rows=7 width=499) (actual time=881.828..1,562.697 rows=355 loops=1)

10. 0.585 1,561.244 ↓ 50.7 355 1

Nested Loop Left Join (cost=39,320.78..51,131.00 rows=7 width=467) (actual time=881.819..1,561.244 rows=355 loops=1)

11. 0.775 1,560.304 ↓ 50.7 355 1

Nested Loop Left Join (cost=39,320.63..51,129.74 rows=7 width=471) (actual time=881.814..1,560.304 rows=355 loops=1)

12. 0.639 1,557.944 ↓ 45.3 317 1

Nested Loop Left Join (cost=39,320.20..50,959.35 rows=7 width=463) (actual time=881.783..1,557.944 rows=317 loops=1)

13. 0.501 1,556.037 ↓ 45.3 317 1

Nested Loop Left Join (cost=39,319.78..50,948.23 rows=7 width=459) (actual time=881.771..1,556.037 rows=317 loops=1)

14. 0.562 1,554.585 ↓ 45.3 317 1

Nested Loop Left Join (cost=39,319.37..50,937.13 rows=7 width=455) (actual time=881.763..1,554.585 rows=317 loops=1)

15. 0.722 1,553.072 ↓ 45.3 317 1

Nested Loop Left Join (cost=39,318.95..50,926.03 rows=7 width=451) (actual time=881.754..1,553.072 rows=317 loops=1)

16. 0.407 1,551.082 ↓ 45.3 317 1

Nested Loop Left Join (cost=39,318.53..50,914.93 rows=7 width=447) (actual time=881.736..1,551.082 rows=317 loops=1)

17. 0.727 1,550.358 ↓ 45.3 317 1

Nested Loop Left Join (cost=39,318.39..50,913.83 rows=7 width=329) (actual time=881.731..1,550.358 rows=317 loops=1)

18. 0.792 1,548.997 ↓ 45.3 317 1

Nested Loop (cost=39,318.11..50,911.41 rows=7 width=321) (actual time=881.719..1,548.997 rows=317 loops=1)

19. 40.727 1,546.937 ↓ 45.3 317 1

Merge Left Join (cost=39,317.69..50,908.10 rows=7 width=300) (actual time=881.697..1,546.937 rows=317 loops=1)

  • Merge Cond: ((ca.id = aat.application_id) AND (aa.id = aat.applicant_application_id))
  • Join Filter: (aat.cid = aa.cid)
20. 0.539 286.088 ↓ 45.3 317 1

Sort (cost=117.48..117.50 rows=7 width=296) (actual time=285.893..286.088 rows=317 loops=1)

  • Sort Key: ca.id, aa.id
  • Sort Method: quicksort Memory: 83kB
21. 0.622 285.549 ↓ 45.3 317 1

Nested Loop (cost=76.71..117.38 rows=7 width=296) (actual time=11.763..285.549 rows=317 loops=1)

22. 1.634 283.025 ↓ 45.3 317 1

Nested Loop (cost=76.28..99.37 rows=7 width=276) (actual time=11.751..283.025 rows=317 loops=1)

  • Join Filter: (lp.property_id = ca.property_id)
23. 0.670 8.991 ↓ 454.0 454 1

Nested Loop (cost=0.53..3.38 rows=1 width=183) (actual time=6.633..8.991 rows=454 loops=1)

24. 6.959 6.959 ↓ 454.0 454 1

Function Scan on load_properties lp (cost=0.25..0.88 rows=1 width=4) (actual time=6.627..6.959 rows=454 loops=1)

  • Filter: (is_disabled = 0)
  • Rows Removed by Filter: 66
25. 1.362 1.362 ↑ 1.0 1 454

Index Scan using idx_properties_id on properties p (cost=0.28..2.50 rows=1 width=179) (actual time=0.003..0.003 rows=1 loops=454)

  • Index Cond: (id = lp.property_id)
  • Filter: (cid = 2603)
26. 41.768 272.400 ↑ 7.0 1 454

Bitmap Heap Scan on cached_applications ca (cost=75.76..95.90 rows=7 width=101) (actual time=0.555..0.600 rows=1 loops=454)

  • Recheck Cond: ((property_id = p.id) AND (application_status_id = 3))
  • Filter: ((cid = 2603) AND (application_stage_id = 3))
  • Rows Removed by Filter: 12
  • Heap Blocks: exact=5217
27. 10.586 230.632 ↓ 0.0 0 454

BitmapAnd (cost=75.76..75.76 rows=19 width=0) (actual time=0.508..0.508 rows=0 loops=454)

28. 31.326 31.326 ↑ 1.8 835 454

Bitmap Index Scan on idx_cached_applications_property_id (cost=0.00..14.30 rows=1,504 width=0) (actual time=0.069..0.069 rows=835 loops=454)

  • Index Cond: (property_id = p.id)
29. 188.720 188.720 ↓ 1.0 5,758 337

Bitmap Index Scan on idx_cached_applications_status (cost=0.00..60.66 rows=5,685 width=0) (actual time=0.560..0.560 rows=5,758 loops=337)

  • Index Cond: (application_status_id = 3)
30. 1.902 1.902 ↑ 1.0 1 317

Index Scan using idx_applicant_applications_cid_application_id on applicant_applications aa (cost=0.42..2.56 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=317)

  • Index Cond: ((cid = 2603) AND (application_id = ca.id))
  • Filter: ((deleted_on IS NULL) AND (customer_type_id = 1))
  • Rows Removed by Filter: 1
31. 79.257 1,220.122 ↓ 57.1 88,209 1

Materialize (cost=39,200.21..50,782.83 rows=1,544 width=16) (actual time=439.579..1,220.122 rows=88,209 loops=1)

32. 162.767 1,140.865 ↓ 57.1 88,209 1

Subquery Scan on aat (cost=39,200.21..50,778.97 rows=1,544 width=16) (actual time=439.576..1,140.865 rows=88,209 loops=1)

  • Filter: ((aat.cid = 2603) AND (aat.rank = 1))
  • Rows Removed by Filter: 220541
33. 393.265 978.098 ↑ 1.0 308,750 1

WindowAgg (cost=39,200.21..46,147.47 rows=308,767 width=28) (actual time=439.573..978.098 rows=308,750 loops=1)

34. 408.621 584.833 ↑ 1.0 308,750 1

Sort (cost=39,200.21..39,972.13 rows=308,767 width=20) (actual time=439.563..584.833 rows=308,750 loops=1)

  • Sort Key: applicant_application_transmissions.application_id, applicant_application_transmissions.applicant_application_id, applicant_application_transmissions.id DESC
  • Sort Method: quicksort Memory: 36411kB
35. 176.212 176.212 ↑ 1.0 308,767 1

Seq Scan on applicant_application_transmissions (cost=0.00..11,046.59 rows=308,767 width=20) (actual time=0.007..176.212 rows=308,767 loops=1)

  • Filter: (cid = 2603)
36. 1.268 1.268 ↑ 1.0 1 317

Index Scan using idx_applicants_id on applicants appt (cost=0.42..0.47 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=317)

  • Index Cond: (id = aa.applicant_id)
  • Filter: (cid = 2603)
37. 0.634 0.634 ↑ 1.0 1 317

Index Scan using idx_company_employees_id on company_employees ce (cost=0.28..0.33 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=317)

  • Index Cond: (id = ca.leasing_agent_id)
  • Filter: ((cid = 2603) AND (cid = ca.cid))
38. 0.317 0.317 ↓ 0.0 0 317

Index Scan using pk_transmission_response_types on transmission_response_types trt (cost=0.14..0.16 rows=1 width=122) (actual time=0.001..0.001 rows=0 loops=317)

  • Index Cond: (id = aat.transmission_response_type_id)
39. 1.268 1.268 ↓ 0.0 0 317

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp_1 (cost=0.42..1.58 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=317)

  • Index Cond: ((cid = ca.cid) AND (cid = 2603) AND (property_id = ca.property_id) AND ((key)::text = 'DONT_EXPORT_APPLICATION_CHARGES'::text))
  • Filter: (value IS NOT NULL)
40. 0.951 0.951 ↓ 0.0 0 317

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp_2 (cost=0.42..1.58 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=317)

  • Index Cond: ((cid = ca.cid) AND (cid = 2603) AND (property_id = ca.property_id) AND ((key)::text = 'DONT_EXPORT_APPLICATIONS'::text))
  • Filter: (value IS NOT NULL)
41. 0.951 0.951 ↓ 0.0 0 317

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp_3 (cost=0.42..1.58 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=317)

  • Index Cond: ((cid = ca.cid) AND (cid = 2603) AND (property_id = ca.property_id) AND ((key)::text = 'DONT_EXPORT_GUEST_CARDS'::text))
  • Filter: (value IS NOT NULL)
42. 1.268 1.268 ↑ 1.0 1 317

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp_4 (cost=0.42..1.58 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=317)

  • Index Cond: ((cid = ca.cid) AND (cid = 2603) AND (property_id = ca.property_id) AND ((key)::text = 'SCREEN_AFTER_LEASE_COMPLETED'::text))
  • Filter: (value <> '0'::text)
43. 1.585 1.585 ↓ 0.0 0 317

Index Scan using idx_ar_transactions_cid_lease on ar_transactions at (cost=0.43..24.31 rows=3 width=12) (actual time=0.005..0.005 rows=0 loops=317)

  • Index Cond: ((cid = ca.cid) AND (cid = 2603) AND (lease_id = ca.lease_id))
  • Filter: ((remote_primary_key IS NULL) AND (customer_id IS NOT NULL))
  • Rows Removed by Filter: 2
44. 0.355 0.355 ↓ 0.0 0 355

Index Scan using idx_ar_codes on ar_codes ac (cost=0.14..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=355)

  • Index Cond: (id = at.ar_code_id)
  • Filter: (((default_ar_code_id IS NULL) OR (default_ar_code_id <> 1101)) AND (cid = 2603) AND (cid = at.cid))
45. 0.710 0.710 ↑ 1.0 1 355

Index Scan using idx_dashboard_priorities_cid on dashboard_priorities dp (cost=0.13..0.15 rows=1 width=36) (actual time=0.001..0.002 rows=1 loops=355)

  • Index Cond: ((cid = ca.cid) AND (cid = 2603))
46. 1.065 2.485 ↑ 1.0 1 355

Nested Loop (cost=0.27..0.33 rows=1 width=16) (actual time=0.005..0.007 rows=1 loops=355)

  • Join Filter: (ctv.id = ptv.company_transmission_vendor_id)
47. 0.710 0.710 ↑ 1.0 1 355

Index Scan using pk_company_transmission_vendors on company_transmission_vendors ctv (cost=0.12..0.15 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=355)

  • Index Cond: ((cid = ca.cid) AND (cid = 2603))
  • Filter: (transmission_type_id = 5)
48. 0.710 0.710 ↑ 1.0 1 355

Index Scan using idx_property_transmission_vendors_property_id on property_transmission_vendors ptv (cost=0.15..0.17 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=355)

  • Index Cond: (property_id = ca.property_id)
  • Filter: (cid = 2603)
49. 0.710 0.710 ↓ 0.0 0 355

Index Scan using idx_screening_application_requests_application_id on screening_application_requests sapr (cost=0.29..0.77 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=355)

  • Index Cond: (ca.id = application_id)
  • Filter: ((cid = 2603) AND (ca.cid = cid))
50. 0.710 0.710 ↓ 0.0 0 355

Index Scan using idx_screening_application_condition_sets_application_id on screening_application_condition_sets sacs (cost=0.28..0.34 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=355)

  • Index Cond: (ca.id = application_id)
  • Filter: ((cid = 2603) AND (is_active = 1) AND (ca.cid = cid))
51. 0.488 0.488 ↓ 0.0 0 244

Index Scan using idx_screening_applicant_results_application_id on screening_applicant_results sar (cost=0.29..0.97 rows=2 width=12) (actual time=0.002..0.002 rows=0 loops=244)

  • Index Cond: (ca.id = application_id)
  • Filter: ((cid = 2603) AND (ca.cid = cid))
52.          

SubPlan (forNested Loop Left Join)

53. 0.000 0.000 ↓ 0.0 0

Limit (cost=3.89..3.90 rows=1 width=8) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=3.89..3.90 rows=1 width=8) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Index Scan using uk_property_application_preferences_cid_property_id_company_app on property_application_preferences pap (cost=0.42..3.89 rows=1 width=4) (never executed)

  • Index Cond: ((cid = 2603) AND (property_id = ca.property_id) AND (company_application_id = ca.company_application_id) AND ((key)::text = 'HIDE_OPTION_PET'::text))