explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 110

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 34,761.331 ↑ 1.0 1 1

Aggregate (cost=438,492.96..438,492.99 rows=1 width=12) (actual time=34,761.331..34,761.331 rows=1 loops=1)

2. 0.017 34,761.316 ↓ 79.0 79 1

Subquery Scan on t (cost=438,492.89..438,492.96 rows=1 width=4) (actual time=34,761.281..34,761.316 rows=79 loops=1)

  • Filter: CASE WHEN ((t.is_allow_lease_progression IS NULL) OR (t.app_id IS NULL)) THEN (t.rank1 = 1) ELSE (t.rank1 IS NOT NULL) END
3. 0.021 34,761.299 ↓ 79.0 79 1

Unique (cost=438,492.89..438,492.92 rows=1 width=87) (actual time=34,761.277..34,761.299 rows=79 loops=1)

4. 0.079 34,761.278 ↓ 79.0 79 1

Sort (cost=438,492.89..438,492.90 rows=1 width=87) (actual time=34,761.275..34,761.278 rows=79 loops=1)

  • Sort Key: (CASE WHEN ((btrim((dp.approvals_esign_docs ->> 'urgent_move_in_date_within'::text)) <> ''::text) AND (ca.lease_start_date <= (CURRENT_DATE + ((dp.approvals_esign_docs ->> 'urgent_move_in_date_within'::text))::integer))) THEN 3 WHEN ((btrim((dp.approvals_esign_docs ->> 'urgent_lease_type_ids'::text)) <> ''::text) AND (ca.lease_interval_type_id = ANY (((dp.approvals_esign_docs ->> 'urgent_lease_type_ids'::text))::integer[]))) THEN 3 WHEN ((btrim((dp.approvals_esign_docs ->> 'urgent_document_signed_since'::text)) <> ''::text) AND (CURRENT_DATE >= ((f.file_signed_on)::date + ((dp.approvals_esign_docs ->> 'urgent_document_signed_since'::text))::integer))) THEN 3 WHEN ((btrim((dp.approvals_esign_docs ->> 'important_document_signed_since'::text)) <> ''::text) AND (CURRENT_DATE >= ((f.file_signed_on)::date + ((dp.approvals_esign_docs ->> 'important_document_signed_since'::text))::integer))) THEN 2 WHEN ((btrim((dp.approvals_esign_docs ->> 'important_lease_type_ids'::text)) <> ''::text) AND (ca.lease_interval_type_id = ANY (((dp.approvals_esign_docs ->> 'important_lease_type_ids'::text))::integer[]))) THEN 2 WHEN ((btrim((dp.approvals_esign_docs ->> 'important_move_in_date_within'::text)) <> ''::text) AND (ca.lease_start_date <= (CURRENT_DATE + ((dp.approvals_esign_docs ->> 'important_move_in_date_within'::text))::integer))) THEN 2 ELSE 1 END), ca.id, f.lease_id, ap.lease_signed_on, f.applicant_id, f.application_id, f.is_allow_lease_progression, f.id, p.property_name, p.id, (rank() OVER (?))
  • Sort Method: quicksort Memory: 36kB
5. 0.089 34,761.199 ↓ 79.0 79 1

WindowAgg (cost=438,492.83..438,492.88 rows=1 width=87) (actual time=34,761.141..34,761.199 rows=79 loops=1)

6. 0.050 34,761.110 ↓ 79.0 79 1

Sort (cost=438,492.83..438,492.84 rows=1 width=79) (actual time=34,761.106..34,761.110 rows=79 loops=1)

  • Sort Key: fa_1.file_id, lc.customer_type_id, lc.id
  • Sort Method: quicksort Memory: 36kB
7. 0.047 34,761.060 ↓ 79.0 79 1

Nested Loop (cost=438,392.08..438,492.82 rows=1 width=79) (actual time=34,759.140..34,761.060 rows=79 loops=1)

8. 0.051 34,760.302 ↓ 79.0 79 1

Nested Loop (cost=438,391.65..438,492.35 rows=1 width=87) (actual time=34,759.043..34,760.302 rows=79 loops=1)

