explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IXtO

Settings
# exclusive inclusive rows x rows loops node
1. 0.565 109.941 ↓ 1.2 122 1

Sort (cost=903.23..903.48 rows=100 width=342) (actual time=109.928..109.941 rows=122 loops=1)

  • Output: deduction.deduction_id, ((deduction.deduction_number)::text), deduction.company_id, ((company.company_name)::text), ((company.company_number)::text), ((deduction.distributor_invoice_number)::text), 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_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), ((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), (COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state)), deduction.client_deduction_amount_open, ((deduction.client_deduction_reason_major)::text), ((deduction.client_deduction_reason_minor)::text), deduction.deduction_label, ((deduction.client_check_number)::text), note.note, ((COALESCE(deduction.deduction_date_closed, (now())::date) - deduction.deduction_date_created)), deduction.deduction_number
  • Sort Key: ((deduction.deduction_number)::text)
  • Sort Method: quicksort Memory: 80kB
  • Buffers: shared hit=6419 read=994
2. 2.051 109.376 ↓ 1.2 122 1

Nested Loop Left Join (cost=9.45..899.90 rows=100 width=342) (actual time=31.007..109.376 rows=122 loops=1)

  • Output: deduction.deduction_id, (deduction.deduction_number)::text, deduction.company_id, (company.company_name)::text, (company.company_number)::text, (deduction.distributor_invoice_number)::text, 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_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, (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, COALESCE(('RELIABLE'::enum.deduction_reliability_state), 'UNRELIABLE'::enum.deduction_reliability_state), deduction.client_deduction_amount_open, (deduction.client_deduction_reason_major)::text, (deduction.client_deduction_reason_minor)::text, deduction.deduction_label, (deduction.client_check_number)::text, note.note, (COALESCE(deduction.deduction_date_closed, (now())::date) - deduction.deduction_date_created), deduction.deduction_number
  • Buffers: shared hit=6416 read=994
3. 0.257 67.797 ↓ 1.2 122 1

Hash Left Join (cost=7.36..548.80 rows=100 width=227) (actual time=30.481..67.797 rows=122 loops=1)

  • Output: company.company_name, company.company_number, ded.deduction_id, d_1.deduction_amount_requested, deduction_balance_materialized_2.deduction_amount_cleared, deduction_1.deduction_amount_requested, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized.deduction_amount_remaining, 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, note.note
  • Hash Cond: (deduction.deduction_id = note.object_id)
  • Buffers: shared hit=4421 read=583
4. 0.245 38.198 ↓ 1.2 122 1

Nested Loop (cost=4.86..545.91 rows=100 width=195) (actual time=1.123..38.198 rows=122 loops=1)

  • Output: company.company_name, company.company_number, ded.deduction_id, d_1.deduction_amount_requested, deduction_balance_materialized_2.deduction_amount_cleared, deduction_1.deduction_amount_requested, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized.deduction_amount_remaining, 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=3717 read=343
5. 0.170 30.145 ↓ 1.2 122 1

Nested Loop (cost=4.57..512.08 rows=100 width=163) (actual time=0.918..30.145 rows=122 loops=1)

  • Output: ded.deduction_id, d_1.deduction_amount_requested, deduction_balance_materialized_2.deduction_amount_cleared, deduction_1.deduction_amount_requested, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized.deduction_amount_remaining, 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=3421 read=269
6. 0.240 29.609 ↓ 1.2 122 1

Nested Loop (cost=4.16..462.54 rows=100 width=152) (actual time=0.908..29.609 rows=122 loops=1)

  • Output: ded.deduction_id, unnest.unnest, d_1.deduction_amount_requested, d_1.deduction_id, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_balance_materialized.deduction_amount_remaining, deduction_balance_materialized.deduction_id, d.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=2933 read=269
7. 0.130 29.125 ↓ 1.2 122 1

Nested Loop (cost=3.74..418.22 rows=100 width=136) (actual time=0.902..29.125 rows=122 loops=1)

  • Output: ded.deduction_id, unnest.unnest, d_1.deduction_amount_requested, d_1.deduction_id, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id, deduction_balance_materialized.deduction_amount_remaining, deduction_balance_materialized.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=2552 read=269
8. 0.201 28.629 ↓ 1.2 122 1

Nested Loop (cost=3.33..371.51 rows=100 width=117) (actual time=0.897..28.629 rows=122 loops=1)

  • Output: ded.deduction_id, unnest.unnest, d_1.deduction_amount_requested, d_1.deduction_id, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id, deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=2064 read=269
9. 0.259 28.062 ↓ 1.2 122 1

Nested Loop (cost=2.91..324.80 rows=100 width=98) (actual time=0.890..28.062 rows=122 loops=1)

  • Output: ded.deduction_id, unnest.unnest, d_1.deduction_amount_requested, d_1.deduction_id, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id, deduction_1.deduction_amount_requested, deduction_1.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=1576 read=269
10. 0.210 27.315 ↓ 1.2 122 1

Nested Loop (cost=2.50..275.26 rows=100 width=76) (actual time=0.883..27.315 rows=122 loops=1)

  • Output: ded.deduction_id, unnest.unnest, d_1.deduction_amount_requested, d_1.deduction_id, deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=1088 read=269
11. 0.262 14.783 ↓ 1.2 122 1

Nested Loop (cost=2.08..228.54 rows=100 width=54) (actual time=0.618..14.783 rows=122 loops=1)

  • Output: ded.deduction_id, unnest.unnest, d_1.deduction_amount_requested, d_1.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=736 read=133
12. 0.152 12.203 ↓ 1.2 122 1

Nested Loop (cost=1.67..179.01 rows=100 width=32) (actual time=0.482..12.203 rows=122 loops=1)

  • Output: ded.deduction_id, unnest.unnest
  • Inner Unique: true
  • Buffers: shared hit=266 read=115
13. 0.184 0.217 ↓ 1.2 122 1

HashAggregate (cost=1.25..2.25 rows=100 width=16) (actual time=0.081..0.217 rows=122 loops=1)

  • Output: unnest.unnest
  • Group Key: unnest.unnest
14. 0.033 0.033 ↓ 1.2 122 1

Function Scan on pg_catalog.unnest (cost=0.00..1.00 rows=100 width=16) (actual time=0.023..0.033 rows=122 loops=1)

  • Output: unnest.unnest
  • Function Call: unnest('{d5ee3601-9b37-45e7-8aac-78d7d4883bc9,fc8eb8bf-11ff-44d3-bbf4-e811cbb60247,ef53796e-f973-4f25-99f9-360b09bb242f,c73b0f42-4d8e-433f-a980-6cce1444c6cd,c3d0d216-7418-405e-b6ad-bc2c56653370,a6937aba-fddf-4f46-bc29-c39b90103506,f737cd3f-0f07-4346-9b0b-6004adf47fa7,bca8f6e0-b0f2-4234-8a1c-666505643b8a,6041b5d3-58bc-498c-83e9-34cdb302ee9e,b8b47e4b-0aff-4650-9eb1-1730c0702bbb,e206498c-0499-4441-b82f-dd84fe3338d0,53dcb91d-b5a9-4d81-84ea-3c5fbd2a886d,bec3ca0d-55ad-4cd0-a293-e795865b72f1,c6596d23-22b4-4204-9b2b-c30c02d68d24,074d2e1a-cdb4-4915-94de-dbab82d9857a,f6795d92-f8b3-4779-be35-5d9b83e2d64f,3c0cc060-bc55-4c99-bb0d-1d8563833388,600a512c-a484-4a42-b176-06d163202a41,5f9292a6-cc27-4aac-bac1-dd9ef119a2d1,adf39fb7-22d7-49c5-94dc-ce8d809632b8,4572a984-2719-4fe5-9512-39f4bbe31f25,24e24c27-f195-4ffa-acd6-d9a56d731086,56c41a72-44ea-4e69-9587-df71940fdce1,0abf3606-1ac8-4137-891b-f69ef4d54e38,f7cf802d-21e2-42c2-97f1-e871f8517d23,0e8882c3-bfae-4a15-a8ae-f380c0edba03,50e860d6-bc8e-4755-b52a-4dca11d7aae3,cda66e58-5585-4d70-9204-ced8626f038a,91d9e85b-8870-49ab-9b48-898ac3f4f75c,838b398a-2ca2-4c17-bbc7-521e5a9ce6c4,2b369e54-dabd-45cc-b37c-afab37a0a651,637fd4c8-f471-466a-bc0c-2c3ae918f7b5,72aef4f8-c315-440a-b942-1beb046387bb,bb064b42-13c2-4b01-9907-af91b3580f70,2f62bd0a-384f-4a22-9bcc-cd59fab4f01a,ce45dd11-4b3c-4bce-82ab-64e101c0cc12,c94cd4f3-847a-46fe-8164-39a558153aad,f14bf801-dd5b-4379-90f2-7fab699db7ee,dd51db28-0953-4953-af6d-7bee0c3cd082,b70a0628-aceb-4f9d-a156-45992d326cec,6b299200-6faf-4a9f-ac0f-75c2aa70c0e2,0892b717-d5ee-4283-b153-1ba4b30f7457,a88fc464-e57d-4fa9-9efc-2b6a74b04d50,c1c0d4cf-14a5-4990-8c69-5131dcd3e3fb,212138f4-705d-41c2-9d6d-35ecbee6e061,e7e06de6-b927-43dd-b003-8cbbf718cc12,2b41c58e-640f-4317-801d-05aa5ac45eea,f56a5c09-3d43-4bca-ad7a-a79c9d2b8276,3453e80a-7554-4ebc-95f6-400ab10f7710,93b135e3-5d3f-46fb-9e1b-0704c389ba55,a2fce8ac-ec78-4ae7-9ae3-066f47c96bd1,8aa050ee-6228-40bf-a0b5-7000e6133f22,9be6040b-2dbb-4b6e-b4e2-2a6e34c7ec2d,6490d13c-3327-45a0-9c57-b03949e07afa,bea71889-ed18-401e-8c69-4f0caf01da13,d61371e3-4b93-42d8-b026-608bddf8c18c,723f826f-0903-47b1-abcb-725c34cbb3dd,2214986f-a261-43bf-accc-3b9a0fc35d55,392560cb-183c-4043-b5b4-b4aec91e18fd,94948e3f-f8fb-4540-81e6-c8ba1285a5a7,d6cf667f-6771-4294-ae03-32b234c97842,be18239c-0ae6-4e71-95e1-8ce25ff41a9c,a7cdb9f6-56c5-4d08-ad5e-133ca2983675,e17a5015-81db-435b-ae28-6707a27683cb,b335427b-092e-4de9-b12d-41128ace17f9,b8f88c71-4825-4fe7-958f-079a80af6073,dc8a68f7-eecd-4aea-bdc7-16a97a0fceb8,c704cb5e-e086-4d72-a269-6f3bf1b3e8cb,6f7293d7-a5dc-4e79-a734-490c1b5ba7e5,72ec2d54-b1fa-471d-86de-3a01e2a519a3,be0b2cd2-fde1-4f95-9f16-b817d13e5a1e,02afdf37-2228-4bd7-8339-35e946491d73,620cba80-f6ce-4c34-9041-9231b580af08,68351493-e6a7-4f0e-a69c-7d2fba615f49,b6ff8908-c240-43e6-9919-e4a23c43153d,f8cb1f04-d8bf-48fd-80ef-4a4aeb0748bb,ca994cb3-2e45-407b-8ac8-41f54bc8d07b,65b813f1-cfb0-4916-8203-d13d7798fed2,200e04cb-13a3-4021-8e18-244ac5e06b69,bd03dfdf-67b9-452e-95c9-522b81a12e97,1b58577b-8cac-4f3d-be22-54bce0a8fe26,cb9befb1-1e3b-4f7f-9458-deeb98059a7a,138d5838-f214-4979-b84d-d16d13d79c86,f18b585e-23d8-457b-874a-1aed97a1ffda,4961e74a-3aac-484d-becb-41ab327a3f7b,b7cb2f56-d77b-4c0e-8f65-147bbde182bc,eb67cd76-0cfe-481a-ae46-db9d6bb5d234,c5b738ab-5e6e-400b-aed3-2481227344e8,7fc40b20-86c5-4718-883c-6b68d5ecd630,ec6a9139-12cd-4302-bf00-4682e8af0707,884727f9-3772-442c-ac75-97e766ba6e7e,591e9ecd-dc88-49b2-97c4-16cf2c1b204e,75af0eda-94a9-4ff8-8bf6-c7c95f6b54b0,18fc540d-defa-4171-a462-e1fde41b613f,a4e97730-dcef-4828-b947-2333c9942d3d,d4df147b-257f-4fec-83ca-c330f497de9e,8fa97083-df5f-445a-899b-5b84644c81b8,50fedbc2-74b4-495e-8f75-fb20b4f729b1,06049043-4482-4eef-b9e0-5e23e29dcdb3,e31298c1-550f-4f4b-bc7d-fa0a8dcb8be0,c9968f76-10f4-405a-ba65-ee016a830a64,e39bee81-c541-43b5-b149-e03b2dee32a0,cb818c25-cc5e-4612-a2f9-fbf9e88442b8,3706d181-eaba-4ef4-8b3d-a885751d1fb0,abfdbd37-2392-4e3e-9a7d-21d669630a73,9cb270c8-d7d6-40d5-8376-1f334ec2441f,8f0b4ef8-ff2f-4705-b2be-a69503cb5bdb,6f032602-eb25-42e4-81b0-7c8ab8039e1a,51c8fb28-7883-4abe-874b-588d7023cddf,63de869c-7607-4aba-96fe-83b978965fc9,edebe647-8081-419a-9dd7-02467b8d4241,2872bb0b-b003-49a2-b9dc-bf17ce784e81,f206feb2-40f8-4ca2-8f12-2e32f66b0f4b,cfe0669a-827d-4e31-bb89-039d4facdb60,66e25366-2d72-49a4-9dbe-9c60fee8be94,864365de-28dd-4090-8bad-ef6261dfee33,913d509d-2596-4ee3-b4ed-6b278b6d3987,9ef07e75-3b4d-4b78-b842-3861d721174a,8ed8c9ff-e53f-4798-a452-167cdef731a3,2c20a0be-6a0e-453d-89ee-3c8e8ccf81fe,092c2689-1054-4042-8006-8c0c3ae573a0,191fd88f-98ab-4177-9b5a-d13394ce0814}'::uuid[])
15. 11.834 11.834 ↑ 1.0 1 122

Index Only Scan using deduction_pkey on client_basic_american.deduction ded (cost=0.41..1.77 rows=1 width=16) (actual time=0.097..0.097 rows=1 loops=122)

  • Output: ded.deduction_id
  • Index Cond: (ded.deduction_id = unnest.unnest)
  • Heap Fetches: 14
  • Buffers: shared hit=266 read=115
16. 2.318 2.318 ↑ 1.0 1 122

Index Scan using deduction_pkey on client_basic_american.deduction d_1 (cost=0.41..0.50 rows=1 width=22) (actual time=0.019..0.019 rows=1 loops=122)

  • Output: d_1.deduction_amount_requested, d_1.deduction_id
  • Index Cond: (d_1.deduction_id = ded.deduction_id)
  • Buffers: shared hit=470 read=18
17. 12.322 12.322 ↑ 1.0 1 122

Index Scan using deduction_balance_materialized_pkey on client_basic_american.deduction_balance_materialized deduction_balance_materialized_2 (cost=0.41..0.47 rows=1 width=22) (actual time=0.101..0.101 rows=1 loops=122)

  • Output: deduction_balance_materialized_2.deduction_amount_cleared, deduction_balance_materialized_2.deduction_id
  • Index Cond: (deduction_balance_materialized_2.deduction_id = d_1.deduction_id)
  • Buffers: shared hit=352 read=136
18. 0.488 0.488 ↑ 1.0 1 122

Index Scan using deduction_pkey on client_basic_american.deduction deduction_1 (cost=0.41..0.50 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=122)

  • Output: deduction_1.deduction_amount_requested, deduction_1.deduction_id
  • Index Cond: (deduction_1.deduction_id = d_1.deduction_id)
  • Buffers: shared hit=488
19. 0.366 0.366 ↑ 1.0 1 122

Index Scan using deduction_balance_materialized_pkey on client_basic_american.deduction_balance_materialized deduction_balance_materialized_1 (cost=0.41..0.47 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=122)

  • Output: deduction_balance_materialized_1.deduction_amount_remaining, deduction_balance_materialized_1.deduction_id
  • Index Cond: (deduction_balance_materialized_1.deduction_id = deduction_1.deduction_id)
  • Buffers: shared hit=488
20. 0.366 0.366 ↑ 1.0 1 122

Index Scan using deduction_balance_materialized_pkey on client_basic_american.deduction_balance_materialized (cost=0.41..0.47 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=122)

  • Output: deduction_balance_materialized.deduction_amount_remaining, deduction_balance_materialized.deduction_id
  • Index Cond: (deduction_balance_materialized.deduction_id = deduction_1.deduction_id)
  • Buffers: shared hit=488
21. 0.244 0.244 ↑ 1.0 1 122

Index Only Scan using deduction_pkey on client_basic_american.deduction d (cost=0.41..0.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=122)

  • Output: d.deduction_id
  • Index Cond: (d.deduction_id = deduction_balance_materialized.deduction_id)
  • Heap Fetches: 14
  • Buffers: shared hit=381
22. 0.366 0.366 ↑ 1.0 1 122

Index Scan using deduction_pkey on client_basic_american.deduction (cost=0.41..0.50 rows=1 width=123) (actual time=0.003..0.003 rows=1 loops=122)

  • Output: 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
  • Index Cond: (deduction.deduction_id = deduction_balance_materialized.deduction_id)
  • Buffers: shared hit=488
23. 7.808 7.808 ↑ 1.0 1 122

Index Scan using company_pkey on client_basic_american.company (cost=0.29..0.34 rows=1 width=48) (actual time=0.064..0.064 rows=1 loops=122)

  • 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, company.deduction_contact_email
  • Index Cond: (company.company_id = deduction.company_id)
  • Buffers: shared hit=296 read=74
24. 0.032 29.342 ↑ 1.1 89 1

Hash (cost=1.25..1.25 rows=100 width=48) (actual time=29.342..29.342 rows=89 loops=1)

  • Output: note.note, note.object_id
  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
  • Buffers: shared hit=704 read=240
25. 29.310 29.310 ↑ 1.1 89 1

Function Scan on gosimple.note_select_aggregate note (cost=0.25..1.25 rows=100 width=48) (actual time=29.293..29.310 rows=89 loops=1)

  • Output: note.note, note.object_id
  • Function Call: gosimple.note_select_aggregate('DEDUCTION'::text, '{d5ee3601-9b37-45e7-8aac-78d7d4883bc9,fc8eb8bf-11ff-44d3-bbf4-e811cbb60247,ef53796e-f973-4f25-99f9-360b09bb242f,c73b0f42-4d8e-433f-a980-6cce1444c6cd,c3d0d216-7418-405e-b6ad-bc2c56653370,a6937aba-fddf-4f46-bc29-c39b90103506,f737cd3f-0f07-4346-9b0b-6004adf47fa7,bca8f6e0-b0f2-4234-8a1c-666505643b8a,6041b5d3-58bc-498c-83e9-34cdb302ee9e,b8b47e4b-0aff-4650-9eb1-1730c0702bbb,e206498c-0499-4441-b82f-dd84fe3338d0,53dcb91d-b5a9-4d81-84ea-3c5fbd2a886d,bec3ca0d-55ad-4cd0-a293-e795865b72f1,c6596d23-22b4-4204-9b2b-c30c02d68d24,074d2e1a-cdb4-4915-94de-dbab82d9857a,f6795d92-f8b3-4779-be35-5d9b83e2d64f,3c0cc060-bc55-4c99-bb0d-1d8563833388,600a512c-a484-4a42-b176-06d163202a41,5f9292a6-cc27-4aac-bac1-dd9ef119a2d1,adf39fb7-22d7-49c5-94dc-ce8d809632b8,4572a984-2719-4fe5-9512-39f4bbe31f25,24e24c27-f195-4ffa-acd6-d9a56d731086,56c41a72-44ea-4e69-9587-df71940fdce1,0abf3606-1ac8-4137-891b-f69ef4d54e38,f7cf802d-21e2-42c2-97f1-e871f8517d23,0e8882c3-bfae-4a15-a8ae-f380c0edba03,50e860d6-bc8e-4755-b52a-4dca11d7aae3,cda66e58-5585-4d70-9204-ced8626f038a,91d9e85b-8870-49ab-9b48-898ac3f4f75c,838b398a-2ca2-4c17-bbc7-521e5a9ce6c4,2b369e54-dabd-45cc-b37c-afab37a0a651,637fd4c8-f471-466a-bc0c-2c3ae918f7b5,72aef4f8-c315-440a-b942-1beb046387bb,bb064b42-13c2-4b01-9907-af91b3580f70,2f62bd0a-384f-4a22-9bcc-cd59fab4f01a,ce45dd11-4b3c-4bce-82ab-64e101c0cc12,c94cd4f3-847a-46fe-8164-39a558153aad,f14bf801-dd5b-4379-90f2-7fab699db7ee,dd51db28-0953-4953-af6d-7bee0c3cd082,b70a0628-aceb-4f9d-a156-45992d326cec,6b299200-6faf-4a9f-ac0f-75c2aa70c0e2,0892b717-d5ee-4283-b153-1ba4b30f7457,a88fc464-e57d-4fa9-9efc-2b6a74b04d50,c1c0d4cf-14a5-4990-8c69-5131dcd3e3fb,212138f4-705d-41c2-9d6d-35ecbee6e061,e7e06de6-b927-43dd-b003-8cbbf718cc12,2b41c58e-640f-4317-801d-05aa5ac45eea,f56a5c09-3d43-4bca-ad7a-a79c9d2b8276,3453e80a-7554-4ebc-95f6-400ab10f7710,93b135e3-5d3f-46fb-9e1b-0704c389ba55,a2fce8ac-ec78-4ae7-9ae3-066f47c96bd1,8aa050ee-6228-40bf-a0b5-7000e6133f22,9be6040b-2dbb-4b6e-b4e2-2a6e34c7ec2d,6490d13c-3327-45a0-9c57-b03949e07afa,bea71889-ed18-401e-8c69-4f0caf01da13,d61371e3-4b93-42d8-b026-608bddf8c18c,723f826f-0903-47b1-abcb-725c34cbb3dd,2214986f-a261-43bf-accc-3b9a0fc35d55,392560cb-183c-4043-b5b4-b4aec91e18fd,94948e3f-f8fb-4540-81e6-c8ba1285a5a7,d6cf667f-6771-4294-ae03-32b234c97842,be18239c-0ae6-4e71-95e1-8ce25ff41a9c,a7cdb9f6-56c5-4d08-ad5e-133ca2983675,e17a5015-81db-435b-ae28-6707a27683cb,b335427b-092e-4de9-b12d-41128ace17f9,b8f88c71-4825-4fe7-958f-079a80af6073,dc8a68f7-eecd-4aea-bdc7-16a97a0fceb8,c704cb5e-e086-4d72-a269-6f3bf1b3e8cb,6f7293d7-a5dc-4e79-a734-490c1b5ba7e5,72ec2d54-b1fa-471d-86de-3a01e2a519a3,be0b2cd2-fde1-4f95-9f16-b817d13e5a1e,02afdf37-2228-4bd7-8339-35e946491d73,620cba80-f6ce-4c34-9041-9231b580af08,68351493-e6a7-4f0e-a69c-7d2fba615f49,b6ff8908-c240-43e6-9919-e4a23c43153d,f8cb1f04-d8bf-48fd-80ef-4a4aeb0748bb,ca994cb3-2e45-407b-8ac8-41f54bc8d07b,65b813f1-cfb0-4916-8203-d13d7798fed2,200e04cb-13a3-4021-8e18-244ac5e06b69,bd03dfdf-67b9-452e-95c9-522b81a12e97,1b58577b-8cac-4f3d-be22-54bce0a8fe26,cb9befb1-1e3b-4f7f-9458-deeb98059a7a,138d5838-f214-4979-b84d-d16d13d79c86,f18b585e-23d8-457b-874a-1aed97a1ffda,4961e74a-3aac-484d-becb-41ab327a3f7b,b7cb2f56-d77b-4c0e-8f65-147bbde182bc,eb67cd76-0cfe-481a-ae46-db9d6bb5d234,c5b738ab-5e6e-400b-aed3-2481227344e8,7fc40b20-86c5-4718-883c-6b68d5ecd630,ec6a9139-12cd-4302-bf00-4682e8af0707,884727f9-3772-442c-ac75-97e766ba6e7e,591e9ecd-dc88-49b2-97c4-16cf2c1b204e,75af0eda-94a9-4ff8-8bf6-c7c95f6b54b0,18fc540d-defa-4171-a462-e1fde41b613f,a4e97730-dcef-4828-b947-2333c9942d3d,d4df147b-257f-4fec-83ca-c330f497de9e,8fa97083-df5f-445a-899b-5b84644c81b8,50fedbc2-74b4-495e-8f75-fb20b4f729b1,06049043-4482-4eef-b9e0-5e23e29dcdb3,e31298c1-550f-4f4b-bc7d-fa0a8dcb8be0,c9968f76-10f4-405a-ba65-ee016a830a64,e39bee81-c541-43b5-b149-e03b2dee32a0,cb818c25-cc5e-4612-a2f9-fbf9e88442b8,3706d181-eaba-4ef4-8b3d-a885751d1fb0,abfdbd37-2392-4e3e-9a7d-21d669630a73,9cb270c8-d7d6-40d5-8376-1f334ec2441f,8f0b4ef8-ff2f-4705-b2be-a69503cb5bdb,6f032602-eb25-42e4-81b0-7c8ab8039e1a,51c8fb28-7883-4abe-874b-588d7023cddf,63de869c-7607-4aba-96fe-83b978965fc9,edebe647-8081-419a-9dd7-02467b8d4241,2872bb0b-b003-49a2-b9dc-bf17ce784e81,f206feb2-40f8-4ca2-8f12-2e32f66b0f4b,cfe0669a-827d-4e31-bb89-039d4facdb60,66e25366-2d72-49a4-9dbe-9c60fee8be94,864365de-28dd-4090-8bad-ef6261dfee33,913d509d-2596-4ee3-b4ed-6b278b6d3987,9ef07e75-3b4d-4b78-b842-3861d721174a,8ed8c9ff-e53f-4798-a452-167cdef731a3,2c20a0be-6a0e-453d-89ee-3c8e8ccf81fe,092c2689-1054-4042-8006-8c0c3ae573a0,191fd88f-98ab-4177-9b5a-d13394ce0814}'::uuid[])
  • Buffers: shared hit=704 read=240
26. 0.244 39.528 ↓ 0.0 0 122

Nested Loop Semi Join (cost=2.09..2.81 rows=1 width=20) (actual time=0.324..0.324 rows=0 loops=122)

  • Output: d_2.deduction_id, 'RELIABLE'::enum.deduction_reliability_state
  • Buffers: shared hit=1944 read=411
27. 0.244 38.552 ↑ 1.0 1 122

Nested Loop Anti Join (cost=1.25..1.63 rows=1 width=16) (actual time=0.316..0.316 rows=1 loops=122)

  • Output: d_2.deduction_id
  • Buffers: shared hit=1388 read=411
28. 0.366 0.366 ↑ 1.0 1 122

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.003..0.003 rows=1 loops=122)

  • Output: d_2.deduction_id
  • Index Cond: (d_2.deduction_id = ded.deduction_id)
  • Heap Fetches: 14
  • Buffers: shared hit=381
