explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nKt6

Settings
# exclusive inclusive rows x rows loops node
1. 339.930 28,225.543 ↓ 56,378.5 112,757 1

GroupAggregate (cost=28,553.84..28,554.14 rows=2 width=1,586) (actual time=27,837.146..28,225.543 rows=112,757 loops=1)

  • Group Key: outer_details.property_name, outer_details.account_number, outer_details.is_beginning_balance, outer_details.transaction_date, outer_details.post_month, outer_details.post_date, outer_details.gl_detail_id, outer_details.property_id, outer_details.lookup_code, outer_details.grouping_gl_account_id, outer_details.gl_account, outer_details.account_name, outer_details.gl_transaction_type_id, outer_details.transaction_type, outer_details.transaction_type_id, outer_details.gh_header_id, outer_details.header_number, outer_details.reference_id, outer_details.reference, outer_details.transaction_id, outer_details.memo, outer_details.details, outer_details.ar_code_type_id, outer_details.ar_trigger_id, outer_details.ar_code_id, outer_details.ar_transaction_payment_id, outer_details.ap_payment_id, outer_details.ar_payment_id, outer_details.art_id, outer_details.vendor_id, outer_details.resident, outer_details.bldg_unit, outer_details.display_number, outer_details.lease_id, outer_details.occupancy_type_id, outer_details.customer_id, outer_details.beginning_balance, outer_details.hide_zero_beginning_balance_for_no_activities, outer_details.min_debit, outer_details.min_credit, outer_details.min_beginning_balance, outer_details.max_debit, outer_details.max_credit, outer_details.max_beginning_balance
2. 927.165 27,885.613 ↓ 57,576.0 115,152 1

Sort (cost=28,553.84..28,553.85 rows=2 width=1,554) (actual time=27,837.125..27,885.613 rows=115,152 loops=1)

  • Sort Key: outer_details.property_name, outer_details.account_number, outer_details.is_beginning_balance DESC, outer_details.transaction_date, outer_details.post_month, outer_details.post_date, outer_details.gl_detail_id, outer_details.property_id, outer_details.lookup_code, outer_details.grouping_gl_account_id, outer_details.gl_account, outer_details.account_name, outer_details.gl_transaction_type_id, outer_details.transaction_type, outer_details.transaction_type_id, outer_details.gh_header_id, outer_details.header_number, outer_details.reference_id, outer_details.reference, outer_details.transaction_id, outer_details.memo, outer_details.details, outer_details.ar_code_type_id, outer_details.ar_trigger_id, outer_details.ar_code_id, outer_details.ar_transaction_payment_id, outer_details.ap_payment_id, outer_details.ar_payment_id, outer_details.art_id, outer_details.vendor_id, outer_details.resident, outer_details.bldg_unit, outer_details.display_number, outer_details.lease_id, outer_details.occupancy_type_id, outer_details.customer_id, outer_details.beginning_balance, outer_details.hide_zero_beginning_balance_for_no_activities, outer_details.min_debit, outer_details.min_credit, outer_details.min_beginning_balance, outer_details.max_debit, outer_details.max_credit, outer_details.max_beginning_balance
  • Sort Method: quicksort Memory: 64,308kB
3. 101.198 26,958.448 ↓ 57,576.0 115,152 1

Subquery Scan on outer_details (cost=28,553.68..28,553.83 rows=2 width=1,554) (actual time=26,575.605..26,958.448 rows=115,152 loops=1)

  • Filter: (outer_details.hide_zero_beginning_balance_for_no_activities <> 1)
4. 238.806 26,857.250 ↓ 38,384.0 115,152 1

WindowAgg (cost=28,553.68..28,553.80 rows=3 width=1,590) (actual time=26,575.602..26,857.250 rows=115,152 loops=1)

5. 175.015 26,618.444 ↓ 38,384.0 115,152 1

Sort (cost=28,553.68..28,553.69 rows=3 width=1,362) (actual time=26,574.940..26,618.444 rows=115,152 loops=1)

  • Sort Key: details.property_id, details.gl_account
  • Sort Method: quicksort Memory: 55,586kB
6. 164.660 26,443.429 ↓ 38,384.0 115,152 1

WindowAgg (cost=28,553.60..28,553.66 rows=3 width=1,362) (actual time=26,232.094..26,443.429 rows=115,152 loops=1)

7. 240.805 26,278.769 ↓ 38,384.0 115,152 1

Sort (cost=28,553.60..28,553.61 rows=3 width=1,354) (actual time=26,232.073..26,278.769 rows=115,152 loops=1)

  • Sort Key: details.property_id, details.grouping_gl_account_id
  • Sort Method: quicksort Memory: 53,777kB
8. 124.272 26,037.964 ↓ 38,384.0 115,152 1

Subquery Scan on details (cost=201.30..28,553.58 rows=3 width=1,354) (actual time=1.099..26,037.964 rows=115,152 loops=1)

9. 90.779 25,913.692 ↓ 38,384.0 115,152 1

Append (cost=201.30..28,553.55 rows=3 width=1,390) (actual time=1.098..25,913.692 rows=115,152 loops=1)

