explain.depesz.com

PostgreSQL's explain analyze made readable

Result: is2I

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 2,658.678 ↑ 1.0 1 1

Unique (cost=816,143.73..816,143.80 rows=1 width=343) (actual time=2,658.677..2,658.678 rows=1 loops=1)

2. 0.060 2,658.676 ↑ 1.0 1 1

Sort (cost=816,143.73..816,143.74 rows=1 width=343) (actual time=2,658.675..2,658.676 rows=1 loops=1)

  • Sort Key: ah.header_number, ad.id, ad.ap_header_id, ad.transaction_amount, ad.pre_approval_amount, ad.bank_account_id, ((ad.transaction_amount - COALESCE(ad.pre_approval_amount, '0'::numeric))), (sum((ad.transaction_amount - COALESCE(ad.pre_approval_amount, '0'::numeric))) OVER (?)), (CASE WHEN (ah.lease_customer_id IS NULL) THEN ap.company_name ELSE func_format_refund_customer_names(ah.header_memo, false) END), p.id, p.property_name, ad.gl_account_id, ((SubPlan 1)), ba.account_name, ad.description, ah.due_date, ap.ap_payee_status_type_id, (CASE WHEN (ah.lease_customer_id IS NOT NULL) THEN func_format_refund_customer_names(ah.header_memo, false) ELSE ap.company_name END), apl.phone_number, apl.street_line1, apl.street_line2, apl.city, apl.state_code, apl.postal_code, ah.ap_payee_id, cu.name_last
  • Sort Method: quicksort Memory: 25kB
3. 0.034 2,658.616 ↑ 1.0 1 1

WindowAgg (cost=816,134.15..816,143.72 rows=1 width=343) (actual time=2,658.615..2,658.616 rows=1 loops=1)

4. 0.020 2,656.673 ↑ 1.0 1 1

Sort (cost=816,134.15..816,134.15 rows=1 width=236) (actual time=2,656.673..2,656.673 rows=1 loops=1)

  • Sort Key: ad.bank_account_id, ah.id
  • Sort Method: quicksort Memory: 25kB
5. 0.005 2,656.653 ↑ 1.0 1 1

Nested Loop Left Join (cost=18,334.33..816,134.14 rows=1 width=236) (actual time=2,621.840..2,656.653 rows=1 loops=1)

  • Filter: (rsr.reference_id IS NULL)
6. 0.004 2,655.581 ↑ 1.0 1 1

Nested Loop Left Join (cost=18,333.63..816,128.64 rows=1 width=236) (actual time=2,620.770..2,655.581 rows=1 loops=1)

  • Join Filter: (ad.cid = apl.cid)
7. 0.004 2,655.198 ↑ 1.0 1 1

Nested Loop Left Join (cost=18,333.21..816,126.19 rows=1 width=190) (actual time=2,620.387..2,655.198 rows=1 loops=1)

8. 0.003 2,655.187 ↑ 1.0 1 1

Nested Loop Left Join (cost=18,332.78..816,124.86 rows=1 width=191) (actual time=2,620.377..2,655.187 rows=1 loops=1)

9. 0.006 2,655.174 ↑ 1.0 1 1

Nested Loop Left Join (cost=18,332.35..816,122.40 rows=1 width=187) (actual time=2,620.365..2,655.174 rows=1 loops=1)

  • Filter: ((ap.ap_payee_status_type_id = ANY ('{1,3}'::integer[])) OR (ah.lease_customer_id IS NOT NULL))
10. 0.005 2,653.821 ↑ 1.0 1 1

Nested Loop (cost=18,331.93..816,119.94 rows=1 width=163) (actual time=2,619.013..2,653.821 rows=1 loops=1)

11. 0.004 2,653.448 ↑ 1.0 1 1

Nested Loop (cost=18,331.65..816,119.64 rows=1 width=139) (actual time=2,618.641..2,653.448 rows=1 loops=1)

12. 0.004 2,653.429 ↑ 1.0 1 1

Nested Loop (cost=18,331.37..816,119.34 rows=1 width=121) (actual time=2,618.623..2,653.429 rows=1 loops=1)

13. 0.005 2,652.370 ↑ 1.0 1 1

Nested Loop (cost=18,331.23..816,117.17 rows=1 width=121) (actual time=2,617.566..2,652.370 rows=1 loops=1)

14. 3.753 2,652.327 ↑ 1.0 1 1

