explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5fTy

Settings
# exclusive inclusive rows x rows loops node
1. 260.579 3,000.130 ↓ 443.5 44,351 1

Sort (cost=1,869.32..1,869.57 rows=100 width=209) (actual time=2,987.530..3,000.130 rows=44,351 loops=1)

  • Output: deduction.deduction_id, deduction.deduction_number, deduction.company_id, company.company_name, company.company_number, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction_balance_materialized.deduction_amount_remaining, deduction.deduction_active_state, ((CASE WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'NONE'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'OPEN'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'PARTIAL'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'MATCHED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'BALANCED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'PARTIAL'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'PARTIAL'::enum.deduction_balance_allowed_state)) THEN 'MATCHED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'PARTIAL'::enum.deduction_balance_allowed_state)) THEN 'BALANCED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'PARTIAL'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'PARTIAL'::enum.deduction_balance_allowed_state)) THEN 'MATCHED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'PARTIAL'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'MATCHED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'CLEARED'::enum.deduction_balance_allowed_state)) THEN 'CLEARED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'PARTIAL'::enum.deduction_balance_allowed_state)) THEN 'COMPLETE'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'COMPLETE'::text ELSE 'INVALID'::text END)::enum.deduction_state), ((CASE WHEN (deduction_balance_materialized_3.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_3.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_3.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_3.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_3.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state), ((CASE WHEN (deduction_balance_materialized_4.deduction_amount_remaining = deduction_2.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_4.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_4.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state), (COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state)), deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, (string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text)), ((COALESCE(deduction.deduction_date_closed, (now())::date) - deduction.deduction_date_created))
  • Sort Key: deduction.deduction_number
  • Sort Method: external merge Disk: 7416kB
  • Buffers: shared hit=2881282 read=1833, temp read=2567 written=2570
2. 176.069 2,739.551 ↓ 443.5 44,351 1

Nested Loop (cost=838.08..1,866.00 rows=100 width=209) (actual time=1,208.304..2,739.551 rows=44,351 loops=1)

  • Output: deduction.deduction_id, deduction.deduction_number, deduction.company_id, company.company_name, company.company_number, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction_balance_materialized.deduction_amount_remaining, deduction.deduction_active_state, (CASE WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'NONE'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'OPEN'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'PARTIAL'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'MATCHED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'BALANCED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'PARTIAL'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'PARTIAL'::enum.deduction_balance_allowed_state)) THEN 'MATCHED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'UNRELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'PARTIAL'::enum.deduction_balance_allowed_state)) THEN 'BALANCED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'PARTIAL'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'PARTIAL'::enum.deduction_balance_allowed_state)) THEN 'MATCHED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'PARTIAL'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'MATCHED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'CLEARED'::enum.deduction_balance_allowed_state)) THEN 'CLEARED'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'PARTIAL'::enum.deduction_balance_allowed_state)) THEN 'COMPLETE'::text WHEN ((COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state) = 'RELIABLE'::enum.deduction_reliability_state) AND ((CASE WHEN (deduction_balance_materialized_1.deduction_amount_remaining = deduction_1.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_1.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state = 'BALANCED'::enum.deduction_balance_requested_state) AND ((CASE WHEN (deduction_balance_materialized_2.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_1.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_2.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state = 'OPEN'::enum.deduction_balance_allowed_state)) THEN 'COMPLETE'::text ELSE 'INVALID'::text END)::enum.deduction_state, (CASE WHEN (deduction_balance_materialized_3.deduction_amount_cleared = '0'::numeric) THEN 'OPEN'::text WHEN (((d_3.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_3.deduction_amount_cleared)::double precision) >= '0.01'::double precision) THEN 'PARTIAL'::text WHEN (((d_3.deduction_amount_requested)::double precision <-> (deduction_balance_materialized_3.deduction_amount_cleared)::double precision) < '0.01'::double precision) THEN 'CLEARED'::text ELSE NULL::text END)::enum.deduction_balance_allowed_state, (CASE WHEN (deduction_balance_materialized_4.deduction_amount_remaining = deduction_2.deduction_amount_requested) THEN 'NONE'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_4.deduction_amount_remaining)::double precision)) > '0.01'::double precision) THEN 'PARTIAL'::text WHEN (('0'::double precision <-> (@ (deduction_balance_materialized_4.deduction_amount_remaining)::double precision)) <= '0.01'::double precision) THEN 'BALANCED'::text ELSE NULL::text END)::enum.deduction_balance_requested_state, COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state), deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, (string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text)), (COALESCE(deduction.deduction_date_closed, (now())::date) - deduction.deduction_date_created)
  • Inner Unique: true
  • Buffers: shared hit=2881282 read=1833, temp read=1640 written=1642