10. 141.619 25,822.866 ↓ 57,576.0 115,152 1

Result (cost=201.30..28,523.33 rows=2 width=1,376) (actual time=1.097..25,822.866 rows=115,152 loops=1)

11. 94.355 25,681.247 ↓ 57,576.0 115,152 1

Append (cost=201.30..28,523.31 rows=2 width=1,348) (actual time=1.095..25,681.247 rows=115,152 loops=1)

12. 1,018.591 6,033.434 ↓ 68,584.0 68,584 1

Nested Loop Left Join (cost=201.30..614.65 rows=1 width=1,143) (actual time=1.094..6,033.434 rows=68,584 loops=1)

13. 145.132 4,877.675 ↓ 68,584.0 68,584 1

Nested Loop Left Join (cost=200.88..612.37 rows=1 width=1,079) (actual time=0.147..4,877.675 rows=68,584 loops=1)

14. 128.744 4,526.791 ↓ 68,584.0 68,584 1

Nested Loop Left Join (cost=200.46..604.87 rows=1 width=1,031) (actual time=0.135..4,526.791 rows=68,584 loops=1)

15. 159.865 3,232.119 ↓ 68,584.0 68,584 1

Nested Loop Left Join (cost=200.31..602.13 rows=1 width=1,017) (actual time=0.119..3,232.119 rows=68,584 loops=1)

16. 126.464 3,003.670 ↓ 68,584.0 68,584 1

Nested Loop Left Join (cost=200.17..601.97 rows=1 width=1,003) (actual time=0.113..3,003.670 rows=68,584 loops=1)

  • Join Filter: (gh.cid = art1.cid)
17. 187.858 2,671.454 ↓ 68,584.0 68,584 1

Nested Loop Left Join (cost=199.73..597.96 rows=1 width=784) (actual time=0.106..2,671.454 rows=68,584 loops=1)

  • Join Filter: (gh.gl_transaction_type_id = ANY ('{7,8,9,17}'::integer[]))
  • Rows Removed by Join Filter: 26,762
18. 123.303 1,934.924 ↓ 68,584.0 68,584 1

Nested Loop Left Join (cost=1.71..395.90 rows=1 width=772) (actual time=0.089..1,934.924 rows=68,584 loops=1)

  • Join Filter: (gh.gl_transaction_type_id = ANY ('{13,14,18,23,24}'::integer[]))
19. 360.526 1,605.869 ↓ 68,584.0 68,584 1

Nested Loop (cost=1.28..388.49 rows=1 width=764) (actual time=0.079..1,605.869 rows=68,584 loops=1)

  • Join Filter: (gh.gl_transaction_type_id = gtty.id)
  • Rows Removed by Join Filter: 581,784
20. 147.562 902.423 ↓ 68,584.0 68,584 1

Nested Loop (cost=1.28..385.89 rows=1 width=744) (actual time=0.073..902.423 rows=68,584 loops=1)

21. 167.394 549.109 ↓ 9,797.7 68,584 1

Nested Loop (cost=0.84..327.29 rows=7 width=648) (actual time=0.063..549.109 rows=68,584 loops=1)

22. 72.208 175.195 ↓ 2,294.7 68,840 1

Nested Loop (cost=0.56..296.89 rows=30 width=579) (actual time=0.056..175.195 rows=68,840 loops=1)

23. 0.009 0.009 ↑ 1.0 6 1

Seq Scan on load_prop (cost=0.00..1.06 rows=6 width=515) (actual time=0.004..0.009 rows=6 loops=1)

24. 102.978 102.978 ↓ 2,294.6 11,473 6

Index Scan using idx_gl_details_cid_property_id_post_month on gl_details gd (cost=0.56..49.26 rows=5 width=68) (actual time=0.250..17.163 rows=11,473 loops=6)

  • Index Cond: ((cid = 15,667) AND (property_id = load_prop.property_id) AND (property_id = ANY ('{640825,640826,640827,640828,640829,640830}'::integer[])) AND (post_month >= '2018-01-01'::date) AND (post_month <= '2020-06-01'::date))
  • Filter: (gl_transaction_type_id = ANY ('{13,14,18,23,24,7,8,9,17}'::integer[]))
  • Rows Removed by Filter: 7,896
25. 206.520 206.520 ↑ 1.0 1 68,840

