explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nnpy

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 281,287.854 ↓ 0.0 0 1

Unique (cost=38,885.08..38,885.15 rows=1 width=343) (actual time=281,287.854..281,287.854 rows=0 loops=1)

2. 0.037 281,287.853 ↓ 0.0 0 1

Sort (cost=38,885.08..38,885.09 rows=1 width=343) (actual time=281,287.853..281,287.853 rows=0 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, (util_get_translated('name'::text, (gat.name)::text, gat.details, 'en_US'::text, false)), 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.004 281,287.816 ↓ 0.0 0 1

WindowAgg (cost=38,884.29..38,885.07 rows=1 width=343) (actual time=281,287.815..281,287.816 rows=0 loops=1)

4. 0.010 281,287.812 ↓ 0.0 0 1

Sort (cost=38,884.29..38,884.30 rows=1 width=456) (actual time=281,287.812..281,287.812 rows=0 loops=1)

  • Sort Key: ad.bank_account_id, ah.id
  • Sort Method: quicksort Memory: 25kB
5. 0.001 281,287.802 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,060.10..38,884.28 rows=1 width=456) (actual time=281,287.802..281,287.802 rows=0 loops=1)

  • Filter: (rsr.reference_id IS NULL)
6. 0.001 281,287.801 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,059.40..38,878.77 rows=1 width=456) (actual time=281,287.801..281,287.801 rows=0 loops=1)

  • Join Filter: (ad.cid = apl.cid)
7. 0.001 281,287.800 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,058.98..38,876.32 rows=1 width=414) (actual time=281,287.800..281,287.800 rows=0 loops=1)

8. 0.002 281,287.799 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,058.55..38,875.01 rows=1 width=415) (actual time=281,287.798..281,287.799 rows=0 loops=1)

9. 0.001 281,287.797 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,058.12..38,872.55 rows=1 width=411) (actual time=281,287.797..281,287.797 rows=0 loops=1)

  • Filter: ((ap.ap_payee_status_type_id = ANY ('{1,3}'::integer[])) OR (ah.lease_customer_id IS NOT NULL))
10. 31,597.167 281,287.796 ↓ 0.0 0 1

Nested Loop (cost=2,057.70..38,870.10 rows=1 width=387) (actual time=281,287.796..281,287.796 rows=0 loops=1)

  • Join Filter: (ar.id = ah.ap_remittance_id)
11. 51,209.545 76,051.037 ↓ 17,185.2 57,879,864 1

Merge Join (cost=2,057.27..27,708.59 rows=3,368 width=347) (actual time=1,292.333..76,051.037 rows=57,879,864 loops=1)

  • Merge Cond: (apt.id = ar.ap_payment_type_id)
12. 105.008 1,409.749 ↓ 7,002.6 98,036 1

Nested Loop (cost=1.69..51,206.15 rows=14 width=343) (actual time=0.728..1,409.749 rows=98,036 loops=1)

13. 0.035 0.035 ↑ 2.2 6 1

Index Only Scan using pk_ap_payment_types on ap_payment_types apt (cost=0.14..3.33 rows=13 width=4) (actual time=0.017..0.035 rows=6 loops=1)

  • Heap Fetches: 6
14. 65.442 1,304.706 ↓ 16,339.0 16,339 6

Materialize (cost=1.55..51,202.66 rows=1 width=339) (actual time=0.119..217.451 rows=16,339 loops=6)

15. 29.501 1,239.264 ↓ 19,607.0 19,607 1

Nested Loop (cost=1.55..51,202.66 rows=1 width=339) (actual time=0.704..1,239.264 rows=19,607 loops=1)

16. 26.226 1,170.549 ↓ 19,607.0 19,607 1

Nested Loop (cost=1.41..51,200.48 rows=1 width=335) (actual time=0.693..1,170.549 rows=19,607 loops=1)

17. 25.860 1,105.109 ↓ 817.0 19,607 1

Nested Loop (cost=1.13..51,193.34 rows=24 width=311) (actual time=0.681..1,105.109 rows=19,607 loops=1)

18. 16.479 1,040.035 ↓ 20.9 19,607 1

Nested Loop (cost=0.85..50,912.07 rows=937 width=293) (actual time=0.666..1,040.035 rows=19,607 loops=1)

19. 0.571 0.571 ↓ 19.1 381 1

