explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Fee

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3,656.831 ↑ 2.0 1 1

Unique (cost=138,899.85..138,899.87 rows=2 width=1,362) (actual time=3,656.829..3,656.831 rows=1 loops=1)

2. 0.012 3,656.829 ↑ 2.0 1 1

Sort (cost=138,899.85..138,899.85 rows=2 width=1,362) (actual time=3,656.828..3,656.829 rows=1 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.003 3,656.817 ↑ 2.0 1 1

Subquery Scan on sub_query (cost=138,899.71..138,899.84 rows=2 width=1,362) (actual time=3,656.811..3,656.817 rows=1 loops=1)

4. 0.005 3,656.814 ↑ 2.0 1 1

Unique (cost=138,899.71..138,899.82 rows=2 width=1,358) (actual time=3,656.809..3,656.814 rows=1 loops=1)

5. 0.026 3,656.809 ↑ 1.0 2 1

Sort (cost=138,899.71..138,899.72 rows=2 width=1,358) (actual time=3,656.807..3,656.809 rows=2 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.004 3,656.783 ↑ 1.0 2 1

Append (cost=69,445.77..138,899.70 rows=2 width=1,358) (actual time=1,784.031..3,656.783 rows=2 loops=1)

7. 0.004 1,784.033 ↑ 1.0 1 1

Unique (cost=69,445.77..69,446.05 rows=1 width=192) (actual time=1,784.030..1,784.033 rows=1 loops=1)

8. 0.255 1,784.029 ↑ 1.0 1 1

WindowAgg (cost=69,445.77..69,446.04 rows=1 width=192) (actual time=1,784.027..1,784.029 rows=1 loops=1)

9. 0.009 1,783.774 ↑ 1.0 1 1

Sort (cost=69,445.77..69,445.77 rows=1 width=147) (actual time=1,783.773..1,783.774 rows=1 loops=1)

  • Sort Key: ap.id, gh.gl_transaction_type_id
  • Sort Method: quicksort Memory: 25kB
10. 0.013 1,783.765 ↑ 1.0 1 1

Nested Loop (cost=20,866.24..69,445.76 rows=1 width=147) (actual time=1,752.463..1,783.765 rows=1 loops=1)

  • Join Filter: (aa.gl_transaction_type_id = gtt.id)
  • Rows Removed by Join Filter: 21
11. 0.006 1,783.738 ↑ 1.0 1 1

Nested Loop (cost=20,866.24..69,444.15 rows=1 width=135) (actual time=1,752.437..1,783.738 rows=1 loops=1)

  • Join Filter: (aa.gl_transaction_type_id = gd.gl_transaction_type_id)
12. 0.011 1,783.708 ↓ 3.0 3 1

Nested Loop (cost=20,865.67..68,476.68 rows=1 width=92) (actual time=1,752.421..1,783.708 rows=3 loops=1)

13. 0.030 1,783.625 ↓ 1.8 9 1

Nested Loop (cost=20,865.10..68,438.31 rows=5 width=54) (actual time=1,745.009..1,783.625 rows=9 loops=1)

14. 0.045 1,783.496 ↑ 1.2 9 1

Nested Loop (cost=20,857.63..68,268.83 rows=11 width=51) (actual time=1,744.978..1,783.496 rows=9 loops=1)

15. 215.557 1,783.401 ↑ 1.2 25 1

Nested Loop (cost=20,857.21..68,049.98 rows=31 width=25) (actual time=1,103.001..1,783.401 rows=25 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: 546631
16. 1.447 1.447 ↑ 1.0 1 1

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

  • Filter: (cid = 3395)
17. 576.650 1,566.397 ↓ 86.0 546,656 1

Hash Join (cost=20,856.96..67,937.93 rows=6,353 width=37) (actual time=444.451..1,566.397 rows=546,656 loops=1)

  • Hash Cond: ((ad.ap_header_id = ah.id) AND (ad.gl_transaction_type_id = ah.gl_transaction_type_id) AND (ad.post_month = ah.post_month))
18. 545.901 545.901 ↓ 1.1 546,656 1

Seq Scan on ap_details ad (cost=0.00..43,143.29 rows=500,023 width=33) (actual time=0.007..545.901 rows=546,656 loops=1)

  • Filter: (cid = 3395)
19. 200.031 443.846 ↓ 1.0 349,043 1

Hash (cost=14,750.65..14,750.65 rows=348,932 width=24) (actual time=443.846..443.846 rows=349,043 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 22280kB
20. 243.815 243.815 ↓ 1.0 349,043 1

Seq Scan on ap_headers ah (cost=0.00..14,750.65 rows=348,932 width=24) (actual time=0.005..243.815 rows=349,043 loops=1)

  • Filter: (cid = 3395)
21. 0.050 0.050 ↓ 0.0 0 25

Index Scan using idx_ap_payments_id on ap_payments ap (cost=0.42..7.06 rows=1 width=38) (actual time=0.002..0.002 rows=0 loops=25)

  • Index Cond: (id = ah.ap_payment_id)
  • Filter: (cid = 3395)
22. 0.036 0.099 ↑ 1.0 1 9

Bitmap Heap Scan on ap_allocations aa (cost=7.48..15.40 rows=1 width=20) (actual time=0.010..0.011 rows=1 loops=9)

  • 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)))
  • Heap Blocks: exact=9
23. 0.009 0.063 ↓ 0.0 0 9

BitmapOr (cost=7.48..7.48 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=9)

24. 0.027 0.027 ↑ 1.0 1 9

Bitmap Index Scan on idx_ap_allocations_credit_ap_detail_id (cost=0.00..3.74 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=9)

  • Index Cond: (ad.id = credit_ap_detail_id)
25. 0.027 0.027 ↓ 0.0 0 9

Bitmap Index Scan on idx_ap_allocations_charge_ap_detail_id (cost=0.00..3.74 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=9)

  • Index Cond: (ad.id = charge_ap_detail_id)
26. 0.072 0.072 ↓ 0.0 0 9

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.007..0.008 rows=0 loops=9)

  • Index Cond: ((cid = 3395) AND (reference_id = aa.id))
  • Filter: ((gl_transaction_type_id = ANY ('{4,5}'::integer[])) AND (post_date >= '2019-06-01'::date) AND (post_date <= '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: 2
27. 0.024 0.024 ↓ 0.0 0 3

Index Scan using idx_gl_details_gl_header_id on gl_details gd (cost=0.57..966.76 rows=57 width=55) (actual time=0.007..0.008 rows=0 loops=3)

  • 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 = 163451) OR (cash_gl_account_id = 163451)))
  • Rows Removed by Filter: 2