Index Scan using idx_temp_gat_gl_account_id on gat_temp gat (cost=0.28..1.00 rows=1 width=81) (actual time=0.003..0.003 rows=1 loops=68,840)

  • Index Cond: (gl_account_id = gd.accrual_gl_account_id)
  • Filter: ((cid = 15,667) AND (grouping_gl_account_id = ANY ('{281795,281796,278055,281797,281798,281799,281800,281801,282813,281802,287419,287420,284923,286710,286936,287435,286937,281803,278066,287589,281804,278075,284275,286486,286487,286488,286489,286490,286491,287438,281805,281806,281807,281808,281809,281810,281811,278074,281584,281812,281813,283734,283735,283774,283783,283899,283900,284273,281814,282237,281815,281816,281817,282815,281818,281819,281820,281821,281822,281823,281824,281825,281826,281827,281828,281829,281830,281831,281832,281833,281834,281835,281836,283687,283700,283776,283826,283827,283868,284186,284551,284552,284553,284565,284798,284909,284924,285478,285479,285482,285485,285494,285526,286480,286485,286511,286717,286725,286727,287433,281837,281838,287623,281839,281840,281841,281842,281843,284133,281844,281845,281851,281846,281852,281853,281848,281854,281849,281855,281850,281856,283820,284103,284104,284105,284106,284107,284108,284109,284110,285497,286513,286514,283825,284274,284393,285492,284796,284929,285484,285501,286498,286499,286500,286713,286965,286966,281857,281858,278072,278080,278079,278070,278073,281859,281860,281861,281862,281863,282301,282240,282302,282303,282304,282305,282306,282307,282308,282309,282310,282311,282312,282313,282241,282314,282315,282316,282317,282318,282319,282320,282321,282322,282323,282324,282325,282326,282327,284563,284564,284771,286747,281864,283627,283655,286741,286957,287620,287621,287622,281865,278076,281866,278065,281867,281868,281869,282287,281870,281871,278067,278081,281872,281873,281874,281875,281876,281877,281878,281879,281880,286731,287416,278077,278082,284908,278078,278048,278068,278069,282814,285514,286729,281881,281882,287595,278062,284116,278063,278049,281585,281883,282089,278051,281884,281885,281886,281887,281888,281889,282919,281890,278050,278052,278083,278071,281891,281892,281893,282207,281894,281895,281896,281897,281898,281899,281900,281901,281902,281903,283786,284555,284556,284557,284710,281904,278053,282288,278056,278058,278064,281905,278057,282286,278059,281906,281907,281908,281909,281910,281911,281912,281913,281914,278061,281915,281916,281917,281918,285535,281919,284916,281920,281921,284925,283838,283895,284825,284847,285496,281922,287641,281923,281924,281925,281926,281927,281928,281929,283689,281930,281931,281932,281933,281934,283672,281935,281936,283657,281937,281938,281939,281940,281941,281942,281943,286709,283733,286492,286493,286494,286495,286496,278054,281944,281945,281946,281947,281948,281949,281950,281951,282811,284088,281952,281953,281954,281955,281956,281957,281958,281959,281960,281961,281962,281963,281964,281965,281966,281967,281968,281969,281970,281971,281972,286482,281973,281974,281975,281976,281977,281978,281979,281980,281981,281982,281983,281984,281985,281986,281987,278060,283836,281988,281989,281990,281991,281992,281993,281994,281995,281996,281997,281998,281999,282000,282001,282002,282003,282004,282005,282006,282007,282008,282009,282010,282011,282012,282013,282298,282014,282016,282017,282018,282235,282015,282019,282020,282021,282208,282022,282023,282024,282025,282026,282027,282028,282029,282030,282031,282032,282236,283675,283676,283677,283678,283679,283680,283681,283894,286483,284917,282910,282056,282911,282049,282912,282913,282914,282915,282916,282917,282918,282058,282055,282920,283835,283837,283877,283878,283879,283880,283881,283882,283883,283884,283885,283886,283887,283888,283889,283890,283891,283892,284130,286630,286631,284118,282033,282034,282035,282036,282037,282038,282039,282812,282040,282205,282041,282042,282043,282044,282045,281847,282046,282047,282048,282050,282051,282052,282053,282054,282057,282059,282060,282061,282062,282063,282064,282065,282066,282067,283682,283686,283685,283684,283683,282068,282069,286716,283782,283785,283821,283840,282070,282071,282072,282073,282074,282075,282076,282077,282078,282079,282080,282081,282082,282083,282084,282085,282086,282087}'::integer[])))
26. 205.752 205.752 ↑ 1.0 1 68,584

Index Scan using idx_gl_headers_id on gl_headers gh (cost=0.44..8.37 rows=1 width=100) (actual time=0.003..0.003 rows=1 loops=68,584)

  • Index Cond: (id = gd.gl_header_id)
  • Filter: ((NOT is_template) AND (cid = 15,667) AND (gl_book_id = 3,683) AND (gl_header_status_type_id <> ALL ('{2,4,5,6}'::integer[])))
27. 342.920 342.920 ↑ 3.0 9 68,584

Seq Scan on gl_transaction_types gtty (cost=0.00..2.27 rows=27 width=24) (actual time=0.001..0.005 rows=9 loops=68,584)

28. 205.752 205.752 ↓ 0.0 0 68,584

Index Scan using pk_ar_allocations on ar_allocations ara (cost=0.43..7.39 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=68,584)

  • Index Cond: ((cid = gh.cid) AND (cid = 15,667) AND (id = gh.reference_id))
29. 205.752 548.672 ↑ 1.0 1 68,584

Bitmap Heap Scan on ar_transactions art (cost=198.02..202.04 rows=1 width=20) (actual time=0.007..0.008 rows=1 loops=68,584)

  • Recheck Cond: (((cid = 15,667) AND (id = gh.reference_id)) OR ((cid = 15,667) AND (id = ara.credit_ar_transaction_id)))
  • Filter: (gh.cid = cid)
  • Heap Blocks: exact=68,584