3. 49.358 2,474.780 ↓ 443.5 44,351 1

Nested Loop Left Join (cost=837.79..1,763.92 rows=100 width=432) (actual time=1,208.251..2,474.780 rows=44,351 loops=1)

  • Output: company.company_name, company.company_number, ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed, (string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text)), ded.deduction_id, deduction_balance_materialized_4.deduction_amount_remaining, deduction_balance_materialized_4.deduction_id, deduction_balance_materialized.deduction_amount_remaining, deduction_balance_materialized.deduction_id, d_1.deduction_amount_requested, d_1.deduction_id, ('RELIABLE'::enum.deduction_reliability_state), ('RELIABLE'::enum.deduction_reliability_state)
  • Buffers: shared hit=2748050 read=1833, temp read=1640 written=1642
4. 54.661 2,114.965 ↓ 443.5 44,351 1

Nested Loop (cost=835.97..1,409.51 rows=100 width=428) (actual time=1,208.232..2,114.965 rows=44,351 loops=1)

  • Output: company.company_name, company.company_number, ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed, (string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text)), ded.deduction_id, deduction_balance_materialized_4.deduction_amount_remaining, deduction_balance_materialized_4.deduction_id, deduction_balance_materialized.deduction_amount_remaining, deduction_balance_materialized.deduction_id, d_1.deduction_amount_requested, d_1.deduction_id, ('RELIABLE'::enum.deduction_reliability_state)
  • Inner Unique: true
  • Buffers: shared hit=2327086 read=1833, temp read=1640 written=1642
5. 23.516 1,971.602 ↓ 443.5 44,351 1

Nested Loop Left Join (cost=835.55..1,360.11 rows=100 width=406) (actual time=1,208.224..1,971.602 rows=44,351 loops=1)

  • Output: company.company_name, company.company_number, ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed, (string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text)), ded.deduction_id, deduction_balance_materialized_4.deduction_amount_remaining, deduction_balance_materialized_4.deduction_id, deduction_balance_materialized.deduction_amount_remaining, deduction_balance_materialized.deduction_id, ('RELIABLE'::enum.deduction_reliability_state)
  • Buffers: shared hit=2149614 read=1833, temp read=1640 written=1642
6. 17.829 1,637.629 ↓ 443.5 44,351 1

Nested Loop (cost=833.73..1,005.70 rows=100 width=402) (actual time=1,208.196..1,637.629 rows=44,351 loops=1)

  • Output: company.company_name, company.company_number, ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed, (string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text)), ded.deduction_id, deduction_balance_materialized_4.deduction_amount_remaining, deduction_balance_materialized_4.deduction_id, deduction_balance_materialized.deduction_amount_remaining, deduction_balance_materialized.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=1801543 read=1733, temp read=1640 written=1642
7. 32.660 1,531.098 ↓ 443.5 44,351 1

Nested Loop (cost=833.44..972.87 rows=100 width=380) (actual time=1,208.188..1,531.098 rows=44,351 loops=1)

  • Output: company.company_name, company.company_number, ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed, (string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text)), ded.deduction_id, deduction_balance_materialized_4.deduction_amount_remaining, deduction_balance_materialized_4.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=1668329 read=1733, temp read=1640 written=1642
