explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mtu7 : old plan

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 177,431.070 ↓ 3.5 7 1

Unique (cost=16,418.98..16,419.00 rows=2 width=1,354) (actual time=177,431.060..177,431.070 rows=7 loops=1)

2.          

CTE cte_gl_details

3. 0.048 177,426.679 ↓ 13.5 27 1

Unique (cost=16,383.67..16,383.87 rows=2 width=715) (actual time=177,426.619..177,426.679 rows=27 loops=1)

4. 0.111 177,426.631 ↓ 13.5 27 1

Sort (cost=16,383.67..16,383.67 rows=2 width=715) (actual time=177,426.617..177,426.631 rows=27 loops=1)

  • Sort Key: gd_2.id, gd_2.cid, gd_2.period_id, gd_2.property_id, gd_2.gl_header_id, gd_2.gl_transaction_type_id, gd_2.accrual_gl_account_id, gd_2.cash_gl_account_id, gd_2.template_amount_type_id, gd_2.template_balance_from_id, gd_2.lease_id, gd_2.property_unit_id, gd_2.gl_reconciliation_id, gd_2.ap_detail_id, gd_2.offsetting_gl_detail_id, gd_2.gl_export_batch_id, gd_2.company_department_id, gd_2.gl_dimension_id, gd_2.ap_payee_location_id, gd_2.ap_code_id, gd_2.ar_code_id, gd_2.reference_id, gd_2.job_phase_id, gd_2.ap_contract_id, gd_2.asset_id, gd_2.property_building_id, gd_2.reclass_gl_detail_id, gd_2.post_month, gd_2.amount, gd_2.memo, gd_2.is_confidential, gd_2.updated_by, gd_2.updated_on, gd_2.created_by, gd_2.created_on, gd_2.maintenance_location_id, gd_2.unit_type_id, gh.post_date, gh.memo
  • Sort Method: quicksort Memory: 31kB
5. 0.030 177,426.520 ↓ 13.5 27 1

Append (cost=60.00..16,383.66 rows=2 width=715) (actual time=2.681..177,426.520 rows=27 loops=1)

6. 0.041 2.840 ↓ 17.0 17 1

Nested Loop (cost=60.00..72.06 rows=1 width=218) (actual time=2.680..2.840 rows=17 loops=1)

7. 0.061 2.697 ↓ 17.0 17 1

Bitmap Heap Scan on gl_details gd_2 (cost=59.57..63.59 rows=1 width=189) (actual time=2.649..2.697 rows=17 loops=1)

  • Recheck Cond: ((gl_reconciliation_id = 28206) AND (cid = 10624) AND (property_id = 584683))
  • Filter: (gl_transaction_type_id = ANY ('{4,5}'::integer[]))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=5
8. 0.004 2.636 ↓ 0.0 0 1

BitmapAnd (cost=59.57..59.57 rows=1 width=0) (actual time=2.635..2.636 rows=0 loops=1)

9. 0.057 0.057 ↑ 19.1 18 1

Bitmap Index Scan on idx_gl_details_gl_reconciliation_id (cost=0.00..7.01 rows=343 width=0) (actual time=0.056..0.057 rows=18 loops=1)

  • Index Cond: (gl_reconciliation_id = 28206)
10. 2.575 2.575 ↓ 11.7 27,882 1

Bitmap Index Scan on idx_gl_details_cid_property_id (cost=0.00..52.31 rows=2,387 width=0) (actual time=2.575..2.575 rows=27,882 loops=1)

  • Index Cond: ((cid = 10624) AND (property_id = 584683))
11. 0.102 0.102 ↑ 1.0 1 17

Index Scan using idx_gl_headers_id on gl_headers gh (cost=0.43..8.47 rows=1 width=41) (actual time=0.006..0.006 rows=1 loops=17)

  • Index Cond: (id = gd_2.gl_header_id)
  • Filter: ((gl_transaction_type_id = ANY ('{4,5}'::integer[])) AND (cid = 10624) AND (gd_2.gl_transaction_type_id = gl_transaction_type_id) AND (gl_header_status_type_id <> ALL ('{2,4,5,6}'::integer[])))
12. 22.803 177,423.650 ↓ 10.0 10 1

Nested Loop Semi Join (cost=79.45..16,311.57 rows=1 width=218) (actual time=10,396.079..177,423.650 rows=10 loops=1)

  • Join Filter: (gd_3.id = gre.gl_detail_id)
  • Rows Removed by Join Filter: 27215