30. 68.584 342.920 ↓ 0.0 0 68,584

BitmapOr (cost=198.02..198.02 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=68,584)

31. 205.752 205.752 ↑ 1.0 1 68,584

Bitmap Index Scan on pk_ar_transactions (cost=0.00..3.64 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=68,584)

  • Index Cond: ((cid = 15,667) AND (id = gh.reference_id))
32. 68.584 68.584 ↓ 0.0 0 68,584

Bitmap Index Scan on pk_ar_transactions (cost=0.00..1.02 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=68,584)

  • Index Cond: ((cid = 15,667) AND (id = ara.credit_ar_transaction_id))
33. 205.752 205.752 ↑ 1.0 1 68,584

Index Scan using pk_ar_transactions on ar_transactions art1 (cost=0.44..4.00 rows=1 width=227) (actual time=0.003..0.003 rows=1 loops=68,584)

  • Index Cond: ((cid = 15,667) AND (id = COALESCE(ara.charge_ar_transaction_id, art.id)))
34. 68.584 68.584 ↑ 1.0 1 68,584

Index Scan using pk_ar_triggers on ar_triggers att (cost=0.14..0.16 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=68,584)

  • Index Cond: (art1.ar_trigger_id = id)
35. 1,165.928 1,165.928 ↑ 1.0 1 68,584

Index Scan using idx_temp_cid on ar_codes_temp ac (cost=0.14..2.73 rows=1 width=26) (actual time=0.011..0.017 rows=1 loops=68,584)

  • Index Cond: ((cid = art1.cid) AND (cid = 15,667))
  • Filter: (id = art1.ar_code_id)
  • Rows Removed by Filter: 114
36. 205.752 205.752 ↑ 1.0 1 68,584

Index Scan using pk_cached_leases on cached_leases cl (cost=0.43..7.49 rows=1 width=60) (actual time=0.003..0.003 rows=1 loops=68,584)

  • Index Cond: ((cid = gd.cid) AND (cid = 15,667) AND (id = gd.lease_id))
37. 137.168 137.168 ↓ 0.0 0 68,584

Index Scan using idx_ar_deposits_id on ar_deposits ad (cost=0.42..1.39 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=68,584)

  • Index Cond: (gh.reference_id = id)
  • Filter: ((cid = 15,667) AND (gh.gl_transaction_type_id = gl_transaction_type_id))
38. 56.991 19,553.458 ↓ 46,568.0 46,568 1

Subquery Scan on "*SELECT* 2" (cost=6.13..27,908.65 rows=1 width=1,013) (actual time=0.533..19,553.458 rows=46,568 loops=1)

39. 406.868 19,496.467 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=6.13..27,908.64 rows=1 width=1,013) (actual time=0.531..19,496.467 rows=46,568 loops=1)

40. 120.261 18,996.463 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=5.71..27,906.54 rows=1 width=949) (actual time=0.508..18,996.463 rows=46,568 loops=1)

41. 103.504 18,783.066 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=5.29..27,905.80 rows=1 width=938) (actual time=0.499..18,783.066 rows=46,568 loops=1)

42. 74.320 18,679.562 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=4.86..27,903.03 rows=1 width=933) (actual time=0.494..18,679.562 rows=46,568 loops=1)

43. 105.114 18,512.106 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=4.43..27,900.26 rows=1 width=924) (actual time=0.489..18,512.106 rows=46,568 loops=1)

44. 110.397 18,406.992 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=4.00..27,899.60 rows=1 width=897) (actual time=0.484..18,406.992 rows=46,568 loops=1)

45. 92.590 18,296.595 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=3.57..27,896.19 rows=1 width=889) (actual time=0.478..18,296.595 rows=46,568 loops=1)

46. 102.111 18,064.301 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=3.15..27,895.66 rows=1 width=893) (actual time=0.473..18,064.301 rows=46,568 loops=1)

47. 94.232 17,962.190 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=2.72..27,892.52 rows=1 width=889) (actual time=0.468..17,962.190 rows=46,568 loops=1)

48. 2,039.995 17,774.822 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=2.29..27,890.20 rows=1 width=851) (actual time=0.462..17,774.822 rows=46,568 loops=1)

  • Join Filter: ((pb.cid = gd_1.cid) AND (pb.id = CASE WHEN (gd_1.gl_transaction_type_id = 1) THEN gd_1.property_building_id ELSE COALESCE(ad1.property_building_id, ad_1.property_building_id, gd_1.property_building_id) END))
  • Rows Removed by Join Filter: 4,796,504
49. 5,577.961 13,639.267 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=2.29..27,886.11 rows=1 width=866) (actual time=0.367..13,639.267 rows=46,568 loops=1)

  • Join Filter: ((pu.cid = gd_1.cid) AND (pu.id = CASE WHEN (gd_1.gl_transaction_type_id = 1) THEN gd_1.property_unit_id ELSE COALESCE(ad1.property_unit_id, ad_1.property_unit_id) END))
  • Rows Removed by Join Filter: 13,737,460
