explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bl2f

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

Sort (cost=1,869.32..1,869.57 rows=100 width=209) (actual time=3,247.023..3,268.164 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=2883097, temp read=2567 written=2570
2. 215.940 2,915.295 ↓ 443.5 44,351 1

Nested Loop (cost=838.08..1,866.00 rows=100 width=209) (actual time=1,170.922..2,915.295 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=2883097, temp read=1640 written=1642
3. 53.295 2,610.653 ↓ 443.5 44,351 1

Nested Loop Left Join (cost=837.79..1,763.92 rows=100 width=432) (actual time=1,170.900..2,610.653 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=2749883, temp read=1640 written=1642
4. 73.369 2,202.550 ↓ 443.5 44,351 1

Nested Loop (cost=835.97..1,409.51 rows=100 width=428) (actual time=1,170.875..2,202.550 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=2328919, temp read=1640 written=1642
5. 24.471 2,040.479 ↓ 443.5 44,351 1

Nested Loop Left Join (cost=835.55..1,360.11 rows=100 width=406) (actual time=1,170.866..2,040.479 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=2151447, temp read=1640 written=1642
6. 30.960 1,661.200 ↓ 443.5 44,351 1

Nested Loop (cost=833.73..1,005.70 rows=100 width=402) (actual time=1,170.843..1,661.200 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=1803276, temp read=1640 written=1642
7. 50.838 1,541.538 ↓ 443.5 44,351 1

Nested Loop (cost=833.44..972.87 rows=100 width=380) (actual time=1,170.832..1,541.538 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=1670062, temp read=1640 written=1642
8. 62.959 1,401.998 ↓ 443.5 44,351 1

Nested Loop (cost=833.15..940.05 rows=100 width=358) (actual time=1,170.818..1,401.998 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=1536848, temp read=1640 written=1642
9. 42.622 1,250.337 ↓ 443.5 44,351 1

Merge Left Join (cost=832.73..895.63 rows=100 width=342) (actual time=1,170.794..1,250.337 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=1394374, temp read=1640 written=1642
10. 118.610 1,188.399 ↓ 443.5 44,351 1

Sort (cost=522.89..523.14 rows=100 width=310) (actual time=1,162.538..1,188.399 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=1394340, temp read=1640 written=1642
11. 35.825 1,069.789 ↓ 443.5 44,351 1

Nested Loop (cost=4.61..519.57 rows=100 width=310) (actual time=21.859..1,069.789 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=1394340
12. 19.406 945.262 ↓ 443.5 44,351 1

Nested Loop (cost=4.32..485.20 rows=100 width=278) (actual time=21.849..945.262 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=1261264
13. 47.070 837.154 ↓ 443.5 44,351 1

Nested Loop (cost=3.91..435.80 rows=100 width=155) (actual time=21.844..837.154 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=1083792
14. 10.513 745.733 ↓ 443.5 44,351 1

Nested Loop (cost=3.49..391.38 rows=100 width=139) (actual time=21.838..745.733 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=941318
15. 14.111 646.518 ↓ 443.5 44,351 1

Nested Loop (cost=3.08..341.97 rows=100 width=117) (actual time=21.833..646.518 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=763846
16. 7.558 543.705 ↓ 443.5 44,351 1

Nested Loop (cost=2.66..292.57 rows=100 width=95) (actual time=21.828..543.705 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=586374
17. 38.745 447.445 ↓ 443.5 44,351 1

Nested Loop (cost=2.37..259.74 rows=100 width=73) (actual time=21.822..447.445 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=453160
18. 10.005 319.998 ↓ 443.5 44,351 1

Nested Loop (cost=1.96..210.34 rows=100 width=51) (actual time=21.808..319.998 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=275688
19. 6.027 176.940 ↓ 443.5 44,351 1

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

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

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

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

Function Scan on pg_catalog.unnest (cost=0.00..1.00 rows=100 width=16) (actual time=3.866..7.282 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=133214
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=177472
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=133076
31. 10.257 19.316 ↓ 1.0 1,564 1

GroupAggregate (cost=309.84..352.73 rows=1,557 width=48) (actual time=8.247..19.316 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.893 9.059 ↓ 1.0 1,578 1

Sort (cost=309.84..313.74 rows=1,562 width=114) (actual time=8.210..9.059 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.991 7.166 ↓ 1.0 1,578 1

Hash Join (cost=157.88..226.98 rows=1,562 width=114) (actual time=5.912..7.166 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.289 0.289 ↓ 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.289 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.044 5.886 ↑ 1.0 103 1

Hash (cost=156.59..156.59 rows=103 width=29) (actual time=5.886..5.886 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.364 5.842 ↑ 1.0 103 1

Hash Join (cost=104.32..156.59 rows=103 width=29) (actual time=5.361..5.842 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.428 5.428 ↓ 1.3 1,240 1

Foreign Scan on global.account_foreign (cost=100.00..149.67 rows=989 width=29) (actual time=5.283..5.428 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.050 ↑ 1.0 103 1

Hash (cost=3.03..3.03 rows=103 width=32) (actual time=0.050..0.050 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.028 0.028 ↑ 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.028 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. 19.042 354.808 ↓ 0.0 0 44,351

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

  • Output: d_4.deduction_id, 'RELIABLE'::enum.deduction_reliability_state
  • Buffers: shared hit=348171
44. 44.351 266.106 ↓ 0.0 0 44,351

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

  • Output: d_4.deduction_id
  • Buffers: shared hit=271257
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. 4.776 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=128442
47. 88.702 88.702 ↓ 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.002..0.002 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=96756
48. 39.575 39.575 ↑ 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.005..0.005 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=31686
49. 14.427 69.660 ↓ 0.0 0 7,740

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

  • Output: deduction_claim_3.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=76914
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=23815
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=53099
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. 44.723 354.808 ↓ 0.0 0 44,351

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

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

Nested Loop Anti Join (cost=1.12..1.99 rows=1 width=16) (actual time=0.005..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. 61.878 133.053 ↓ 0.0 0 44,351

Nested Loop (cost=0.71..1.54 rows=1 width=16) (actual time=0.003..0.003 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 : 68.001 ms
Execution time : 3,282.547 ms