explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PwLQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 615.599 ↑ 1.0 1 1

Unique (cost=816,143.73..816,143.80 rows=1 width=343) (actual time=615.597..615.599 rows=1 loops=1)

2. 0.092 615.586 ↑ 1.0 1 1

Sort (cost=816,143.73..816,143.74 rows=1 width=343) (actual time=615.585..615.586 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.028 615.494 ↑ 1.0 1 1

WindowAgg (cost=816,134.15..816,143.72 rows=1 width=343) (actual time=615.492..615.494 rows=1 loops=1)

4. 0.017 615.305 ↑ 1.0 1 1

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

  • Sort Key: ad.bank_account_id, ah.id
  • Sort Method: quicksort Memory: 25kB
5. 0.006 615.288 ↑ 1.0 1 1

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

  • Filter: (rsr.reference_id IS NULL)
6. 0.005 615.271 ↑ 1.0 1 1

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

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

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

8. 0.005 615.236 ↑ 1.0 1 1

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

9. 0.009 615.229 ↑ 1.0 1 1

Nested Loop Left Join (cost=18,332.35..816,122.40 rows=1 width=187) (actual time=600.173..615.229 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.004 615.210 ↑ 1.0 1 1

Nested Loop (cost=18,331.93..816,119.94 rows=1 width=163) (actual time=600.156..615.210 rows=1 loops=1)

11. 0.005 615.198 ↑ 1.0 1 1

Nested Loop (cost=18,331.65..816,119.64 rows=1 width=139) (actual time=600.145..615.198 rows=1 loops=1)

12. 0.005 615.183 ↑ 1.0 1 1

Nested Loop (cost=18,331.37..816,119.34 rows=1 width=121) (actual time=600.131..615.183 rows=1 loops=1)

13. 0.005 615.168 ↑ 1.0 1 1

Nested Loop (cost=18,331.23..816,117.17 rows=1 width=121) (actual time=600.120..615.168 rows=1 loops=1)

14. 4.814 615.133 ↑ 1.0 1 1

Nested Loop (cost=18,331.09..816,117.01 rows=1 width=125) (actual time=600.086..615.133 rows=1 loops=1)

  • Join Filter: (ah.ap_remittance_id = ar.id)
  • Rows Removed by Join Filter: 2,955
15. 27.987 27.987 ↑ 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=0.096..27.987 rows=2,956 loops=1)

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

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

17. 0.007 581.277 ↑ 1.0 1 1

Nested Loop (cost=18,330.67..810,630.49 rows=1 width=125) (actual time=578.951..581.277 rows=1 loops=1)

18. 471.065 581.253 ↑ 8.0 1 1

Bitmap Heap Scan on ap_headers ah (cost=18,330.24..810,610.71 rows=8 width=72) (actual time=578.930..581.253 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.652 110.146 ↓ 0.0 0 1

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

20. 41.878 41.878 ↓ 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=41.878..41.878 rows=465,901 loops=1)

  • Index Cond: (cid = 4,632)
21. 66.616 66.616 ↓ 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=66.616..66.616 rows=1,349,717 loops=1)

  • Index Cond: (is_posted = true)
22.          

SubPlan (for Bitmap Heap Scan)

23. 0.001 0.042 ↓ 0.0 0 1

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

24. 0.003 0.041 ↓ 0.0 0 1

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

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

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

26. 0.023 0.023 ↑ 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=0.022..0.023 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.012 0.012 ↓ 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.011..0.012 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.017 0.017 ↑ 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.016..0.017 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.030 0.030 ↑ 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.030..0.030 rows=1 loops=1)

  • Index Cond: (id = ar.ap_payment_type_id)
  • Heap Fetches: 1
30. 0.010 0.010 ↑ 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=0.009..0.010 rows=1 loops=1)

  • Index Cond: (cid = 4,632)
  • Filter: ((system_code)::text = 'DEFAULT'::text)
31. 0.010 0.010 ↑ 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.010..0.010 rows=1 loops=1)

  • Index Cond: (id = ad.property_id)
  • Filter: (cid = 4,632)
32. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

  • Index Cond: (id = ad.bank_account_id)
  • Filter: (cid = 4,632)
33. 0.010 0.010 ↑ 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=0.010..0.010 rows=1 loops=1)

  • Index Cond: (ah.ap_payee_id = id)
  • Filter: ((cid = 4,632) AND (ah.cid = cid))
34. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Index Cond: (ah.lease_customer_id = id)
  • Filter: ((cid = 4,632) AND (ah.cid = cid))
35. 0.015 0.015 ↓ 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.014..0.015 rows=0 loops=1)

  • Index Cond: (lc.customer_id = id)
  • Filter: ((cid = 4,632) AND (lc.cid = cid))
36. 0.011 0.011 ↑ 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.011..0.011 rows=1 loops=1)

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

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

38. 0.002 0.010 ↓ 0.0 0 1

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

39. 0.008 0.008 ↓ 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=0.008..0.008 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. 0.161 0.161 ↑ 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=0.160..0.161 rows=1 loops=1)

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