13. 17.320 80.399 ↓ 2,727.0 2,727 1

Nested Loop (cost=79.45..123.55 rows=1 width=218) (actual time=2.520..80.399 rows=2,727 loops=1)

14. 19.729 22.114 ↓ 2,731.0 2,731 1

Bitmap Heap Scan on gl_details gd_3 (cost=79.01..115.08 rows=1 width=189) (actual time=2.491..22.114 rows=2,731 loops=1)

  • Recheck Cond: (((cid = 10624) AND (property_id = 584683) AND (accrual_gl_account_id = 160204)) OR ((cid = 10624) AND (property_id = 584683) AND (cash_gl_account_id = 160204)))
  • Filter: (gl_transaction_type_id = ANY ('{4,5}'::integer[]))
  • Rows Removed by Filter: 593
  • Heap Blocks: exact=724
15. 0.002 2.385 ↓ 0.0 0 1

BitmapOr (cost=79.01..79.01 rows=9 width=0) (actual time=2.384..2.385 rows=0 loops=1)

16. 0.342 0.342 ↓ 831.0 3,324 1

Bitmap Index Scan on idx_gl_details_cid_property_id_accrual_gl_account_id (cost=0.00..4.61 rows=4 width=0) (actual time=0.342..0.342 rows=3,324 loops=1)

  • Index Cond: ((cid = 10624) AND (property_id = 584683) AND (accrual_gl_account_id = 160204))
17. 2.041 2.041 ↓ 831.0 3,324 1

Bitmap Index Scan on idx_gl_details_cid_property_id_post_month_cash_gl_account_id (cost=0.00..74.40 rows=4 width=0) (actual time=2.040..2.041 rows=3,324 loops=1)

  • Index Cond: ((cid = 10624) AND (property_id = 584683) AND (cash_gl_account_id = 160204))
18. 40.965 40.965 ↑ 1.0 1 2,731

Index Scan using idx_gl_headers_id on gl_headers gh_1 (cost=0.43..8.47 rows=1 width=41) (actual time=0.015..0.015 rows=1 loops=2,731)

  • Index Cond: (id = gd_3.gl_header_id)
  • Filter: ((gl_transaction_type_id = ANY ('{4,5}'::integer[])) AND (post_date <= '2019-05-01'::date) AND (cid = 10624) AND (gd_3.gl_transaction_type_id = gl_transaction_type_id) AND (gl_header_status_type_id <> ALL ('{2,4,5,6}'::integer[])))
  • Rows Removed by Filter: 0
19. 177,320.448 177,320.448 ↑ 13.6 10 2,727

Seq Scan on gl_reconciliation_exceptions gre (cost=0.00..16,186.32 rows=136 width=4) (actual time=64.999..65.024 rows=10 loops=2,727)

  • Filter: ((cid = 10624) AND (gl_reconciliation_id = 28206))
  • Rows Removed by Filter: 693677
20. 0.021 177,431.062 ↓ 3.5 7 1