29. 0.328 37.942 ↓ 0.0 0 122

Nested Loop (cost=0.84..1.17 rows=1 width=16) (actual time=0.311..0.311 rows=0 loops=122)

  • Output: deduction_claim_1.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=1007 read=411
30. 12.688 12.688 ↓ 2.0 2 122

Index Scan using idx_deduction_claim_deduction_id on client_basic_american.deduction_claim deduction_claim_1 (cost=0.42..0.48 rows=1 width=32) (actual time=0.099..0.104 rows=2 loops=122)

  • 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=316 read=131
31. 24.926 24.926 ↓ 0.0 0 242

Index Scan using claim_pkey on client_basic_american.claim cl_1 (cost=0.42..0.69 rows=1 width=16) (actual time=0.103..0.103 rows=0 loops=242)

  • 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=691 read=280
32. 0.092 0.732 ↓ 0.0 0 122

Nested Loop (cost=0.84..1.17 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=122)

  • Output: deduction_claim.deduction_id
  • Inner Unique: true
  • Buffers: shared hit=556
33. 0.488 0.488 ↓ 0.0 0 122

Index Scan using idx_deduction_claim_deduction_id on client_basic_american.deduction_claim (cost=0.42..0.48 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=122)

  • 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=404
34. 0.152 0.152 ↑ 1.0 1 38

Index Scan using claim_pkey on client_basic_american.claim cl (cost=0.42..0.69 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=38)

  • 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=152
Planning time : 273.359 ms
Execution time : 110.797 ms