50. 94.490 2,054.034 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=2.29..27,875.26 rows=1 width=874) (actual time=0.114..2,054.034 rows=46,568 loops=1)

51. 86.310 1,819.840 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=1.86..27,871.22 rows=1 width=837) (actual time=0.108..1,819.840 rows=46,568 loops=1)

52. 91.638 1,640.394 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=1.43..27,867.19 rows=1 width=796) (actual time=0.103..1,640.394 rows=46,568 loops=1)

  • Join Filter: (gh_1.gl_transaction_type_id = ANY ('{4,5}'::integer[]))
53. 489.410 1,409.052 ↓ 46,568.0 46,568 1

Nested Loop (cost=1.00..27,862.97 rows=1 width=784) (actual time=0.097..1,409.052 rows=46,568 loops=1)

  • Join Filter: (gh_1.gl_transaction_type_id = gtty_1.id)
  • Rows Removed by Join Filter: 913,026
54. 112.458 500.530 ↓ 46,568.0 46,568 1

Nested Loop (cost=1.00..27,860.36 rows=1 width=760) (actual time=0.074..500.530 rows=46,568 loops=1)

55. 50.760 199.476 ↓ 148.7 47,149 1

Nested Loop (cost=0.56..25,926.91 rows=317 width=664) (actual time=0.063..199.476 rows=47,149 loops=1)

56. 3.370 6.300 ↓ 1.6 3,312 1

Nested Loop (cost=0.00..424.03 rows=2,094 width=596) (actual time=0.018..6.300 rows=3,312 loops=1)

57. 1.274 1.274 ↓ 1.6 552 1

Seq Scan on gat_temp gat_1 (cost=0.00..396.78 rows=349 width=81) (actual time=0.008..1.274 rows=552 loops=1)

  • Filter: ((cid = 15,667) AND (grouping_gl_account_id = ANY ('{281795,281796,278055,281797,281798,281799,281800,281801,282813,281802,287419,287420,284923,286710,286936,287435,286937,281803,278066,287589,281804,278075,284275,286486,286487,286488,286489,286490,286491,287438,281805,281806,281807,281808,281809,281810,281811,278074,281584,281812,281813,283734,283735,283774,283783,283899,283900,284273,281814,282237,281815,281816,281817,282815,281818,281819,281820,281821,281822,281823,281824,281825,281826,281827,281828,281829,281830,281831,281832,281833,281834,281835,281836,283687,283700,283776,283826,283827,283868,284186,284551,284552,284553,284565,284798,284909,284924,285478,285479,285482,285485,285494,285526,286480,286485,286511,286717,286725,286727,287433,281837,281838,287623,281839,281840,281841,281842,281843,284133,281844,281845,281851,281846,281852,281853,281848,281854,281849,281855,281850,281856,283820,284103,284104,284105,284106,284107,284108,284109,284110,285497,286513,286514,283825,284274,284393,285492,284796,284929,285484,285501,286498,286499,286500,286713,286965,286966,281857,281858,278072,278080,278079,278070,278073,281859,281860,281861,281862,281863,282301,282240,282302,282303,282304,282305,282306,282307,282308,282309,282310,282311,282312,282313,282241,282314,282315,282316,282317,282318,282319,282320,282321,282322,282323,282324,282325,282326,282327,284563,284564,284771,286747,281864,283627,283655,286741,286957,287620,287621,287622,281865,278076,281866,278065,281867,281868,281869,282287,281870,281871,278067,278081,281872,281873,281874,281875,281876,281877,281878,281879,281880,286731,287416,278077,278082,284908,278078,278048,278068,278069,282814,285514,286729,281881,281882,287595,278062,284116,278063,278049,281585,281883,282089,278051,281884,281885,281886,281887,281888,281889,282919,281890,278050,278052,278083,278071,281891,281892,281893,282207,281894,281895,281896,281897,281898,281899,281900,281901,281902,281903,283786,284555,284556,284557,284710,281904,278053,282288,278056,278058,278064,281905,278057,282286,278059,281906,281907,281908,281909,281910,281911,281912,281913,281914,278061,281915,281916,281917,281918,285535,281919,284916,281920,281921,284925,283838,283895,284825,284847,285496,281922,287641,281923,281924,281925,281926,281927,281928,281929,283689,281930,281931,281932,281933,281934,283672,281935,281936,283657,281937,281938,281939,281940,281941,281942,281943,286709,283733,286492,286493,286494,286495,286496,278054,281944,281945,281946,281947,281948,281949,281950,281951,282811,284088,281952,281953,281954,281955,281956,281957,281958,281959,281960,281961,281962,281963,281964,281965,281966,281967,281968,281969,281970,281971,281972,286482,281973,281974,281975,281976,281977,281978,281979,281980,281981,281982,281983,281984,281985,281986,281987,278060,283836,281988,281989,281990,281991,281992,281993,281994,281995,281996,281997,281998,281999,282000,282001,282002,282003,282004,282005,282006,282007,282008,282009,282010,282011,282012,282013,282298,282014,282016,282017,282018,282235,282015,282019,282020,282021,282208,282022,282023,282024,282025,282026,282027,282028,282029,282030,282031,282032,282236,283675,283676,283677,283678,283679,283680,283681,283894,286483,284917,282910,282056,282911,282049,282912,282913,282914,282915,282916,282917,282918,282058,282055,282920,283835,283837,283877,283878,283879,283880,283881,283882,283883,283884,283885,283886,283887,283888,283889,283890,283891,283892,284130,286630,286631,284118,282033,282034,282035,282036,282037,282038,282039,282812,282040,282205,282041,282042,282043,282044,282045,281847,282046,282047,282048,282050,282051,282052,282053,282054,282057,282059,282060,282061,282062,282063,282064,282065,282066,282067,283682,283686,283685,283684,283683,282068,282069,286716,283782,283785,283821,283840,282070,282071,282072,282073,282074,282075,282076,282077,282078,282079,282080,282081,282082,282083,282084,282085,282086,282087}'::integer[])))
