explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p5F6

Settings
# exclusive inclusive rows x rows loops node
1. 0.194 347.858 ↓ 118.0 118 1

WindowAgg (cost=44,205.95..44,205.98 rows=1 width=607) (actual time=347.667..347.858 rows=118 loops=1)

2. 0.203 347.664 ↓ 118.0 118 1

Sort (cost=44,205.95..44,205.96 rows=1 width=599) (actual time=347.653..347.664 rows=118 loops=1)

  • Sort Key: fa.file_id, lc.customer_type_id, lc.id
  • Sort Method: quicksort Memory: 85kB
3. 3.848 347.461 ↓ 118.0 118 1

Nested Loop Left Join (cost=23.61..44,205.94 rows=1 width=599) (actual time=40.935..347.461 rows=118 loops=1)

  • Join Filter: (ptv.property_id = f.property_id)
  • Rows Removed by Join Filter: 30,562
4. 0.188 329.571 ↓ 118.0 118 1

Nested Loop Left Join (cost=4.73..44,186.79 rows=1 width=595) (actual time=36.441..329.571 rows=118 loops=1)

5. 0.204 328.439 ↓ 118.0 118 1

Nested Loop (cost=4.31..44,186.35 rows=1 width=591) (actual time=36.416..328.439 rows=118 loops=1)

6. 2.132 327.291 ↓ 4.7 236 1

Nested Loop (cost=3.88..44,163.51 rows=50 width=587) (actual time=26.799..327.291 rows=236 loops=1)

7. 25.792 278.595 ↓ 35.8 6,652 1

Hash Join (cost=3.32..43,859.74 rows=186 width=555) (actual time=3.225..278.595 rows=6,652 loops=1)

  • Hash Cond: (f.file_type_id = ft.id)
8. 91.431 252.788 ↓ 2.2 188,867 1

Nested Loop (cost=0.81..43,633.89 rows=84,293 width=555) (actual time=1.920..252.788 rows=188,867 loops=1)

9. 1.900 1.900 ↑ 1.0 1 1

Function Scan on load_properties lp (cost=0.25..0.88 rows=1 width=4) (actual time=1.899..1.900 rows=1 loops=1)

  • Filter: (is_disabled = 0)
10. 159.457 159.457 ↓ 2.2 188,867 1

Index Scan using idx_files_cid_property_id on files f (cost=0.56..42,790.09 rows=84,293 width=555) (actual time=0.017..159.457 rows=188,867 loops=1)

  • Index Cond: ((cid = 3,395) AND (property_id = lp.property_id))
11. 0.003 0.015 ↑ 1.0 1 1

Hash (cost=2.49..2.49 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: ((cid = 3,395) AND ((system_code)::text = 'LP'::text))
13. 46.564 46.564 ↓ 0.0 0 6,652

Index Scan using idx_file_associations_cid_file_id on file_associations fa (cost=0.56..1.50 rows=13 width=32) (actual time=0.007..0.007 rows=0 loops=6,652)

  • Index Cond: ((cid = 3,395) AND (file_id = f.id))
  • Filter: ((deleted_on IS NULL) AND (lease_id IS NOT NULL) AND (approved_by IS NULL) AND ((application_id IS NOT NULL) OR (f.require_countersign = 1)))
  • Rows Removed by Filter: 2
14. 0.944 0.944 ↓ 0.0 0 236

Index Scan using idx_lease_customers_cid_lease_id_customer_id_lease_status_type_ on lease_customers lc (cost=0.43..0.46 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=236)

  • Index Cond: ((cid = 3,395) AND (lease_id = fa.lease_id) AND (customer_id = fa.customer_id))
15. 0.944 0.944 ↑ 1.0 1 118

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp2 (cost=0.42..0.45 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=118)

  • Index Cond: ((cid = f.cid) AND (cid = 3,395) AND (property_id = f.property_id) AND ((key)::text = 'ALLOW_INDEPENDENT_APPLICANT_LEASE_PROGRESSION'::text))
  • Filter: (value IS NOT NULL)
16. 6.962 14.042 ↓ 26.0 260 118

Unique (cost=18.88..18.93 rows=10 width=12) (actual time=0.038..0.119 rows=260 loops=118)

17. 2.782 7.080 ↓ 26.0 260 118

Sort (cost=18.88..18.90 rows=10 width=12) (actual time=0.038..0.060 rows=260 loops=118)

  • Sort Key: ptv.property_id
  • Sort Method: quicksort Memory: 37kB
18. 0.198 4.298 ↓ 26.0 260 1

Nested Loop (cost=0.69..18.71 rows=10 width=12) (actual time=0.066..4.298 rows=260 loops=1)

19. 0.097 0.341 ↓ 25.6 537 1

Nested Loop (cost=0.41..6.02 rows=21 width=8) (actual time=0.031..0.341 rows=537 loops=1)

20. 0.021 0.021 ↑ 1.0 1 1

Index Scan using pk_company_transmission_vendors on company_transmission_vendors ctv (cost=0.14..2.36 rows=1 width=8) (actual time=0.008..0.021 rows=1 loops=1)

  • Index Cond: (cid = 3,395)
  • Filter: (transmission_vendor_id = 21)
  • Rows Removed by Filter: 25
21. 0.223 0.223 ↓ 16.3 537 1

Index Scan using idx_property_transmission_vendors_company_transmission_vendor_i on property_transmission_vendors ptv (cost=0.28..3.33 rows=33 width=12) (actual time=0.020..0.223 rows=537 loops=1)

  • Index Cond: (company_transmission_vendor_id = ctv.id)
  • Filter: (cid = 3,395)
22. 3.759 3.759 ↓ 0.0 0 537

Index Scan using idx_property_products_cid_property_id on property_products p_prod (cost=0.28..0.59 rows=1 width=8) (actual time=0.006..0.007 rows=0 loops=537)

  • 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
Planning time : 6.806 ms
Execution time : 348.079 ms