Index Scan using uk_gl_account_trees_gl_account_id on gl_account_trees gat (cost=0.42..89.94 rows=20 width=232) (actual time=0.014..0.571 rows=381 loops=1)

  • Index Cond: (cid = 4,632)
  • Filter: (is_default = 1)
20. 1,022.985 1,022.985 ↑ 8.9 51 381

Index Scan using idx_ap_details_gl_account_id on ap_details ad (cost=0.43..2,536.56 rows=455 width=69) (actual time=0.356..2.685 rows=51 loops=381)

  • Index Cond: (gl_account_id = gat.gl_account_id)
  • Filter: ((reversal_ap_detail_id IS NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL) AND (cid = 4,632) AND ((transaction_amount - COALESCE(pre_approval_amount, '0'::numeric)) <> '0'::numeric))
  • Rows Removed by Filter: 2,278
21. 39.214 39.214 ↑ 1.0 1 19,607

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

  • Index Cond: (id = ad.property_id)
  • Filter: (cid = 4,632)
22. 39.214 39.214 ↑ 1.0 1 19,607

Index Scan using idx_bank_accounts on bank_accounts ba (cost=0.28..0.30 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=19,607)

  • Index Cond: (id = ad.bank_account_id)
  • Filter: (cid = 4,632)
  • Rows Removed by Filter: 0
23. 39.214 39.214 ↑ 1.0 1 19,607

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.001..0.002 rows=1 loops=19,607)

  • Index Cond: (cid = 4,632)
  • Filter: ((system_code)::text = 'DEFAULT'::text)
24. 23,429.606 23,431.743 ↓ 19,051.8 57,860,258 1

Sort (cost=2,055.58..2,063.18 rows=3,037 width=12) (actual time=3.620..23,431.743 rows=57,860,258 loops=1)

  • Sort Key: ar.ap_payment_type_id
  • Sort Method: quicksort Memory: 235kB
25. 2.137 2.137 ↑ 1.0 2,952 1

Index Scan using pk_ap_remittances on ap_remittances ar (cost=0.42..1,879.92 rows=3,037 width=12) (actual time=0.017..2.137 rows=2,952 loops=1)

  • Index Cond: (cid = 4,632)
26. 173,639.592 173,639.592 ↓ 0.0 0 57,879,864

Index Scan using idx_ap_headers_id on ap_headers ah (cost=0.43..3.30 rows=1 width=72) (actual time=0.003..0.003 rows=0 loops=57,879,864)

  • Index Cond: (id = ad.ap_header_id)
  • 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 ~~* '%test%'::text) AND (cid = 4,632) AND (ad.gl_transaction_type_id = gl_transaction_type_id) AND (ad.post_month = post_month) AND (ap_header_sub_type_id = ANY ('{5,6,7,8,17,12}'::integer[])) AND (NOT (SubPlan 1)))
  • Rows Removed by Filter: 1
27.          

SubPlan (for Index Scan)

28. 0.000 0.000 ↓ 0.0 0

Unique (cost=0.71..4.77 rows=1 width=4) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Result (cost=0.71..4.77 rows=1 width=4) (never executed)

  • One-Time Filter: (ah.cid = 4,632)
30. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..4.77 rows=1 width=4) (never executed)

31. 0.000 0.000 ↓ 0.0 0

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) (never executed)

  • 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))
32. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_properties_id on properties p_1 (cost=0.28..2.30 rows=1 width=8) (never executed)

  • Index Cond: (id = ad_1.property_id)
  • Filter: ((cid = 4,632) AND (is_disabled = 1))
33. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ap_payees_id on ap_payees ap (cost=0.42..2.44 rows=1 width=32) (never executed)

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

Index Scan using idx_lease_customers_id on lease_customers lc (cost=0.43..2.45 rows=1 width=12) (never executed)

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

Index Scan using idx_customers_id on customers cu (cost=0.43..1.29 rows=1 width=15) (never executed)

  • Index Cond: (lc.customer_id = id)
  • Filter: ((cid = 4,632) AND (lc.cid = cid))
36. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ap_payee_locations_id on ap_payee_locations apl (cost=0.42..2.44 rows=1 width=58) (never executed)

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

Unique (cost=0.70..5.49 rows=1 width=4) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.70..5.49 rows=1 width=4) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_rule_stop_results_reference_id on rule_stop_results rsr (cost=0.43..3.19 rows=1 width=8) (never executed)

  • 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