explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I0fl

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 6,614.012 ↓ 0.0 0 1

Unique (cost=128,361.36..128,361.38 rows=2 width=1,362) (actual time=6,614.012..6,614.012 rows=0 loops=1)

2. 0.037 6,614.011 ↓ 0.0 0 1

Sort (cost=128,361.36..128,361.37 rows=2 width=1,362) (actual time=6,614.010..6,614.011 rows=0 loops=1)

  • Sort Key: sub_query.ap_payment_id, sub_query.ap_header_id, sub_query.gl_transaction_type_id, sub_query.gl_header_id DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.002 6,613.974 ↓ 0.0 0 1

Subquery Scan on sub_query (cost=128,361.23..128,361.35 rows=2 width=1,362) (actual time=6,613.973..6,613.974 rows=0 loops=1)

4. 0.001 6,613.972 ↓ 0.0 0 1

Unique (cost=128,361.23..128,361.33 rows=2 width=1,358) (actual time=6,613.972..6,613.972 rows=0 loops=1)

5. 0.049 6,613.971 ↓ 0.0 0 1

Sort (cost=128,361.23..128,361.23 rows=2 width=1,358) (actual time=6,613.971..6,613.971 rows=0 loops=1)

  • Sort Key: ap.id, ap.payment_number, ap.payment_number, (func_format_refund_customer_names(ap.payee_name, false)), ap.ap_payment_type_id, ah.id, gh.post_date, gh.post_month, (sum(gd.amount) OVER (?)), gd.gl_header_id, gd.property_id, gd.accrual_gl_account_id, gd.cash_gl_account_id, gh.gl_transaction_type_id, gtt.description, gd.gl_reconciliation_id, gh.memo, gd.memo, ap.bank_account_id, ah.ap_payee_id
  • Sort Method: quicksort Memory: 25kB
6. 0.003 6,613.922 ↓ 0.0 0 1

Append (cost=64,176.50..128,361.22 rows=2 width=1,358) (actual time=6,613.921..6,613.922 rows=0 loops=1)

7. 0.001 5,531.900 ↓ 0.0 0 1

Unique (cost=64,176.50..64,176.77 rows=1 width=221) (actual time=5,531.900..5,531.900 rows=0 loops=1)

8. 0.003 5,531.899 ↓ 0.0 0 1

WindowAgg (cost=64,176.50..64,176.77 rows=1 width=221) (actual time=5,531.899..5,531.899 rows=0 loops=1)

9. 0.006 5,531.896 ↓ 0.0 0 1

Sort (cost=64,176.50..64,176.50 rows=1 width=176) (actual time=5,531.895..5,531.896 rows=0 loops=1)

  • Sort Key: ap.id, gh.gl_transaction_type_id
  • Sort Method: quicksort Memory: 25kB
10. 0.002 5,531.890 ↓ 0.0 0 1

Nested Loop (cost=9.71..64,176.49 rows=1 width=176) (actual time=5,531.889..5,531.890 rows=0 loops=1)

  • Join Filter: (aa.gl_transaction_type_id = gtt.id)
11. 4.727 5,531.888 ↓ 0.0 0 1

Nested Loop (cost=9.71..64,174.88 rows=1 width=164) (actual time=5,531.888..5,531.888 rows=0 loops=1)

  • Join Filter: (aa.gl_transaction_type_id = gd.gl_transaction_type_id)
12. 6.301 5,191.047 ↓ 2,367.0 2,367 1

Nested Loop (cost=9.15..63,323.80 rows=1 width=94) (actual time=654.034..5,191.047 rows=2,367 loops=1)

13. 9.491 3,379.446 ↓ 2,579.0 2,579 1

Nested Loop (cost=8.58..63,316.12 rows=1 width=56) (actual time=650.178..3,379.446 rows=2,579 loops=1)

14. 11.180 3,108.637 ↓ 898.0 2,694 1

