explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L3uN

Settings
# exclusive inclusive rows x rows loops node
1. 231.788 11,711.017 ↓ 445.2 97,052 1

GroupAggregate (cost=2,500,094.72..2,500,376.03 rows=218 width=1,586) (actual time=11,470.483..11,711.017 rows=97,052 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=2450602 read=10628 dirtied=1, local hit=91413
2. 2,131.636 11,479.229 ↓ 45.2 98,123 1

Sort (cost=2,500,094.72..2,500,100.15 rows=2,172 width=1,554) (actual time=11,470.463..11,479.229 rows=98,123 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: quicksort Memory: 65263kB
  • Buffers: shared hit=2450602 read=10628 dirtied=1, local hit=91413
3. 26.716 9,347.593 ↓ 45.2 98,123 1

Subquery Scan on outer_details (cost=2,499,777.87..2,499,974.34 rows=2,172 width=1,554) (actual time=9,123.462..9,347.593 rows=98,123 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: 83
  • Buffers: shared hit=2450582 read=10628 dirtied=1, local hit=91413
4. 189.276 9,320.877 ↓ 45.0 98,206 1

WindowAgg (cost=2,499,777.87..2,499,903.39 rows=2,183 width=1,590) (actual time=9,123.460..9,320.877 rows=98,206 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=2450582 read=10628 dirtied=1, local hit=91413
5. 252.955 9,131.601 ↓ 45.0 98,206 1

Sort (cost=2,499,777.87..2,499,783.33 rows=2,183 width=1,362) (actual time=9,123.442..9,131.601 rows=98,206 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: quicksort Memory: 59138kB
  • Buffers: shared hit=2450582 read=10628 dirtied=1, local hit=91413
6. 117.391 8,878.646 ↓ 45.0 98,206 1

WindowAgg (cost=2,499,569.48..2,499,656.80 rows=2,183 width=1,362) (actual time=8,752.354..8,878.646 rows=98,206 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=2450582 read=10628 dirtied=1, local hit=91413
7. 290.285 8,761.255 ↓ 45.0 98,206 1

Sort (cost=2,499,569.48..2,499,574.94 rows=2,183 width=1,354) (actual time=8,752.343..8,761.255 rows=98,206 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: quicksort Memory: 56974kB
  • Buffers: shared hit=2450582 read=10628 dirtied=1, local hit=91413
8. 36.249 8,470.970 ↓ 45.0 98,206 1

Subquery Scan on details (cost=1,846.82..2,499,448.41 rows=2,183 width=1,354) (actual time=4.309..8,470.970 rows=98,206 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=2450582 read=10628 dirtied=1, local hit=91413
9. 13.084 8,434.721 ↓ 45.0 98,206 1

Append (cost=1,846.82..2,499,382.92 rows=2,183 width=1,390) (actual time=4.307..8,434.721 rows=98,206 loops=1)

  • Buffers: shared hit=2450582 read=10628 dirtied=1, local hit=91413
10. 58.159 8,419.879 ↓ 53.6 97,840 1

Result (cost=1,846.82..2,498,847.58 rows=1,827 width=1,376) (actual time=4.306..8,419.879 rows=97,840 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=2450579 read=10628 dirtied=1, local hit=91400
11. 14.135 8,361.720 ↓ 53.6 97,840 1

Append (cost=1,846.82..2,498,788.20 rows=1,827 width=1,348) (actual time=4.304..8,361.720 rows=97,840 loops=1)

  • Buffers: shared hit=2450579 read=10628 dirtied=1, local hit=91400
12. 1,375.519 7,979.312 ↓ 51.9 91,372 1

Nested Loop Left Join (cost=1,846.82..2,492,745.27 rows=1,760 width=644) (actual time=4.303..7,979.312 rows=91,372 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=2282807 read=9639 dirtied=1, local hit=91382
13. 89.889 6,512.421 ↓ 51.9 91,372 1

Nested Loop Left Join (cost=1,846.52..2,490,107.20 rows=1,760 width=594) (actual time=2.029..6,512.421 rows=91,372 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=2099930 read=9639 dirtied=1, local hit=91382
14. 93.613 6,057.044 ↓ 51.9 91,372 1

Nested Loop Left Join (cost=1,846.10..2,487,822.57 rows=1,760 width=543) (actual time=2.012..6,057.044 rows=91,372 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=1734397 read=9560 dirtied=1, local hit=91382
15. 117.786 3,130.899 ↓ 51.9 91,372 1

Nested Loop Left Join (cost=1,845.13..2,478,239.46 rows=1,760 width=306) (actual time=1.966..3,130.899 rows=91,372 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: 41848
  • Buffers: shared hit=1186065 read=9178 dirtied=1, local hit=10
16. 94.774 2,464.881 ↓ 51.9 91,372 1

Nested Loop Left Join (cost=470.75..57,429.54 rows=1,760 width=294) (actual time=1.927..2,464.881 rows=91,372 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=695221 read=8126 dirtied=1, local hit=10
17. 39.337 725.411 ↓ 51.9 91,372 1

Hash Join (cost=470.31..56,016.18 rows=1,760 width=286) (actual time=1.912..725.411 rows=91,372 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=383514 read=4003 dirtied=1, local hit=10
18. 26.001 686.049 ↓ 51.9 91,372 1

Nested Loop (cost=466.62..56,005.84 rows=1,760 width=266) (actual time=1.878..686.049 rows=91,372 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
  • Buffers: shared hit=383512 read=4003 dirtied=1, local hit=10
19. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on pg_temp_13.load_prop (cost=0.00..1.03 rows=1 width=28) (actual time=0.005..0.005 rows=1 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
  • Filter: (load_prop.property_id = 503992)
  • Buffers: local hit=1
20. 85.566 660.043 ↓ 51.9 91,372 1

Nested Loop (cost=466.62..55,952.01 rows=1,760 width=242) (actual time=1.870..660.043 rows=91,372 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.property_id, gd.cid, 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=383512 read=4003 dirtied=1, local hit=9
21. 36.543 208.989 ↓ 6.2 91,372 1

Hash Join (cost=466.19..23,061.62 rows=14,787 width=145) (actual time=1.840..208.989 rows=91,372 loops=1)

  • Output: gd.post_month, gd.id, gd.memo, gd.amount, gd.lease_id, gd.property_id, gd.cid, gd.gl_header_id, 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=20043 read=1750, local hit=9
22. 170.659 170.659 ↓ 4.0 91,982 1

Index Scan using idx_gl_details_cid_property_id_post_month_accrual_gl_account_id on public.gl_details gd (cost=0.56..22,065.73 rows=23,108 width=80) (actual time=0.045..170.659 rows=91,982 loops=1)

  • 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 = 503992) AND (gd.post_month >= '07/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: 6490
  • Buffers: shared hit=20043 read=1750
23. 0.234 1.787 ↑ 1.0 571 1

Hash (cost=447.07..447.07 rows=571 width=73) (actual time=1.787..1.787 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.gl_account_id
  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
  • Buffers: local hit=9
24. 1.553 1.553 ↑ 1.0 571 1

Seq Scan on pg_temp_13.gat_temp gat (cost=0.00..447.07 rows=571 width=73) (actual time=0.011..1.553 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.gl_account_id
  • Filter: (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. 365.488 365.488 ↑ 1.0 1 91,372

Index Scan using pk_gl_headers on public.gl_headers gh (cost=0.43..2.22 rows=1 width=101) (actual time=0.004..0.004 rows=1 loops=91,372)

  • 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=363469 read=2253 dirtied=1
26. 0.009 0.025 ↑ 1.0 27 1

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

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

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

  • Output: gtty.description, gtty.id
  • Buffers: shared hit=2
28. 1,644.696 1,644.696 ↓ 0.0 0 91,372

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

  • 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=311707 read=4123
29. 182.744 548.232 ↑ 1.0 1 91,372

Bitmap Heap Scan on public.ar_transactions art (cost=1,374.39..1,375.43 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=91,372)

  • 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=91610
  • Buffers: shared hit=490844 read=1052
30. 91.372 365.488 ↓ 0.0 0 91,372

BitmapOr (cost=1,374.39..1,374.39 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=91,372)

  • Buffers: shared hit=399816 read=470
31. 182.744 182.744 ↑ 1.0 1 91,372

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=91,372)

  • Index Cond: ((art.cid = 14181) AND (art.id = gh.reference_id))
  • Buffers: shared hit=274886 read=294
32. 91.372 91.372 ↓ 0.0 0 91,372

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=91,372)

  • Index Cond: ((art.cid = 14181) AND (art.id = ara.credit_ar_transaction_id))
  • Buffers: shared hit=124930 read=176
33. 182.744 2,832.532 ↑ 1.0 1 91,372

Nested Loop Left Join (cost=0.97..5.41 rows=1 width=249) (actual time=0.020..0.031 rows=1 loops=91,372)

  • 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
  • Inner Unique: true
  • Buffers: shared hit=548332 read=382, local hit=91372
34. 1,370.580 2,558.416 ↑ 1.0 1 91,372

Hash Right Join (cost=0.83..5.23 rows=1 width=235) (actual time=0.017..0.028 rows=1 loops=91,372)

  • 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, ac.name
  • Hash Cond: (ac.id = art1.ar_code_id)
  • Buffers: shared hit=365589 read=381, local hit=91372
35. 730.976 730.976 ↑ 1.0 100 91,372

Seq Scan on pg_temp_13.ar_codes_temp ac (cost=0.00..4.00 rows=100 width=21) (actual time=0.002..0.008 rows=100 loops=91,372)

  • Output: ac.id, ac.name, ac.cid
  • Buffers: local hit=91372
36. 91.372 456.860 ↑ 1.0 1 91,372

Hash (cost=0.79..0.79 rows=1 width=218) (actual time=0.005..0.005 rows=1 loops=91,372)

  • 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
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=365589 read=381
37. 365.488 365.488 ↑ 1.0 1 91,372

Index Scan using pk_ar_transactions on public.ar_transactions art1 (cost=0.43..0.79 rows=1 width=218) (actual time=0.004..0.004 rows=1 loops=91,372)

  • 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
  • Index Cond: ((art1.cid = 14181) AND (art1.id = COALESCE(ara.charge_ar_transaction_id, art.id)))
  • Buffers: shared hit=365589 read=381
38. 91.372 91.372 ↑ 1.0 1 91,372

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=91,372)

  • 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=182743 read=1
39. 365.488 365.488 ↑ 1.0 1 91,372

Index Scan using pk_cached_leases on public.cached_leases cl (cost=0.42..1.30 rows=1 width=63) (actual time=0.004..0.004 rows=1 loops=91,372)

  • 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=365533 read=79
40. 91.372 91.372 ↓ 0.0 0 91,372

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=91,372)

  • 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=182744
41. 2.318 368.273 ↓ 96.5 6,468 1

Subquery Scan on *SELECT* 2 (cost=569.94..5,990.13 rows=67 width=522) (actual time=2.487..368.273 rows=6,468 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=167772 read=989, local hit=18
42. 37.958 365.955 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=569.94..5,988.12 rows=67 width=522) (actual time=2.485..365.955 rows=6,468 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=167772 read=989, local hit=18
43. 6.347 315.061 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=569.52..5,907.79 rows=67 width=484) (actual time=2.454..315.061 rows=6,468 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=145580 read=989, local hit=18
44. 7.110 308.714 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=569.10..5,863.56 rows=67 width=479) (actual time=2.449..308.714 rows=6,468 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=145444 read=989, local hit=18
45. 11.098 288.668 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=568.68..5,819.33 rows=67 width=470) (actual time=2.059..288.668 rows=6,468 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=134780 read=941, local hit=18
46. 5.581 277.570 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=568.25..5,776.25 rows=67 width=441) (actual time=2.054..277.570 rows=6,468 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=134020 read=941, local hit=18
47. 6.120 271.989 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=567.83..5,729.02 rows=67 width=433) (actual time=2.049..271.989 rows=6,468 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=133331 read=918, local hit=18
48. 4.682 246.465 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=567.40..5,693.91 rows=67 width=437) (actual time=2.036..246.465 rows=6,468 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=111131 read=918, local hit=18
49. 7.435 241.783 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=566.97..5,640.48 rows=67 width=433) (actual time=2.032..241.783 rows=6,468 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=110995 read=918, local hit=18
50. 2.934 221.412 ↓ 96.5 6,468 1

Hash Left Join (cost=566.54..5,598.72 rows=67 width=393) (actual time=2.016..221.412 rows=6,468 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
  • Hash Cond: (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 = pb.id)
  • Buffers: shared hit=100099 read=918, local hit=18
51. 3.754 218.299 ↓ 96.5 6,468 1

Hash Left Join (cost=519.54..5,549.38 rows=67 width=406) (actual time=1.830..218.299 rows=6,468 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_id, 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: (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=100099 read=918, local hit=14
52. 4.345 214.359 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=471.61..5,499.09 rows=67 width=414) (actual time=1.637..214.359 rows=6,468 loops=1)

  • Output: gd_1.post_month, gd_1.id, gd_1.memo, gd_1.amount, gd_1.lease_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, 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=100099 read=918, local hit=10
53. 8.889 184.142 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=471.17..5,404.20 rows=67 width=370) (actual time=1.616..184.142 rows=6,468 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=78285 read=530, local hit=10
54. 1.684 168.785 ↓ 96.5 6,468 1

Nested Loop Left Join (cost=470.74..5,324.13 rows=67 width=318) (actual time=1.593..168.785 rows=6,468 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=67536 read=389, local hit=10
55. 3.176 121.825 ↓ 96.5 6,468 1

Hash Join (cost=470.31..5,286.43 rows=67 width=306) (actual time=1.188..121.825 rows=6,468 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=47550 read=227, local hit=10
56. 5.838 118.630 ↓ 96.5 6,468 1

Nested Loop (cost=466.62..5,282.51 rows=67 width=282) (actual time=1.162..118.630 rows=6,468 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=47548 read=227, local hit=10
57. 4.043 73.864 ↓ 11.5 6,488 1

Hash Join (cost=466.19..4,425.63 rows=566 width=185) (actual time=1.141..73.864 rows=6,488 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=21793, local hit=10
58. 1.306 68.773 ↓ 7.3 6,490 1

Nested Loop (cost=0.56..3,939.71 rows=885 width=120) (actual time=0.087..68.773 rows=6,490 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=21793, local hit=1
59. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on pg_temp_13.load_prop load_prop_1 (cost=0.00..1.03 rows=1 width=28) (actual time=0.003..0.004 rows=1 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
60. 67.463 67.463 ↓ 7.3 6,490 1

Index Scan using idx_gl_details_cid_property_id_post_month_accrual_gl_account_id on public.gl_details gd_1 (cost=0.56..3,912.13 rows=885 width=96) (actual time=0.080..67.463 rows=6,490 loops=1)

  • 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 >= '07/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: 91982
  • Buffers: shared hit=21793
61. 0.129 1.048 ↑ 1.0 571 1

Hash (cost=447.07..447.07 rows=571 width=73) (actual time=1.048..1.048 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.gl_account_id
  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
  • Buffers: local hit=9
62. 0.919 0.919 ↑ 1.0 571 1

Seq Scan on pg_temp_13.gat_temp gat_1 (cost=0.00..447.07 rows=571 width=73) (actual time=0.009..0.919 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.gl_account_id
  • Filter: (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
63. 38.928 38.928 ↑ 1.0 1 6,488

Index Scan using pk_gl_headers on public.gl_headers gh_1 (cost=0.43..1.51 rows=1 width=101) (actual time=0.006..0.006 rows=1 loops=6,488)

  • 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=25755 read=227
64. 0.007 0.019 ↑ 1.0 27 1

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

  • Output: gtty_1.description, gtty_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=2
65. 0.012 0.012 ↑ 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.012 rows=27 loops=1)

  • Output: gtty_1.description, gtty_1.id
  • Buffers: shared hit=2
66. 45.276 45.276 ↓ 0.0 0 6,468

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

  • 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=19986 read=162
67. 6.468 6.468 ↓ 0.0 0 6,468

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=6,468)

  • 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=10749 read=141
68. 25.872 25.872 ↑ 1.0 1 6,468

Index Scan using idx_ap_details on public.ap_details ad1 (cost=0.43..1.39 rows=1 width=60) (actual time=0.004..0.004 rows=1 loops=6,468)

  • 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=21814 read=388
69. 0.092 0.186 ↑ 1.0 703 1

Hash (cost=25.09..25.09 rows=703 width=8) (actual time=0.186..0.186 rows=703 loops=1)

  • Output: pu.unit_number, pu.id
  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
  • Buffers: local hit=4
70. 0.094 0.094 ↑ 1.0 703 1

Seq Scan on pg_temp_13.property_units_temp pu (cost=0.00..25.09 rows=703 width=8) (actual time=0.009..0.094 rows=703 loops=1)

  • Output: pu.unit_number, pu.id
  • Buffers: local hit=4
71. 0.089 0.179 ↑ 1.0 688 1

Hash (cost=24.64..24.64 rows=688 width=7) (actual time=0.179..0.179 rows=688 loops=1)

  • Output: pb.building_name, pb.id
  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
  • Buffers: local hit=4
72. 0.090 0.090 ↑ 1.0 688 1

Seq Scan on pg_temp_13.property_buildings_temp pb (cost=0.00..24.64 rows=688 width=7) (actual time=0.007..0.090 rows=688 loops=1)

  • Output: pb.building_name, pb.id
  • Buffers: local hit=4
73. 12.936 12.936 ↓ 0.0 0 6,468

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

  • 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=10896
74. 0.000 0.000 ↓ 0.0 0 6,468

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=6,468)

  • 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=136
75. 19.404 19.404 ↑ 1.0 1 6,468

Index Scan using idx_ap_headers on public.ap_headers ah2 (cost=0.43..0.49 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=6,468)

  • 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=22200
76. 0.000 0.000 ↓ 0.0 0 6,468

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=6,468)

  • 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=689 read=23
77. 0.000 0.000 ↓ 0.0 0 6,468

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=6,468)

  • 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=760
78. 12.936 12.936 ↓ 0.0 0 6,468

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.002..0.002 rows=0 loops=6,468)

  • 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=10664 read=48
79. 0.000 0.000 ↓ 0.0 0 6,468

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=6,468)

  • 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=136
80. 12.936 12.936 ↑ 1.0 1 6,468

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=6,468)

  • 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=22192
81. 0.138 1.758 ↓ 1.0 366 1

Subquery Scan on *SELECT* 3 (cost=509.53..535.34 rows=356 width=579) (actual time=1.452..1.758 rows=366 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=3, local hit=13
82. 0.438 1.620 ↓ 1.0 366 1

HashAggregate (cost=509.53..521.99 rows=356 width=495) (actual time=1.449..1.620 rows=366 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=3, local hit=13
83. 0.052 1.182 ↓ 1.0 367 1

Nested Loop (cost=26.21..501.52 rows=356 width=101) (actual time=0.147..1.182 rows=367 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
  • Join Filter: (load_prop_2.property_id = bbt.property_id)
  • Buffers: shared hit=3, local hit=13
84. 0.002 0.015 ↑ 1.0 1 1

Nested Loop (cost=0.28..3.41 rows=1 width=34) (actual time=0.015..0.015 rows=1 loops=1)

  • Output: p.id, p.property_name, p.lookup_code, load_prop_2.property_id
  • Inner Unique: true
  • Buffers: shared hit=3, local hit=1
85. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on pg_temp_13.load_prop load_prop_2 (cost=0.00..1.03 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)

  • Output: load_prop_2.cid, load_prop_2.property_id, load_prop_2.is_student_property, load_prop_2.property_name, load_prop_2.lookup_code, load_prop_2.property_type_id, load_prop_2.occupancy_type_ids
  • Buffers: local hit=1
86. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_properties_id on public.properties p (cost=0.28..2.32 rows=1 width=30) (actual time=0.007..0.007 rows=1 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.id = load_prop_2.property_id)
  • Filter: (p.cid = 14181)
  • Buffers: shared hit=3
87. 0.131 1.115 ↓ 1.0 367 1

Hash Join (cost=25.94..486.54 rows=356 width=75) (actual time=0.130..1.115 rows=367 loops=1)

  • Output: gat_2.grouping_gl_account_id, gat_2.gl_account, gat_2.name, gat_2.formatted_account_number, bbt.beginning_balance, bbt.property_id
  • Hash Cond: (gat_2.gl_account_id = bbt.gl_account_id)
  • Buffers: local hit=12
88. 0.871 0.871 ↑ 1.0 571 1

Seq Scan on pg_temp_13.gat_temp gat_2 (cost=0.00..447.07 rows=571 width=69) (actual time=0.008..0.871 rows=571 loops=1)

  • 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
  • Filter: (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[]))
  • Rows Removed by Filter: 18
  • Buffers: local hit=9
89. 0.057 0.113 ↑ 1.0 367 1

Hash (cost=14.01..14.01 rows=367 width=14) (actual time=0.113..0.113 rows=367 loops=1)

  • Output: bbt.beginning_balance, bbt.property_id, bbt.gl_account_id
  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
  • Buffers: local hit=3
90. 0.056 0.056 ↑ 1.0 367 1

Seq Scan on pg_temp_13.beg_bal_temp bbt (cost=0.00..14.01 rows=367 width=14) (actual time=0.008..0.056 rows=367 loops=1)

  • Output: bbt.beginning_balance, bbt.property_id, bbt.gl_account_id
  • Buffers: local hit=3
Planning time : 39.500 ms
Execution time : 11,717.005 ms