9. 0.038 34,759.777 ↓ 2.6 79 1

Nested Loop (cost=438,391.22..438,478.13 rows=30 width=79) (actual time=34,759.023..34,759.777 rows=79 loops=1)

10. 0.038 34,759.028 ↓ 19.8 79 1

Group (cost=438,390.66..438,391.46 rows=4 width=191) (actual time=34,758.990..34,759.028 rows=79 loops=1)

  • Group Key: ((dp.approvals_esign_docs ->> 'urgent_move_in_date_within'::text)), ((dp.approvals_esign_docs ->> 'urgent_lease_type_ids'::text)), ((dp.approvals_esign_docs ->> 'important_lease_type_ids'::text)), ((dp.approvals_esign_docs ->> 'important_move_in_date_within'::text)), p.id, p.property_name, ca.id, (CASE WHEN ((btrim((dp.approvals_esign_docs ->> 'urgent_move_in_date_within'::text)) <> ''::text) AND (ca.lease_start_date <= (CURRENT_DATE + ((dp.approvals_esign_docs ->> 'urgent_move_in_date_within'::text))::integer))) THEN 3 WHEN ((btrim((dp.approvals_esign_docs ->> 'urgent_lease_type_ids'::text)) <> ''::text) AND (ca.lease_interval_type_id = ANY (((dp.approvals_esign_docs ->> 'urgent_lease_type_ids'::text))::integer[]))) THEN 3 WHEN ((btrim((dp.approvals_esign_docs ->> 'urgent_document_signed_since'::text)) <> ''::text) AND (CURRENT_DATE >= ((f.file_signed_on)::date + ((dp.approvals_esign_docs ->> 'urgent_document_signed_since'::text))::integer))) THEN 3 WHEN ((btrim((dp.approvals_esign_docs ->> 'important_document_signed_since'::text)) <> ''::text) AND (CURRENT_DATE >= ((f.file_signed_on)::date + ((dp.approvals_esign_docs ->> 'important_document_signed_since'::text))::integer))) THEN 2 WHEN ((btrim((dp.approvals_esign_docs ->> 'important_lease_type_ids'::text)) <> ''::text) AND (ca.lease_interval_type_id = ANY (((dp.approvals_esign_docs ->> 'important_lease_type_ids'::text))::integer[]))) THEN 2 WHEN ((btrim((dp.approvals_esign_docs ->> 'important_move_in_date_within'::text)) <> ''::text) AND (ca.lease_start_date <= (CURRENT_DATE + ((dp.approvals_esign_docs ->> 'important_move_in_date_within'::text))::integer))) THEN 2 ELSE 1 END), f.lease_id, f.cid, f.id, ap.lease_signed_on, f.application_id, f.applicant_id, f.is_allow_lease_progression
11. 0.280 34,758.990 ↓ 19.8 79 1