58. 1.649 1.656 ↑ 1.0 6 552

Materialize (cost=0.00..1.09 rows=6 width=515) (actual time=0.000..0.003 rows=6 loops=552)

59. 0.007 0.007 ↑ 1.0 6 1

Seq Scan on load_prop load_prop_1 (cost=0.00..1.06 rows=6 width=515) (actual time=0.003..0.007 rows=6 loops=1)

60. 142.416 142.416 ↓ 14.0 14 3,312

Index Scan using idx_gl_details_cid_property_id_accrual_gl_account_id on gl_details gd_1 (cost=0.56..12.17 rows=1 width=84) (actual time=0.006..0.043 rows=14 loops=3,312)

  • Index Cond: ((cid = 15,667) AND (property_id = load_prop_1.property_id) AND (accrual_gl_account_id = gat_1.gl_account_id))
  • Filter: ((post_month >= '2018-01-01'::date) AND (post_month <= '2020-06-01'::date) AND (gl_transaction_type_id <> ALL ('{13,14,18,23,24,7,8,9,17}'::integer[])))
  • Rows Removed by Filter: 22
61. 188.596 188.596 ↑ 1.0 1 47,149

Index Scan using idx_gl_headers_id on gl_headers gh_1 (cost=0.44..6.10 rows=1 width=100) (actual time=0.004..0.004 rows=1 loops=47,149)

  • Index Cond: (id = gd_1.gl_header_id)
  • Filter: ((is_template IS FALSE) AND (cid = 15,667) AND (gl_book_id = 3,683) AND (gl_header_status_type_id <> ALL ('{2,4,5,6}'::integer[])))
  • Rows Removed by Filter: 0
62. 419.112 419.112 ↑ 1.3 21 46,568

Seq Scan on gl_transaction_types gtty_1 (cost=0.00..2.27 rows=27 width=24) (actual time=0.001..0.009 rows=21 loops=46,568)

63. 139.704 139.704 ↓ 0.0 0 46,568

Index Scan using pk_ap_allocations on ap_allocations aa (cost=0.43..4.21 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=46,568)

  • Index Cond: ((gh_1.cid = cid) AND (cid = 15,667) AND (gh_1.reference_id = id))
  • Filter: (gl_transaction_type_id = gh_1.gl_transaction_type_id)
64. 93.136 93.136 ↓ 0.0 0 46,568

Index Scan using idx_ap_details on ap_details ad_1 (cost=0.43..4.03 rows=1 width=49) (actual time=0.002..0.002 rows=0 loops=46,568)

  • Index Cond: (aa.credit_ap_detail_id = id)
  • Filter: ((cid = 15,667) AND (aa.cid = cid))
65. 139.704 139.704 ↑ 1.0 1 46,568

Index Scan using idx_ap_details on ap_details ad1 (cost=0.43..4.02 rows=1 width=49) (actual time=0.003..0.003 rows=1 loops=46,568)

  • Index Cond: (id = COALESCE(gd_1.ap_detail_id, aa.charge_ap_detail_id))
  • Filter: (cid = COALESCE(gd_1.cid, aa.cid))
66. 6,007.272 6,007.272 ↑ 1.0 295 46,568

Seq Scan on property_units_temp pu (cost=0.00..5.69 rows=295 width=12) (actual time=0.004..0.129 rows=295 loops=46,568)

  • Filter: (cid = 15,667)
67. 2,095.560 2,095.560 ↑ 1.0 103 46,568

Seq Scan on property_buildings_temp pb (cost=0.00..2.29 rows=103 width=13) (actual time=0.002..0.045 rows=103 loops=46,568)

  • Filter: (cid = 15,667)
68. 93.136 93.136 ↓ 0.0 0 46,568

Index Scan using idx_ap_headers_id on ap_headers ah (cost=0.43..2.31 rows=1 width=58) (actual time=0.002..0.002 rows=0 loops=46,568)

  • Index Cond: (ad_1.ap_header_id = id)
  • Filter: ((ap_financial_status_type_id IS DISTINCT FROM 8) AND (cid = 15,667) AND (ad_1.cid = cid) AND (ad_1.gl_transaction_type_id = gl_transaction_type_id) AND (ad_1.post_month = post_month))
  • Rows Removed by Filter: 0
