explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3RIU

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

GroupAggregate (cost=28,553.84..28,554.14 rows=2 width=1,586) (actual time=28,216.913..28,640.166 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. 974.697 28,265.135 ↓ 57,576.0 115,152 1

Sort (cost=28,553.84..28,553.85 rows=2 width=1,554) (actual time=28,216.892..28,265.135 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.830 27,290.438 ↓ 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,901.754..27,290.438 rows=115,152 loops=1)

  • Filter: (outer_details.hide_zero_beginning_balance_for_no_activities <> 1)
4. 243.410 27,188.608 ↓ 38,384.0 115,152 1

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

5. 176.735 26,945.198 ↓ 38,384.0 115,152 1

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

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

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

7. 241.309 26,599.272 ↓ 38,384.0 115,152 1

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

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

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

9. 92.832 26,235.390 ↓ 38,384.0 115,152 1

Append (cost=201.30..28,553.55 rows=3 width=1,390) (actual time=1.118..26,235.390 rows=115,152 loops=1)

10. 147.223 26,142.511 ↓ 57,576.0 115,152 1

Result (cost=201.30..28,523.33 rows=2 width=1,376) (actual time=1.117..26,142.511 rows=115,152 loops=1)

11. 96.625 25,995.288 ↓ 57,576.0 115,152 1

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

12. 1,050.929 6,192.177 ↓ 68,584.0 68,584 1

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

13. 160.000 5,004.080 ↓ 68,584.0 68,584 1

Nested Loop Left Join (cost=200.88..612.37 rows=1 width=1,079) (actual time=0.151..5,004.080 rows=68,584 loops=1)

14. 132.400 4,638.328 ↓ 68,584.0 68,584 1

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

15. 99.118 3,340.000 ↓ 68,584.0 68,584 1

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

16. 141.044 3,103.714 ↓ 68,584.0 68,584 1

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

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

Nested Loop Left Join (cost=199.73..597.96 rows=1 width=784) (actual time=0.108..2,756.918 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. 141.600 1,997.812 ↓ 68,584.0 68,584 1

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

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

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

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

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

21. 177.486 563.735 ↓ 9,797.7 68,584 1

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

22. 73.531 179.729 ↓ 2,294.7 68,840 1

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

23. 0.010 0.010 ↑ 1.0 6 1

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

24. 106.188 106.188 ↓ 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.248..17.698 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. 274.336 274.336 ↑ 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.004..0.004 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. 137.168 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.008..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. 137.168 411.504 ↓ 0.0 0 68,584

BitmapOr (cost=198.02..198.02 rows=1 width=0) (actual time=0.006..0.006 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. 137.168 137.168 ↑ 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.002..0.002 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. 57.290 19,706.486 ↓ 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.538..19,706.486 rows=46,568 loops=1)

39. 423.548 19,649.196 ↓ 46,568.0 46,568 1

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

40. 124.434 19,132.512 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=5.71..27,906.54 rows=1 width=949) (actual time=0.510..19,132.512 rows=46,568 loops=1)

41. 105.145 18,914.942 ↓ 46,568.0 46,568 1

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

42. 75.449 18,809.797 ↓ 46,568.0 46,568 1

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

43. 105.186 18,641.212 ↓ 46,568.0 46,568 1

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

44. 65.309 18,536.026 ↓ 46,568.0 46,568 1

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

45. 94.893 18,424.149 ↓ 46,568.0 46,568 1

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

46. 60.041 18,189.552 ↓ 46,568.0 46,568 1

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

47. 99.378 18,082.943 ↓ 46,568.0 46,568 1

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

48. 2,035.111 17,890.429 ↓ 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,890.429 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,611.406 13,713.190 ↓ 46,568.0 46,568 1

Nested Loop Left Join (cost=2.29..27,886.11 rows=1 width=866) (actual time=0.369..13,713.190 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. 101.480 2,094.512 ↓ 46,568.0 46,568 1

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

51. 92.293 1,853.328 ↓ 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,853.328 rows=46,568 loops=1)

52. 97.574 1,667.899 ↓ 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,667.899 rows=46,568 loops=1)

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

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

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

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

55. 54.607 203.490 ↓ 148.7 47,149 1

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

56. 3.488 6.467 ↓ 1.6 3,312 1

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

57. 1.323 1.323 ↓ 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.323 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. 235.745 235.745 ↑ 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.005..0.005 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,142.128 2,142.128 ↑ 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.046 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. 46.568 46.568 ↓ 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.001..0.001 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. 46.568 46.568 ↓ 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.001..0.001 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.047..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.046..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.014 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.030 ↓ 0.0 0 1

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

81. 0.001 0.029 ↓ 0.0 0 1

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

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

Hash Join (cost=5.33..14.83 rows=1 width=48) (actual time=0.028..0.028 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.004..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.660 ms