explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FN9

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 345.329 ↓ 30.0 30 1

Limit (cost=4,087.17..4,087.17 rows=1 width=613) (actual time=345.262..345.329 rows=30 loops=1)

2.          

CTE cte_property_prefs

3. 0.634 3.324 ↑ 1.0 454 1

Hash Join (cost=410.02..529.87 rows=454 width=195) (actual time=2.383..3.324 rows=454 loops=1)

  • Hash Cond: (lp_1.property_id = p_1.id)
4. 0.332 2.072 ↑ 1.0 454 1

Hash Right Join (cost=367.21..480.82 rows=454 width=20) (actual time=1.752..2.072 rows=454 loops=1)

  • Hash Cond: (pp_3.property_id = lp_1.property_id)
5. 0.001 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on property_preferences pp_3 (cost=2.33..114.86 rows=87 width=8) (actual time=0.006..0.007 rows=0 loops=1)

  • Recheck Cond: ((key)::text = 'DONT_EXPORT_GUEST_CARDS'::text)
  • Filter: ((value IS NOT NULL) AND (cid = 2603))
6. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on idx_property_preferences_key (cost=0.00..2.31 rows=105 width=0) (actual time=0.005..0.006 rows=0 loops=1)

  • Index Cond: ((key)::text = 'DONT_EXPORT_GUEST_CARDS'::text)
7. 0.228 1.733 ↑ 1.0 454 1