69. 0.000 0.000 ↓ 0.0 0 46,568

Index Scan using idx_ap_headers_id on ap_headers ah1 (cost=0.43..3.12 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=46,568)

  • Index Cond: (id = aa.lump_ap_header_id)
  • Filter: ((ap_financial_status_type_id IS DISTINCT FROM 8) AND (cid = 15,667) AND (cid = aa.cid))
70. 139.704 139.704 ↑ 1.0 1 46,568

Index Scan using idx_ap_headers_id on ap_headers ah2 (cost=0.43..0.52 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=46,568)

  • Index Cond: (ad1.ap_header_id = id)
  • Filter: ((ap_financial_status_type_id IS DISTINCT FROM 8) AND (ad1.cid = cid) AND (ad1.gl_transaction_type_id = gl_transaction_type_id) AND (ad1.post_month = post_month))
  • Rows Removed by Filter: 0
71. 0.000 0.000 ↓ 0.0 0 46,568

Index Scan using idx_lease_customers_id on lease_customers lc (cost=0.43..3.40 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=46,568)

  • Index Cond: (id = COALESCE(ah2.lease_customer_id, ah.lease_customer_id))
  • Filter: (cid = COALESCE(ah2.cid, ah.cid))
72. 0.000 0.000 ↓ 0.0 0 46,568

Index Scan using idx_customers_id on customers cust (cost=0.43..0.64 rows=1 width=39) (actual time=0.000..0.000 rows=0 loops=46,568)

  • Index Cond: (id = lc.customer_id)
  • Filter: (cid = lc.cid)
73. 93.136 93.136 ↓ 0.0 0 46,568

Index Scan using idx_ap_payments_id on ap_payments ap_ref (cost=0.43..2.76 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=46,568)

  • Index Cond: (id = ah.ap_payment_id)
  • Filter: ((cid = 15,667) AND (cid = ah.cid))
74. 0.000 0.000 ↓ 0.0 0 46,568

Index Scan using idx_ap_payments_id on ap_payments ap (cost=0.43..2.76 rows=1 width=17) (actual time=0.000..0.000 rows=0 loops=46,568)

  • Index Cond: (id = ah1.ap_payment_id)
  • Filter: ((cid = 15,667) AND (cid = ah1.cid))
75. 93.136 93.136 ↑ 1.0 1 46,568

Index Scan using idx_ap_payees_id on ap_payees app (cost=0.42..0.74 rows=1 width=31) (actual time=0.002..0.002 rows=1 loops=46,568)

  • Index Cond: (id = COALESCE(ah.ap_payee_id, ah2.ap_payee_id))
  • Filter: (cid = COALESCE(ah.cid, ah2.cid))
76. 93.136 93.136 ↓ 0.0 0 46,568

Index Scan using idx_ar_deposits_id on ar_deposits ad_2 (cost=0.42..1.39 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=46,568)

  • Index Cond: (gh_1.reference_id = id)
  • Filter: ((cid = 15,667) AND (gh_1.gl_transaction_type_id = gl_transaction_type_id))
77. 0.001 0.047 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 3" (cost=30.15..30.21 rows=1 width=580) (actual time=0.046..0.047 rows=0 loops=1)

78. 0.002 0.046 ↓ 0.0 0 1

GroupAggregate (cost=30.15..30.20 rows=1 width=496) (actual time=0.045..0.046 rows=0 loops=1)

  • Group Key: p.id, p.property_name, p.lookup_code, gat_2.grouping_gl_account_id, gat_2.formatted_account_number, gat_2.name, gat_2.gl_account
79. 0.013 0.044 ↓ 0.0 0 1

Sort (cost=30.15..30.16 rows=1 width=128) (actual time=0.044..0.044 rows=0 loops=1)

  • Sort Key: p.id, p.property_name, p.lookup_code, gat_2.grouping_gl_account_id, gat_2.formatted_account_number, gat_2.name, gat_2.gl_account
  • Sort Method: quicksort Memory: 25kB
80. 0.001 0.031 ↓ 0.0 0 1

Nested Loop (cost=5.89..30.14 rows=1 width=128) (actual time=0.030..0.031 rows=0 loops=1)

81. 0.001 0.030 ↓ 0.0 0 1

Nested Loop (cost=5.62..23.15 rows=1 width=67) (actual time=0.029..0.030 rows=0 loops=1)

  • Join Filter: (load_prop_2.property_id = p.id)
82. 0.011 0.029 ↓ 0.0 0 1

Hash Join (cost=5.33..14.83 rows=1 width=48) (actual time=0.028..0.029 rows=0 loops=1)

  • Hash Cond: (bbt.property_id = load_prop_2.property_id)
83. 0.002 0.005 ↓ 0.0 0 1

Bitmap Heap Scan on beg_bal_temp bbt (cost=4.20..13.67 rows=6 width=44) (actual time=0.005..0.005 rows=0 loops=1)

  • Recheck Cond: (cid = 15,667)
84. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on idx_beg_bal_temp_cid (cost=0.00..4.20 rows=6 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (cid = 15,667)
85. 0.005 0.013 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.013..0.013 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
86. 0.008 0.008 ↑ 1.0 6 1

Seq Scan on load_prop load_prop_2 (cost=0.00..1.06 rows=6 width=4) (actual time=0.005..0.008 rows=6 loops=1)

87. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_properties_id on properties p (cost=0.28..8.30 rows=1 width=31) (never executed)

  • Index Cond: (id = bbt.property_id)
  • Filter: (cid = 15,667)
88. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_temp_gat_gl_account_id on gat_temp gat_2 (cost=0.28..6.99 rows=1 width=77) (never executed)

  • Index Cond: (gl_account_id = bbt.gl_account_id)
  • Filter: ((cid = 15,667) AND (grouping_gl_account_id = ANY ('{281795,281796,278055,281797,281798,281799,281800,281801,282813,281802,287419,287420,284923,286710,286936,287435,286937,281803,278066,287589,281804,278075,284275,286486,286487,286488,286489,286490,286491,287438,281805,281806,281807,281808,281809,281810,281811,278074,281584,281812,281813,283734,283735,283774,283783,283899,283900,284273,281814,282237,281815,281816,281817,282815,281818,281819,281820,281821,281822,281823,281824,281825,281826,281827,281828,281829,281830,281831,281832,281833,281834,281835,281836,283687,283700,283776,283826,283827,283868,284186,284551,284552,284553,284565,284798,284909,284924,285478,285479,285482,285485,285494,285526,286480,286485,286511,286717,286725,286727,287433,281837,281838,287623,281839,281840,281841,281842,281843,284133,281844,281845,281851,281846,281852,281853,281848,281854,281849,281855,281850,281856,283820,284103,284104,284105,284106,284107,284108,284109,284110,285497,286513,286514,283825,284274,284393,285492,284796,284929,285484,285501,286498,286499,286500,286713,286965,286966,281857,281858,278072,278080,278079,278070,278073,281859,281860,281861,281862,281863,282301,282240,282302,282303,282304,282305,282306,282307,282308,282309,282310,282311,282312,282313,282241,282314,282315,282316,282317,282318,282319,282320,282321,282322,282323,282324,282325,282326,282327,284563,284564,284771,286747,281864,283627,283655,286741,286957,287620,287621,287622,281865,278076,281866,278065,281867,281868,281869,282287,281870,281871,278067,278081,281872,281873,281874,281875,281876,281877,281878,281879,281880,286731,287416,278077,278082,284908,278078,278048,278068,278069,282814,285514,286729,281881,281882,287595,278062,284116,278063,278049,281585,281883,282089,278051,281884,281885,281886,281887,281888,281889,282919,281890,278050,278052,278083,278071,281891,281892,281893,282207,281894,281895,281896,281897,281898,281899,281900,281901,281902,281903,283786,284555,284556,284557,284710,281904,278053,282288,278056,278058,278064,281905,278057,282286,278059,281906,281907,281908,281909,281910,281911,281912,281913,281914,278061,281915,281916,281917,281918,285535,281919,284916,281920,281921,284925,283838,283895,284825,284847,285496,281922,287641,281923,281924,281925,281926,281927,281928,281929,283689,281930,281931,281932,281933,281934,283672,281935,281936,283657,281937,281938,281939,281940,281941,281942,281943,286709,283733,286492,286493,286494,286495,286496,278054,281944,281945,281946,281947,281948,281949,281950,281951,282811,284088,281952,281953,281954,281955,281956,281957,281958,281959,281960,281961,281962,281963,281964,281965,281966,281967,281968,281969,281970,281971,281972,286482,281973,281974,281975,281976,281977,281978,281979,281980,281981,281982,281983,281984,281985,281986,281987,278060,283836,281988,281989,281990,281991,281992,281993,281994,281995,281996,281997,281998,281999,282000,282001,282002,282003,282004,282005,282006,282007,282008,282009,282010,282011,282012,282013,282298,282014,282016,282017,282018,282235,282015,282019,282020,282021,282208,282022,282023,282024,282025,282026,282027,282028,282029,282030,282031,282032,282236,283675,283676,283677,283678,283679,283680,283681,283894,286483,284917,282910,282056,282911,282049,282912,282913,282914,282915,282916,282917,282918,282058,282055,282920,283835,283837,283877,283878,283879,283880,283881,283882,283883,283884,283885,283886,283887,283888,283889,283890,283891,283892,284130,286630,286631,284118,282033,282034,282035,282036,282037,282038,282039,282812,282040,282205,282041,282042,282043,282044,282045,281847,282046,282047,282048,282050,282051,282052,282053,282054,282057,282059,282060,282061,282062,282063,282064,282065,282066,282067,283682,283686,283685,283684,283683,282068,282069,286716,283782,283785,283821,283840,282070,282071,282072,282073,282074,282075,282076,282077,282078,282079,282080,282081,282082,282083,282084,282085,282086,282087}'::integer[])))
Planning time : 25.465 ms