Sort (cost=438,390.66..438,390.67 rows=4 width=191) (actual time=34,758.982..34,758.990 rows=79 loops=1)

  • Sort Key: ((dp.approvals_esign_docs ->> 'urgent_move_in_date_within'::text)), ((dp.approvals_esign_docs ->> 'urgent_lease_type_ids'::text)), ((dp.approvals_esign_docs ->> 'important_lease_type_ids'::text)), ((dp.approvals_esign_docs ->> 'important_move_in_date_within'::text)), p.id, p.property_name, ca.id, (CASE WHEN ((btrim((dp.approvals_esign_docs ->> 'urgent_move_in_date_within'::text)) <> ''::text) AND (ca.lease_start_date <= (CURRENT_DATE + ((dp.approvals_esign_docs ->> 'urgent_move_in_date_within'::text))::integer))) THEN 3 WHEN ((btrim((dp.approvals_esign_docs ->> 'urgent_lease_type_ids'::text)) <> ''::text) AND (ca.lease_interval_type_id = ANY (((dp.approvals_esign_docs ->> 'urgent_lease_type_ids'::text))::integer[]))) THEN 3 WHEN ((btrim((dp.approvals_esign_docs ->> 'urgent_document_signed_since'::text)) <> ''::text) AND (CURRENT_DATE >= ((f.file_signed_on)::date + ((dp.approvals_esign_docs ->> 'urgent_document_signed_since'::text))::integer))) THEN 3 WHEN ((btrim((dp.approvals_esign_docs ->> 'important_document_signed_since'::text)) <> ''::text) AND (CURRENT_DATE >= ((f.file_signed_on)::date + ((dp.approvals_esign_docs ->> 'important_document_signed_since'::text))::integer))) THEN 2 WHEN ((btrim((dp.approvals_esign_docs ->> 'important_lease_type_ids'::text)) <> ''::text) AND (ca.lease_interval_type_id = ANY (((dp.approvals_esign_docs ->> 'important_lease_type_ids'::text))::integer[]))) THEN 2 WHEN ((btrim((dp.approvals_esign_docs ->> 'important_move_in_date_within'::text)) <> ''::text) AND (ca.lease_start_date <= (CURRENT_DATE + ((dp.approvals_esign_docs ->> 'important_move_in_date_within'::text))::integer))) THEN 2 ELSE 1 END), f.lease_id, f.id, ap.lease_signed_on, f.application_id, f.applicant_id, f.is_allow_lease_progression
  • Sort Method: quicksort Memory: 36kB
12. 1.670 34,758.710 ↓ 19.8 79 1

Hash Left Join (cost=396,722.88..438,390.62 rows=4 width=191) (actual time=34,379.708..34,758.710 rows=79 loops=1)

  • Hash Cond: (f.id = allowed_files.file_id)
  • Filter: (((allowed_files.file_id IS NOT NULL) AND (0 < allowed_files.addendas_count)) OR (f.document_id IS NULL))
13. 0.215 397.490 ↓ 79.0 79 1

Nested Loop Left Join (cost=37.66..41,704.75 rows=1 width=178) (actual time=20.069..397.490 rows=79 loops=1)

  • Join Filter: (dp.cid = f.cid)
14. 1.080 397.117 ↓ 79.0 79 1

Nested Loop Left Join (cost=37.66..41,703.65 rows=1 width=79) (actual time=20.046..397.117 rows=79 loops=1)

  • Join Filter: (f.cid = sar.cid)
  • Filter: ((CASE WHEN (f.is_resident_verify = 1) THEN CASE WHEN (sar.id IS NOT NULL) THEN ((sar.request_status_type_id = 2) AND CASE WHEN (sar.screening_decision_type_id IS NOT NULL) THEN ((sar.screening_decision_type_id <> 3) AND CASE WHEN ((sar.screening_decision_type_id = 2) AND (alternatives: SubPlan 3 or hashed SubPlan 4)) THEN ((ap.lease_signed_on IS NOT NULL) AND (ap.lease_signature IS NOT NULL) AND (ap.lease_ip_address IS NOT NULL)) ELSE true END) ELSE NULL::boolean END) WHEN (sar.id IS NULL) THEN (sar.id IS NULL) ELSE NULL::boolean END ELSE true END AND (((ca.lease_interval_type_id = 1) AND ((SubPlan 5) IS NOT NULL) AND (pp.id IS NOT NULL)) OR (ca.lease_interval_type_id = ANY ('{3,4,5}'::integer[])) OR (pp.id IS NULL)) AND (f.applicant_id <> ALL ('{17731377,17736429,17741395,17756234,17762399,17764600,17764729,17791487,17794074,17811731,17825707,17842401,17848771,17854014,17856304,17871083,17873160,17886116,17886594,17888890,17893816,17899631,17909879,17916356,17923575,17928942,17938111,17943022,17945391,17947958,17959815,17965086,17965758,17968494,17969365,17969851,17972292,17982979,17983309,17985536,17985614,17994152,17994446,17996273}'::integer[])) AND (NOT (alternatives: SubPlan 6 or hashed SubPlan 7)) AND (ca.application_stage_id = 4) AND CASE WHEN (f.is_allow_lease_progression IS NULL) THEN (ca.application_status_id = 3) ELSE ((ca.application_status_id = ANY ('{2,3}'::integer[])) AND (ap.lease_signed_on IS NOT NULL)) END) OR ((ca.id IS NULL) AND (f.file_signed_on IS NOT NULL) AND ((cl.lease_status_type_id = 3) OR (cl.lease_status_type_id = 4))))
  • Rows Removed by Filter: 331
