explain.depesz.com

PostgreSQL's explain analyze made readable

Result: igDy

Settings
# exclusive inclusive rows x rows loops node
1. 1,245.421 42,849.062 ↓ 2,510.9 502,185 1

GroupAggregate (cost=308,320.43..308,485.68 rows=200 width=1,586) (actual time=41,310.267..42,849.062 rows=502,185 loops=1)

  • Output: outer_details.is_beginning_balance, outer_details.property_id, outer_details.property_name, outer_details.lookup_code, outer_details.grouping_gl_account_id, outer_details.gl_account, outer_details.account_name, outer_details.account_number, outer_details.transaction_type, outer_details.transaction_type_id, outer_details.gl_transaction_type_id, outer_details.post_month, outer_details.post_date, outer_details.transaction_date, outer_details.gh_header_id, outer_details.header_number, outer_details.reference_id, outer_details.reference, outer_details.gl_detail_id, 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, sum(outer_details.debit), sum(outer_details.credit), (sum(outer_details.debit) + sum(outer_details.credit)), 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, sum(outer_details.row_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
  • Group Key: outer_details.property_name, outer_details.account_number, outer_details.is_beginning_balance, outer_details.gl_account, outer_details.post_month, outer_details.post_date, outer_details.transaction_date, outer_details.gl_detail_id, outer_details.property_id, outer_details.lookup_code, outer_details.grouping_gl_account_id, 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
  • Buffers: shared hit=12695056 read=9084, local hit=927007 read=9, temp read=66530 written=66537
2. 10,646.489 41,603.641 ↓ 408.3 510,434 1

Sort (cost=308,320.43..308,323.56 rows=1,250 width=1,554) (actual time=41,310.249..41,603.641 rows=510,434 loops=1)

  • Output: outer_details.is_beginning_balance, outer_details.property_id, outer_details.property_name, outer_details.lookup_code, outer_details.grouping_gl_account_id, outer_details.gl_account, outer_details.account_name, outer_details.account_number, outer_details.transaction_type, outer_details.transaction_type_id, outer_details.gl_transaction_type_id, outer_details.post_month, outer_details.post_date, outer_details.transaction_date, outer_details.gh_header_id, outer_details.header_number, outer_details.reference_id, outer_details.reference, outer_details.gl_detail_id, 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, outer_details.debit, outer_details.credit, outer_details.row_balance
  • Sort Key: outer_details.property_name, outer_details.account_number, outer_details.is_beginning_balance DESC, outer_details.gl_account, outer_details.post_month, outer_details.post_date, outer_details.transaction_date, outer_details.gl_detail_id, outer_details.property_id, outer_details.lookup_code, outer_details.grouping_gl_account_id, 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: external merge Disk: 188608kB
  • Buffers: shared hit=12695056 read=9084, local hit=927007 read=9, temp read=66530 written=66537
3. 152.145 30,957.152 ↓ 408.3 510,434 1

Subquery Scan on outer_details (cost=308,143.10..308,256.14 rows=1,250 width=1,554) (actual time=29,694.064..30,957.152 rows=510,434 loops=1)

  • Output: outer_details.is_beginning_balance, outer_details.property_id, outer_details.property_name, outer_details.lookup_code, outer_details.grouping_gl_account_id, outer_details.gl_account, outer_details.account_name, outer_details.account_number, outer_details.transaction_type, outer_details.transaction_type_id, outer_details.gl_transaction_type_id, outer_details.post_month, outer_details.post_date, outer_details.transaction_date, outer_details.gh_header_id, outer_details.header_number, outer_details.reference_id, outer_details.reference, outer_details.gl_detail_id, 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, outer_details.debit, outer_details.credit, outer_details.row_balance
  • Filter: (outer_details.hide_zero_beginning_balance_for_no_activities <> 1)
  • Rows Removed by Filter: 317
  • Buffers: shared hit=12695045 read=9084, local hit=927007 read=9, temp read=42954 written=42958
4. 961.226 30,805.007 ↓ 406.6 510,751 1

WindowAgg (cost=308,143.10..308,215.32 rows=1,256 width=1,590) (actual time=29,694.061..30,805.007 rows=510,751 loops=1)

  • Output: details.is_beginning_balance, details.property_id, details.property_name, details.lookup_code, details.grouping_gl_account_id, details.gl_account, details.account_name, details.account_number, details.post_month, details.transaction_type, details.transaction_type_id, details.gl_transaction_type_id, details.post_date, details.transaction_date, details.vendor_id, details.resident, details.display_number, details.gh_header_id, details.header_number, details.reference_id, details.reference, details.gl_detail_id, details.transaction_id, details.memo, details.details, details.ar_code_type_id, details.ar_trigger_id, details.ar_code_id, details.ar_transaction_payment_id, NULL::integer, details.ap_payment_id, details.ar_payment_id, details.art_id, details.debit, details.credit, NULL::numeric, details.bldg_unit, details.lease_id, details.occupancy_type_id, details.customer_id, details.beginning_balance, details.row_balance, CASE WHEN ((details.is_beginning_balance = 1) AND (details.beginning_balance = '0'::numeric)) THEN (count(details.grouping_gl_account_id) OVER (?)) ELSE '0'::bigint END, min(details.debit) OVER (?), min(details.credit) OVER (?), min(details.beginning_balance) OVER (?), max(details.debit) OVER (?), max(details.credit) OVER (?), max(details.beginning_balance) OVER (?)
  • Buffers: shared hit=12695045 read=9084, local hit=927007 read=9, temp read=42954 written=42958
5. 1,015.946 29,843.781 ↓ 406.6 510,751 1

Sort (cost=308,143.10..308,146.24 rows=1,256 width=1,362) (actual time=29,694.042..29,843.781 rows=510,751 loops=1)

  • Output: details.property_id, details.grouping_gl_account_id, details.gl_account, details.is_beginning_balance, details.property_name, details.lookup_code, details.account_name, details.account_number, details.post_month, details.transaction_type, details.transaction_type_id, details.gl_transaction_type_id, details.post_date, details.transaction_date, details.vendor_id, details.resident, details.display_number, details.gh_header_id, details.header_number, details.reference_id, details.reference, details.gl_detail_id, details.transaction_id, details.memo, details.details, details.ar_code_type_id, details.ar_trigger_id, details.ar_code_id, details.ar_transaction_payment_id, details.ap_payment_id, details.ar_payment_id, details.art_id, details.debit, details.credit, details.bldg_unit, details.lease_id, details.occupancy_type_id, details.customer_id, details.beginning_balance, details.row_balance, (count(details.grouping_gl_account_id) OVER (?))
  • Sort Key: details.property_id, details.gl_account
  • Sort Method: external merge Disk: 174568kB
  • Buffers: shared hit=12695045 read=9084, local hit=927007 read=9, temp read=42954 written=42958
6. 409.860 28,827.835 ↓ 406.6 510,751 1

WindowAgg (cost=308,028.21..308,078.45 rows=1,256 width=1,362) (actual time=28,293.611..28,827.835 rows=510,751 loops=1)

  • Output: details.property_id, details.grouping_gl_account_id, details.gl_account, details.is_beginning_balance, details.property_name, details.lookup_code, details.account_name, details.account_number, details.post_month, details.transaction_type, details.transaction_type_id, details.gl_transaction_type_id, details.post_date, details.transaction_date, details.vendor_id, details.resident, details.display_number, details.gh_header_id, details.header_number, details.reference_id, details.reference, details.gl_detail_id, details.transaction_id, details.memo, details.details, details.ar_code_type_id, details.ar_trigger_id, details.ar_code_id, details.ar_transaction_payment_id, details.ap_payment_id, details.ar_payment_id, details.art_id, details.debit, details.credit, details.bldg_unit, details.lease_id, details.occupancy_type_id, details.customer_id, details.beginning_balance, details.row_balance, count(details.grouping_gl_account_id) OVER (?)
  • Buffers: shared hit=12695045 read=9084, local hit=927007 read=9, temp read=21133 written=21135
7. 1,306.549 28,417.975 ↓ 406.6 510,751 1

Sort (cost=308,028.21..308,031.35 rows=1,256 width=1,354) (actual time=28,293.599..28,417.975 rows=510,751 loops=1)

  • Output: details.property_id, details.grouping_gl_account_id, details.gl_account, details.is_beginning_balance, details.property_name, details.lookup_code, details.account_name, details.account_number, details.post_month, details.transaction_type, details.transaction_type_id, details.gl_transaction_type_id, details.post_date, details.transaction_date, details.vendor_id, details.resident, details.display_number, details.gh_header_id, details.header_number, details.reference_id, details.reference, details.gl_detail_id, details.transaction_id, details.memo, details.details, details.ar_code_type_id, details.ar_trigger_id, details.ar_code_id, details.ar_transaction_payment_id, details.ap_payment_id, details.ar_payment_id, details.art_id, details.debit, details.credit, details.bldg_unit, details.lease_id, details.occupancy_type_id, details.customer_id, details.beginning_balance, details.row_balance
  • Sort Key: details.property_id, details.grouping_gl_account_id
  • Sort Method: external merge Disk: 169064kB
  • Buffers: shared hit=12695045 read=9084, local hit=927007 read=9, temp read=21133 written=21135
8. 175.242 27,111.426 ↓ 406.6 510,751 1

Subquery Scan on details (cost=1,848.04..307,963.56 rows=1,256 width=1,354) (actual time=1.730..27,111.426 rows=510,751 loops=1)

  • Output: details.property_id, details.grouping_gl_account_id, details.gl_account, details.is_beginning_balance, details.property_name, details.lookup_code, details.account_name, details.account_number, details.post_month, details.transaction_type, details.transaction_type_id, details.gl_transaction_type_id, details.post_date, details.transaction_date, details.vendor_id, details.resident, details.display_number, details.gh_header_id, details.header_number, details.reference_id, details.reference, details.gl_detail_id, details.transaction_id, details.memo, details.details, details.ar_code_type_id, details.ar_trigger_id, details.ar_code_id, details.ar_transaction_payment_id, details.ap_payment_id, details.ar_payment_id, details.art_id, details.debit, details.credit, details.bldg_unit, details.lease_id, details.occupancy_type_id, details.customer_id, details.beginning_balance, details.row_balance
  • Buffers: shared hit=12695045 read=9084, local hit=927007 read=9
9. 67.489 26,936.184 ↓ 406.6 510,751 1

Append (cost=1,848.04..307,925.88 rows=1,256 width=1,390) (actual time=1.729..26,936.184 rows=510,751 loops=1)

  • Buffers: shared hit=12695045 read=9084, local hit=927007 read=9
10. 241.110 26,862.080 ↓ 535.7 509,497 1

Result (cost=1,848.04..307,728.21 rows=951 width=1,376) (actual time=1.728..26,862.080 rows=509,497 loops=1)

  • Output: (0), load_prop.property_id, load_prop.property_name, load_prop.lookup_code, gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gd.post_month, (concat(gtty.description, CASE WHEN ((gh.gl_transaction_type_id = 1) AND (gh.gl_header_status_type_id = 2)) THEN '- Unposted'::text WHEN ((gh.gl_transaction_type_id = 1) AND (gh.gl_header_status_type_id = 3) AND (gh.id > gh.offsetting_gl_header_id)) THEN ' Reversal'::text ELSE NULL::text END)), (NULL::text), gh.gl_transaction_type_id, ((gh.transaction_datetime)::date), gh.post_date, (NULL::integer), (CASE WHEN (gh.gl_transaction_type_id = ANY ('{2,3,4,5}'::integer[])) THEN NULL::character varying ELSE func_format_customer_name(cl.name_first, cl.name_last, cl.company_name) END), cl.display_number, (CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gh.id END), (CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gh.header_number END), (CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gh.reference_id END), (CASE WHEN (gh.header_number IS NOT NULL) THEN ((('JE #'::text || (COALESCE(gh.header_number, 0))::text) || CASE WHEN (gh.reference IS NOT NULL) THEN (' - '::text || (gh.reference)::text) ELSE ''::text END))::character varying ELSE gh.reference END), (CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gd.id END), (CASE WHEN (gh.gl_transaction_type_id = 1) THEN gh.id WHEN (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[])) THEN NULL::integer WHEN (gh.gl_transaction_type_id = ANY ('{7,8,13,14,18,23,24}'::integer[])) THEN COALESCE(ara.charge_ar_transaction_id, art.id) ELSE gh.reference_id END), ((CASE WHEN (gh.gl_transaction_type_id = ANY ('{15,16}'::integer[])) THEN ((util_get_translated('deposit_memo'::text, (ad.deposit_memo)::text, ad.details, NULL::text, false)))::character varying WHEN (gh.gl_transaction_type_id = ANY ('{7,13,14}'::integer[])) THEN (COALESCE(get_ar_transaction_memo(art1.details, att.name, (ac.name)::character varying), (art1.memo)::text))::character varying WHEN (art.id IS NOT NULL) THEN (ac.name)::character varying WHEN (gh.gl_transaction_type_id = 1) THEN gd.memo WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::character varying ELSE gh.memo END)::text), art1.details, art1.ar_code_type_id, art1.ar_trigger_id, art1.ar_code_id, art1.ar_payment_id, (NULL::integer), (NULL::integer), art.ar_payment_id, (COALESCE(ara.charge_ar_transaction_id, art.id)), (CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE 0.00 END), (CASE WHEN (gd.amount < '0'::numeric) THEN gd.amount ELSE 0.00 END), ((CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE 0.00 END + CASE WHEN (gd.amount < '0'::numeric) THEN gd.amount ELSE 0.00 END)), (CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::character varying ELSE func_format_unit_number(NULL::character varying, NULL::character varying, cl.building_name, cl.unit_number_cache) END), (CASE WHEN (gat.gl_account_type_id = 101) THEN COALESCE(art.lease_id, gd.lease_id) WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE COALESCE(art.lease_id, gd.lease_id) END), (CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE cl.occupancy_type_id END), (CASE WHEN (gat.gl_account_type_id = 101) THEN cl.primary_customer_id WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE cl.primary_customer_id END), (0), gd.amount
  • Buffers: shared hit=12695017 read=9084, local hit=923211 read=4