28. 0.014 0.014 ↑ 1.2 22 1

Seq Scan on gl_transaction_types gtt (cost=0.00..1.27 rows=27 width=24) (actual time=0.006..0.014 rows=22 loops=1)

29. 0.003 1,872.746 ↑ 1.0 1 1

Subquery Scan on "*SELECT* 2" (cost=69,453.35..69,453.64 rows=1 width=192) (actual time=1,872.743..1,872.746 rows=1 loops=1)

30. 0.005 1,872.743 ↑ 1.0 1 1

Unique (cost=69,453.35..69,453.63 rows=1 width=205) (actual time=1,872.740..1,872.743 rows=1 loops=1)

31. 0.327 1,872.738 ↑ 1.0 1 1

WindowAgg (cost=69,453.35..69,453.63 rows=1 width=205) (actual time=1,872.736..1,872.738 rows=1 loops=1)

32. 0.021 1,872.411 ↑ 1.0 1 1

Sort (cost=69,453.35..69,453.36 rows=1 width=160) (actual time=1,872.410..1,872.411 rows=1 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
33. 0.013 1,872.390 ↑ 1.0 1 1

Nested Loop (cost=20,866.24..69,453.34 rows=1 width=160) (actual time=1,841.776..1,872.390 rows=1 loops=1)

  • Join Filter: (aa_1.gl_transaction_type_id = gtt_1.id)
  • Rows Removed by Join Filter: 21
34. 0.008 1,872.363 ↑ 1.0 1 1

Nested Loop (cost=20,866.24..69,451.73 rows=1 width=148) (actual time=1,841.750..1,872.363 rows=1 loops=1)

  • Join Filter: (aa_1.gl_transaction_type_id = gd_1.gl_transaction_type_id)
35. 0.015 1,872.322 ↓ 3.0 3 1

Nested Loop (cost=20,865.67..68,484.26 rows=1 width=105) (actual time=1,841.724..1,872.322 rows=3 loops=1)

36. 0.027 1,872.244 ↓ 1.5 9 1

Nested Loop (cost=20,865.10..68,438.31 rows=6 width=67) (actual time=1,834.154..1,872.244 rows=9 loops=1)

37. 0.042 1,872.127 ↑ 1.2 9 1

Nested Loop (cost=20,857.63..68,268.83 rows=11 width=64) (actual time=1,834.124..1,872.127 rows=9 loops=1)

38. 221.742 1,872.035 ↑ 1.2 25 1

Nested Loop (cost=20,857.21..68,049.98 rows=31 width=38) (actual time=1,169.209..1,872.035 rows=25 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: 546631
39. 6.070 6.070 ↑ 1.0 1 1

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

  • Filter: (cid = 3395)
40. 601.364 1,644.223 ↓ 86.0 546,656 1

Hash Join (cost=20,856.96..67,937.93 rows=6,353 width=50) (actual time=465.941..1,644.223 rows=546,656 loops=1)

  • Hash Cond: ((ad_1.ap_header_id = ah_1.id) AND (ad_1.gl_transaction_type_id = ah_1.gl_transaction_type_id) AND (ad_1.post_month = ah_1.post_month))
41. 578.838 578.838 ↓ 1.1 546,656 1

Seq Scan on ap_details ad_1 (cost=0.00..43,143.29 rows=500,023 width=33) (actual time=0.007..578.838 rows=546,656 loops=1)

  • Filter: (cid = 3395)
42. 210.318 464.021 ↓ 1.0 349,043 1

Hash (cost=14,750.65..14,750.65 rows=348,932 width=33) (actual time=464.021..464.021 rows=349,043 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 25761kB
43. 253.703 253.703 ↓ 1.0 349,043 1

Seq Scan on ap_headers ah_1 (cost=0.00..14,750.65 rows=348,932 width=33) (actual time=0.008..253.703 rows=349,043 loops=1)

  • Filter: (cid = 3395)
44. 0.050 0.050 ↓ 0.0 0 25

Index Scan using idx_ap_payments_id on ap_payments ap_1 (cost=0.42..7.06 rows=1 width=38) (actual time=0.002..0.002 rows=0 loops=25)

  • Index Cond: (id = ah_1.ap_payment_id)
  • Filter: (cid = 3395)
45. 0.027 0.090 ↑ 1.0 1 9

Bitmap Heap Scan on ap_allocations aa_1 (cost=7.48..15.40 rows=1 width=20) (actual time=0.009..0.010 rows=1 loops=9)

  • 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)))
  • Heap Blocks: exact=9