Nested Loop (cost=1.09..63,269.85 rows=3 width=53) (actual time=646.564..3,108.637 rows=2,694 loops=1)

15. 12.364 2,985.338 ↓ 536.5 5,901 1

Nested Loop (cost=0.67..63,192.02 rows=11 width=25) (actual time=530.621..2,985.338 rows=5,901 loops=1)

16. 274.110 2,211.745 ↓ 4.7 5,901 1

Nested Loop (cost=0.25..54,289.92 rows=1,254 width=21) (actual time=529.105..2,211.745 rows=5,901 loops=1)

  • Join Filter: ((ad.property_id = lp.property_id) OR ((3 = ad.reimbursement_method_id) AND (ad.inter_co_property_id = lp.property_id)))
  • Rows Removed by Join Filter: 550767
17. 109.845 109.845 ↑ 1.0 1 1

Function Scan on load_properties lp (cost=0.25..0.88 rows=1 width=8) (actual time=109.844..109.845 rows=1 loops=1)

  • Filter: (cid = 3395)
18. 1,827.790 1,827.790 ↑ 1.0 556,668 1

Seq Scan on ap_details ad (cost=0.00..44,547.35 rows=556,668 width=33) (actual time=3.540..1,827.790 rows=556,668 loops=1)

  • Filter: (cid = 3395)
19. 761.229 761.229 ↑ 1.0 1 5,901

Index Scan using idx_ap_headers on ap_headers ah (cost=0.42..7.09 rows=1 width=24) (actual time=0.129..0.129 rows=1 loops=5,901)

  • Index Cond: (id = ad.ap_header_id)
  • Filter: ((cid = 3395) AND (ad.gl_transaction_type_id = gl_transaction_type_id) AND (ad.post_month = post_month))
20. 112.119 112.119 ↓ 0.0 0 5,901

Index Scan using idx_ap_payments_id on ap_payments ap (cost=0.42..7.08 rows=1 width=40) (actual time=0.019..0.019 rows=0 loops=5,901)

  • Index Cond: (id = ah.ap_payment_id)
  • Filter: (cid = 3395)
21. 129.312 261.318 ↑ 1.0 1 2,694

Bitmap Heap Scan on ap_allocations aa (cost=7.49..15.41 rows=1 width=20) (actual time=0.096..0.097 rows=1 loops=2,694)

  • Recheck Cond: ((ad.id = credit_ap_detail_id) OR (ad.id = charge_ap_detail_id))
  • Filter: ((NOT is_deleted) AND (cid = 3395) AND ((ad.id = credit_ap_detail_id) OR ((ad.id = charge_ap_detail_id) AND ad.is_cross_allocation)))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=2794
22. 5.388 132.006 ↓ 0.0 0 2,694

BitmapOr (cost=7.49..7.49 rows=2 width=0) (actual time=0.049..0.049 rows=0 loops=2,694)

23. 59.268 59.268 ↑ 1.0 1 2,694

Bitmap Index Scan on idx_ap_allocations_credit_ap_detail_id (cost=0.00..3.75 rows=1 width=0) (actual time=0.022..0.022 rows=1 loops=2,694)

  • Index Cond: (ad.id = credit_ap_detail_id)
24. 67.350 67.350 ↓ 0.0 0 2,694

Bitmap Index Scan on idx_ap_allocations_charge_ap_detail_id (cost=0.00..3.75 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=2,694)

  • Index Cond: (ad.id = charge_ap_detail_id)
25. 1,805.300 1,805.300 ↑ 1.0 1 2,579

Index Scan using idx_gl_headers_cid_reference_id on gl_headers gh (cost=0.56..7.67 rows=1 width=50) (actual time=0.682..0.700 rows=1 loops=2,579)

  • Index Cond: ((cid = 3395) AND (reference_id = aa.id))
  • Filter: ((gl_transaction_type_id = ANY ('{4,5}'::integer[])) AND (post_month <= '2019-06-30'::date) AND (aa.gl_transaction_type_id = gl_transaction_type_id) AND (gl_header_status_type_id <> ALL ('{2,4,5,6}'::integer[])))
  • Rows Removed by Filter: 1