Nested Loop (cost=18,331.09..816,117.01 rows=1 width=125) (actual time=2,617.524..2,652.327 rows=1 loops=1)

  • Join Filter: (ah.ap_remittance_id = ar.id)
  • Rows Removed by Join Filter: 2,955
15. 115.282 115.282 ↑ 1.0 2,956 1

Index Scan using idx_ap_remittances_ap_payment_type_id on ap_remittances ar (cost=0.42..5,442.00 rows=2,968 width=12) (actual time=17.061..115.282 rows=2,956 loops=1)

  • Filter: (cid = 4,632)
  • Rows Removed by Filter: 150,277
16. 2.061 2,533.292 ↑ 1.0 1 2,956

Materialize (cost=18,330.67..810,630.50 rows=1 width=125) (actual time=0.850..0.857 rows=1 loops=2,956)

17. 0.007 2,531.231 ↑ 1.0 1 1

Nested Loop (cost=18,330.67..810,630.49 rows=1 width=125) (actual time=2,510.803..2,531.231 rows=1 loops=1)

18. 2,290.803 2,531.206 ↑ 8.0 1 1

Bitmap Heap Scan on ap_headers ah (cost=18,330.24..810,610.71 rows=8 width=72) (actual time=2,510.781..2,531.206 rows=1 loops=1)

  • Recheck Cond: (cid = 4,632)
  • Filter: ((NOT is_template) AND (is_posted IS TRUE) AND (reversal_ap_header_id IS NULL) AND (ap_payment_id IS NULL) AND (header_number ~~* '%918257372%'::text) AND (ap_header_sub_type_id = ANY ('{5,6,7,8,17,12}'::integer[])) AND (NOT (SubPlan 2)))
  • Rows Removed by Filter: 444,488
  • Heap Blocks: exact=33,495
19. 1.812 238.818 ↓ 0.0 0 1

BitmapAnd (cost=18,330.24..18,330.24 rows=260,911 width=0) (actual time=238.818..238.818 rows=0 loops=1)

20. 65.470 65.470 ↓ 1.0 465,901 1

Bitmap Index Scan on pk_ap_headers (cost=0.00..4,646.64 rows=453,495 width=0) (actual time=65.469..65.470 rows=465,901 loops=1)

  • Index Cond: (cid = 4,632)
21. 171.536 171.536 ↓ 1.0 1,349,717 1

Bitmap Index Scan on idx_ap_headers_is_posted (cost=0.00..13,683.34 rows=1,335,722 width=0) (actual time=171.536..171.536 rows=1,349,717 loops=1)

  • Index Cond: (is_posted = true)
22.          

SubPlan (for Bitmap Heap Scan)

23. 0.002 1.585 ↓ 0.0 0 1

Unique (cost=0.71..4.77 rows=1 width=4) (actual time=1.585..1.585 rows=0 loops=1)

24. 0.002 1.583 ↓ 0.0 0 1

Result (cost=0.71..4.77 rows=1 width=4) (actual time=1.583..1.583 rows=0 loops=1)

  • One-Time Filter: (ah.cid = 4,632)
25. 0.004 1.581 ↓ 0.0 0 1

Nested Loop (cost=0.71..4.77 rows=1 width=4) (actual time=1.581..1.581 rows=0 loops=1)

26. 1.551 1.551 ↑ 1.0 1 1

Index Scan using idx_ap_details_cid_ap_header_id_gl_transaction_type_id_pm on ap_details ad_1 (cost=0.43..2.46 rows=1 width=12) (actual time=1.550..1.551 rows=1 loops=1)

  • Index Cond: ((cid = 4,632) AND (ap_header_id = ah.id) AND (gl_transaction_type_id = ah.gl_transaction_type_id) AND (post_month = ah.post_month))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL))
27. 0.026 0.026 ↓ 0.0 0 1

Index Scan using idx_properties_id on properties p_1 (cost=0.28..2.30 rows=1 width=8) (actual time=0.026..0.026 rows=0 loops=1)

  • Index Cond: (id = ad_1.property_id)
  • Filter: ((cid = 4,632) AND (is_disabled = 1))
  • Rows Removed by Filter: 1
28. 0.018 0.018 ↑ 1.0 1 1