8. 43.853 1,409.736 ↓ 443.5 44,351 1

Nested Loop (cost=833.15..940.05 rows=100 width=358) (actual time=1,208.177..1,409.736 rows=44,351 loops=1)

  • Output: company.company_name, company.company_number, ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed, (string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text)), ded.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=1535115 read=1733, temp read=1640 written=1642
9. 37.862 1,277.181 ↓ 443.5 44,351 1

Merge Left Join (cost=832.73..895.63 rows=100 width=342) (actual time=1,208.153..1,277.181 rows=44,351 loops=1)

  • Output: company.company_name, company.company_number, ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed, (string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text))
  • Inner Unique: true
  • Merge Cond: (deduction.deduction_id = deduction_note.object_id)
  • Buffers: shared hit=1392641 read=1733, temp read=1640 written=1642
10. 117.637 1,223.787 ↓ 443.5 44,351 1

Sort (cost=522.89..523.14 rows=100 width=310) (actual time=1,201.202..1,223.787 rows=44,351 loops=1)

  • Output: company.company_name, company.company_number, ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed
  • Sort Key: ded_1.deduction_id
  • Sort Method: external merge Disk: 13120kB
  • Buffers: shared hit=1392607 read=1733, temp read=1640 written=1642
11. 38.751 1,106.150 ↓ 443.5 44,351 1

Nested Loop (cost=4.61..519.57 rows=100 width=310) (actual time=22.501..1,106.150 rows=44,351 loops=1)

  • Output: company.company_name, company.company_number, ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed
  • Inner Unique: true
  • Buffers: shared hit=1392607 read=1733
12. 21.727 978.697 ↓ 443.5 44,351 1

Nested Loop (cost=4.32..485.20 rows=100 width=278) (actual time=22.491..978.697 rows=44,351 loops=1)

  • Output: ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id, deduction.deduction_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.deduction_active_state, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_label, deduction.client_check_number, deduction.deduction_date_closed
  • Inner Unique: true
  • Buffers: shared hit=1259639 read=1625
13. 50.052 868.268 ↓ 443.5 44,351 1

Nested Loop (cost=3.91..435.80 rows=100 width=155) (actual time=22.485..868.268 rows=44,351 loops=1)

  • Output: ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, d.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=1082167 read=1625
14. 12.906 773.865 ↓ 443.5 44,351 1

Nested Loop (cost=3.49..391.38 rows=100 width=139) (actual time=22.478..773.865 rows=44,351 loops=1)

  • Output: ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=939693 read=1625
15. 15.937 672.257 ↓ 443.5 44,351 1

Nested Loop (cost=3.08..341.97 rows=100 width=117) (actual time=22.473..672.257 rows=44,351 loops=1)

  • Output: ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_2.deduction_amount_requested, deduction_2.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=762221 read=1625
16. 10.420 567.618 ↓ 443.5 44,351 1

Nested Loop (cost=2.66..292.57 rows=100 width=95) (actual time=22.467..567.618 rows=44,351 loops=1)

  • Output: ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=584749 read=1625
17. 48.117 468.496 ↓ 443.5 44,351 1

Nested Loop (cost=2.37..259.74 rows=100 width=73) (actual time=22.461..468.496 rows=44,351 loops=1)

  • Output: ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, d_3.deduction_amount_requested, d_3.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=451535 read=1625
18. 17.114 331.677 ↓ 443.5 44,351 1

Nested Loop (cost=1.96..210.34 rows=100 width=51) (actual time=22.434..331.677 rows=44,351 loops=1)

  • Output: ded_1.deduction_id, unnest.unnest, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=275121 read=567
19. 9.433 181.510 ↓ 443.5 44,351 1

Nested Loop (cost=1.67..177.51 rows=100 width=32) (actual time=22.366..181.510 rows=44,351 loops=1)

  • Output: ded_1.deduction_id, unnest.unnest
  • Inner Unique: true
  • Buffers: shared hit=142474
20. 31.406 39.024 ↓ 443.5 44,351 1