26. 336.114 336.114 ↓ 0.0 0 2,367

Index Scan using idx_gl_details_gl_header_id on gl_details gd (cost=0.57..850.71 rows=30 width=82) (actual time=0.142..0.142 rows=0 loops=2,367)

  • Index Cond: (gl_header_id = gh.id)
  • Filter: ((gl_reconciliation_id IS NULL) AND (cid = 3395) AND (gh.gl_transaction_type_id = gl_transaction_type_id) AND ((accrual_gl_account_id = 163455) OR (cash_gl_account_id = 163455)))
  • Rows Removed by Filter: 2
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on gl_transaction_types gtt (cost=0.00..1.27 rows=27 width=24) (never executed)

28. 0.001 1,082.019 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 2" (cost=64,184.14..64,184.43 rows=1 width=221) (actual time=1,082.019..1,082.019 rows=0 loops=1)

29. 0.002 1,082.018 ↓ 0.0 0 1

Unique (cost=64,184.14..64,184.42 rows=1 width=233) (actual time=1,082.017..1,082.018 rows=0 loops=1)

30. 0.002 1,082.016 ↓ 0.0 0 1

WindowAgg (cost=64,184.14..64,184.42 rows=1 width=233) (actual time=1,082.016..1,082.016 rows=0 loops=1)

31. 0.014 1,082.014 ↓ 0.0 0 1

Sort (cost=64,184.14..64,184.14 rows=1 width=188) (actual time=1,082.013..1,082.014 rows=0 loops=1)

  • Sort Key: ap_1.id, gh_1.gl_transaction_type_id, ah_1.header_number, ad_1.ap_header_id
  • Sort Method: quicksort Memory: 25kB
32. 0.002 1,082.000 ↓ 0.0 0 1

Nested Loop (cost=9.71..64,184.13 rows=1 width=188) (actual time=1,082.000..1,082.000 rows=0 loops=1)

  • Join Filter: (aa_1.gl_transaction_type_id = gtt_1.id)
33. 3.902 1,081.998 ↓ 0.0 0 1

Nested Loop (cost=9.71..64,182.52 rows=1 width=176) (actual time=1,081.998..1,081.998 rows=0 loops=1)

  • Join Filter: (aa_1.gl_transaction_type_id = gd_1.gl_transaction_type_id)
34. 4.309 1,041.738 ↓ 2,597.0 2,597 1

Nested Loop (cost=9.15..63,331.44 rows=1 width=106) (actual time=12.847..1,041.738 rows=2,597 loops=1)

35. 5.227 829.563 ↓ 1,404.5 2,809 1

Nested Loop (cost=8.58..63,316.12 rows=2 width=68) (actual time=12.823..829.563 rows=2,809 loops=1)

36. 9.161 802.784 ↓ 898.0 2,694 1

Nested Loop (cost=1.09..63,269.85 rows=3 width=65) (actual time=12.800..802.784 rows=2,694 loops=1)

37. 9.095 787.722 ↓ 536.5 5,901 1

Nested Loop (cost=0.67..63,192.02 rows=11 width=37) (actual time=10.323..787.722 rows=5,901 loops=1)

38. 221.996 760.924 ↓ 4.7 5,901 1

Nested Loop (cost=0.25..54,289.92 rows=1,254 width=21) (actual time=10.308..760.924 rows=5,901 loops=1)

  • Join Filter: ((ad_1.property_id = lp_1.property_id) OR ((3 = ad_1.reimbursement_method_id) AND (ad_1.inter_co_property_id = lp_1.property_id)))
  • Rows Removed by Join Filter: 550767
39. 1.679 1.679 ↑ 1.0 1 1