Hash (cost=359.20..359.20 rows=454 width=16) (actual time=1.733..1.733 rows=454 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
8. 0.387 1.505 ↑ 1.0 454 1

Hash Left Join (cost=348.05..359.20 rows=454 width=16) (actual time=0.197..1.505 rows=454 loops=1)

  • Hash Cond: (lp_1.property_id = pp_4.property_id)
9. 0.379 0.988 ↑ 1.0 454 1

Hash Left Join (cost=231.90..241.85 rows=454 width=12) (actual time=0.053..0.988 rows=454 loops=1)

  • Hash Cond: (lp_1.property_id = pp_2.property_id)
10. 0.389 0.602 ↑ 1.0 454 1

Hash Left Join (cost=115.95..124.70 rows=454 width=8) (actual time=0.036..0.602 rows=454 loops=1)

  • Hash Cond: (lp_1.property_id = pp_1.property_id)
11. 0.196 0.196 ↑ 1.0 454 1

Seq Scan on lp lp_1 (cost=0.00..7.54 rows=454 width=4) (actual time=0.006..0.196 rows=454 loops=1)

12. 0.001 0.017 ↓ 0.0 0 1

Hash (cost=114.86..114.86 rows=87 width=8) (actual time=0.016..0.017 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
13. 0.004 0.016 ↓ 0.0 0 1

Bitmap Heap Scan on property_preferences pp_1 (cost=2.33..114.86 rows=87 width=8) (actual time=0.015..0.016 rows=0 loops=1)

  • Recheck Cond: ((key)::text = 'DONT_EXPORT_APPLICATION_CHARGES'::text)
  • Filter: ((value IS NOT NULL) AND (cid = 2603))
14. 0.012 0.012 ↓ 0.0 0 1

Bitmap Index Scan on idx_property_preferences_key (cost=0.00..2.31 rows=105 width=0) (actual time=0.011..0.012 rows=0 loops=1)

  • Index Cond: ((key)::text = 'DONT_EXPORT_APPLICATION_CHARGES'::text)
15. 0.001 0.007 ↓ 0.0 0 1

Hash (cost=114.86..114.86 rows=87 width=8) (actual time=0.006..0.007 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 0.001 0.006 ↓ 0.0 0 1

Bitmap Heap Scan on property_preferences pp_2 (cost=2.33..114.86 rows=87 width=8) (actual time=0.005..0.006 rows=0 loops=1)

  • Recheck Cond: ((key)::text = 'DONT_EXPORT_APPLICATIONS'::text)
  • Filter: ((value IS NOT NULL) AND (cid = 2603))
17. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on idx_property_preferences_key (cost=0.00..2.31 rows=105 width=0) (actual time=0.004..0.005 rows=0 loops=1)

  • Index Cond: ((key)::text = 'DONT_EXPORT_APPLICATIONS'::text)
18. 0.028 0.130 ↑ 1.5 54 1

Hash (cost=115.12..115.12 rows=82 width=8) (actual time=0.130..0.130 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
19. 0.083 0.102 ↑ 1.5 54 1

Bitmap Heap Scan on property_preferences pp_4 (cost=2.33..115.12 rows=82 width=8) (actual time=0.026..0.102 rows=54 loops=1)

  • Recheck Cond: ((key)::text = 'SCREEN_AFTER_LEASE_COMPLETED'::text)
  • Filter: ((value <> '0'::text) AND (cid = 2603))
  • Heap Blocks: exact=37
20. 0.019 0.019 ↑ 1.9 54 1

Bitmap Index Scan on idx_property_preferences_key (cost=0.00..2.31 rows=105 width=0) (actual time=0.019..0.019 rows=54 loops=1)

  • Index Cond: ((key)::text = 'SCREEN_AFTER_LEASE_COMPLETED'::text)
21. 0.283 0.618 ↑ 1.0 525 1

Hash (cost=36.25..36.25 rows=525 width=179) (actual time=0.618..0.618 rows=525 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
22. 0.335 0.335 ↑ 1.0 525 1

Seq Scan on properties p_1 (cost=0.00..36.25 rows=525 width=179) (actual time=0.004..0.335 rows=525 loops=1)

23.          

CTE cte_property_transmission_vendors

24. 0.237 1.612 ↑ 1.1 278 1

Unique (cost=42.95..44.45 rows=300 width=16) (actual time=1.263..1.612 rows=278 loops=1)

25. 0.272 1.375 ↑ 1.1 278 1

Sort (cost=42.95..43.70 rows=300 width=16) (actual time=1.262..1.375 rows=278 loops=1)

  • Sort Key: ptv_1.property_id, (CASE WHEN (ctv.transmission_vendor_id = 21) THEN 1 ELSE 0 END) DESC, ptv_1.id DESC
  • Sort Method: quicksort Memory: 38kB
26. 0.260 1.103 ↑ 1.1 278 1

Hash Semi Join (cost=13.21..30.61 rows=300 width=16) (actual time=0.457..1.103 rows=278 loops=1)

  • Hash Cond: (ptv_1.property_id = lp_2.property_id)
27. 0.250 0.403 ↑ 1.0 300 1

Nested Loop (cost=0.00..12.52 rows=300 width=16) (actual time=0.011..0.403 rows=300 loops=1)

  • Join Filter: (ctv.id = ptv_1.company_transmission_vendor_id)
28. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on company_transmission_vendors ctv (cost=0.00..1.01 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: ((cid = 2603) AND (transmission_type_id = 5))
29. 0.147 0.147 ↑ 1.0 300 1

Seq Scan on property_transmission_vendors ptv_1 (cost=0.00..7.75 rows=300 width=16) (actual time=0.004..0.147 rows=300 loops=1)

  • Filter: (cid = 2603)
30. 0.216 0.440 ↑ 1.0 454 1

Hash (cost=7.54..7.54 rows=454 width=4) (actual time=0.440..0.440 rows=454 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
31. 0.224 0.224 ↑ 1.0 454 1

Seq Scan on lp lp_2 (cost=0.00..7.54 rows=454 width=4) (actual time=0.005..0.224 rows=454 loops=1)

32.          

CTE cte_company_app_prefs

33. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=13.64..3,156.23 rows=131 width=12) (never executed)

  • Hash Cond: (pap.property_id = lp_3.property_id)
34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uk_property_application_preferences_cid_property_id_company_app on property_application_preferences pap (cost=0.42..3,141.21 rows=131 width=12) (never executed)

  • Index Cond: ((cid = 2603) AND (key = 'HIDE_OPTION_PET'::text))
  • Heap Fetches: 0
35. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.54..7.54 rows=454 width=4) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Seq Scan on lp lp_3 (cost=0.00..7.54 rows=454 width=4) (never executed)

37. 0.212 345.273 ↓ 30.0 30 1

Sort (cost=356.61..356.62 rows=1 width=613) (actual time=345.261..345.273 rows=30 loops=1)

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

Subquery Scan on apps (cost=356.53..356.60 rows=1 width=613) (actual time=344.512..345.061 rows=242 loops=1)

  • Filter: (apps.cid = 2603)
39. 0.237 344.856 ↓ 60.5 242 1

Unique (cost=356.53..356.55 rows=4 width=613) (actual time=344.511..344.856 rows=242 loops=1)

40. 0.486 344.619 ↓ 60.5 242 1

Sort (cost=356.53..356.54 rows=4 width=613) (actual time=344.509..344.619 rows=242 loops=1)

  • Sort Key: ca.id
  • Sort Method: quicksort Memory: 88kB
41. 4.203 344.133 ↓ 60.5 242 1

Nested Loop Left Join (cost=98.14..356.49 rows=4 width=613) (actual time=9.744..344.133 rows=242 loops=1)

  • Filter: CASE WHEN (ptv.id IS NULL) THEN true WHEN ((ptv.is_resident_verify <> 1) AND (p.pp_4_id IS NULL)) THEN ((aat.applicant_application_id IS NOT NULL) AND (ca.lease_interval_type_id = 1)) WHEN ((ptv.is_resident_verify = 1) AND (sapr.screening_vendor_id = 2) AND (p.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.is_resident_verify <> 1) AND (p.pp_4_id IS NOT NULL)) THEN ((aat.applicant_application_id IS NULL) AND (ca.lease_interval_type_id = 1)) WHEN ((ptv.is_resident_verify = 1) AND (p.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: 75
42. 36.895 335.809 ↓ 45.3 317 1

Nested Loop Left Join (cost=93.86..309.16 rows=7 width=514) (actual time=9.673..335.809 rows=317 loops=1)

  • Join Filter: ((ptv.cid = ca.cid) AND (ptv.property_id = ca.property_id))
  • Rows Removed by Join Filter: 87809
43. 0.457 257.704 ↓ 45.3 317 1

Nested Loop Left Join (cost=93.86..302.08 rows=7 width=502) (actual time=8.385..257.704 rows=317 loops=1)

  • Join Filter: ((sacs.cid = sapr.cid) AND (sacs.screening_application_request_id = sapr.id))
44. 0.470 256.613 ↓ 45.3 317 1

Hash Semi Join (cost=93.58..299.59 rows=7 width=490) (actual time=8.381..256.613 rows=317 loops=1)

  • Hash Cond: (p.id = lp.property_id)
45. 0.669 255.707 ↓ 22.6 317 1

Nested Loop Left Join (cost=80.37..286.26 rows=14 width=490) (actual time=7.934..255.707 rows=317 loops=1)

  • Join Filter: ((sar.cid = aa.cid) AND (sar.applicant_id = aa.applicant_id))
  • Rows Removed by Join Filter: 9
46. 0.656 254.404 ↓ 22.6 317 1

Nested Loop Left Join (cost=80.08..272.36 rows=14 width=498) (actual time=7.929..254.404 rows=317 loops=1)

47. 0.651 253.114 ↓ 22.6 317 1

Nested Loop Left Join (cost=79.79..261.46 rows=14 width=478) (actual time=7.925..253.114 rows=317 loops=1)

  • Join Filter: (dp.cid = ca.cid)
48. 0.710 252.146 ↓ 22.6 317 1

Nested Loop Left Join (cost=79.79..260.22 rows=14 width=446) (actual time=7.917..252.146 rows=317 loops=1)

49. 0.680 250.802 ↓ 22.6 317 1

Nested Loop (cost=79.51..255.39 rows=14 width=438) (actual time=7.914..250.802 rows=317 loops=1)

50. 0.677 248.854 ↓ 22.6 317 1

Nested Loop (cost=79.08..248.76 rows=14 width=409) (actual time=7.907..248.854 rows=317 loops=1)

51. 1.834 245.958 ↓ 21.1 317 1

Nested Loop (cost=78.66..210.16 rows=15 width=389) (actual time=7.890..245.958 rows=317 loops=1)

52. 3.958 3.958 ↓ 227.0 454 1

CTE Scan on cte_property_prefs p (cost=0.00..10.21 rows=2 width=288) (actual time=2.388..3.958 rows=454 loops=1)

  • Filter: (cid = 2603)
53. 8.626 240.166 ↑ 7.0 1 454

Bitmap Heap Scan on cached_applications ca (cost=78.66..99.90 rows=7 width=101) (actual time=0.522..0.529 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
54. 10.806 231.540 ↓ 0.0 0 454

BitmapAnd (cost=78.66..78.66 rows=19 width=0) (actual time=0.510..0.510 rows=0 loops=454)

55. 32.688 32.688 ↑ 1.8 835 454

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

  • Index Cond: (property_id = p.id)
56. 188.046 188.046 ↓ 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.558..0.558 rows=5,758 loops=337)

  • Index Cond: (application_status_id = 3)
57. 2.219 2.219 ↑ 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.006..0.007 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
58. 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)
59. 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))
60. 0.312 0.317 ↑ 1.0 1 317

Materialize (cost=0.00..1.03 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=317)

61. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on dashboard_priorities dp (cost=0.00..1.02 rows=1 width=36) (actual time=0.003..0.005 rows=1 loops=1)

  • Filter: (cid = 2603)
  • Rows Removed by Filter: 1
62. 0.634 0.634 ↓ 0.0 0 317

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=317)

  • Index Cond: (application_id = ca.id)
  • Filter: ((cid = 2603) AND (cid = ca.cid))
63. 0.634 0.634 ↓ 0.0 0 317

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

  • Index Cond: (ca.id = application_id)
  • Filter: (cid = 2603)
64. 0.228 0.436 ↑ 1.0 454 1

Hash (cost=7.54..7.54 rows=454 width=4) (actual time=0.435..0.436 rows=454 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
65. 0.208 0.208 ↑ 1.0 454 1

Seq Scan on lp (cost=0.00..7.54 rows=454 width=4) (actual time=0.008..0.208 rows=454 loops=1)

66. 0.634 0.634 ↓ 0.0 0 317

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=317)

  • Index Cond: (application_id = ca.id)
  • Filter: ((cid = 2603) AND (is_active = 1))
67. 41.210 41.210 ↓ 139.0 278 317

CTE Scan on cte_property_transmission_vendors ptv (cost=0.00..6.75 rows=2 width=16) (actual time=0.005..0.130 rows=278 loops=317)

  • Filter: (cid = 2603)
68. 0.317 4.121 ↓ 0.0 0 317

Limit (cost=4.28..4.28 rows=1 width=130) (actual time=0.013..0.013 rows=0 loops=317)

69. 1.268 3.804 ↓ 0.0 0 317

Sort (cost=4.28..4.28 rows=1 width=130) (actual time=0.012..0.012 rows=0 loops=317)

  • Sort Key: aat.id DESC
  • Sort Method: quicksort Memory: 25kB
70. 1.169 2.536 ↓ 0.0 0 317

Hash Join (cost=3.02..4.27 rows=1 width=130) (actual time=0.007..0.008 rows=0 loops=317)

  • Hash Cond: (trt.id = aat.transmission_response_type_id)
71. 0.099 0.099 ↑ 1.1 16 11

Seq Scan on transmission_response_types trt (cost=0.00..1.17 rows=17 width=122) (actual time=0.002..0.009 rows=16 loops=11)

72. 0.317 1.268 ↓ 0.0 0 317

Hash (cost=3.01..3.01 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=317)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
73. 0.951 0.951 ↓ 0.0 0 317

Index Scan using idx_applicant_application_transmissions_applicant_application_i on applicant_application_transmissions aat (cost=0.42..3.01 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=317)

  • Index Cond: (applicant_application_id = aa.id)
  • Filter: ((cid = 2603) AND (application_id = ca.id))
74.          

SubPlan (forNested Loop Left Join)

75. 0.000 0.000 ↓ 0.0 0

CTE Scan on cte_company_app_prefs cte (cost=0.00..3.60 rows=1 width=0) (never executed)

  • Filter: ((cid = ca.cid) AND (property_id = ca.property_id) AND (company_application_id = ca.company_application_id))
76. 0.000 0.000 ↓ 0.0 0

CTE Scan on cte_company_app_prefs cte_1 (cost=0.00..2.62 rows=131 width=12) (never executed)