HashAggregate (cost=1.25..2.25 rows=100 width=16) (actual time=22.336..39.024 rows=44,351 loops=1)

  • Output: unnest.unnest
  • Group Key: unnest.unnest
21. 7.618 7.618 ↓ 443.5 44,351 1

Function Scan on pg_catalog.unnest (cost=0.00..1.00 rows=100 width=16) (actual time=3.821..7.618 rows=44,351 loops=1)

  • Output: unnest.unnest
  • Function Call: unnest($1)
22. 133.053 133.053 ↑ 1.0 1 44,351

Index Only Scan using deduction_pkey on client_basic_american.deduction ded_1 (cost=0.41..1.75 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=44,351)

  • Output: ded_1.deduction_id
  • Index Cond: (ded_1.deduction_id = unnest.unnest)
  • Heap Fetches: 9352
  • Buffers: shared hit=142474
23. 133.053 133.053 ↑ 1.0 1 44,351

Index Scan using deduction_balance_materialized_pkey on client_basic_american.deduction_balance_materialized deduction_balance_materialized_2 (cost=0.29..0.33 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=44,351)

  • Output: deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id
  • Index Cond: (deduction_balance_materialized_2.deduction_id = ded_1.deduction_id)
  • Buffers: shared hit=132647 read=567
24. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using deduction_pkey on client_basic_american.deduction d_3 (cost=0.41..0.49 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: d_3.deduction_id, d_3.client_id, d_3.source_row_id, d_3.deduction_number, d_3.company_id, d_3.distributor_invoice_number, d_3.deduction_date_created, d_3.deduction_amount_requested, d_3.client_deduction_amount_open, d_3.client_deduction_reason_major, d_3.client_deduction_reason_minor, d_3.deduction_active_state, d_3.client_check_number, d_3.deduction_label, d_3.deduction_comment, d_3.client_deduction_comment, d_3.bill_to_company_number, d_3.ship_to_company_number, d_3.deduction_date_closed
  • Index Cond: (d_3.deduction_id = deduction_balance_materialized_2.deduction_id)
  • Buffers: shared hit=176414 read=1058
25. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using deduction_balance_materialized_pkey on client_basic_american.deduction_balance_materialized deduction_balance_materialized_1 (cost=0.29..0.33 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id
  • Index Cond: (deduction_balance_materialized_1.deduction_id = deduction_balance_materialized_2.deduction_id)
  • Buffers: shared hit=133214
26. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using deduction_pkey on client_basic_american.deduction deduction_2 (cost=0.41..0.49 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: deduction_2.deduction_id, deduction_2.client_id, deduction_2.source_row_id, deduction_2.deduction_number, deduction_2.company_id, deduction_2.distributor_invoice_number, deduction_2.deduction_date_created, deduction_2.deduction_amount_requested, deduction_2.client_deduction_amount_open, deduction_2.client_deduction_reason_major, deduction_2.client_deduction_reason_minor, deduction_2.deduction_active_state, deduction_2.client_check_number, deduction_2.deduction_label, deduction_2.deduction_comment, deduction_2.client_deduction_comment, deduction_2.bill_to_company_number, deduction_2.ship_to_company_number, deduction_2.deduction_date_closed
  • Index Cond: (deduction_2.deduction_id = deduction_balance_materialized_1.deduction_id)
  • Buffers: shared hit=177472
27. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using deduction_pkey on client_basic_american.deduction deduction_1 (cost=0.41..0.49 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: deduction_1.deduction_id, deduction_1.client_id, deduction_1.source_row_id, deduction_1.deduction_number, deduction_1.company_id, deduction_1.distributor_invoice_number, deduction_1.deduction_date_created, deduction_1.deduction_amount_requested, deduction_1.client_deduction_amount_open, deduction_1.client_deduction_reason_major, deduction_1.client_deduction_reason_minor, deduction_1.deduction_active_state, deduction_1.client_check_number, deduction_1.deduction_label, deduction_1.deduction_comment, deduction_1.client_deduction_comment, deduction_1.bill_to_company_number, deduction_1.ship_to_company_number, deduction_1.deduction_date_closed
  • Index Cond: (deduction_1.deduction_id = deduction_balance_materialized_1.deduction_id)
  • Buffers: shared hit=177472
28. 44.351 44.351 ↑ 1.0 1 44,351

Index Only Scan using deduction_pkey on client_basic_american.deduction d (cost=0.41..0.44 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=44,351)

  • Output: d.deduction_id
  • Index Cond: (d.deduction_id = deduction_1.deduction_id)
  • Heap Fetches: 9352
  • Buffers: shared hit=142474
29. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using deduction_pkey on client_basic_american.deduction (cost=0.41..0.49 rows=1 width=123) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: deduction.deduction_id, deduction.client_id, deduction.source_row_id, deduction.deduction_number, deduction.company_id, deduction.distributor_invoice_number, deduction.deduction_date_created, deduction.deduction_amount_requested, deduction.client_deduction_amount_open, deduction.client_deduction_reason_major, deduction.client_deduction_reason_minor, deduction.deduction_active_state, deduction.client_check_number, deduction.deduction_label, deduction.deduction_comment, deduction.client_deduction_comment, deduction.bill_to_company_number, deduction.ship_to_company_number, deduction.deduction_date_closed
  • Index Cond: (deduction.deduction_id = deduction_1.deduction_id)
  • Buffers: shared hit=177472
30. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using company_pkey on client_basic_american.company (cost=0.29..0.34 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: company.company_id, company.client_id, company.company_name, company.company_number, company.company_type_id, company.active_ind, company.address_1, company.address_2, company.address_3, company.address_4, company.city, company.region, company.postal_code, company.country, company.company_agg_id, company.deduction_interval, company.alt_address_active, company.alt_address_1, company.alt_address_2, company.alt_address_3, company.alt_address_4, company.alt_city, company.alt_region, company.alt_postal_code, company.alt_country, company.atlas_company_id, company.email, company.tax_type_id, company.redistributor_ind, company.operator_request, company.early_payment_discount, company.vip
  • Index Cond: (company.company_id = deduction.company_id)
  • Buffers: shared hit=132968 read=108
31. 7.902 15.532 ↓ 1.0 1,564 1

GroupAggregate (cost=309.84..352.73 rows=1,557 width=48) (actual time=6.942..15.532 rows=1,564 loops=1)

  • Output: deduction_note.object_id, string_agg(format('%1$s - %2$s: %3$s'::text, deduction_note.note_timestamp, ((account_foreign.first_name || ' '::text) || account_foreign.last_name), deduction_note.note), ' ~~ '::text)
  • Group Key: deduction_note.object_id
  • Buffers: shared hit=34
32. 1.423 7.630 ↓ 1.0 1,578 1

Sort (cost=309.84..313.74 rows=1,562 width=114) (actual time=6.915..7.630 rows=1,578 loops=1)

  • Output: deduction_note.object_id, deduction_note.note_timestamp, account_foreign.first_name, account_foreign.last_name, deduction_note.note
  • Sort Key: deduction_note.object_id
  • Sort Method: quicksort Memory: 365kB
  • Buffers: shared hit=34
33. 0.598 6.207 ↓ 1.0 1,578 1

Hash Join (cost=157.88..226.98 rows=1,562 width=114) (actual time=5.453..6.207 rows=1,578 loops=1)

  • Output: deduction_note.object_id, deduction_note.note_timestamp, account_foreign.first_name, account_foreign.last_name, deduction_note.note
  • Hash Cond: (deduction_note.account_id = account_client.account_id)
  • Buffers: shared hit=34
34. 0.178 0.178 ↓ 1.0 1,578 1

Seq Scan on client_basic_american.deduction_note (cost=0.00..47.62 rows=1,562 width=117) (actual time=0.011..0.178 rows=1,578 loops=1)

  • Output: deduction_note.note_id, deduction_note.object_id, deduction_note.account_id, deduction_note.note, deduction_note.note_timestamp, deduction_note.notify_account_ids
  • Buffers: shared hit=32
35. 0.024 5.431 ↑ 1.0 103 1

Hash (cost=156.59..156.59 rows=103 width=29) (actual time=5.431..5.431 rows=103 loops=1)

  • Output: account_foreign.first_name, account_foreign.last_name, account_client.account_id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=2
36. 0.185 5.407 ↑ 1.0 103 1

Hash Join (cost=104.32..156.59 rows=103 width=29) (actual time=5.168..5.407 rows=103 loops=1)

  • Output: account_foreign.first_name, account_foreign.last_name, account_client.account_id
  • Inner Unique: true
  • Hash Cond: (account_foreign.account_id = account_client.account_id_foreign)
  • Buffers: shared hit=2
37. 5.173 5.173 ↓ 1.3 1,240 1

Foreign Scan on global.account_foreign (cost=100.00..149.67 rows=989 width=29) (actual time=5.094..5.173 rows=1,240 loops=1)

  • Output: account_foreign.account_id, account_foreign.email, account_foreign.password, account_foreign.first_name, account_foreign.last_name, account_foreign.create_date, account_foreign.timezone
  • Remote SQL: SELECT account_id, first_name, last_name FROM public.account
38. 0.022 0.049 ↑ 1.0 103 1

Hash (cost=3.03..3.03 rows=103 width=32) (actual time=0.049..0.049 rows=103 loops=1)

  • Output: account_client.account_id_foreign, account_client.account_id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=2
39. 0.027 0.027 ↑ 1.0 103 1

Seq Scan on client_basic_american.account_client (cost=0.00..3.03 rows=103 width=32) (actual time=0.006..0.027 rows=103 loops=1)

  • Output: account_client.account_id_foreign, account_client.account_id
  • Buffers: shared hit=2
40. 88.702 88.702 ↑ 1.0 1 44,351

Index Only Scan using deduction_pkey on client_basic_american.deduction ded (cost=0.41..0.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: ded.deduction_id
  • Index Cond: (ded.deduction_id = deduction.deduction_id)
  • Heap Fetches: 9352
  • Buffers: shared hit=142474
41. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using deduction_balance_materialized_pkey on client_basic_american.deduction_balance_materialized deduction_balance_materialized_4 (cost=0.29..0.33 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: deduction_balance_materialized_4.deduction_amount_remaining, deduction_balance_materialized_4.deduction_id
  • Index Cond: (deduction_balance_materialized_4.deduction_id = deduction.deduction_id)
  • Buffers: shared hit=133214
42. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using deduction_balance_materialized_pkey on client_basic_american.deduction_balance_materialized (cost=0.29..0.33 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: deduction_balance_materialized.deduction_amount_remaining, deduction_balance_materialized.deduction_id
  • Index Cond: (deduction_balance_materialized.deduction_id = deduction.deduction_id)
  • Buffers: shared hit=133214
43. 26.782 310.457 ↓ 0.0 0 44,351

Nested Loop Anti Join (cost=1.83..3.53 rows=1 width=20) (actual time=0.007..0.007 rows=0 loops=44,351)

  • Output: d_4.deduction_id, 'RELIABLE'::enum.deduction_reliability_state
  • Buffers: shared hit=348071 read=100
44. 0.000 221.755 ↓ 0.0 0 44,351

Nested Loop Semi Join (cost=1.12..1.99 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=44,351)

  • Output: d_4.deduction_id
  • Buffers: shared hit=271230 read=27
45. 88.702 88.702 ↑ 1.0 1 44,351

Index Only Scan using deduction_pkey on client_basic_american.deduction d_4 (cost=0.41..0.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: d_4.deduction_id
  • Index Cond: (d_4.deduction_id = ded_1.deduction_id)
  • Heap Fetches: 9352
  • Buffers: shared hit=142815
46. 57.042 133.053 ↓ 0.0 0 44,351

Nested Loop (cost=0.71..1.53 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=44,351)

  • Output: deduction_claim_2.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=128415 read=27
47. 44.351 44.351 ↓ 0.0 0 44,351

Index Scan using idx_deduction_claim_deduction_id on client_basic_american.deduction_claim deduction_claim_2 (cost=0.29..0.31 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=44,351)

  • Output: deduction_claim_2.deduction_id, deduction_claim_2.claim_id, deduction_claim_2.deduction_amount_allotted, deduction_claim_2.active_range, deduction_claim_2.inoperative, deduction_claim_2.deduction_claim_id, deduction_claim_2.match_account_id, deduction_claim_2.unmatch_account_id
  • Index Cond: (deduction_claim_2.deduction_id = d_4.deduction_id)
  • Buffers: shared hit=96754 read=2
48. 31.660 31.660 ↑ 1.0 1 7,915

Index Scan using claim_pkey on client_basic_american.claim cl_2 (cost=0.42..1.22 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=7,915)

  • Output: cl_2.claim_id, cl_2.invoice_id, cl_2.claim_state, cl_2.claim_sequence, cl_2.transaction_type, cl_2.contract_sequence, cl_2.claim_tag
  • Index Cond: (cl_2.claim_id = deduction_claim_2.claim_id)
  • Filter: (cl_2.claim_state = ANY ('{COMPLETE,REJECTED}'::enum.claim_state[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=31661 read=25
49. 6.687 61.920 ↓ 0.0 0 7,740

Nested Loop (cost=0.71..1.54 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=7,740)

  • Output: deduction_claim_3.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=76841 read=73
50. 15.480 15.480 ↓ 2.0 2 7,740

Index Scan using idx_deduction_claim_deduction_id on client_basic_american.deduction_claim deduction_claim_3 (cost=0.29..0.31 rows=1 width=32) (actual time=0.002..0.002 rows=2 loops=7,740)

  • Output: deduction_claim_3.deduction_id, deduction_claim_3.claim_id, deduction_claim_3.deduction_amount_allotted, deduction_claim_3.active_range, deduction_claim_3.inoperative, deduction_claim_3.deduction_claim_id, deduction_claim_3.match_account_id, deduction_claim_3.unmatch_account_id
  • Index Cond: (d_4.deduction_id = deduction_claim_3.deduction_id)
  • Buffers: shared hit=23766 read=49
51. 39.753 39.753 ↓ 0.0 0 13,251

Index Scan using claim_pkey on client_basic_american.claim cl_3 (cost=0.42..1.22 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=13,251)

  • Output: cl_3.claim_id, cl_3.invoice_id, cl_3.claim_state, cl_3.claim_sequence, cl_3.transaction_type, cl_3.contract_sequence, cl_3.claim_tag
  • Index Cond: (cl_3.claim_id = deduction_claim_3.claim_id)
  • Filter: (cl_3.claim_state <> ALL ('{COMPLETE,REJECTED}'::enum.claim_state[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=53075 read=24
52. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using deduction_pkey on client_basic_american.deduction d_1 (cost=0.41..0.49 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: d_1.deduction_id, d_1.client_id, d_1.source_row_id, d_1.deduction_number, d_1.company_id, d_1.distributor_invoice_number, d_1.deduction_date_created, d_1.deduction_amount_requested, d_1.client_deduction_amount_open, d_1.client_deduction_reason_major, d_1.client_deduction_reason_minor, d_1.deduction_active_state, d_1.client_check_number, d_1.deduction_label, d_1.deduction_comment, d_1.client_deduction_comment, d_1.bill_to_company_number, d_1.ship_to_company_number, d_1.deduction_date_closed
  • Index Cond: (d_1.deduction_id = deduction.deduction_id)
  • Buffers: shared hit=177472
53. 0.372 310.457 ↓ 0.0 0 44,351

Nested Loop Semi Join (cost=1.83..3.53 rows=1 width=20) (actual time=0.007..0.007 rows=0 loops=44,351)

  • Output: d_2.deduction_id, 'RELIABLE'::enum.deduction_reliability_state
  • Buffers: shared hit=420964
54. 44.351 221.755 ↑ 1.0 1 44,351

Nested Loop Anti Join (cost=1.12..1.99 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=44,351)

  • Output: d_2.deduction_id
  • Buffers: shared hit=293883
55. 88.702 88.702 ↑ 1.0 1 44,351

Index Only Scan using deduction_pkey on client_basic_american.deduction d_2 (cost=0.41..0.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: d_2.deduction_id
  • Index Cond: (d_2.deduction_id = ded.deduction_id)
  • Heap Fetches: 9352
  • Buffers: shared hit=142815
56. 17.527 88.702 ↓ 0.0 0 44,351

Nested Loop (cost=0.71..1.54 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=44,351)

  • Output: deduction_claim_1.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=151068
57. 44.351 44.351 ↓ 0.0 0 44,351

Index Scan using idx_deduction_claim_deduction_id on client_basic_american.deduction_claim deduction_claim_1 (cost=0.29..0.31 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=44,351)

  • Output: deduction_claim_1.deduction_id, deduction_claim_1.claim_id, deduction_claim_1.deduction_amount_allotted, deduction_claim_1.active_range, deduction_claim_1.inoperative, deduction_claim_1.deduction_claim_id, deduction_claim_1.match_account_id, deduction_claim_1.unmatch_account_id
  • Index Cond: (d_2.deduction_id = deduction_claim_1.deduction_id)
  • Buffers: shared hit=97325
58. 26.824 26.824 ↓ 0.0 0 13,412

Index Scan using claim_pkey on client_basic_american.claim cl_1 (cost=0.42..1.22 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=13,412)

  • Output: cl_1.claim_id, cl_1.invoice_id, cl_1.claim_state, cl_1.claim_sequence, cl_1.transaction_type, cl_1.contract_sequence, cl_1.claim_tag
  • Index Cond: (cl_1.claim_id = deduction_claim_1.claim_id)
  • Filter: (cl_1.claim_state <> ALL ('{COMPLETE,REJECTED}'::enum.claim_state[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=53743
59. 28.735 88.330 ↓ 0.0 0 44,165

Nested Loop (cost=0.71..1.53 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=44,165)

  • Output: deduction_claim.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=127081
60. 44.165 44.165 ↓ 0.0 0 44,165

Index Scan using idx_deduction_claim_deduction_id on client_basic_american.deduction_claim (cost=0.29..0.31 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=44,165)

  • Output: deduction_claim.deduction_id, deduction_claim.claim_id, deduction_claim.deduction_amount_allotted, deduction_claim.active_range, deduction_claim.inoperative, deduction_claim.deduction_claim_id, deduction_claim.match_account_id, deduction_claim.unmatch_account_id
  • Index Cond: (deduction_claim.deduction_id = d_2.deduction_id)
  • Buffers: shared hit=96196
61. 15.430 15.430 ↑ 1.0 1 7,715

Index Scan using claim_pkey on client_basic_american.claim cl (cost=0.42..1.22 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=7,715)

  • Output: cl.claim_id, cl.invoice_id, cl.claim_state, cl.claim_sequence, cl.transaction_type, cl.contract_sequence, cl.claim_tag
  • Index Cond: (cl.claim_id = deduction_claim.claim_id)
  • Filter: (cl.claim_state = ANY ('{COMPLETE,REJECTED}'::enum.claim_state[]))
  • Buffers: shared hit=30885
62. 88.702 88.702 ↑ 1.0 1 44,351

Index Scan using deduction_balance_materialized_pkey on client_basic_american.deduction_balance_materialized deduction_balance_materialized_3 (cost=0.29..0.33 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=44,351)

  • Output: deduction_balance_materialized_3.deduction_amount_cleared, deduction_balance_materialized_3.deduction_id
  • Index Cond: (deduction_balance_materialized_3.deduction_id = deduction.deduction_id)
  • Buffers: shared hit=133214
Planning time : 55.266 ms
Execution time : 3,010.590 ms