Index Scan using idx_ap_details_cid_ap_header_id_gl_transaction_type_id_pm on ap_details ad (cost=0.43..2.46 rows=1 width=69) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: ((cid = 4,632) AND (ap_header_id = ah.id) AND (gl_transaction_type_id = ah.gl_transaction_type_id) AND (post_month = ah.post_month))
  • Filter: ((reversal_ap_detail_id IS NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL) AND ((transaction_amount - COALESCE(pre_approval_amount, '0'::numeric)) <> '0'::numeric))
29. 0.038 0.038 ↑ 1.0 1 1

Index Only Scan using pk_ap_payment_types on ap_payment_types apt (cost=0.14..0.15 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=1)

  • Index Cond: (id = ar.ap_payment_type_id)
  • Heap Fetches: 1
30. 1.055 1.055 ↑ 1.0 1 1

Index Scan using idx_gl_trees_cid_is_system on gl_trees gt (cost=0.14..2.17 rows=1 width=4) (actual time=1.054..1.055 rows=1 loops=1)

  • Index Cond: (cid = 4,632)
  • Filter: ((system_code)::text = 'DEFAULT'::text)
31. 0.015 0.015 ↑ 1.0 1 1

Index Scan using idx_properties_id on properties p (cost=0.28..0.30 rows=1 width=26) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (id = ad.property_id)
  • Filter: (cid = 4,632)
32. 0.368 0.368 ↑ 1.0 1 1

Index Scan using idx_bank_accounts on bank_accounts ba (cost=0.28..0.30 rows=1 width=32) (actual time=0.367..0.368 rows=1 loops=1)

  • Index Cond: (id = ad.bank_account_id)
  • Filter: (cid = 4,632)
33. 1.347 1.347 ↑ 1.0 1 1

Index Scan using idx_ap_payees_id on ap_payees ap (cost=0.42..2.44 rows=1 width=32) (actual time=1.347..1.347 rows=1 loops=1)

  • Index Cond: (ah.ap_payee_id = id)
  • Filter: ((cid = 4,632) AND (ah.cid = cid))
34. 0.010 0.010 ↓ 0.0 0 1

Index Scan using idx_lease_customers_id on lease_customers lc (cost=0.43..2.45 rows=1 width=12) (actual time=0.009..0.010 rows=0 loops=1)

  • Index Cond: (ah.lease_customer_id = id)
  • Filter: ((cid = 4,632) AND (ah.cid = cid))
35. 0.007 0.007 ↓ 0.0 0 1

Index Scan using idx_customers_id on customers cu (cost=0.43..1.33 rows=1 width=15) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (lc.customer_id = id)
  • Filter: ((cid = 4,632) AND (lc.cid = cid))
36. 0.379 0.379 ↑ 1.0 1 1

Index Scan using idx_ap_payee_locations_id on ap_payee_locations apl (cost=0.42..2.44 rows=1 width=58) (actual time=0.378..0.379 rows=1 loops=1)

  • Index Cond: (ah.ap_payee_location_id = id)
  • Filter: (cid = 4,632)
37. 0.002 1.067 ↓ 0.0 0 1

Unique (cost=0.70..5.47 rows=1 width=4) (actual time=1.067..1.067 rows=0 loops=1)

38. 0.002 1.065 ↓ 0.0 0 1

Nested Loop (cost=0.70..5.47 rows=1 width=4) (actual time=1.065..1.065 rows=0 loops=1)

39. 1.063 1.063 ↓ 0.0 0 1

Index Scan using idx_rule_stop_results_reference_id on rule_stop_results rsr (cost=0.43..3.17 rows=1 width=8) (actual time=1.063..1.063 rows=0 loops=1)

  • Index Cond: (reference_id = ah.id)
  • Filter: ((NOT is_archived) AND (cid = 4,632) AND (route_type_id = 5) AND (rule_stop_status_type_id = 1))
40. 0.000 0.000 ↓ 0.0 0

Index Scan using uk_cid_route_type_id on approval_preferences apr (cost=0.28..2.29 rows=1 width=4) (never executed)

  • Index Cond: ((cid = 4,632) AND (route_type_id = 5))
  • Filter: is_enabled
41.          

SubPlan (for WindowAgg)

42. 1.909 1.909 ↑ 1.0 1 1

Index Scan using idx_gl_account_trees_gl_account_id on gl_account_trees gat (cost=0.42..9.05 rows=1 width=32) (actual time=1.908..1.909 rows=1 loops=1)

  • Index Cond: (gl_account_id = ad.gl_account_id)
  • Filter: ((cid = ad.cid) AND (is_default = 1))