Sort (cost=35.12..35.12 rows=2 width=1,354) (actual time=177,431.059..177,431.062 rows=7 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: 26kB
21. 0.009 177,431.041 ↓ 3.5 7 1

Subquery Scan on sub_query (cost=34.99..35.11 rows=2 width=1,354) (actual time=177,431.013..177,431.041 rows=7 loops=1)

22. 0.017 177,431.032 ↓ 3.5 7 1

Unique (cost=34.99..35.09 rows=2 width=1,350) (actual time=177,431.011..177,431.032 rows=7 loops=1)

23. 0.069 177,431.015 ↓ 7.0 14 1

Sort (cost=34.99..35.00 rows=2 width=1,350) (actual time=177,431.009..177,431.015 rows=14 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, gd.gh_post_date, gd.post_month, (sum(gd.amount) OVER (?)), gd.gl_header_id, gd.property_id, gd.accrual_gl_account_id, gd.cash_gl_account_id, gtt.description, gd.gl_transaction_type_id, gd.gl_reconciliation_id, gd.gh_memo, gd.memo
  • Sort Method: quicksort Memory: 28kB
24. 0.013 177,430.946 ↓ 7.0 14 1

Append (cost=19.31..34.98 rows=2 width=1,350) (actual time=177,428.753..177,430.946 rows=14 loops=1)

25. 0.023 177,429.391 ↓ 7.0 7 1

Unique (cost=19.31..19.59 rows=1 width=688) (actual time=177,428.752..177,429.391 rows=7 loops=1)

26. 0.979 177,429.368 ↓ 27.0 27 1

WindowAgg (cost=19.31..19.59 rows=1 width=688) (actual time=177,428.749..177,429.368 rows=27 loops=1)

27. 0.060 177,428.389 ↓ 27.0 27 1

Sort (cost=19.31..19.32 rows=1 width=657) (actual time=177,428.377..177,428.389 rows=27 loops=1)

  • Sort Key: ap.id
  • Sort Method: quicksort Memory: 31kB
28. 0.063 177,428.329 ↓ 27.0 27 1

Nested Loop (cost=2.40..19.30 rows=1 width=657) (actual time=177,426.829..177,428.329 rows=27 loops=1)

29. 0.060 177,428.104 ↓ 27.0 27 1

Nested Loop (cost=2.40..14.96 rows=1 width=637) (actual time=177,426.804..177,428.104 rows=27 loops=1)

30. 0.037 177,427.909 ↓ 27.0 27 1

Nested Loop (cost=1.98..14.34 rows=1 width=610) (actual time=177,426.777..177,427.909 rows=27 loops=1)

31. 0.066 177,427.683 ↓ 27.0 27 1

Nested Loop (cost=1.56..13.68 rows=1 width=614) (actual time=177,426.740..177,427.683 rows=27 loops=1)

32. 0.040 177,426.969 ↓ 27.0 27 1

Nested Loop (cost=0.42..8.50 rows=1 width=610) (actual time=177,426.676..177,426.969 rows=27 loops=1)

33. 177,426.713 177,426.713 ↓ 27.0 27 1

CTE Scan on cte_gl_details gd (cost=0.00..0.05 rows=1 width=606) (actual time=177,426.622..177,426.713 rows=27 loops=1)

  • Filter: ((cid = 10624) AND (gl_transaction_type_id = 4))
34. 0.216 0.216 ↑ 1.0 1 27

Index Scan using idx_ap_allocations_cid_gltrans_id_prop on ap_allocations aa (cost=0.42..8.45 rows=1 width=20) (actual time=0.008..0.008 rows=1 loops=27)

  • Index Cond: ((cid = 10624) AND (gl_transaction_type_id = 4) AND (id = gd.reference_id))
  • Filter: (NOT is_deleted)
35. 0.351 0.648 ↑ 1.0 1 27

Bitmap Heap Scan on ap_details ad (cost=1.14..5.17 rows=1 width=21) (actual time=0.018..0.024 rows=1 loops=27)

  • Recheck Cond: ((id = aa.credit_ap_detail_id) OR (id = aa.charge_ap_detail_id))
  • Filter: ((cid = 10624) AND ((id = aa.credit_ap_detail_id) OR ((id = aa.charge_ap_detail_id) AND is_cross_allocation)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=54
36. 0.027 0.297 ↓ 0.0 0 27

BitmapOr (cost=1.14..1.14 rows=2 width=0) (actual time=0.011..0.011 rows=0 loops=27)

37. 0.135 0.135 ↑ 1.0 1 27

Bitmap Index Scan on idx_ap_details (cost=0.00..0.57 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=27)

  • Index Cond: (id = aa.credit_ap_detail_id)
38. 0.135 0.135 ↑ 1.0 1 27

Bitmap Index Scan on idx_ap_details (cost=0.00..0.57 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=27)

  • Index Cond: (id = aa.charge_ap_detail_id)
39. 0.189 0.189 ↑ 1.0 1 27

Index Scan using idx_ap_headers on ap_headers ah (cost=0.42..0.65 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=27)

  • Index Cond: (id = ad.ap_header_id)
  • Filter: ((cid = 10624) AND (ad.gl_transaction_type_id = gl_transaction_type_id) AND (ad.post_month = post_month))
40. 0.135 0.135 ↑ 1.0 1 27

Index Scan using idx_ap_payments_id on ap_payments ap (cost=0.42..0.62 rows=1 width=39) (actual time=0.005..0.005 rows=1 loops=27)

  • Index Cond: (id = ah.ap_payment_id)
  • Filter: (cid = 10624)
41. 0.162 0.162 ↑ 1.0 1 27

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

  • Filter: (id = 4)
  • Rows Removed by Filter: 26
42. 0.012 1.542 ↓ 7.0 7 1

Subquery Scan on "*SELECT* 2" (cost=15.09..15.38 rows=1 width=688) (actual time=0.893..1.542 rows=7 loops=1)

43. 0.021 1.530 ↓ 7.0 7 1

Unique (cost=15.09..15.37 rows=1 width=702) (actual time=0.889..1.530 rows=7 loops=1)

44. 0.657 1.509 ↓ 27.0 27 1

WindowAgg (cost=15.09..15.37 rows=1 width=702) (actual time=0.887..1.509 rows=27 loops=1)

45. 0.052 0.852 ↓ 27.0 27 1

Sort (cost=15.09..15.10 rows=1 width=671) (actual time=0.841..0.852 rows=27 loops=1)

  • Sort Key: ap_1.id, gd_1.gl_transaction_type_id, ah_1.header_number, ad_1.ap_header_id
  • Sort Method: quicksort Memory: 32kB
46. 0.033 0.800 ↓ 27.0 27 1

Nested Loop (cost=2.52..15.08 rows=1 width=671) (actual time=0.067..0.800 rows=27 loops=1)

47. 0.053 0.713 ↓ 27.0 27 1

Nested Loop (cost=2.38..14.93 rows=1 width=655) (actual time=0.049..0.713 rows=27 loops=1)

48. 0.033 0.606 ↓ 27.0 27 1

Nested Loop (cost=1.96..14.31 rows=1 width=628) (actual time=0.042..0.606 rows=27 loops=1)

49. 0.073 0.492 ↓ 27.0 27 1

Nested Loop (cost=1.54..13.65 rows=1 width=618) (actual time=0.033..0.492 rows=27 loops=1)

50. 0.051 0.149 ↓ 27.0 27 1

Nested Loop (cost=0.42..8.49 rows=1 width=614) (actual time=0.014..0.149 rows=27 loops=1)

51. 0.017 0.017 ↓ 27.0 27 1

CTE Scan on cte_gl_details gd_1 (cost=0.00..0.04 rows=1 width=606) (actual time=0.001..0.017 rows=27 loops=1)

  • Filter: (cid = 10624)
52. 0.081 0.081 ↑ 1.0 1 27

Index Scan using idx_ap_allocations_cid_gltrans_id_prop on ap_allocations aa_1 (cost=0.42..8.45 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=27)

  • Index Cond: ((cid = 10624) AND (gl_transaction_type_id = gd_1.gl_transaction_type_id) AND (id = gd_1.reference_id))
53. 0.108 0.270 ↑ 1.0 1 27

Bitmap Heap Scan on ap_details ad_1 (cost=1.11..5.15 rows=1 width=21) (actual time=0.009..0.010 rows=1 loops=27)

  • Recheck Cond: ((id = aa_1.credit_ap_detail_id) OR (id = aa_1.charge_ap_detail_id))
  • Filter: ((cid = 10624) AND ((id = aa_1.credit_ap_detail_id) OR ((id = aa_1.charge_ap_detail_id) AND is_cross_allocation)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=54
54. 0.054 0.162 ↓ 0.0 0 27

BitmapOr (cost=1.11..1.11 rows=2 width=0) (actual time=0.006..0.006 rows=0 loops=27)

55. 0.054 0.054 ↑ 1.0 1 27

Bitmap Index Scan on idx_ap_details (cost=0.00..0.56 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=27)

  • Index Cond: (id = aa_1.credit_ap_detail_id)
56. 0.054 0.054 ↑ 1.0 1 27

Bitmap Index Scan on idx_ap_details (cost=0.00..0.56 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=27)

  • Index Cond: (id = aa_1.charge_ap_detail_id)
57. 0.081 0.081 ↑ 1.0 1 27

Index Scan using idx_ap_headers on ap_headers ah_1 (cost=0.42..0.65 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=27)

  • Index Cond: (id = ad_1.ap_header_id)
  • Filter: ((cid = 10624) AND (ad_1.gl_transaction_type_id = gl_transaction_type_id) AND (ad_1.post_month = post_month))
58. 0.054 0.054 ↑ 1.0 1 27

Index Scan using idx_ap_payments_id on ap_payments ap_1 (cost=0.42..0.62 rows=1 width=39) (actual time=0.002..0.002 rows=1 loops=27)

  • Index Cond: (id = ah_1.ap_payment_id)
  • Filter: (cid = 10624)
59. 0.054 0.054 ↑ 1.0 1 27

Index Scan using pk_gl_transaction_types on gl_transaction_types gtt_1 (cost=0.14..0.16 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=27)

  • Index Cond: (id = aa_1.gl_transaction_type_id)