46. 0.018 0.063 ↓ 0.0 0 9

BitmapOr (cost=7.48..7.48 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=9)

47. 0.027 0.027 ↑ 1.0 1 9

Bitmap Index Scan on idx_ap_allocations_credit_ap_detail_id (cost=0.00..3.74 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=9)

  • Index Cond: (ad_1.id = credit_ap_detail_id)
48. 0.018 0.018 ↓ 0.0 0 9

Bitmap Index Scan on idx_ap_allocations_charge_ap_detail_id (cost=0.00..3.74 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=9)

  • Index Cond: (ad_1.id = charge_ap_detail_id)
49. 0.063 0.063 ↓ 0.0 0 9

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.007..0.007 rows=0 loops=9)

  • Index Cond: ((cid = 3395) AND (reference_id = aa_1.id))
  • Filter: ((gl_transaction_type_id = ANY ('{4,5}'::integer[])) AND (post_date >= '2019-06-01'::date) AND (post_date <= '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: 2
50. 0.033 0.033 ↓ 0.0 0 3

Index Scan using idx_gl_details_gl_header_id on gl_details gd_1 (cost=0.57..966.76 rows=57 width=55) (actual time=0.011..0.011 rows=0 loops=3)

  • 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 = 163451) OR (cash_gl_account_id = 163451)))
  • Rows Removed by Filter: 2
51. 0.014 0.014 ↑ 1.2 22 1

Seq Scan on gl_transaction_types gtt_1 (cost=0.00..1.27 rows=27 width=24) (actual time=0.006..0.014 rows=22 loops=1)

Planning time : 77.566 ms