Function Scan on load_properties lp_1 (cost=0.25..0.88 rows=1 width=8) (actual time=1.678..1.679 rows=1 loops=1)

  • Filter: (cid = 3395)
40. 537.249 537.249 ↑ 1.0 556,668 1

Seq Scan on ap_details ad_1 (cost=0.00..44,547.35 rows=556,668 width=33) (actual time=0.043..537.249 rows=556,668 loops=1)

  • Filter: (cid = 3395)
41. 17.703 17.703 ↑ 1.0 1 5,901

Index Scan using idx_ap_headers on ap_headers ah_1 (cost=0.42..7.09 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=5,901)

  • Index Cond: (id = ad_1.ap_header_id)
  • Filter: ((cid = 3395) AND (ad_1.gl_transaction_type_id = gl_transaction_type_id) AND (ad_1.post_month = post_month))
42. 5.901 5.901 ↓ 0.0 0 5,901

Index Scan using idx_ap_payments_id on ap_payments ap_1 (cost=0.42..7.08 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=5,901)

  • Index Cond: (id = ah_1.ap_payment_id)
  • Filter: (cid = 3395)
43. 8.082 21.552 ↑ 1.0 1 2,694

Bitmap Heap Scan on ap_allocations aa_1 (cost=7.49..15.41 rows=1 width=20) (actual time=0.007..0.008 rows=1 loops=2,694)

  • Recheck Cond: ((ad_1.id = credit_ap_detail_id) OR (ad_1.id = charge_ap_detail_id))
  • Filter: ((cid = 3395) AND ((ad_1.id = credit_ap_detail_id) OR ((ad_1.id = charge_ap_detail_id) AND ad_1.is_cross_allocation)))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=2794
44. 2.694 13.470 ↓ 0.0 0 2,694

BitmapOr (cost=7.49..7.49 rows=2 width=0) (actual time=0.005..0.005 rows=0 loops=2,694)

45. 5.388 5.388 ↑ 1.0 1 2,694

Bitmap Index Scan on idx_ap_allocations_credit_ap_detail_id (cost=0.00..3.75 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=2,694)

  • Index Cond: (ad_1.id = credit_ap_detail_id)
46. 5.388 5.388 ↓ 0.0 0 2,694

Bitmap Index Scan on idx_ap_allocations_charge_ap_detail_id (cost=0.00..3.75 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=2,694)

  • Index Cond: (ad_1.id = charge_ap_detail_id)
47. 207.866 207.866 ↑ 1.0 1 2,809

Index Scan using idx_gl_headers_cid_reference_id on gl_headers gh_1 (cost=0.56..7.65 rows=1 width=50) (actual time=0.072..0.074 rows=1 loops=2,809)

  • Index Cond: ((cid = 3395) AND (reference_id = aa_1.id))
  • Filter: ((gl_transaction_type_id = ANY ('{4,5}'::integer[])) AND (post_month <= '2019-06-30'::date) AND (aa_1.gl_transaction_type_id = gl_transaction_type_id) AND (gl_header_status_type_id <> ALL ('{2,4,5,6}'::integer[])))
  • Rows Removed by Filter: 1
48. 36.358 36.358 ↓ 0.0 0 2,597

Index Scan using idx_gl_details_gl_header_id on gl_details gd_1 (cost=0.57..850.71 rows=30 width=82) (actual time=0.014..0.014 rows=0 loops=2,597)

  • Index Cond: (gl_header_id = gh_1.id)
  • Filter: ((gl_reconciliation_id IS NULL) AND (cid = 3395) AND (gh_1.gl_transaction_type_id = gl_transaction_type_id) AND ((accrual_gl_account_id = 163455) OR (cash_gl_account_id = 163455)))
  • Rows Removed by Filter: 2
49. 0.000 0.000 ↓ 0.0 0

Seq Scan on gl_transaction_types gtt_1 (cost=0.00..1.27 rows=27 width=24) (never executed)

Planning time : 353.315 ms