11. 70.763 26,620.970 ↓ 535.7 509,497 1

Append (cost=1,848.04..307,697.31 rows=951 width=1,348) (actual time=1.727..26,620.970 rows=509,497 loops=1)

  • Buffers: shared hit=12695017 read=9084, local hit=923211 read=4
12. 5,914.285 24,842.661 ↓ 2,613.1 459,904 1

Nested Loop Left Join (cost=1,848.04..248,974.32 rows=176 width=641) (actual time=1.726..24,842.661 rows=459,904 loops=1)

  • Output: 0, load_prop.property_id, load_prop.property_name, load_prop.lookup_code, gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gd.post_month, concat(gtty.description, CASE WHEN ((gh.gl_transaction_type_id = 1) AND (gh.gl_header_status_type_id = 2)) THEN '- Unposted'::text WHEN ((gh.gl_transaction_type_id = 1) AND (gh.gl_header_status_type_id = 3) AND (gh.id > gh.offsetting_gl_header_id)) THEN ' Reversal'::text ELSE NULL::text END), NULL::text, gh.gl_transaction_type_id, (gh.transaction_datetime)::date, gh.post_date, NULL::integer, CASE WHEN (gh.gl_transaction_type_id = ANY ('{2,3,4,5}'::integer[])) THEN NULL::character varying ELSE func_format_customer_name(cl.name_first, cl.name_last, cl.company_name) END, cl.display_number, CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gh.id END, CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gh.header_number END, CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gh.reference_id END, CASE WHEN (gh.header_number IS NOT NULL) THEN ((('JE #'::text || (COALESCE(gh.header_number, 0))::text) || CASE WHEN (gh.reference IS NOT NULL) THEN (' - '::text || (gh.reference)::text) ELSE ''::text END))::character varying ELSE gh.reference END, CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gd.id END, CASE WHEN (gh.gl_transaction_type_id = 1) THEN gh.id WHEN (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[])) THEN NULL::integer WHEN (gh.gl_transaction_type_id = ANY ('{7,8,13,14,18,23,24}'::integer[])) THEN COALESCE(ara.charge_ar_transaction_id, art.id) ELSE gh.reference_id END, (CASE WHEN (gh.gl_transaction_type_id = ANY ('{15,16}'::integer[])) THEN ((util_get_translated('deposit_memo'::text, (ad.deposit_memo)::text, ad.details, NULL::text, false)))::character varying WHEN (gh.gl_transaction_type_id = ANY ('{7,13,14}'::integer[])) THEN (COALESCE(get_ar_transaction_memo(art1.details, att.name, (ac.name)::character varying), (art1.memo)::text))::character varying WHEN (art.id IS NOT NULL) THEN (ac.name)::character varying WHEN (gh.gl_transaction_type_id = 1) THEN gd.memo WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::character varying ELSE gh.memo END)::text, art1.details, art1.ar_code_type_id, art1.ar_trigger_id, art1.ar_code_id, art1.ar_payment_id, NULL::integer, NULL::integer, art.ar_payment_id, COALESCE(ara.charge_ar_transaction_id, art.id), CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE 0.00 END, CASE WHEN (gd.amount < '0'::numeric) THEN gd.amount ELSE 0.00 END, (CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE 0.00 END + CASE WHEN (gd.amount < '0'::numeric) THEN gd.amount ELSE 0.00 END), CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::character varying ELSE func_format_unit_number(NULL::character varying, NULL::character varying, cl.building_name, cl.unit_number_cache) END, CASE WHEN (gat.gl_account_type_id = 101) THEN COALESCE(art.lease_id, gd.lease_id) WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE COALESCE(art.lease_id, gd.lease_id) END, CASE WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE cl.occupancy_type_id END, CASE WHEN (gat.gl_account_type_id = 101) THEN cl.primary_customer_id WHEN ((gat.gl_account_type_id = 101) AND (gh.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE cl.primary_customer_id END, 0, gd.amount
  • Inner Unique: true
  • Buffers: shared hit=11474879 read=7083, local hit=919817 read=1
13. 332.058 18,468.472 ↓ 2,613.1 459,904 1

Nested Loop Left Join (cost=1,847.75..248,710.51 rows=176 width=591) (actual time=1.248..18,468.472 rows=459,904 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, load_prop.property_id, load_prop.property_name, load_prop.lookup_code, gh.gl_transaction_type_id, gh.gl_header_status_type_id, gh.id, gh.offsetting_gl_header_id, gh.transaction_datetime, gh.post_date, gh.header_number, gh.reference_id, gh.reference, gh.memo, gtty.description, gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gat.gl_account_type_id, ara.charge_ar_transaction_id, art.id, art.ar_payment_id, art.lease_id, art1.details, art1.memo, art1.ar_code_type_id, art1.ar_trigger_id, art1.ar_code_id, art1.ar_payment_id, att.name, ac.name, cl.name_first, cl.name_last, cl.company_name, cl.display_number, cl.building_name, cl.unit_number_cache, cl.occupancy_type_id, cl.primary_customer_id
  • Inner Unique: true
  • Buffers: shared hit=10554968 read=7083, local hit=919817 read=1
14. 209.159 16,756.702 ↓ 2,613.1 459,904 1

Nested Loop Left Join (cost=1,847.32..248,564.35 rows=176 width=540) (actual time=1.236..16,756.702 rows=459,904 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.cid, load_prop.property_id, load_prop.property_name, load_prop.lookup_code, gh.gl_transaction_type_id, gh.gl_header_status_type_id, gh.id, gh.offsetting_gl_header_id, gh.transaction_datetime, gh.post_date, gh.header_number, gh.reference_id, gh.reference, gh.memo, gtty.description, gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gat.gl_account_type_id, ara.charge_ar_transaction_id, art.id, art.ar_payment_id, art.lease_id, art1.details, art1.memo, art1.ar_code_type_id, art1.ar_trigger_id, art1.ar_code_id, art1.ar_payment_id, att.name, ac.name
  • Join Filter: (gh.cid = art1.cid)
  • Buffers: shared hit=8713944 read=7083, local hit=919817 read=1
15. 544.286 6,889.559 ↓ 2,613.1 459,904 1

Nested Loop Left Join (cost=1,846.60..247,608.68 rows=176 width=303) (actual time=1.202..6,889.559 rows=459,904 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.cid, load_prop.property_id, load_prop.property_name, load_prop.lookup_code, gh.gl_transaction_type_id, gh.gl_header_status_type_id, gh.id, gh.offsetting_gl_header_id, gh.transaction_datetime, gh.post_date, gh.header_number, gh.reference_id, gh.reference, gh.memo, gh.cid, gtty.description, gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gat.gl_account_type_id, ara.charge_ar_transaction_id, art.id, art.ar_payment_id, art.lease_id
  • Join Filter: (gh.gl_transaction_type_id = ANY ('{7,8,9,17}'::integer[]))
  • Rows Removed by Join Filter: 213228
  • Buffers: shared hit=5951995 read=7040, local hit=10
16. 457.893 4,045.753 ↓ 2,613.1 459,904 1

Nested Loop Left Join (cost=472.22..5,527.69 rows=176 width=291) (actual time=1.181..4,045.753 rows=459,904 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.cid, load_prop.property_id, load_prop.property_name, load_prop.lookup_code, gh.gl_transaction_type_id, gh.gl_header_status_type_id, gh.id, gh.offsetting_gl_header_id, gh.transaction_datetime, gh.post_date, gh.header_number, gh.reference_id, gh.reference, gh.memo, gh.cid, gtty.description, gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gat.gl_account_type_id, ara.charge_ar_transaction_id, ara.credit_ar_transaction_id
  • Inner Unique: true
  • Join Filter: (gh.gl_transaction_type_id = ANY ('{13,14,18,23,24}'::integer[]))
  • Buffers: shared hit=3471896 read=6831, local hit=10
17. 212.597 2,668.052 ↓ 2,613.1 459,904 1

Hash Join (cost=471.78..5,386.35 rows=176 width=283) (actual time=1.171..2,668.052 rows=459,904 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.cid, load_prop.property_id, load_prop.property_name, load_prop.lookup_code, gh.gl_transaction_type_id, gh.gl_header_status_type_id, gh.id, gh.offsetting_gl_header_id, gh.transaction_datetime, gh.post_date, gh.header_number, gh.reference_id, gh.reference, gh.memo, gh.cid, gtty.description, gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gat.gl_account_type_id
  • Inner Unique: true
  • Hash Cond: (gh.gl_transaction_type_id = gtty.id)
  • Buffers: shared hit=1884185 read=2484, local hit=10
18. 343.040 2,455.436 ↓ 2,613.1 459,904 1

Nested Loop (cost=468.10..5,381.99 rows=176 width=263) (actual time=1.146..2,455.436 rows=459,904 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.cid, load_prop.property_id, load_prop.property_name, load_prop.lookup_code, gh.gl_transaction_type_id, gh.gl_header_status_type_id, gh.id, gh.offsetting_gl_header_id, gh.transaction_datetime, gh.post_date, gh.header_number, gh.reference_id, gh.reference, gh.memo, gh.cid, gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gat.gl_account_type_id
  • Inner Unique: true
  • Buffers: shared hit=1884183 read=2484, local hit=10
19. 213.169 732.684 ↓ 311.0 459,904 1

Hash Join (cost=467.66..2,866.64 rows=1,479 width=166) (actual time=1.130..732.684 rows=459,904 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.cid, gd.gl_header_id, load_prop.property_id, load_prop.property_name, load_prop.lookup_code, gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gat.gl_account_type_id
  • Hash Cond: (gd.accrual_gl_account_id = gat.gl_account_id)
  • Buffers: shared hit=43799 read=2010, local hit=10
20. 91.377 518.448 ↓ 200.4 463,080 1

Nested Loop (cost=0.56..2,346.50 rows=2,311 width=101) (actual time=0.059..518.448 rows=463,080 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.cid, gd.gl_header_id, gd.accrual_gl_account_id, load_prop.property_id, load_prop.property_name, load_prop.lookup_code
  • Buffers: shared hit=43799 read=2010, local hit=1
21. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on pg_temp_79.load_prop (cost=0.00..1.12 rows=4 width=25) (actual time=0.004..0.007 rows=4 loops=1)

  • Output: load_prop.cid, load_prop.property_id, load_prop.is_student_property, load_prop.property_name, load_prop.lookup_code, load_prop.property_type_id, load_prop.occupancy_type_ids
  • Buffers: local hit=1
22. 427.064 427.064 ↓ 200.3 115,770 4

Index Scan using idx_gl_details_cid_property_id_post_month on public.gl_details gd (cost=0.56..569.01 rows=578 width=80) (actual time=0.077..106.766 rows=115,770 loops=4)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.property_id, gd.cid, gd.gl_header_id, gd.accrual_gl_account_id
  • Index Cond: ((gd.cid = 14181) AND (gd.property_id = load_prop.property_id) AND (gd.property_id = ANY ('{503992,503993,503999,504008}'::integer[])) AND (gd.post_month >= '01/01/2019'::date) AND (gd.post_month <= '09/01/2019'::date))
  • Filter: (gd.gl_transaction_type_id = ANY ('{13,14,18,23,24,7,8,9,17}'::integer[]))
  • Rows Removed by Filter: 12481
  • Buffers: shared hit=43799 read=2010
23. 0.130 1.067 ↑ 1.0 571 1

Hash (cost=448.54..448.54 rows=571 width=77) (actual time=1.066..1.067 rows=571 loops=1)

  • Output: gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gat.gl_account_type_id, gat.cid, gat.gl_account_id
  • Buckets: 1024 Batches: 1 Memory Usage: 70kB
  • Buffers: local hit=9
24. 0.937 0.937 ↑ 1.0 571 1

Seq Scan on pg_temp_79.gat_temp gat (cost=0.00..448.54 rows=571 width=77) (actual time=0.006..0.937 rows=571 loops=1)

  • Output: gat.grouping_gl_account_id, gat.gl_account, gat.name, gat.formatted_account_number, gat.gl_account_type_id, gat.cid, gat.gl_account_id
  • Filter: ((gat.cid = 14181) AND (gat.grouping_gl_account_id = ANY ('{276755,277181,276723,282512,277994,282520,277182,276734,276746,276756,276757,276747,276758,276759,277856,305890,276760,277858,276761,276762,305887,276763,276764,276765,276766,282511,276752,276744,276751,276767,276768,276769,276770,279436,282510,276771,276772,276773,276774,276775,276776,276777,281803,276778,276779,281804,276780,276781,276782,281805,276783,281806,276784,281807,276785,281808,276786,281809,305917,305918,276787,281810,276788,281811,276789,281812,276790,281813,276791,281814,276792,281815,276793,276794,282471,305892,305891,305919,305920,305924,305925,306536,306537,305894,305893,276795,281816,276796,281817,276797,281818,276798,281819,276799,281820,276800,281821,276801,281822,276802,276803,281823,276804,281774,276805,281839,276806,281824,276807,281825,276808,281826,276809,281832,276810,281833,276811,281834,276812,281835,276813,276814,281836,276815,281837,276816,281838,276817,281827,276818,281828,276819,276820,281829,276821,282513,276822,276823,281830,276824,282521,276825,276826,276827,276828,276829,276830,276831,276832,276833,282524,276834,282522,276835,305868,276836,276837,276838,276839,276840,306544,276841,276842,276843,276844,276845,306545,277861,276745,276846,276847,276848,276719,276849,276738,276750,279435,276754,278169,276733,276851,276749,276852,276853,276854,276855,276856,276857,277823,276858,276859,276860,276861,276862,276863,276864,276865,276866,305856,276867,276868,276869,276870,276871,276872,276873,276748,276874,276742,276876,276753,276878,276879,276880,276881,277862,276882,276740,276739,276730,276731,276883,276736,276735,276737,276884,276885,276886,276887,276888,276889,276890,276891,277866,277867,276892,276893,276894,276895,276896,276897,276898,276899,276900,276901,276902,276903,276904,276905,276906,276907,276908,276909,276910,276911,276912,276913,276914,276915,276916,276917,276918,276919,276920,276921,276922,276923,276924,276925,276926,276927,276928,276929,276930,276931,276932,276933,276934,276935,276936,276937,276938,276939,276940,276941,276727,276942,276943,276944,276945,276946,276947,276948,276949,276732,276726,276724,276950,276951,276952,276953,276954,276725,276955,276956,276957,276958,276959,276960,276961,276721,276962,276963,276964,276965,276966,276967,276968,276969,276970,276971,276972,276973,276729,276974,276975,276976,276977,276978,276979,276980,276981,276982,276983,276720,276741,276743,276728,276985,276986,276987,276988,276989,276990,276991,276992,276993,276994,276995,276996,276997,276998,276999,277000,277001,277002,277003,277004,277005,277006,277007,277008,282036,277009,277010,277011,277012,277013,277014,277015,277016,277017,277868,277018,277019,277869,277179,277180,277020,277021,277022,277023,277024,277025,277026,277027,277870,277028,276722,277029,277871,277030,305849,277031,277032,277033,277034,277035,277036,277037,277038,277039,277040,277041,277042,277043,277044,277045,277046,277047,277048,277049,277050,277051,277052,277053,277054,277055,277056,277057,277058,277059,277060,277061,277062,277063,277064,277065,277066,277067,277068,277069,277070,277071,277072,277073,277074,277075,277076,277077,277078,277079,277080,277081,277082,277083,277084,277085,277086,277087,277088,277089,277090,277091,277092,277093,277094,277095,277096,277097,277098,277099,277100,277101,277102,277103,277872,277104,277105,277106,277107,277108,277109,277110,277111,276984,277112,282523,277113,277114,281776,281777,281778,281779,281780,281781,281782,281783,281784,281785,281786,281787,281788,281789,281790,281791,281792,281793,281794,305866,277115,277116,277117,277118,277857,277873,277874,277875,277119,277120,277121,277122,277123,277124,277125,277126,277127,277128,277129,277130,277131,277132,277133,277134,277135,277136,277137,277138,277139,277140,305915,277141,277142,277143,277144,277145,277146,277147,277148,277149,277150,277151,277152,277153,277154,277155,277156,277157,277158,277159,277160,277161,277162,277163,277164,277165,277166,277167,277168,277169,277170,277171,277172,277173,277174,282536,277175,277176,277177,305867,277178}'::integer[])))
  • Rows Removed by Filter: 18
  • Buffers: local hit=9
25. 1,379.712 1,379.712 ↑ 1.0 1 459,904

Index Scan using pk_gl_headers on public.gl_headers gh (cost=0.43..1.70 rows=1 width=101) (actual time=0.003..0.003 rows=1 loops=459,904)

  • Output: gh.id, gh.cid, gh.gl_header_type_id, gh.gl_transaction_type_id, gh.gl_header_status_type_id, gh.gl_book_id, gh.reference_id, gh.property_id, gh.lease_id, gh.offsetting_gl_header_id, gh.template_gl_header_id, gh.close_period_id, gh.bulk_property_id, gh.bulk_property_unit_id, gh.bulk_company_department_id, gh.bulk_gl_dimension_id, gh.bulk_job_phase_id, gh.bulk_ap_contract_id, gh.bulk_property_building_id, gh.gl_header_schedule_id, gh.ap_routing_tag_id, gh.reclass_gl_header_id, gh.header_number, gh.transaction_datetime, gh.post_month, gh.reverse_post_month, gh.post_date, gh.reference, gh.memo, gh.external_url, gh.template_name, gh.bulk_is_confidential, gh.is_template, gh.is_reverse, gh.updated_by, gh.updated_on, gh.created_by, gh.created_on
  • Index Cond: ((gh.cid = 14181) AND (gh.id = gd.gl_header_id))
  • Filter: ((NOT gh.is_template) AND (gh.gl_book_id = 103) AND (gh.gl_header_status_type_id <> ALL ('{2,4,5,6}'::integer[])))
  • Buffers: shared hit=1840384 read=474
26. 0.006 0.019 ↑ 1.0 27 1

Hash (cost=2.81..2.81 rows=27 width=24) (actual time=0.019..0.019 rows=27 loops=1)

  • Output: gtty.description, gtty.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=2
27. 0.013 0.013 ↑ 1.0 27 1

Seq Scan on public.gl_transaction_types gtty (cost=0.00..2.81 rows=27 width=24) (actual time=0.005..0.013 rows=27 loops=1)

  • Output: gtty.description, gtty.id
  • Buffers: shared hit=2
28. 919.808 919.808 ↓ 0.0 0 459,904

Index Scan using pk_ar_allocations on public.ar_allocations ara (cost=0.43..0.77 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=459,904)

  • Output: ara.id, ara.cid, ara.property_id, ara.lease_id, ara.period_id, ara.gl_transaction_type_id, ara.charge_ar_transaction_id, ara.credit_ar_transaction_id, ara.accrual_debit_gl_account_id, ara.accrual_credit_gl_account_id, ara.cash_debit_gl_account_id, ara.cash_credit_gl_account_id, ara.origin_ar_allocation_id, ara.ar_process_id, ara.ap_allocation_id, ara.allocation_datetime, ara.allocation_amount, ara.reporting_post_date, ara.post_date, ara.post_month, ara.charge_post_to_cash, ara.credit_post_to_cash, ara.is_deleted, ara.is_posted, ara.is_initial_import, ara.updated_by, ara.updated_on, ara.created_by, ara.created_on
  • Index Cond: ((ara.cid = gh.cid) AND (ara.cid = 14181) AND (ara.id = gh.reference_id))
  • Buffers: shared hit=1587711 read=4347
29. 919.808 2,299.520 ↑ 1.0 1 459,904

Bitmap Heap Scan on public.ar_transactions art (cost=1,374.39..1,375.43 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=459,904)

  • Output: art.id, art.cid, art.property_id, art.lease_id, art.lease_interval_id, art.customer_id, art.ar_code_type_id, art.ar_code_id, art.ar_origin_id, art.ar_origin_reference_id, art.ar_origin_object_id, art.ar_trigger_id, art.ar_process_id, art.ar_payment_id, art.ar_payment_split_id, art.accrual_debit_gl_account_id, art.accrual_credit_gl_account_id, art.cash_debit_gl_account_id, art.cash_credit_gl_account_id, art.ar_transaction_id, art.ap_allocation_id, art.ap_detail_id, art.scheduled_charge_id, art.period_id, art.dependent_ar_transaction_id, art.remote_primary_key, art.transaction_datetime, art.transaction_amount, art.transaction_amount_due, art.initial_amount_due, art.reporting_post_date, art.post_date, art.post_month, art.memo, art.internal_memo, art.is_payment_in_kind, art.is_deposit_credit, art.is_reversal, art.is_temporary, art.is_posted, art.is_initial_import, art.has_dependencies, art.is_deleted, art.updated_by, art.updated_on, art.created_by, art.created_on, art.gl_dimension_id, art.details
  • Recheck Cond: (((art.cid = 14181) AND (art.id = gh.reference_id)) OR ((art.cid = 14181) AND (art.id = ara.credit_ar_transaction_id)))
  • Filter: (gh.cid = art.cid)
  • Heap Blocks: exact=461348
  • Buffers: shared hit=2480099 read=209
30. 0.000 1,379.712 ↓ 0.0 0 459,904

BitmapOr (cost=1,374.39..1,374.39 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=459,904)

  • Buffers: shared hit=2018954 read=6
31. 919.808 919.808 ↑ 1.0 1 459,904

Bitmap Index Scan on pk_ar_transactions (cost=0.00..0.49 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=459,904)

  • Index Cond: ((art.cid = 14181) AND (art.id = gh.reference_id))
  • Buffers: shared hit=1382428
32. 459.904 459.904 ↓ 0.0 0 459,904

Bitmap Index Scan on pk_ar_transactions (cost=0.00..0.48 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=459,904)

  • Index Cond: ((art.cid = 14181) AND (art.id = ara.credit_ar_transaction_id))
  • Buffers: shared hit=636526 read=6
33. 459.904 9,657.984 ↑ 1.0 1 459,904

Nested Loop Left Join (cost=0.72..5.40 rows=1 width=249) (actual time=0.014..0.021 rows=1 loops=459,904)

  • Output: art1.details, art1.memo, art1.ar_code_type_id, art1.ar_trigger_id, art1.ar_code_id, art1.ar_payment_id, art1.cid, art1.id, att.name, ac.name
  • Buffers: shared hit=2761949 read=43, local hit=919807 read=1
34. 459.904 2,299.520 ↑ 1.0 1 459,904

Nested Loop Left Join (cost=0.58..0.97 rows=1 width=232) (actual time=0.004..0.005 rows=1 loops=459,904)

  • Output: art1.details, art1.memo, art1.ar_code_type_id, art1.ar_trigger_id, art1.ar_code_id, art1.ar_payment_id, art1.cid, art1.id, att.name
  • Inner Unique: true
  • Buffers: shared hit=2761949 read=43
35. 1,379.712 1,379.712 ↑ 1.0 1 459,904

Index Scan using pk_ar_transactions on public.ar_transactions art1 (cost=0.43..0.79 rows=1 width=218) (actual time=0.002..0.003 rows=1 loops=459,904)

  • Output: art1.id, art1.cid, art1.property_id, art1.lease_id, art1.lease_interval_id, art1.customer_id, art1.ar_code_type_id, art1.ar_code_id, art1.ar_origin_id, art1.ar_origin_reference_id, art1.ar_origin_object_id, art1.ar_trigger_id, art1.ar_process_id, art1.ar_payment_id, art1.ar_payment_split_id, art1.accrual_debit_gl_account_id, art1.accrual_credit_gl_account_id, art1.cash_debit_gl_account_id, art1.cash_credit_gl_account_id, art1.ar_transaction_id, art1.ap_allocation_id, art1.ap_detail_id, art1.scheduled_charge_id, art1.period_id, art1.dependent_ar_transaction_id, art1.remote_primary_key, art1.transaction_datetime, art1.transaction_amount, art1.transaction_amount_due, art1.initial_amount_due, art1.reporting_post_date, art1.post_date, art1.post_month, art1.memo, art1.internal_memo, art1.is_payment_in_kind, art1.is_deposit_credit, art1.is_reversal, art1.is_temporary, art1.is_posted, art1.is_initial_import, art1.has_dependencies, art1.is_deleted, art1.updated_by, art1.updated_on, art1.created_by, art1.created_on, art1.gl_dimension_id, art1.details
  • Index Cond: ((art1.cid = 14181) AND (art1.id = COALESCE(ara.charge_ar_transaction_id, art.id)))
  • Buffers: shared hit=1842141 read=43
36. 459.904 459.904 ↑ 1.0 1 459,904

Index Scan using pk_ar_triggers on public.ar_triggers att (cost=0.14..0.18 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=459,904)

  • Output: att.id, att.ar_trigger_type_id, att.frequency_id, att.utility_type_id, att.name, att.description, att.allow_flexible_lease_caching, att.allow_rate_offsets, att.allow_ar_code_mapping, att.require_customer_relationship, att.require_lease_interval_type, att.is_optional, att.order_num, att.details
  • Index Cond: (art1.ar_trigger_id = att.id)
  • Buffers: shared hit=919808
37. 6,898.560 6,898.560 ↑ 1.0 1 459,904

Index Scan using idx_temp_cid on pg_temp_79.ar_codes_temp ac (cost=0.14..4.39 rows=1 width=25) (actual time=0.009..0.015 rows=1 loops=459,904)

  • Output: ac.id, ac.name, ac.cid
  • Index Cond: ((ac.cid = art1.cid) AND (ac.cid = 14181))
  • Filter: (ac.id = art1.ar_code_id)
  • Rows Removed by Filter: 99
  • Buffers: local hit=919807 read=1
38. 1,379.712 1,379.712 ↑ 1.0 1 459,904

Index Scan using pk_cached_leases on public.cached_leases cl (cost=0.42..0.83 rows=1 width=63) (actual time=0.003..0.003 rows=1 loops=459,904)

  • Output: cl.name_first, cl.name_last, cl.company_name, cl.display_number, cl.building_name, cl.unit_number_cache, cl.occupancy_type_id, cl.primary_customer_id, cl.cid, cl.id
  • Index Cond: ((cl.cid = gd.cid) AND (cl.cid = 14181) AND (cl.id = gd.lease_id))
  • Buffers: shared hit=1841024
39. 459.904 459.904 ↓ 0.0 0 459,904

Index Scan using idx_ar_deposits_id on public.ar_deposits ad (cost=0.29..0.59 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=459,904)

  • Output: ad.id, ad.gl_transaction_type_id, util_get_translated('deposit_memo'::text, (ad.deposit_memo)::text, ad.details, NULL::text, false)
  • Index Cond: (gh.reference_id = ad.id)
  • Filter: ((ad.cid = 14181) AND (gh.gl_transaction_type_id = ad.gl_transaction_type_id))
  • Buffers: shared hit=919808
40. 16.202 1,707.546 ↓ 64.0 49,593 1

Subquery Scan on *SELECT* 2 (cost=569.27..58,717.71 rows=775 width=519) (actual time=1.724..1,707.546 rows=49,593 loops=1)

  • Output: 0, "*SELECT* 2".property_id, "*SELECT* 2".property_name, "*SELECT* 2".lookup_code, "*SELECT* 2".grouping_gl_account_id, "*SELECT* 2".gl_account, "*SELECT* 2".account_name, "*SELECT* 2".account_number, "*SELECT* 2".post_month, "*SELECT* 2".transaction_type, NULL::text, "*SELECT* 2".gl_transaction_type_id, "*SELECT* 2".post_date, "*SELECT* 2".transaction_date, "*SELECT* 2".vendor_id, "*SELECT* 2".resident, NULL::integer, "*SELECT* 2".gh_header_id, "*SELECT* 2".header_number, "*SELECT* 2".reference_id, "*SELECT* 2".reference, "*SELECT* 2".gl_detail_id, "*SELECT* 2".transaction_id, "*SELECT* 2".memo, NULL::jsonb, NULL::integer, NULL::integer, NULL::integer, NULL::bigint, "*SELECT* 2".reversal_ap_header_id, "*SELECT* 2".ap_payment_id, NULL::bigint, NULL::integer, "*SELECT* 2".debit, "*SELECT* 2".credit, "*SELECT* 2".net_change, "*SELECT* 2".bldg_unit, "*SELECT* 2".lease_id, "*SELECT* 2".occupancy_type_id, "*SELECT* 2".customer_id, 0, "*SELECT* 2".row_balance
  • Buffers: shared hit=1220138 read=2001, local hit=3394 read=3
41. 194.038 1,691.344 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=569.27..58,694.46 rows=775 width=519) (actual time=1.722..1,691.344 rows=49,593 loops=1)

  • Output: 0, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gd_1.post_month, concat(gtty_1.description, CASE WHEN ((gh_1.gl_transaction_type_id = 1) AND (gh_1.gl_header_status_type_id = 2)) THEN '- Unposted'::text WHEN ((gh_1.gl_transaction_type_id = 1) AND (gh_1.gl_header_status_type_id = 3) AND (gh_1.id > gh_1.offsetting_gl_header_id)) THEN ' Reversal'::text ELSE NULL::text END), NULL::text, CASE WHEN ((ah.gl_transaction_type_id = 2) AND (aa.lump_ap_header_id IS NULL)) THEN 2 ELSE gh_1.gl_transaction_type_id END, (gh_1.transaction_datetime)::date, gh_1.post_date, app.id, CASE WHEN (gh_1.gl_transaction_type_id = ANY ('{2,3,4,5}'::integer[])) THEN CASE WHEN (lc.id IS NOT NULL) THEN func_format_customer_name(cust.name_first, cust.name_last, cust.company_name) ELSE app.company_name END ELSE NULL::character varying END, NULL::integer, CASE WHEN ((gat_1.gl_account_type_id = 101) AND (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gh_1.id END, CASE WHEN ((gat_1.gl_account_type_id = 101) AND (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gh_1.header_number END, CASE WHEN ((gat_1.gl_account_type_id = 101) AND (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gh_1.reference_id END, CASE WHEN (gh_1.header_number IS NOT NULL) THEN (('JE #'::text || (COALESCE(gh_1.header_number, 0))::text) || CASE WHEN (gh_1.reference IS NOT NULL) THEN (' - '::text || (gh_1.reference)::text) ELSE ''::text END) WHEN (((ap_ref.ap_payment_type_id = ANY ('{2,5,7,9}'::integer[])) OR (ap.ap_payment_type_id = ANY ('{2,5,7,9}'::integer[]))) AND (gtty_1.id = 5)) THEN COALESCE((('Check #'::text || (ap_ref.payment_number)::text) || ' - Reversal'::text), (gh_1.reference)::text, (('Check #'::text || (ap.payment_number)::text) || ' - Reversal'::text)) WHEN ((ap_ref.ap_payment_type_id = ANY ('{2,5,7,9}'::integer[])) OR (ap.ap_payment_type_id = ANY ('{2,5,7,9}'::integer[]))) THEN COALESCE(('Check #'::text || (ap_ref.payment_number)::text), (gh_1.reference)::text, ('Check #'::text || (ap.payment_number)::text)) WHEN (gtty_1.id = 5) THEN COALESCE((('Payment #'::text || (ap_ref.payment_number)::text) || ' - Reversal'::text), (gh_1.reference)::text, (('Payment #'::text || (ap.payment_number)::text) || ' - Reversal'::text)) ELSE COALESCE(('Payment #'::text || (ap_ref.payment_number)::text), (gh_1.reference)::text, ('Payment #'::text || (ap.payment_number)::text)) END, CASE WHEN ((gat_1.gl_account_type_id = 101) AND (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE gd_1.id END, CASE WHEN (gh_1.gl_transaction_type_id = 1) THEN gh_1.id WHEN (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[])) THEN COALESCE(ah.ap_payment_id, ah1.ap_payment_id, ah.id) WHEN (gh_1.gl_transaction_type_id = ANY ('{7,8,13,14,18,23,24}'::integer[])) THEN NULL::integer ELSE gh_1.reference_id END, (CASE WHEN (gh_1.gl_transaction_type_id = ANY ('{15,16}'::integer[])) THEN NULL::character varying WHEN (gh_1.gl_transaction_type_id = ANY ('{7,13,14}'::integer[])) THEN NULL::character varying WHEN (gh_1.gl_transaction_type_id = 1) THEN gd_1.memo WHEN ((gat_1.gl_account_type_id = 101) AND (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::character varying ELSE COALESCE(ad1.description, ad_1.description, ah.header_memo, gh_1.memo) END)::text, NULL::jsonb, NULL::integer, NULL::integer, NULL::integer, NULL::bigint, ah.reversal_ap_header_id, COALESCE(ah.ap_payment_id, ah1.ap_payment_id), NULL::bigint, NULL::integer, CASE WHEN (gd_1.amount > '0'::numeric) THEN gd_1.amount ELSE 0.00 END, CASE WHEN (gd_1.amount < '0'::numeric) THEN gd_1.amount ELSE 0.00 END, (CASE WHEN (gd_1.amount > '0'::numeric) THEN gd_1.amount ELSE 0.00 END + CASE WHEN (gd_1.amount < '0'::numeric) THEN gd_1.amount ELSE 0.00 END), CASE WHEN ((gat_1.gl_account_type_id = 101) AND (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::character varying ELSE COALESCE(func_format_unit_number((pu.unit_number)::character varying, NULL::character varying, (pb.building_name)::character varying, NULL::character varying), (pu.unit_number)::character varying, (pb.building_name)::character varying) END, CASE WHEN ((gat_1.gl_account_type_id = 101) AND (app.ap_payee_type_id = 4)) THEN COALESCE(gd_1.lease_id, lc.lease_id) WHEN ((gat_1.gl_account_type_id = 101) AND (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE COALESCE(gd_1.lease_id, lc.lease_id) END, CASE WHEN ((gat_1.gl_account_type_id = 101) AND (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE NULL::integer END, CASE WHEN ((gat_1.gl_account_type_id = 101) AND (app.ap_payee_type_id = 4)) THEN cust.id WHEN ((gat_1.gl_account_type_id = 101) AND (gh_1.gl_transaction_type_id = ANY ('{3,4,5}'::integer[]))) THEN NULL::integer ELSE cust.id END, 0, gd_1.amount
  • Inner Unique: true
  • Buffers: shared hit=1220138 read=2001, local hit=3394 read=3
42. 29.293 1,398.120 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=568.86..57,765.26 rows=775 width=481) (actual time=1.692..1,398.120 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, ad_1.description, ad1.description, pu.unit_number, pb.building_name, ah.gl_transaction_type_id, ah.ap_payment_id, ah.id, ah.header_memo, ah.reversal_ap_header_id, ah.cid, ah.ap_payee_id, ah1.ap_payment_id, ah2.cid, ah2.ap_payee_id, lc.id, lc.lease_id, cust.name_first, cust.name_last, cust.company_name, cust.id, ap_ref.ap_payment_type_id, ap_ref.payment_number, ap.ap_payment_type_id, ap.payment_number
  • Inner Unique: true
  • Buffers: shared hit=1056270 read=2001, local hit=3394 read=3
43. 24.628 1,368.827 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=568.44..57,253.65 rows=775 width=476) (actual time=1.687..1,368.827 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, ad_1.description, ad1.description, pu.unit_number, pb.building_name, ah.gl_transaction_type_id, ah.ap_payment_id, ah.id, ah.header_memo, ah.reversal_ap_header_id, ah.cid, ah.ap_payee_id, ah1.ap_payment_id, ah1.cid, ah2.cid, ah2.ap_payee_id, lc.id, lc.lease_id, cust.name_first, cust.name_last, cust.company_name, cust.id, ap_ref.ap_payment_type_id, ap_ref.payment_number
  • Inner Unique: true
  • Buffers: shared hit=1055398 read=2001, local hit=3394 read=3
44. 34.983 1,294.606 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=568.01..56,742.05 rows=775 width=467) (actual time=1.682..1,294.606 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, ad_1.description, ad1.description, pu.unit_number, pb.building_name, ah.gl_transaction_type_id, ah.ap_payment_id, ah.id, ah.header_memo, ah.reversal_ap_header_id, ah.cid, ah.ap_payee_id, ah1.ap_payment_id, ah1.cid, ah2.cid, ah2.ap_payee_id, lc.id, lc.lease_id, cust.name_first, cust.name_last, cust.company_name, cust.id
  • Inner Unique: true
  • Buffers: shared hit=971650 read=2001, local hit=3394 read=3
45. 37.705 1,259.623 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=567.59..56,243.73 rows=775 width=438) (actual time=1.678..1,259.623 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, ad_1.description, ad1.description, pu.unit_number, pb.building_name, ah.gl_transaction_type_id, ah.ap_payment_id, ah.id, ah.header_memo, ah.reversal_ap_header_id, ah.cid, ah.ap_payee_id, ah1.ap_payment_id, ah1.cid, ah2.cid, ah2.ap_payee_id, lc.id, lc.lease_id, lc.cid, lc.customer_id
  • Inner Unique: true
  • Buffers: shared hit=966786 read=2001, local hit=3394 read=3
46. 45.275 1,221.918 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=567.16..55,697.40 rows=775 width=430) (actual time=1.674..1,221.918 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, ad_1.description, ad1.description, pu.unit_number, pb.building_name, ah.gl_transaction_type_id, ah.ap_payment_id, ah.id, ah.header_memo, ah.reversal_ap_header_id, ah.cid, ah.lease_customer_id, ah.ap_payee_id, ah1.ap_payment_id, ah1.cid, ah2.cid, ah2.lease_customer_id, ah2.ap_payee_id
  • Inner Unique: true
  • Buffers: shared hit=962086 read=2001, local hit=3394 read=3
47. 27.862 1,077.457 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=566.73..55,291.34 rows=775 width=434) (actual time=1.670..1,077.457 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, ad_1.description, ad1.description, ad1.cid, ad1.ap_header_id, ad1.gl_transaction_type_id, ad1.post_month, pu.unit_number, pb.building_name, ah.gl_transaction_type_id, ah.ap_payment_id, ah.id, ah.header_memo, ah.reversal_ap_header_id, ah.cid, ah.lease_customer_id, ah.ap_payee_id, ah1.ap_payment_id, ah1.cid
  • Inner Unique: true
  • Buffers: shared hit=798176 read=2001, local hit=3394 read=3
48. 31.531 1,049.595 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=566.31..54,673.33 rows=775 width=430) (actual time=1.666..1,049.595 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, aa.cid, ad_1.description, ad1.description, ad1.cid, ad1.ap_header_id, ad1.gl_transaction_type_id, ad1.post_month, pu.unit_number, pb.building_name, ah.gl_transaction_type_id, ah.ap_payment_id, ah.id, ah.header_memo, ah.reversal_ap_header_id, ah.cid, ah.lease_customer_id, ah.ap_payee_id
  • Inner Unique: true
  • Buffers: shared hit=797304 read=2001, local hit=3394 read=3
49. 32.838 968.471 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=565.88..54,190.23 rows=775 width=390) (actual time=1.656..968.471 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, aa.cid, ad_1.description, ad_1.cid, ad_1.ap_header_id, ad_1.gl_transaction_type_id, ad_1.post_month, ad1.description, ad1.cid, ad1.ap_header_id, ad1.gl_transaction_type_id, ad1.post_month, pu.unit_number, pb.building_name
  • Buffers: shared hit=712182 read=2001, local hit=3394 read=3
50. 22.860 935.633 ↓ 64.0 49,593 1

Hash Left Join (cost=565.60..53,918.97 rows=775 width=407) (actual time=1.643..935.633 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, gd_1.cid, gd_1.gl_transaction_type_id, gd_1.property_building_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, aa.cid, ad_1.description, ad_1.cid, ad_1.property_building_id, ad_1.ap_header_id, ad_1.gl_transaction_type_id, ad_1.post_month, ad1.description, ad1.cid, ad1.property_building_id, ad1.ap_header_id, ad1.gl_transaction_type_id, ad1.post_month, pu.unit_number
  • Hash Cond: ((gd_1.cid = pu.cid) AND (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 = pu.id))
  • Buffers: shared hit=712182 read=2001, local hit=17
51. 18.115 912.382 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=473.08..53,796.42 rows=775 width=415) (actual time=1.244..912.382 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, gd_1.cid, gd_1.gl_transaction_type_id, gd_1.property_unit_id, gd_1.property_building_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, aa.cid, ad_1.description, ad_1.cid, ad_1.property_unit_id, ad_1.property_building_id, ad_1.ap_header_id, ad_1.gl_transaction_type_id, ad_1.post_month, ad1.description, ad1.cid, ad1.property_unit_id, ad1.property_building_id, ad1.ap_header_id, ad1.gl_transaction_type_id, ad1.post_month
  • Inner Unique: true
  • Buffers: shared hit=712182 read=2001, local hit=10
52. 41.444 745.488 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=472.65..52,872.18 rows=775 width=367) (actual time=1.239..745.488 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, gd_1.cid, gd_1.ap_detail_id, gd_1.gl_transaction_type_id, gd_1.property_unit_id, gd_1.property_building_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, aa.cid, aa.charge_ap_detail_id, ad_1.description, ad_1.cid, ad_1.property_unit_id, ad_1.property_building_id, ad_1.ap_header_id, ad_1.gl_transaction_type_id, ad_1.post_month
  • Inner Unique: true
  • Buffers: shared hit=548212 read=2001, local hit=10
53. 22.833 654.451 ↓ 64.0 49,593 1

Nested Loop Left Join (cost=472.21..51,946.01 rows=775 width=315) (actual time=1.235..654.451 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, gd_1.cid, gd_1.ap_detail_id, gd_1.gl_transaction_type_id, gd_1.property_unit_id, gd_1.property_building_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, aa.lump_ap_header_id, aa.cid, aa.credit_ap_detail_id, aa.charge_ap_detail_id
  • Inner Unique: true
  • Join Filter: (gh_1.gl_transaction_type_id = ANY ('{4,5}'::integer[]))
  • Buffers: shared hit=463222 read=2001, local hit=10
54. 18.159 532.432 ↓ 64.0 49,593 1

Hash Join (cost=471.78..51,509.94 rows=775 width=303) (actual time=1.221..532.432 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, gd_1.cid, gd_1.ap_detail_id, gd_1.gl_transaction_type_id, gd_1.property_unit_id, gd_1.property_building_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gh_1.cid, gtty_1.description, gtty_1.id, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id
  • Inner Unique: true
  • Hash Cond: (gh_1.gl_transaction_type_id = gtty_1.id)
  • Buffers: shared hit=312879 read=1990, local hit=10
55. 42.847 514.253 ↓ 64.0 49,593 1

Nested Loop (cost=468.10..51,503.32 rows=775 width=279) (actual time=1.190..514.253 rows=49,593 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, gd_1.cid, gd_1.ap_detail_id, gd_1.gl_transaction_type_id, gd_1.property_unit_id, gd_1.property_building_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.id, gh_1.offsetting_gl_header_id, gh_1.transaction_datetime, gh_1.post_date, gh_1.header_number, gh_1.reference_id, gh_1.reference, gh_1.memo, gh_1.cid, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id
  • Inner Unique: true
  • Buffers: shared hit=312877 read=1990, local hit=10
56. 20.116 321.724 ↓ 7.7 49,894 1

Hash Join (cost=467.66..44,088.60 rows=6,512 width=182) (actual time=1.177..321.724 rows=49,894 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, gd_1.cid, gd_1.gl_header_id, gd_1.ap_detail_id, gd_1.gl_transaction_type_id, gd_1.property_unit_id, gd_1.property_building_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id
  • Hash Cond: (gd_1.accrual_gl_account_id = gat_1.gl_account_id)
  • Buffers: shared hit=113198 read=1977, local hit=10
57. 9.223 300.552 ↓ 4.9 49,923 1

Nested Loop (cost=0.56..43,387.98 rows=10,177 width=117) (actual time=0.113..300.552 rows=49,923 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, gd_1.cid, gd_1.gl_header_id, gd_1.accrual_gl_account_id, gd_1.ap_detail_id, gd_1.gl_transaction_type_id, gd_1.property_unit_id, gd_1.property_building_id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code
  • Buffers: shared hit=113198 read=1977, local hit=1
58. 0.009 0.009 ↑ 1.0 4 1

Seq Scan on pg_temp_79.load_prop load_prop_1 (cost=0.00..1.12 rows=4 width=25) (actual time=0.004..0.009 rows=4 loops=1)

  • Output: load_prop_1.cid, load_prop_1.property_id, load_prop_1.is_student_property, load_prop_1.property_name, load_prop_1.lookup_code, load_prop_1.property_type_id, load_prop_1.occupancy_type_ids
  • Buffers: local hit=1
59. 291.320 291.320 ↓ 4.9 12,481 4

Index Scan using idx_gl_details_cid_property_id_post_month_accrual_gl_account_id on public.gl_details gd_1 (cost=0.56..10,770.40 rows=2,544 width=96) (actual time=0.067..72.830 rows=12,481 loops=4)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, gd_1.property_id, gd_1.cid, gd_1.gl_header_id, gd_1.accrual_gl_account_id, gd_1.ap_detail_id, gd_1.gl_transaction_type_id, gd_1.property_unit_id, gd_1.property_building_id
  • Index Cond: ((gd_1.cid = 14181) AND (gd_1.property_id = load_prop_1.property_id) AND (gd_1.post_month >= '01/01/2019'::date) AND (gd_1.post_month <= '09/01/2019'::date))
  • Filter: (gd_1.gl_transaction_type_id <> ALL ('{13,14,18,23,24,7,8,9,17}'::integer[]))
  • Rows Removed by Filter: 115770
  • Buffers: shared hit=113198 read=1977
60. 0.128 1.056 ↑ 1.0 571 1

Hash (cost=448.54..448.54 rows=571 width=77) (actual time=1.056..1.056 rows=571 loops=1)

  • Output: gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, gat_1.cid, gat_1.gl_account_id
  • Buckets: 1024 Batches: 1 Memory Usage: 70kB
  • Buffers: local hit=9
61. 0.928 0.928 ↑ 1.0 571 1

Seq Scan on pg_temp_79.gat_temp gat_1 (cost=0.00..448.54 rows=571 width=77) (actual time=0.008..0.928 rows=571 loops=1)

  • Output: gat_1.grouping_gl_account_id, gat_1.gl_account, gat_1.name, gat_1.formatted_account_number, gat_1.gl_account_type_id, gat_1.cid, gat_1.gl_account_id
  • Filter: ((gat_1.cid = 14181) AND (gat_1.grouping_gl_account_id = ANY ('{276755,277181,276723,282512,277994,282520,277182,276734,276746,276756,276757,276747,276758,276759,277856,305890,276760,277858,276761,276762,305887,276763,276764,276765,276766,282511,276752,276744,276751,276767,276768,276769,276770,279436,282510,276771,276772,276773,276774,276775,276776,276777,281803,276778,276779,281804,276780,276781,276782,281805,276783,281806,276784,281807,276785,281808,276786,281809,305917,305918,276787,281810,276788,281811,276789,281812,276790,281813,276791,281814,276792,281815,276793,276794,282471,305892,305891,305919,305920,305924,305925,306536,306537,305894,305893,276795,281816,276796,281817,276797,281818,276798,281819,276799,281820,276800,281821,276801,281822,276802,276803,281823,276804,281774,276805,281839,276806,281824,276807,281825,276808,281826,276809,281832,276810,281833,276811,281834,276812,281835,276813,276814,281836,276815,281837,276816,281838,276817,281827,276818,281828,276819,276820,281829,276821,282513,276822,276823,281830,276824,282521,276825,276826,276827,276828,276829,276830,276831,276832,276833,282524,276834,282522,276835,305868,276836,276837,276838,276839,276840,306544,276841,276842,276843,276844,276845,306545,277861,276745,276846,276847,276848,276719,276849,276738,276750,279435,276754,278169,276733,276851,276749,276852,276853,276854,276855,276856,276857,277823,276858,276859,276860,276861,276862,276863,276864,276865,276866,305856,276867,276868,276869,276870,276871,276872,276873,276748,276874,276742,276876,276753,276878,276879,276880,276881,277862,276882,276740,276739,276730,276731,276883,276736,276735,276737,276884,276885,276886,276887,276888,276889,276890,276891,277866,277867,276892,276893,276894,276895,276896,276897,276898,276899,276900,276901,276902,276903,276904,276905,276906,276907,276908,276909,276910,276911,276912,276913,276914,276915,276916,276917,276918,276919,276920,276921,276922,276923,276924,276925,276926,276927,276928,276929,276930,276931,276932,276933,276934,276935,276936,276937,276938,276939,276940,276941,276727,276942,276943,276944,276945,276946,276947,276948,276949,276732,276726,276724,276950,276951,276952,276953,276954,276725,276955,276956,276957,276958,276959,276960,276961,276721,276962,276963,276964,276965,276966,276967,276968,276969,276970,276971,276972,276973,276729,276974,276975,276976,276977,276978,276979,276980,276981,276982,276983,276720,276741,276743,276728,276985,276986,276987,276988,276989,276990,276991,276992,276993,276994,276995,276996,276997,276998,276999,277000,277001,277002,277003,277004,277005,277006,277007,277008,282036,277009,277010,277011,277012,277013,277014,277015,277016,277017,277868,277018,277019,277869,277179,277180,277020,277021,277022,277023,277024,277025,277026,277027,277870,277028,276722,277029,277871,277030,305849,277031,277032,277033,277034,277035,277036,277037,277038,277039,277040,277041,277042,277043,277044,277045,277046,277047,277048,277049,277050,277051,277052,277053,277054,277055,277056,277057,277058,277059,277060,277061,277062,277063,277064,277065,277066,277067,277068,277069,277070,277071,277072,277073,277074,277075,277076,277077,277078,277079,277080,277081,277082,277083,277084,277085,277086,277087,277088,277089,277090,277091,277092,277093,277094,277095,277096,277097,277098,277099,277100,277101,277102,277103,277872,277104,277105,277106,277107,277108,277109,277110,277111,276984,277112,282523,277113,277114,281776,281777,281778,281779,281780,281781,281782,281783,281784,281785,281786,281787,281788,281789,281790,281791,281792,281793,281794,305866,277115,277116,277117,277118,277857,277873,277874,277875,277119,277120,277121,277122,277123,277124,277125,277126,277127,277128,277129,277130,277131,277132,277133,277134,277135,277136,277137,277138,277139,277140,305915,277141,277142,277143,277144,277145,277146,277147,277148,277149,277150,277151,277152,277153,277154,277155,277156,277157,277158,277159,277160,277161,277162,277163,277164,277165,277166,277167,277168,277169,277170,277171,277172,277173,277174,282536,277175,277176,277177,305867,277178}'::integer[])))
  • Rows Removed by Filter: 18
  • Buffers: local hit=9
62. 149.682 149.682 ↑ 1.0 1 49,894

Index Scan using pk_gl_headers on public.gl_headers gh_1 (cost=0.43..1.14 rows=1 width=101) (actual time=0.003..0.003 rows=1 loops=49,894)

  • Output: gh_1.id, gh_1.cid, gh_1.gl_header_type_id, gh_1.gl_transaction_type_id, gh_1.gl_header_status_type_id, gh_1.gl_book_id, gh_1.reference_id, gh_1.property_id, gh_1.lease_id, gh_1.offsetting_gl_header_id, gh_1.template_gl_header_id, gh_1.close_period_id, gh_1.bulk_property_id, gh_1.bulk_property_unit_id, gh_1.bulk_company_department_id, gh_1.bulk_gl_dimension_id, gh_1.bulk_job_phase_id, gh_1.bulk_ap_contract_id, gh_1.bulk_property_building_id, gh_1.gl_header_schedule_id, gh_1.ap_routing_tag_id, gh_1.reclass_gl_header_id, gh_1.header_number, gh_1.transaction_datetime, gh_1.post_month, gh_1.reverse_post_month, gh_1.post_date, gh_1.reference, gh_1.memo, gh_1.external_url, gh_1.template_name, gh_1.bulk_is_confidential, gh_1.is_template, gh_1.is_reverse, gh_1.updated_by, gh_1.updated_on, gh_1.created_by, gh_1.created_on
  • Index Cond: ((gh_1.cid = 14181) AND (gh_1.id = gd_1.gl_header_id))
  • Filter: ((gh_1.is_template IS FALSE) AND (gh_1.gl_book_id = 103) AND (gh_1.gl_header_status_type_id <> ALL ('{2,4,5,6}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=199679 read=13
63. 0.007 0.020 ↑ 1.0 27 1

Hash (cost=2.81..2.81 rows=27 width=24) (actual time=0.019..0.020 rows=27 loops=1)

  • Output: gtty_1.description, gtty_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=2
64. 0.013 0.013 ↑ 1.0 27 1

Seq Scan on public.gl_transaction_types gtty_1 (cost=0.00..2.81 rows=27 width=24) (actual time=0.005..0.013 rows=27 loops=1)

  • Output: gtty_1.description, gtty_1.id
  • Buffers: shared hit=2
65. 99.186 99.186 ↓ 0.0 0 49,593

Index Scan using pk_ap_allocations on public.ap_allocations aa (cost=0.43..0.53 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=49,593)

  • Output: aa.id, aa.cid, aa.property_id, aa.gl_transaction_type_id, aa.origin_ap_allocation_id, aa.charge_ap_detail_id, aa.credit_ap_detail_id, aa.ap_gl_account_id, aa.purchases_clearing_gl_account_id, aa.pending_reimbursements_gl_account_id, aa.inter_co_ap_gl_account_id, aa.bank_gl_account_id, aa.charge_gl_account_id, aa.credit_gl_account_id, aa.charge_wip_gl_account_id, aa.credit_wip_gl_account_id, aa.charge_ap_transaction_type_id, aa.credit_ap_transaction_type_id, aa.charge_inter_co_property_id, aa.credit_inter_co_property_id, aa.charge_reimbursed_ap_detail_id, aa.credit_reimbursed_ap_detail_id, aa.accrual_debit_gl_account_id, aa.accrual_credit_gl_account_id, aa.cash_debit_gl_account_id, aa.cash_credit_gl_account_id, aa.lump_ap_header_id, aa.period_id, aa.allocation_datetime, aa.allocation_amount, aa.post_month, aa.post_date, aa.charge_is_reimbursement, aa.credit_is_reimbursement, aa.is_deleted, aa.is_posted, aa.is_initial_import, aa.updated_by, aa.updated_on, aa.created_by, aa.created_on
  • Index Cond: ((gh_1.cid = aa.cid) AND (aa.cid = 14181) AND (gh_1.reference_id = aa.id))
  • Filter: (aa.gl_transaction_type_id = gh_1.gl_transaction_type_id)
  • Buffers: shared hit=150343 read=11
66. 49.593 49.593 ↓ 0.0 0 49,593

Index Scan using idx_ap_details on public.ap_details ad_1 (cost=0.43..1.17 rows=1 width=60) (actual time=0.001..0.001 rows=0 loops=49,593)

  • Output: ad_1.description, ad_1.cid, ad_1.id, ad_1.property_unit_id, ad_1.property_building_id, ad_1.ap_header_id, ad_1.gl_transaction_type_id, ad_1.post_month
  • Index Cond: (aa.credit_ap_detail_id = ad_1.id)
  • Filter: ((ad_1.cid = 14181) AND (aa.cid = ad_1.cid))
  • Buffers: shared hit=84990
67. 148.779 148.779 ↑ 1.0 1 49,593

Index Scan using idx_ap_details on public.ap_details ad1 (cost=0.43..1.16 rows=1 width=60) (actual time=0.003..0.003 rows=1 loops=49,593)

  • Output: ad1.description, ad1.cid, ad1.id, ad1.property_unit_id, ad1.property_building_id, ad1.ap_header_id, ad1.gl_transaction_type_id, ad1.post_month
  • Index Cond: (ad1.id = COALESCE(gd_1.ap_detail_id, aa.charge_ap_detail_id))
  • Filter: (ad1.cid = COALESCE(gd_1.cid, aa.cid))
  • Buffers: shared hit=163970
68. 0.191 0.391 ↑ 1.0 1,267 1

Hash (cost=48.18..48.18 rows=1,267 width=12) (actual time=0.391..0.391 rows=1,267 loops=1)

  • Output: pu.unit_number, pu.cid, pu.id
  • Buckets: 2048 Batches: 1 Memory Usage: 75kB
  • Buffers: local hit=7
69. 0.200 0.200 ↑ 1.0 1,267 1

Seq Scan on pg_temp_79.property_units_temp pu (cost=0.00..48.18 rows=1,267 width=12) (actual time=0.009..0.200 rows=1,267 loops=1)

  • Output: pu.unit_number, pu.cid, pu.id
  • Filter: (pu.cid = 14181)
  • Buffers: local hit=7
70. 0.000 0.000 ↓ 0.0 0 49,593

Index Scan using idx_id on pg_temp_79.property_buildings_temp pb (cost=0.28..0.32 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=49,593)

  • Output: pb.id, pb.building_name, pb.cid
  • Index Cond: (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)
  • Filter: ((pb.cid = 14181) AND (pb.cid = gd_1.cid))
  • Buffers: local hit=3377 read=3
71. 49.593 49.593 ↓ 0.0 0 49,593

Index Scan using idx_ap_headers on public.ap_headers ah (cost=0.43..0.59 rows=1 width=60) (actual time=0.001..0.001 rows=0 loops=49,593)

  • Output: ah.id, ah.cid, ah.ap_header_type_id, ah.ap_header_sub_type_id, ah.ap_header_mode_id, ah.ap_physical_status_type_id, ah.ap_financial_status_type_id, ah.ap_payee_id, ah.order_header_id, ah.ap_payee_location_id, ah.ap_payee_account_id, ah.ap_remittance_id, ah.ap_payee_term_id, ah.inter_co_ap_payee_id, ah.inter_co_ap_payee_location_id, ah.inter_co_ap_payee_account_id, ah.inter_co_ap_remittance_id, ah.gl_transaction_type_id, ah.job_phase_id, ah.ap_contract_id, ah.bulk_job_phase_id, ah.bulk_ap_contract_id, ah.bulk_bank_account_id, ah.bulk_property_id, ah.bulk_ap_formula_id, ah.bulk_ap_code_id, ah.bulk_unit_number_id, ah.bulk_company_department_id, ah.bulk_gl_dimension_id, ah.bulk_property_building_id, ah.bulk_budget_ap_header_id, ah.ap_contract_ap_header_ids, ah.po_ap_header_ids, ah.reversal_ap_header_id, ah.payment_ap_header_id, ah.scheduled_ap_header_id, ah.template_ap_header_id, ah.reimbursement_ap_header_id, ah.frequency_id, ah.lease_customer_id, ah.refund_ar_transaction_id, ah.ap_batch_id, ah.accounting_export_batch_id, ah.ap_payment_id, ah.compliance_job_id, ah.ap_routing_tag_id, ah.budget_change_order_id, ah.remote_primary_key, ah.template_name, ah.transaction_datetime, ah.post_date, ah.due_date, ah.post_month, ah.scheduled_payment_date, ah.start_date, ah.end_date, ah.frequency_interval, ah.day_of_week, ah.days_of_month, ah.number_of_occurrences, ah.last_posted_date, ah.next_post_date, ah.header_number, ah.external_url, ah.account_number, ah.header_memo, ah.control_total, ah.pre_approval_amount, ah.transaction_amount, ah.transaction_amount_due, ah.tax_amount, ah.discount_amount, ah.auto_create_po, ah.shipping_amount, ah.bulk_retention_percent, ah.bulk_is_confidential, ah.bulk_is_1099, ah.auto_approve_po, ah.auto_create_invoice, ah.auto_approve_invoice, ah.auto_post_invoice, ah.pay_with_single_check, ah.is_deleted, ah.is_reversed, ah.is_primary, ah.is_on_hold, ah.is_template, ah.is_disabled, ah.is_reimbursement, ah.is_batching, ah.is_temporary, ah.is_posted, ah.is_initial_import, ah.is_sync_to_va, ah.posted_on, ah.imported_on, ah.retention_released_by, ah.retention_released_on, ah.approved_by, ah.approved_on, ah.deleted_by, ah.deleted_on, ah.updated_by, ah.updated_on, ah.created_by, ah.created_on, ah.is_punchout
  • Index Cond: (ad_1.ap_header_id = ah.id)
  • Filter: ((ah.cid = 14181) AND (ad_1.cid = ah.cid) AND (ad_1.gl_transaction_type_id = ah.gl_transaction_type_id) AND (ad_1.post_month = ah.post_month))
  • Buffers: shared hit=85122
72. 0.000 0.000 ↓ 0.0 0 49,593

Index Scan using idx_ap_headers on public.ap_headers ah1 (cost=0.43..0.77 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=49,593)

  • Output: ah1.id, ah1.cid, ah1.ap_header_type_id, ah1.ap_header_sub_type_id, ah1.ap_header_mode_id, ah1.ap_physical_status_type_id, ah1.ap_financial_status_type_id, ah1.ap_payee_id, ah1.order_header_id, ah1.ap_payee_location_id, ah1.ap_payee_account_id, ah1.ap_remittance_id, ah1.ap_payee_term_id, ah1.inter_co_ap_payee_id, ah1.inter_co_ap_payee_location_id, ah1.inter_co_ap_payee_account_id, ah1.inter_co_ap_remittance_id, ah1.gl_transaction_type_id, ah1.job_phase_id, ah1.ap_contract_id, ah1.bulk_job_phase_id, ah1.bulk_ap_contract_id, ah1.bulk_bank_account_id, ah1.bulk_property_id, ah1.bulk_ap_formula_id, ah1.bulk_ap_code_id, ah1.bulk_unit_number_id, ah1.bulk_company_department_id, ah1.bulk_gl_dimension_id, ah1.bulk_property_building_id, ah1.bulk_budget_ap_header_id, ah1.ap_contract_ap_header_ids, ah1.po_ap_header_ids, ah1.reversal_ap_header_id, ah1.payment_ap_header_id, ah1.scheduled_ap_header_id, ah1.template_ap_header_id, ah1.reimbursement_ap_header_id, ah1.frequency_id, ah1.lease_customer_id, ah1.refund_ar_transaction_id, ah1.ap_batch_id, ah1.accounting_export_batch_id, ah1.ap_payment_id, ah1.compliance_job_id, ah1.ap_routing_tag_id, ah1.budget_change_order_id, ah1.remote_primary_key, ah1.template_name, ah1.transaction_datetime, ah1.post_date, ah1.due_date, ah1.post_month, ah1.scheduled_payment_date, ah1.start_date, ah1.end_date, ah1.frequency_interval, ah1.day_of_week, ah1.days_of_month, ah1.number_of_occurrences, ah1.last_posted_date, ah1.next_post_date, ah1.header_number, ah1.external_url, ah1.account_number, ah1.header_memo, ah1.control_total, ah1.pre_approval_amount, ah1.transaction_amount, ah1.transaction_amount_due, ah1.tax_amount, ah1.discount_amount, ah1.auto_create_po, ah1.shipping_amount, ah1.bulk_retention_percent, ah1.bulk_is_confidential, ah1.bulk_is_1099, ah1.auto_approve_po, ah1.auto_create_invoice, ah1.auto_approve_invoice, ah1.auto_post_invoice, ah1.pay_with_single_check, ah1.is_deleted, ah1.is_reversed, ah1.is_primary, ah1.is_on_hold, ah1.is_template, ah1.is_disabled, ah1.is_reimbursement, ah1.is_batching, ah1.is_temporary, ah1.is_posted, ah1.is_initial_import, ah1.is_sync_to_va, ah1.posted_on, ah1.imported_on, ah1.retention_released_by, ah1.retention_released_on, ah1.approved_by, ah1.approved_on, ah1.deleted_by, ah1.deleted_on, ah1.updated_by, ah1.updated_on, ah1.created_by, ah1.created_on, ah1.is_punchout
  • Index Cond: (ah1.id = aa.lump_ap_header_id)
  • Filter: ((ah1.cid = 14181) AND (ah1.cid = aa.cid))
  • Buffers: shared hit=872
73. 99.186 99.186 ↑ 1.0 1 49,593

Index Scan using idx_ap_headers on public.ap_headers ah2 (cost=0.43..0.49 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=49,593)

  • Output: ah2.id, ah2.cid, ah2.ap_header_type_id, ah2.ap_header_sub_type_id, ah2.ap_header_mode_id, ah2.ap_physical_status_type_id, ah2.ap_financial_status_type_id, ah2.ap_payee_id, ah2.order_header_id, ah2.ap_payee_location_id, ah2.ap_payee_account_id, ah2.ap_remittance_id, ah2.ap_payee_term_id, ah2.inter_co_ap_payee_id, ah2.inter_co_ap_payee_location_id, ah2.inter_co_ap_payee_account_id, ah2.inter_co_ap_remittance_id, ah2.gl_transaction_type_id, ah2.job_phase_id, ah2.ap_contract_id, ah2.bulk_job_phase_id, ah2.bulk_ap_contract_id, ah2.bulk_bank_account_id, ah2.bulk_property_id, ah2.bulk_ap_formula_id, ah2.bulk_ap_code_id, ah2.bulk_unit_number_id, ah2.bulk_company_department_id, ah2.bulk_gl_dimension_id, ah2.bulk_property_building_id, ah2.bulk_budget_ap_header_id, ah2.ap_contract_ap_header_ids, ah2.po_ap_header_ids, ah2.reversal_ap_header_id, ah2.payment_ap_header_id, ah2.scheduled_ap_header_id, ah2.template_ap_header_id, ah2.reimbursement_ap_header_id, ah2.frequency_id, ah2.lease_customer_id, ah2.refund_ar_transaction_id, ah2.ap_batch_id, ah2.accounting_export_batch_id, ah2.ap_payment_id, ah2.compliance_job_id, ah2.ap_routing_tag_id, ah2.budget_change_order_id, ah2.remote_primary_key, ah2.template_name, ah2.transaction_datetime, ah2.post_date, ah2.due_date, ah2.post_month, ah2.scheduled_payment_date, ah2.start_date, ah2.end_date, ah2.frequency_interval, ah2.day_of_week, ah2.days_of_month, ah2.number_of_occurrences, ah2.last_posted_date, ah2.next_post_date, ah2.header_number, ah2.external_url, ah2.account_number, ah2.header_memo, ah2.control_total, ah2.pre_approval_amount, ah2.transaction_amount, ah2.transaction_amount_due, ah2.tax_amount, ah2.discount_amount, ah2.auto_create_po, ah2.shipping_amount, ah2.bulk_retention_percent, ah2.bulk_is_confidential, ah2.bulk_is_1099, ah2.auto_approve_po, ah2.auto_create_invoice, ah2.auto_approve_invoice, ah2.auto_post_invoice, ah2.pay_with_single_check, ah2.is_deleted, ah2.is_reversed, ah2.is_primary, ah2.is_on_hold, ah2.is_template, ah2.is_disabled, ah2.is_reimbursement, ah2.is_batching, ah2.is_temporary, ah2.is_posted, ah2.is_initial_import, ah2.is_sync_to_va, ah2.posted_on, ah2.imported_on, ah2.retention_released_by, ah2.retention_released_on, ah2.approved_by, ah2.approved_on, ah2.deleted_by, ah2.deleted_on, ah2.updated_by, ah2.updated_on, ah2.created_by, ah2.created_on, ah2.is_punchout
  • Index Cond: (ad1.ap_header_id = ah2.id)
  • Filter: ((ad1.cid = ah2.cid) AND (ad1.gl_transaction_type_id = ah2.gl_transaction_type_id) AND (ad1.post_month = ah2.post_month))
  • Buffers: shared hit=163910
74. 0.000 0.000 ↓ 0.0 0 49,593

Index Scan using pk_lease_customers on public.lease_customers lc (cost=0.42..0.70 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=49,593)

  • Output: lc.id, lc.cid, lc.property_id, lc.lease_id, lc.customer_id, lc.lease_status_type_id, lc.customer_type_id, lc.customer_relationship_id, lc.refund_percent, lc.remote_primary_key, lc.is_ungrouped, lc.allow_access_to_unit, lc.include_on_fmo_statement, lc.guarantee_percent, lc.updated_by, lc.updated_on, lc.created_by, lc.created_on, lc.responsible_ledger_filter_ids
  • Index Cond: ((lc.cid = COALESCE(ah2.cid, ah.cid)) AND (lc.id = COALESCE(ah2.lease_customer_id, ah.lease_customer_id)))
  • Buffers: shared hit=4700
75. 0.000 0.000 ↓ 0.0 0 49,593

Index Scan using idx_customers_id on public.customers cust (cost=0.43..0.61 rows=1 width=41) (actual time=0.000..0.000 rows=0 loops=49,593)

  • Output: cust.id, cust.cid, cust.integration_database_id, cust.lead_source_id, cust.leasing_agent_id, cust.message_operator_id, cust.payment_allowance_type_id, cust.marital_status_type_id, cust.company_identification_type_id, cust.primary_phone_number_type_id, cust.secondary_phone_number_type_id, cust.remote_primary_key, cust.secondary_number, cust.company_name, cust.name_prefix, cust.name_first, cust.name_middle, cust.name_last, cust.name_suffix, cust.name_maiden, cust.name_spouse, cust.primary_street_line1, cust.primary_street_line2, cust.primary_street_line3, cust.primary_city, cust.primary_state_code, cust.primary_postal_code, cust.primary_country_code, cust.primary_is_verified, cust.phone_number, cust.mobile_number, cust.work_number, cust.fax_number, cust.email_address, cust.tax_number_encrypted, cust.tax_number_masked, cust.returned_payments_count, cust.birth_date, cust.gender, cust.dl_number_encrypted, cust.dl_state_code, cust.dl_province, cust.identification_value, cust.identification_expiration, cust.notes, cust.dont_allow_login, cust.imported_on, cust.exported_by, cust.exported_on, cust.updated_by, cust.updated_on, cust.created_on, cust.created_by, cust.is_organization, cust.alt_name_first, cust.alt_name_middle, cust.alt_name_last, cust.preferred_name, cust.preferred_locale_code, cust.name_last_matronymic, cust.tax_id_type_id, cust.name_full, cust.details
  • Index Cond: (cust.id = lc.customer_id)
  • Filter: (cust.cid = lc.cid)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4864
76. 49.593 49.593 ↓ 0.0 0 49,593

Index Scan using idx_ap_payments_id on public.ap_payments ap_ref (cost=0.42..0.63 rows=1 width=17) (actual time=0.001..0.001 rows=0 loops=49,593)

  • Output: ap_ref.id, ap_ref.cid, ap_ref.merchant_account_id, ap_ref.bank_account_id, ap_ref.merchant_gateway_id, ap_ref.payment_medium_id, ap_ref.ap_payment_type_id, ap_ref.payment_status_type_id, ap_ref.return_type_id, ap_ref.ap_payment_id, ap_ref.check_account_type_id, ap_ref.ap_payment_batch_id, ap_ref.source_check_account_type_id, ap_ref.ap_remittance_id, ap_ref.eft_reference, ap_ref.remote_primary_key, ap_ref.return_remote_primary_key, ap_ref.secure_reference_number, ap_ref.payment_memo, ap_ref.payment_date, ap_ref.issue_datetime, ap_ref.payment_amount, ap_ref.payee_name, ap_ref.billto_ip_address, ap_ref.payment_number, ap_ref.check_bank_name, ap_ref.check_name_on_account, ap_ref.check_name_on_account_line_2, ap_ref.check_routing_number, ap_ref.check_fractional_routing_number, ap_ref.check_account_number_encrypted, ap_ref.source_check_bank_name, ap_ref.source_check_name_on_account, ap_ref.source_check_routing_number, ap_ref.source_check_account_number_encrypted, ap_ref.is_reversed, ap_ref.is_quick_check, ap_ref.is_unclaimed_property, ap_ref.returned_on, ap_ref.batched_on, ap_ref.updated_by, ap_ref.updated_on, ap_ref.created_by, ap_ref.created_on
  • Index Cond: (ap_ref.id = ah.ap_payment_id)
  • Filter: ((ap_ref.cid = 14181) AND (ap_ref.cid = ah.cid))
  • Buffers: shared hit=83748
77. 0.000 0.000 ↓ 0.0 0 49,593

Index Scan using idx_ap_payments_id on public.ap_payments ap (cost=0.42..0.63 rows=1 width=17) (actual time=0.000..0.000 rows=0 loops=49,593)

  • Output: ap.id, ap.cid, ap.merchant_account_id, ap.bank_account_id, ap.merchant_gateway_id, ap.payment_medium_id, ap.ap_payment_type_id, ap.payment_status_type_id, ap.return_type_id, ap.ap_payment_id, ap.check_account_type_id, ap.ap_payment_batch_id, ap.source_check_account_type_id, ap.ap_remittance_id, ap.eft_reference, ap.remote_primary_key, ap.return_remote_primary_key, ap.secure_reference_number, ap.payment_memo, ap.payment_date, ap.issue_datetime, ap.payment_amount, ap.payee_name, ap.billto_ip_address, ap.payment_number, ap.check_bank_name, ap.check_name_on_account, ap.check_name_on_account_line_2, ap.check_routing_number, ap.check_fractional_routing_number, ap.check_account_number_encrypted, ap.source_check_bank_name, ap.source_check_name_on_account, ap.source_check_routing_number, ap.source_check_account_number_encrypted, ap.is_reversed, ap.is_quick_check, ap.is_unclaimed_property, ap.returned_on, ap.batched_on, ap.updated_by, ap.updated_on, ap.created_by, ap.created_on
  • Index Cond: (ap.id = ah1.ap_payment_id)
  • Filter: ((ap.cid = 14181) AND (ap.cid = ah1.cid))
  • Buffers: shared hit=872
78. 99.186 99.186 ↑ 1.0 1 49,593

Index Scan using idx_ap_payees_id on public.ap_payees app (cost=0.41..0.47 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=49,593)

  • Output: app.id, app.cid, app.integration_database_id, app.ap_payee_term_id, app.default_ap_remittance_id, app.gl_account_id, app.vendor_id, app.ap_payee_type_id, app.ap_payee_status_type_id, app.categorization_type_id, app.confidentiality_allowance_type_id, app.cam_allowance_type_id, app.vendor_category_type_id, app.compliance_rulset_id, app.reference_id, app.customer_id, app.secondary_number, app.remote_primary_key, app.system_code, app.company_name, app.username, app.website_url, app.notes, app.sales_tax_description, app.shipping_description, app.discount_description, app.require_po_for_invoice, app.is_categorization_queued, app.is_consolidated, app.is_system, app.is_on_site, app.is_utilities, app.is_capture_invoice_total_only, app.updated_by, app.updated_on, app.created_by, app.created_on, app.details
  • Index Cond: (app.id = COALESCE(ah.ap_payee_id, ah2.ap_payee_id))
  • Filter: (app.cid = COALESCE(ah.cid, ah2.cid))
  • Buffers: shared hit=163868
79. 0.467 6.615 ↓ 4.1 1,254 1

Subquery Scan on *SELECT* 3 (cost=175.55..197.66 rows=305 width=579) (actual time=5.473..6.615 rows=1,254 loops=1)

  • Output: 1, "*SELECT* 3".property_id, "*SELECT* 3".property_name, "*SELECT* 3".lookup_code, "*SELECT* 3".grouping_gl_account_id, "*SELECT* 3".gl_account, "*SELECT* 3".account_name, "*SELECT* 3".account_number, NULL::date, 'Beginning Balance '::text, NULL::text, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::character varying, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::character varying, NULL::integer, NULL::integer, NULL::text, NULL::jsonb, NULL::integer, NULL::integer, NULL::integer, NULL::bigint, NULL::integer, NULL::integer, NULL::bigint, NULL::integer, 0, 0, 0, NULL::character varying, NULL::integer, NULL::integer, NULL::integer, "*SELECT* 3".beginning_balance, "*SELECT* 3".row_balance
  • Buffers: shared hit=28, local hit=3796 read=5
80. 1.635 6.148 ↓ 4.1 1,254 1

HashAggregate (cost=175.55..186.22 rows=305 width=495) (actual time=5.471..6.148 rows=1,254 loops=1)

  • Output: 1, p.id, p.property_name, p.lookup_code, gat_2.grouping_gl_account_id, gat_2.gl_account, gat_2.name, gat_2.formatted_account_number, NULL::date, 'Beginning Balance '::text, NULL::text, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::character varying, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::character varying, NULL::integer, NULL::integer, NULL::text, NULL::jsonb, NULL::integer, NULL::integer, NULL::integer, NULL::bigint, NULL::integer, NULL::integer, NULL::bigint, NULL::integer, 0, 0, 0, NULL::character varying, NULL::integer, NULL::integer, NULL::integer, sum(COALESCE(bbt.beginning_balance, '0'::numeric)), sum(COALESCE(bbt.beginning_balance, '0'::numeric))
  • 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
  • Buffers: shared hit=28, local hit=3796 read=5
81. 0.351 4.513 ↓ 4.1 1,258 1

Merge Join (cost=166.60..168.69 rows=305 width=99) (actual time=0.091..4.513 rows=1,258 loops=1)

  • Output: p.id, p.property_name, p.lookup_code, gat_2.grouping_gl_account_id, gat_2.gl_account, gat_2.name, gat_2.formatted_account_number, bbt.beginning_balance
  • Merge Cond: (p.id = load_prop_2.property_id)
  • Buffers: shared hit=28, local hit=3796 read=5
82. 0.927 4.043 ↓ 17.5 1,258 1

Nested Loop (cost=49.47..195.78 rows=72 width=103) (actual time=0.071..4.043 rows=1,258 loops=1)

  • Output: p.id, p.property_name, p.lookup_code, gat_2.grouping_gl_account_id, gat_2.gl_account, gat_2.name, gat_2.formatted_account_number, bbt.beginning_balance, bbt.property_id
  • Buffers: shared hit=28, local hit=3795 read=5
83. 0.237 0.600 ↓ 17.0 1,258 1

Merge Join (cost=49.19..116.67 rows=74 width=46) (actual time=0.050..0.600 rows=1,258 loops=1)

  • Output: p.id, p.property_name, p.lookup_code, p.cid, bbt.beginning_balance, bbt.property_id, bbt.gl_account_id
  • Merge Cond: (p.id = bbt.property_id)
  • Buffers: shared hit=28, local hit=14 read=5
84. 0.069 0.069 ↑ 2.6 25 1

Index Scan using idx_properties_cid_id_is_disabled_remote_primary_key on public.properties p (cost=0.28..61.85 rows=66 width=34) (actual time=0.010..0.069 rows=25 loops=1)

  • Output: p.id, p.cid, p.property_id, p.property_type_id, p.company_region_id, p.time_zone_id, p.owner_id, p.account_id, p.occupancy_type_ids, p.remote_primary_key, p.property_name, p.lookup_code, p.country_code, p.vaultware_number, p.min_rent, p.max_rent, p.min_square_feet, p.max_square_feet, p.min_bedrooms, p.max_bedrooms, p.min_bathrooms, p.max_bathrooms, p.number_of_units, p.year_built, p.year_remodeled, p.short_description, p.full_description, p.driving_directions, p.termination_reason, p.termination_date, p.details, p.allows_cats, p.allows_dogs, p.has_availability, p.is_disabled, p.is_test, p.is_managerial, p.order_num, p.imported_on, p.disabled_on, p.updated_by, p.updated_on, p.created_by, p.created_on, p.locale_code, p.default_occupancy_type_id
  • Index Cond: (p.cid = 14181)
  • Buffers: shared hit=28
85. 0.294 0.294 ↑ 1.0 1,258 1

Index Scan using idx_beg_bal_temp_pid on pg_temp_79.beg_bal_temp bbt (cost=0.28..61.45 rows=1,258 width=16) (actual time=0.020..0.294 rows=1,258 loops=1)

  • Output: bbt.cid, bbt.property_id, bbt.gl_account_id, bbt.beginning_balance
  • Filter: (bbt.cid = 14181)
  • Buffers: local hit=14 read=5
86. 2.516 2.516 ↑ 1.0 1 1,258

Index Scan using idx_temp_gat_gl_account_id on pg_temp_79.gat_temp gat_2 (cost=0.28..1.04 rows=1 width=73) (actual time=0.002..0.002 rows=1 loops=1,258)

  • Output: gat_2.cid, gat_2.grouping_gl_account_id, gat_2.formatted_account_number, gat_2.gl_account, gat_2.name, gat_2.gl_account_type_id, gat_2.gl_account_id, gat_2.gl_branch_id, gat_2.gl_tree_id
  • Index Cond: (gat_2.gl_account_id = bbt.gl_account_id)
  • Filter: ((gat_2.cid = 14181) AND (gat_2.grouping_gl_account_id = ANY ('{276755,277181,276723,282512,277994,282520,277182,276734,276746,276756,276757,276747,276758,276759,277856,305890,276760,277858,276761,276762,305887,276763,276764,276765,276766,282511,276752,276744,276751,276767,276768,276769,276770,279436,282510,276771,276772,276773,276774,276775,276776,276777,281803,276778,276779,281804,276780,276781,276782,281805,276783,281806,276784,281807,276785,281808,276786,281809,305917,305918,276787,281810,276788,281811,276789,281812,276790,281813,276791,281814,276792,281815,276793,276794,282471,305892,305891,305919,305920,305924,305925,306536,306537,305894,305893,276795,281816,276796,281817,276797,281818,276798,281819,276799,281820,276800,281821,276801,281822,276802,276803,281823,276804,281774,276805,281839,276806,281824,276807,281825,276808,281826,276809,281832,276810,281833,276811,281834,276812,281835,276813,276814,281836,276815,281837,276816,281838,276817,281827,276818,281828,276819,276820,281829,276821,282513,276822,276823,281830,276824,282521,276825,276826,276827,276828,276829,276830,276831,276832,276833,282524,276834,282522,276835,305868,276836,276837,276838,276839,276840,306544,276841,276842,276843,276844,276845,306545,277861,276745,276846,276847,276848,276719,276849,276738,276750,279435,276754,278169,276733,276851,276749,276852,276853,276854,276855,276856,276857,277823,276858,276859,276860,276861,276862,276863,276864,276865,276866,305856,276867,276868,276869,276870,276871,276872,276873,276748,276874,276742,276876,276753,276878,276879,276880,276881,277862,276882,276740,276739,276730,276731,276883,276736,276735,276737,276884,276885,276886,276887,276888,276889,276890,276891,277866,277867,276892,276893,276894,276895,276896,276897,276898,276899,276900,276901,276902,276903,276904,276905,276906,276907,276908,276909,276910,276911,276912,276913,276914,276915,276916,276917,276918,276919,276920,276921,276922,276923,276924,276925,276926,276927,276928,276929,276930,276931,276932,276933,276934,276935,276936,276937,276938,276939,276940,276941,276727,276942,276943,276944,276945,276946,276947,276948,276949,276732,276726,276724,276950,276951,276952,276953,276954,276725,276955,276956,276957,276958,276959,276960,276961,276721,276962,276963,276964,276965,276966,276967,276968,276969,276970,276971,276972,276973,276729,276974,276975,276976,276977,276978,276979,276980,276981,276982,276983,276720,276741,276743,276728,276985,276986,276987,276988,276989,276990,276991,276992,276993,276994,276995,276996,276997,276998,276999,277000,277001,277002,277003,277004,277005,277006,277007,277008,282036,277009,277010,277011,277012,277013,277014,277015,277016,277017,277868,277018,277019,277869,277179,277180,277020,277021,277022,277023,277024,277025,277026,277027,277870,277028,276722,277029,277871,277030,305849,277031,277032,277033,277034,277035,277036,277037,277038,277039,277040,277041,277042,277043,277044,277045,277046,277047,277048,277049,277050,277051,277052,277053,277054,277055,277056,277057,277058,277059,277060,277061,277062,277063,277064,277065,277066,277067,277068,277069,277070,277071,277072,277073,277074,277075,277076,277077,277078,277079,277080,277081,277082,277083,277084,277085,277086,277087,277088,277089,277090,277091,277092,277093,277094,277095,277096,277097,277098,277099,277100,277101,277102,277103,277872,277104,277105,277106,277107,277108,277109,277110,277111,276984,277112,282523,277113,277114,281776,281777,281778,281779,281780,281781,281782,281783,281784,281785,281786,281787,281788,281789,281790,281791,281792,281793,281794,305866,277115,277116,277117,277118,277857,277873,277874,277875,277119,277120,277121,277122,277123,277124,277125,277126,277127,277128,277129,277130,277131,277132,277133,277134,277135,277136,277137,277138,277139,277140,305915,277141,277142,277143,277144,277145,277146,277147,277148,277149,277150,277151,277152,277153,277154,277155,277156,277157,277158,277159,277160,277161,277162,277163,277164,277165,277166,277167,277168,277169,277170,277171,277172,277173,277174,282536,277175,277176,277177,305867,277178}'::integer[])))
  • Buffers: local hit=3781
87. 0.111 0.119 ↓ 235.2 941 1

Sort (cost=1.16..1.17 rows=4 width=4) (actual time=0.017..0.119 rows=941 loops=1)

  • Output: load_prop_2.property_id
  • Sort Key: load_prop_2.property_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: local hit=1
88. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on pg_temp_79.load_prop load_prop_2 (cost=0.00..1.12 rows=4 width=4) (actual time=0.008..0.008 rows=4 loops=1)

  • Output: load_prop_2.property_id
  • Buffers: local hit=1
Planning time : 41.232 ms
Execution time : 43,018.501 ms