15. 0.274 388.207 ↓ 410.0 410 1

Nested Loop Left Join (cost=37.24..41,692.14 rows=1 width=135) (actual time=10.079..388.207 rows=410 loops=1)

16. 0.242 383.013 ↓ 410.0 410 1

Nested Loop Left Join (cost=36.81..41,689.67 rows=1 width=99) (actual time=10.009..383.013 rows=410 loops=1)

  • Filter: ((ca.lease_interval_type_id IS NOT NULL) OR ((f.application_id IS NULL) AND (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • Rows Removed by Filter: 2
17. 0.468 377.415 ↓ 412.0 412 1

Nested Loop (cost=36.38..41,645.56 rows=1 width=71) (actual time=9.964..377.415 rows=412 loops=1)

18. 0.366 376.123 ↓ 412.0 412 1

Nested Loop Left Join (cost=36.10..41,643.24 rows=1 width=52) (actual time=9.955..376.123 rows=412 loops=1)

19. 0.420 374.109 ↓ 412.0 412 1

Nested Loop (cost=35.68..41,640.75 rows=1 width=48) (actual time=9.923..374.109 rows=412 loops=1)

20. 0.099 369.981 ↓ 412.0 412 1

Subquery Scan on f (cost=35.25..41,638.28 rows=1 width=44) (actual time=9.876..369.981 rows=412 loops=1)

  • Filter: (f.cid = 3,395)
21. 8.910 369.882 ↓ 412.0 412 1

Nested Loop Left Join (cost=35.25..41,638.25 rows=1 width=2,024) (actual time=9.876..369.882 rows=412 loops=1)

  • Join Filter: (ptv.property_id = f_1.property_id)
  • Rows Removed by Join Filter: 105,472
22. 0.390 337.488 ↓ 412.0 412 1

Nested Loop Left Join (cost=4.57..41,606.90 rows=1 width=56) (actual time=5.409..337.488 rows=412 loops=1)

23. 0.502 335.038 ↓ 412.0 412 1

Nested Loop (cost=4.15..41,606.43 rows=1 width=52) (actual time=5.367..335.038 rows=412 loops=1)

24. 5.063 329.828 ↓ 17.8 428 1

Nested Loop (cost=3.72..41,594.88 rows=24 width=48) (actual time=5.310..329.828 rows=428 loops=1)

25. 14.860 287.643 ↓ 35.0 6,187 1

Hash Join (cost=3.16..41,159.47 rows=177 width=20) (actual time=5.098..287.643 rows=6,187 loops=1)

  • Hash Cond: (f_1.file_type_id = ft.id)
26. 58.369 272.756 ↓ 2.1 169,217 1

Nested Loop (cost=0.81..40,942.74 rows=79,573 width=24) (actual time=4.231..272.756 rows=169,217 loops=1)

27. 4.132 4.132 ↑ 1.0 1 1

Function Scan on load_properties lp (cost=0.25..1.88 rows=1 width=4) (actual time=4.130..4.132 rows=1 loops=1)

  • Filter: (is_disabled = 0)
28. 210.255 210.255 ↓ 2.1 169,217 1

Index Scan using idx_files_cid_property_id on files f_1 (cost=0.56..38,553.67 rows=79,573 width=24) (actual time=0.100..210.255 rows=169,217 loops=1)

  • Index Cond: ((cid = 3,395) AND (property_id = lp.property_id))
29. 0.001 0.027 ↑ 1.0 1 1

Hash (cost=2.31..2.31 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
30. 0.026 0.026 ↑ 1.0 1 1

Index Scan using idx_file_types_cid_system_code on file_types ft (cost=0.27..2.31 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=1)

  • Index Cond: ((cid = 3,395) AND ((system_code)::text = 'LP'::text))
31. 37.122 37.122 ↓ 0.0 0 6,187

Index Scan using idx_file_associations_cid_file_id on file_associations fa (cost=0.56..2.28 rows=6 width=32) (actual time=0.006..0.006 rows=0 loops=6,187)

  • Index Cond: ((cid = 3,395) AND (file_id = f_1.id))
  • Filter: ((deleted_on IS NULL) AND (lease_id IS NOT NULL) AND CASE WHEN (application_id IS NULL) THEN (customer_id IS NULL) ELSE (customer_id IS NOT NULL) END AND (approved_by IS NULL) AND ((application_id IS NOT NULL) OR (f_1.require_countersign = 1)))
  • Rows Removed by Filter: 2
32. 4.708 4.708 ↑ 1.0 1 428

Index Scan using idx_lease_customers_cid_lease_id_customer_id_lease_status_type_ on lease_customers lc_1 (cost=0.43..0.48 rows=1 width=20) (actual time=0.011..0.011 rows=1 loops=428)

  • Index Cond: ((cid = 3,395) AND (lease_id = fa.lease_id) AND (customer_id = fa.customer_id))
33. 2.060 2.060 ↑ 1.0 1 412

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp2 (cost=0.42..0.47 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=412)

  • Index Cond: ((cid = f_1.cid) AND (cid = 3,395) AND (property_id = f_1.property_id) AND ((key)::text = 'ALLOW_INDEPENDENT_APPLICANT_LEASE_PROGRESSION'::text))
  • Filter: (value IS NOT NULL)
34. 14.008 23.484 ↓ 25.7 257 412

Unique (cost=30.68..30.73 rows=10 width=12) (actual time=0.011..0.057 rows=257 loops=412)

35. 5.107 9.476 ↓ 25.7 257 412

Sort (cost=30.68..30.70 rows=10 width=12) (actual time=0.011..0.023 rows=257 loops=412)

  • Sort Key: ptv.property_id
  • Sort Method: quicksort Memory: 37kB
36. 0.000 4.369 ↓ 25.7 257 1

Nested Loop (cost=0.69..30.51 rows=10 width=12) (actual time=0.143..4.369 rows=257 loops=1)

37. 0.061 0.190 ↓ 25.1 528 1

Nested Loop (cost=0.41..5.69 rows=21 width=8) (actual time=0.063..0.190 rows=528 loops=1)

38. 0.017 0.017 ↑ 1.0 1 1

Index Scan using idx_company_transmission_vendors_transmission_vendor_id on company_transmission_vendors ctv (cost=0.14..2.18 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (transmission_vendor_id = 21)
  • Filter: (cid = 3,395)
39. 0.112 0.112 ↓ 16.5 528 1

Index Only Scan using uk_property_transmission_vendors_cid_company_transmission_vendo on property_transmission_vendors ptv (cost=0.28..2.55 rows=32 width=12) (actual time=0.044..0.112 rows=528 loops=1)

  • Index Cond: ((cid = 3,395) AND (company_transmission_vendor_id = ctv.id))
  • Heap Fetches: 0
40. 4.224 4.224 ↓ 0.0 0 528

Index Scan using idx_property_products_cid_property_id on property_products p_prod (cost=0.28..1.15 rows=1 width=8) (actual time=0.002..0.008 rows=0 loops=528)

  • Index Cond: ((cid = 3,395) AND (property_id = ptv.property_id))
  • Filter: ((ps_product_id = 53) OR (ps_product_id IS NULL))
  • Rows Removed by Filter: 6
41. 3.708 3.708 ↑ 1.0 1 412

Index Scan using pk_cached_leases on cached_leases cl (cost=0.43..2.47 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=412)

  • Index Cond: ((cid = 3,395) AND (id = f.lease_id))
42. 1.648 1.648 ↓ 0.0 0 412

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp (cost=0.42..2.46 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=412)

  • Index Cond: ((cid = f.cid) AND (cid = 3,395) AND (property_id = f.property_id) AND ((key)::text = 'REQUIRE_RENTERS_INSURANCE_FOR_LEASE_APPROVAL'::text))
  • Filter: (value IS NOT NULL)
43. 0.824 0.824 ↑ 1.0 1 412

Index Scan using idx_properties_id on properties p (cost=0.28..2.31 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=412)

  • Index Cond: (id = f.property_id)
  • Filter: (cid = 3,395)
44. 5.356 5.356 ↑ 1.0 1 412

Index Scan using idx_cached_applications_id on cached_applications ca (cost=0.43..2.47 rows=1 width=28) (actual time=0.013..0.013 rows=1 loops=412)

  • Index Cond: (id = f.application_id)
  • Filter: ((cancelled_on IS NULL) AND (cid = 3,395) AND (cid = f.cid))
  • Rows Removed by Filter: 0
45.          

SubPlan (for Nested Loop Left Join)

46. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2.91..41.61 rows=1 width=0) (never executed)

  • Hash Cond: (cf.file_type_id = ft_1.id)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_files_file_id on files cf (cost=0.56..39.06 rows=75 width=8) (never executed)

  • Index Cond: ((cid = 3,395) AND (file_id = f.id))
48. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.31..2.31 rows=1 width=8) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_file_types_cid_system_code on file_types ft_1 (cost=0.27..2.31 rows=1 width=8) (never executed)

  • Index Cond: ((cid = 3,395) AND ((system_code)::text = 'SIGNED'::text))
50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..361,406.82 rows=96,910 width=4) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_file_types_cid_system_code on file_types ft_2 (cost=0.27..2.31 rows=1 width=8) (never executed)

  • Index Cond: ((cid = 3,395) AND ((system_code)::text = 'SIGNED'::text))
52. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_files_cid_file_type_id on files cf_1 (cost=0.56..335,535.50 rows=862,300 width=12) (never executed)

  • Index Cond: ((cid = 3,395) AND (file_type_id = ft_2.id))
53. 4.920 4.920 ↓ 0.0 0 410

Index Scan using uk_applicant_applications_cid_applicant_id_application_id on applicant_applications ap (cost=0.43..2.47 rows=1 width=48) (actual time=0.012..0.012 rows=0 loops=410)

  • Index Cond: ((cid = f.cid) AND (cid = 3,395) AND (applicant_id = f.applicant_id) AND (application_id = f.application_id))
  • Filter: (lease_signed_on IS NOT NULL)
  • Rows Removed by Filter: 1
54. 2.050 2.050 ↓ 0.0 0 410

Index Scan using idx_screening_application_requests_application_id on screening_application_requests sar (cost=0.42..0.47 rows=1 width=20) (actual time=0.005..0.005 rows=0 loops=410)

  • Index Cond: (ca.id = application_id)
  • Filter: (cid = 3,395)
55.          

SubPlan (for Nested Loop Left Join)

56. 0.204 0.204 ↑ 1.0 1 17

Index Scan using idx_screening_application_condition_sets_application_id on screening_application_condition_sets sacs (cost=0.29..2.33 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=17)

  • Index Cond: (application_id = sar.application_id)
  • Filter: ((satisfied_by IS NULL) AND (satisfied_on IS NULL) AND (cid = sar.cid) AND (is_active = 1))
57. 0.000 0.000 ↓ 0.0 0

Seq Scan on screening_application_condition_sets sacs_1 (cost=0.00..1,788.51 rows=1,546 width=8) (never executed)

  • Filter: ((satisfied_by IS NULL) AND (satisfied_on IS NULL) AND (is_active = 1))
58. 0.146 1.606 ↓ 0.0 0 146

Limit (cost=5.64..5.64 rows=1 width=12) (actual time=0.011..0.011 rows=0 loops=146)

59. 0.292 1.460 ↓ 0.0 0 146

Sort (cost=5.64..5.64 rows=1 width=12) (actual time=0.010..0.010 rows=0 loops=146)

  • Sort Key: sub_rip.id DESC
  • Sort Method: quicksort Memory: 25kB
60. 0.076 1.168 ↓ 0.0 0 146

Nested Loop (cost=0.84..5.63 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=146)

61. 1.022 1.022 ↓ 0.0 0 146

Index Scan using idx_insurance_policy_customers_lease_id on insurance_policy_customers ipc (cost=0.42..3.16 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=146)

  • Index Cond: (ca.lease_id = lease_id)
  • Filter: ((cid = cid) AND (cid = f.cid) AND (f.application_id = application_id))
  • Rows Removed by Filter: 0
62. 0.070 0.070 ↑ 1.0 1 2

Index Scan using idx_resident_insurance_policies_id on resident_insurance_policies sub_rip (cost=0.42..2.47 rows=1 width=16) (actual time=0.035..0.035 rows=1 loops=2)

  • Index Cond: (id = ipc.resident_insurance_policy_id)
  • Filter: ((cid = f.cid) AND (insurance_policy_status_type_id = ANY ('{2,3,8}'::integer[])))
63. 3.970 3.970 ↓ 0.0 0 397

Index Scan using idx_applicant_applications_cid_application_id on applicant_applications aa (cost=0.43..2.95 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=397)

  • Index Cond: ((cid = ca.cid) AND (application_id = ca.id))
  • Filter: ((deleted_on IS NOT NULL) AND (customer_type_id = 1))
  • Rows Removed by Filter: 2
64. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_applicant_applications_cid_lease_customer_type_id on applicant_applications aa_1 (cost=0.43..204,213.02 rows=87,408 width=8) (never executed)

  • Index Cond: (customer_type_id = 1)
  • Filter: (deleted_on IS NOT NULL)
65. 0.158 0.158 ↑ 1.0 1 79

Seq Scan on dashboard_priorities dp (cost=0.00..1.06 rows=1 width=103) (actual time=0.002..0.002 rows=1 loops=79)

  • Filter: (cid = 3,395)
  • Rows Removed by Filter: 1
66. 33.415 34,359.550 ↓ 175.9 181,927 1

Hash (cost=396,651.62..396,651.62 rows=1,034 width=12) (actual time=34,359.550..34,359.550 rows=181,927 loops=1)

  • Buckets: 262,144 (originally 2048) Batches: 1 (originally 1) Memory Usage: 10,576kB
67. 18.669 34,326.135 ↓ 175.9 181,928 1

Subquery Scan on allowed_files (cost=396,581.83..396,651.62 rows=1,034 width=12) (actual time=34,195.574..34,326.135 rows=181,928 loops=1)

68. 72.216 34,307.466 ↓ 175.9 181,928 1

GroupAggregate (cost=396,581.83..396,620.60 rows=1,034 width=12) (actual time=34,195.572..34,307.466 rows=181,928 loops=1)

  • Group Key: f1.file_id
69. 167.261 34,235.250 ↓ 384.9 397,994 1

Sort (cost=396,581.83..396,584.41 rows=1,034 width=8) (actual time=34,195.558..34,235.250 rows=397,994 loops=1)

  • Sort Key: f1.file_id
  • Sort Method: quicksort Memory: 30,944kB
70. 972.395 34,067.989 ↓ 384.9 397,994 1

Nested Loop Semi Join (cost=2,728.45..396,530.05 rows=1,034 width=8) (actual time=16.826..34,067.989 rows=397,994 loops=1)

71. 2,134.270 7,018.044 ↓ 107.6 5,215,510 1

Hash Left Join (cost=2,727.89..364,899.11 rows=48,455 width=12) (actual time=16.778..7,018.044 rows=5,215,510 loops=1)

  • Hash Cond: ((f1.cid = da.cid) AND (f1.document_id = da.document_id) AND (f1.document_addenda_id = da.id))
  • Filter: CASE WHEN ((cu.is_administrator = 0) AND (da.countersign_company_group_ids IS NOT NULL)) THEN ((SubPlan 8) && da.countersign_company_group_ids) WHEN ((cu.is_administrator = 1) OR (da.countersign_company_group_ids IS NULL)) THEN true ELSE NULL::boolean END
72. 1,265.180 4,867.199 ↓ 53.8 5,215,510 1

Nested Loop (cost=1.12..361,409.17 rows=96,910 width=32) (actual time=0.106..4,867.199 rows=5,215,510 loops=1)

73. 0.009 0.081 ↑ 1.0 1 1

Nested Loop (cost=0.56..4.67 rows=1 width=20) (actual time=0.058..0.081 rows=1 loops=1)

74. 0.021 0.021 ↑ 1.0 1 1

Index Scan using idx_file_types_cid_system_code on file_types ft1 (cost=0.27..2.31 rows=1 width=8) (actual time=0.013..0.021 rows=1 loops=1)

  • Index Cond: ((cid = 3,395) AND ((system_code)::text = 'LA'::text))
75. 0.051 0.051 ↑ 1.0 1 1

Index Scan using idx_company_users_id on company_users cu (cost=0.29..2.33 rows=1 width=12) (actual time=0.041..0.051 rows=1 loops=1)

  • Index Cond: (id = 100,595)
  • Filter: ((cid = 3,395) AND (company_user_type_id = 2))
76. 3,601.938 3,601.938 ↓ 6.0 5,215,510 1

Index Scan using idx_files_cid_file_type_id on files f1 (cost=0.56..335,535.50 rows=862,300 width=24) (actual time=0.046..3,601.938 rows=5,215,510 loops=1)

  • Index Cond: ((cid = 3,395) AND (file_type_id = ft1.id))
77. 5.947 16.575 ↓ 1.0 30,217 1

Hash (cost=1,593.70..1,593.70 rows=30,215 width=44) (actual time=16.575..16.575 rows=30,217 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,555kB
78. 10.628 10.628 ↓ 1.0 30,217 1

Seq Scan on document_addendas da (cost=0.00..1,593.70 rows=30,215 width=44) (actual time=0.011..10.628 rows=30,217 loops=1)

  • Filter: (cid = 3,395)
  • Rows Removed by Filter: 22
79.          

SubPlan (for Hash Left Join)

80. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uk_company_user_groups_cid_company_user_id_company_group_id on company_user_groups (cost=0.29..2.41 rows=3 width=4) (never executed)

  • Index Cond: ((cid = cu.cid) AND (company_user_id = cu.id))
  • Heap Fetches: 0
81. 26,077.550 26,077.550 ↓ 0.0 0 5,215,510

Index Scan using idx_file_associations_cid_file_id on file_associations fa1 (cost=0.56..2.18 rows=19 width=8) (actual time=0.005..0.005 rows=0 loops=5,215,510)

  • Index Cond: ((cid = 3,395) AND (file_id = f1.id))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (approved_on IS NULL) AND (approved_by IS NULL))
  • Rows Removed by Filter: 1
82. 0.711 0.711 ↑ 7.0 1 79

Index Scan using idx_file_associations_cid_file_id on file_associations fa_1 (cost=0.56..21.43 rows=7 width=24) (actual time=0.008..0.009 rows=1 loops=79)

  • Index Cond: ((cid = 3,395) AND (file_id = f.id))
  • Filter: (((ca.id = application_id) AND (f.applicant_id = applicant_id)) OR (application_id IS NULL))
  • Rows Removed by Filter: 1
83. 0.474 0.474 ↑ 1.0 1 79

Index Scan using idx_lease_customers_cid_lease_id_customer_id_lease_status_type_ on lease_customers lc (cost=0.43..0.47 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=79)

  • Index Cond: ((cid = 3,395) AND (lease_id = fa_1.lease_id) AND (customer_id = fa_1.customer_id))
84. 0.711 0.711 ↑ 1.0 1 79

Index Only Scan using pk_customers on customers c (cost=0.43..0.47 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=79)

  • Index Cond: ((cid = 3,395) AND (id = fa_1.customer_id))
  • Heap Fetches: 12
Planning time : 29.527 ms
Execution time : 34,765.528 ms