explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NblH

Settings
# exclusive inclusive rows x rows loops node
1. 0.297 8.938 ↑ 1.1 359 1

Sort (cost=2,730.44..2,731.44 rows=402 width=400) (actual time=8.912..8.938 rows=359 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, claim_product.distributor_company_id, company.company_name, company.company_number, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc.rate / suomc.rate))), ('DEFAULT'::enum.price_tier), claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, contract_item.won, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, (CASE WHEN (contract_item.rebate_direct_type = 'FIXED_RATE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_direct_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_direct_value / (tuomc_1.rate / suomc_1.rate)) WHEN (contract_item.rebate_direct_decimal_model = 'PERCENT'::enum.decimal_model) THEN contract_item.rebate_direct_value ELSE NULL::numeric END WHEN (contract_item.rebate_direct_type = 'FIXED_PRICE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_direct_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_direct_value / (tuomc_1.rate / suomc_1.rate)) ELSE NULL::numeric END ELSE NULL::numeric END), contract_item.rebate_deviated_type, contract_item.rebate_deviated_decimal_model, (CASE WHEN (contract_item.rebate_deviated_type = 'FIXED_RATE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_deviated_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_deviated_value / (tuomc_1.rate / suomc_1.rate)) WHEN (contract_item.rebate_deviated_decimal_model = 'PERCENT'::enum.decimal_model) THEN contract_item.rebate_deviated_value ELSE NULL::numeric END WHEN (contract_item.rebate_deviated_type = 'FIXED_PRICE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_deviated_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_deviated_value / (tuomc_1.rate / suomc_1.rate)) ELSE NULL::numeric END ELSE NULL::numeric END), (COALESCE(claim_product.distributor_invoice_number, cpl.distributor_invoice_number)), ((cpo.display_order)::integer), cpo.display_order
  • Sort Key: cpo.display_order
  • Sort Method: quicksort Memory: 210kB
  • Buffers: shared hit=1577
2. 0.193 8.641 ↑ 1.1 359 1

Hash Left Join (cost=2,658.37..2,713.05 rows=402 width=400) (actual time=7.582..8.641 rows=359 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, claim_product.distributor_company_id, company.company_name, company.company_number, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc.rate / suomc.rate))), ('DEFAULT'::enum.price_tier), claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, contract_item.won, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, CASE WHEN (contract_item.rebate_direct_type = 'FIXED_RATE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_direct_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_direct_value / (tuomc_1.rate / suomc_1.rate)) WHEN (contract_item.rebate_direct_decimal_model = 'PERCENT'::enum.decimal_model) THEN contract_item.rebate_direct_value ELSE NULL::numeric END WHEN (contract_item.rebate_direct_type = 'FIXED_PRICE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_direct_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_direct_value / (tuomc_1.rate / suomc_1.rate)) ELSE NULL::numeric END ELSE NULL::numeric END, contract_item.rebate_deviated_type, contract_item.rebate_deviated_decimal_model, CASE WHEN (contract_item.rebate_deviated_type = 'FIXED_RATE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_deviated_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_deviated_value / (tuomc_1.rate / suomc_1.rate)) WHEN (contract_item.rebate_deviated_decimal_model = 'PERCENT'::enum.decimal_model) THEN contract_item.rebate_deviated_value ELSE NULL::numeric END WHEN (contract_item.rebate_deviated_type = 'FIXED_PRICE'::enum.rebate_value_type) THEN CASE WHEN (contract_item.rebate_deviated_decimal_model = 'NUMERIC'::enum.decimal_model) THEN (contract_item.rebate_deviated_value / (tuomc_1.rate / suomc_1.rate)) ELSE NULL::numeric END ELSE NULL::numeric END, COALESCE(claim_product.distributor_invoice_number, cpl.distributor_invoice_number), (cpo.display_order)::integer, cpo.display_order
  • Inner Unique: true
  • Hash Cond: (claim_product.claim_product_id = cpl.claim_product_id)
  • Buffers: shared hit=1577
3. 0.172 8.439 ↑ 1.1 359 1

Hash Left Join (cost=2,631.62..2,666.15 rows=402 width=326) (actual time=7.564..8.439 rows=359 loops=1)

  • Output: product.product_name, product.product_number, claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, company.company_name, company.company_number, contract_item.won, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_type, contract_item.rebate_deviated_decimal_model, contract_item.rebate_deviated_value, ('DEFAULT'::enum.price_tier), product_uom_conversions.uom_type_ids, suomc_1.rate, tuomc_1.rate, cpo.display_order, ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc.rate / suomc.rate)))
  • Hash Cond: ((claim_product.claim_id = claim_product_order.claim_id) AND (claim_product.claim_product_id = cpo.claim_product_id))
  • Buffers: shared hit=1577
4. 0.276 8.258 ↑ 1.1 359 1

Hash Left Join (cost=2,624.83..2,656.33 rows=402 width=334) (actual time=7.544..8.258 rows=359 loops=1)

  • Output: product.product_name, product.product_number, claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, company.company_name, company.company_number, contract_item.won, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_type, contract_item.rebate_deviated_decimal_model, contract_item.rebate_deviated_value, ('DEFAULT'::enum.price_tier), product_uom_conversions.uom_type_ids, suomc_1.rate, tuomc_1.rate, ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc.rate / suomc.rate)))
  • Hash Cond: ((claim_product.product_id = pro_1.product_id) AND (contract_item.uom_type_id = suomc_1.uom_type_id) AND (claim_product.uom_type_id = tuomc_1.uom_type_id))
  • Buffers: shared hit=1575
5. 0.202 4.486 ↑ 1.1 359 1

Hash Join (cost=2,418.10..2,429.02 rows=402 width=340) (actual time=4.031..4.486 rows=359 loops=1)

  • Output: product.product_name, product.product_number, claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, company.company_name, company.company_number, contract_item.won, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_type, contract_item.rebate_deviated_decimal_model, contract_item.rebate_deviated_value, contract_item.uom_type_id, ('DEFAULT'::enum.price_tier), product_uom_conversions.uom_type_ids, ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc.rate / suomc.rate)))
  • Inner Unique: true
  • Hash Cond: (claim_product.product_id = product_uom_conversions.product_id)
  • Buffers: shared hit=1545
6. 0.230 3.231 ↑ 1.1 359 1

Merge Left Join (cost=2,373.11..2,382.99 rows=391 width=324) (actual time=2.970..3.231 rows=359 loops=1)

  • Output: product.product_name, product.product_number, product.product_id, claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, company.company_name, company.company_number, contract_item.won, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_type, contract_item.rebate_deviated_decimal_model, contract_item.rebate_deviated_value, contract_item.uom_type_id, ('DEFAULT'::enum.price_tier), ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc.rate / suomc.rate)))
  • Merge Cond: ((claim_product.uom_type_id = tuomc.uom_type_id) AND (claim_product_price.price_id = price_default.price_id))
  • Buffers: shared hit=1534
7. 0.387 3.001 ↓ 1.0 359 1

Sort (cost=1,725.65..1,726.54 rows=357 width=304) (actual time=2.967..3.001 rows=359 loops=1)

  • Output: product.product_name, product.product_number, product.product_id, claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim_product_price.price_id, company.company_name, company.company_number, contract_item.won, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_type, contract_item.rebate_deviated_decimal_model, contract_item.rebate_deviated_value, contract_item.uom_type_id
  • Sort Key: claim_product.uom_type_id, claim_product_price.price_id
  • Sort Method: quicksort Memory: 210kB
  • Buffers: shared hit=1534
8. 0.110 2.614 ↓ 1.0 359 1

Nested Loop (cost=90.82..1,710.51 rows=357 width=304) (actual time=0.987..2.614 rows=359 loops=1)

  • Output: product.product_name, product.product_number, product.product_id, claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim_product_price.price_id, company.company_name, company.company_number, contract_item.won, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_type, contract_item.rebate_deviated_decimal_model, contract_item.rebate_deviated_value, contract_item.uom_type_id
  • Buffers: shared hit=1534
9. 0.015 0.015 ↑ 1.0 1 1

Index Scan using claim_pkey on client_stonyfield.claim (cost=0.42..3.44 rows=1 width=20) (actual time=0.014..0.015 rows=1 loops=1)

  • Output: claim.claim_id, claim.invoice_id, claim.claim_state, claim.claim_sequence, claim.transaction_type, claim.contract_sequence, claim.claim_tag
  • Index Cond: (claim.claim_id = '0d2047b6-df31-4607-a081-a886c0e45a32'::uuid)
  • Buffers: shared hit=4
10. 0.222 2.489 ↓ 1.0 359 1

Nested Loop Left Join (cost=90.40..1,703.51 rows=357 width=304) (actual time=0.969..2.489 rows=359 loops=1)

  • Output: product.product_name, product.product_number, product.product_id, claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim_product_price.price_id, company.company_name, company.company_number, contract_item.won, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_direct_value, contract_item.rebate_deviated_type, contract_item.rebate_deviated_decimal_model, contract_item.rebate_deviated_value, contract_item.uom_type_id
  • Inner Unique: true
  • Buffers: shared hit=1530
11. 0.170 1.549 ↓ 1.0 359 1

Hash Left Join (cost=89.99..607.71 rows=357 width=263) (actual time=0.949..1.549 rows=359 loops=1)

  • Output: product.product_name, product.product_number, product.product_id, claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim_product_price.price_id, company.company_name, company.company_number
  • Inner Unique: true
  • Hash Cond: (claim_product.distributor_company_id = company.company_id)
  • Buffers: shared hit=94
12. 0.159 0.724 ↓ 1.0 359 1

Hash Join (cost=42.61..559.40 rows=357 width=233) (actual time=0.287..0.724 rows=359 loops=1)

  • Output: product.product_name, product.product_number, product.product_id, claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim_product_price.price_id
  • Inner Unique: true
  • Hash Cond: (claim_product.product_id = product.product_id)
  • Buffers: shared hit=68
13. 0.167 0.346 ↓ 1.0 359 1

Hash Left Join (cost=12.05..527.89 rows=357 width=185) (actual time=0.062..0.346 rows=359 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, claim_product.distributor_company_id, claim_product.uom_type_id, claim_product.rebate_calculated_rate, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.resolve_date, claim_product.claimant_contract_name, claim_product.contract_item_id, claim_product.distributor_invoice_number, claim_product.claim_id, claim_product_price.price_id
  • Inner Unique: true
  • Hash Cond: (claim_product.claim_product_id = claim_product_price.claim_product_id)
  • Buffers: shared hit=47
14. 0.135 0.168 ↓ 1.0 359 1

Bitmap Heap Scan on client_stonyfield.claim_product (cost=10.69..525.59 rows=357 width=169) (actual time=0.044..0.168 rows=359 loops=1)

  • Output: claim_product.claim_product_id, claim_product.claim_id, claim_product.product_id, claim_product.uom_type_id, claim_product.rebate_requested_quantity, claim_product.rebate_requested_rate, claim_product.rebate_allowed_quantity, claim_product.rebate_allowed_rate, claim_product.distributor_company_id, claim_product.contract_item_id, claim_product.claimant_contract_name, claim_product.resolve_date, claim_product.rebate_calculated_rate, claim_product.distributor_invoice_number
  • Recheck Cond: (claim_product.claim_id = '0d2047b6-df31-4607-a081-a886c0e45a32'::uuid)
  • Heap Blocks: exact=41
  • Buffers: shared hit=46
15. 0.033 0.033 ↓ 1.0 359 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..10.60 rows=357 width=0) (actual time=0.033..0.033 rows=359 loops=1)

  • Index Cond: (claim_product.claim_id = '0d2047b6-df31-4607-a081-a886c0e45a32'::uuid)
  • Buffers: shared hit=5
16. 0.005 0.011 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=32) (actual time=0.011..0.011 rows=16 loops=1)

  • Output: claim_product_price.claim_product_id, claim_product_price.price_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
17. 0.006 0.006 ↑ 1.0 16 1

Seq Scan on client_stonyfield.claim_product_price (cost=0.00..1.16 rows=16 width=32) (actual time=0.003..0.006 rows=16 loops=1)

  • Output: claim_product_price.claim_product_id, claim_product_price.price_id
  • Buffers: shared hit=1
18. 0.101 0.219 ↑ 1.0 425 1

Hash (cost=25.25..25.25 rows=425 width=48) (actual time=0.218..0.219 rows=425 loops=1)

  • Output: product.product_name, product.product_number, product.product_id
  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
  • Buffers: shared hit=21
19. 0.118 0.118 ↑ 1.0 425 1

Seq Scan on client_stonyfield.product (cost=0.00..25.25 rows=425 width=48) (actual time=0.004..0.118 rows=425 loops=1)

  • Output: product.product_name, product.product_number, product.product_id
  • Buffers: shared hit=21
20. 0.347 0.655 ↑ 1.0 950 1

Hash (cost=35.50..35.50 rows=950 width=46) (actual time=0.655..0.655 rows=950 loops=1)

  • Output: company.company_name, company.company_number, company.company_id
  • Buckets: 1024 Batches: 1 Memory Usage: 81kB
  • Buffers: shared hit=26
21. 0.308 0.308 ↑ 1.0 950 1

Seq Scan on client_stonyfield.company (cost=0.00..35.50 rows=950 width=46) (actual time=0.004..0.308 rows=950 loops=1)

  • Output: company.company_name, company.company_number, company.company_id
  • Buffers: shared hit=26
22. 0.718 0.718 ↑ 1.0 1 359

Index Scan using contract_item_pkey on client_stonyfield.contract_item (cost=0.42..3.07 rows=1 width=57) (actual time=0.002..0.002 rows=1 loops=359)

  • Output: contract_item.contract_item_id, contract_item.contract_id, contract_item.product_id, contract_item.grouping_id, contract_item.estimated_quantity, contract_item.uom_type_id, contract_item.rebate_deviated_value, contract_item.rebate_deviated_type, contract_item.inactive_override, contract_item.rebate_direct_value, contract_item.rebate_direct_type, contract_item.rebate_direct_decimal_model, contract_item.rebate_deviated_decimal_model, contract_item.won
  • Index Cond: (claim_product.contract_item_id = contract_item.contract_item_id)
  • Buffers: shared hit=1436
23. 0.000 0.000 ↓ 0.0 0

Sort (cost=647.46..649.11 rows=659 width=68) (never executed)

  • Output: ('DEFAULT'::enum.price_tier), price_default.price_id, tuomc.uom_type_id, ((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc.rate / suomc.rate)))
  • Sort Key: tuomc.uom_type_id, price_default.price_id
24. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=568.32..616.61 rows=659 width=68) (never executed)

  • Output: ('DEFAULT'::enum.price_tier), price_default.price_id, tuomc.uom_type_id, (CASE WHEN (pm.math_operation_value IS NULL) THEN price_default.price WHEN (pm.math_operation IS NULL) THEN price_default.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default.price * pm.math_operation_value) ELSE price_default.price END / (tuomc.rate / suomc.rate))
  • Hash Cond: (tuomc.product_id = pro.product_id)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_stonyfield.product_uom_conversion tuomc (cost=0.00..23.74 rows=1,274 width=37) (never executed)

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
26. 0.000 0.000 ↓ 0.0 0

Hash (cost=565.57..565.57 rows=220 width=118) (never executed)

  • Output: ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.price_id, price_default.price, suomc.product_id, suomc.rate, pro.product_id, pm.math_operation_value, pm.math_operation
27. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=91.81..565.57 rows=220 width=118) (never executed)

  • Output: ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.price_id, price_default.price, suomc.product_id, suomc.rate, pro.product_id, pm.math_operation_value, pm.math_operation
  • Inner Unique: true
  • Hash Cond: (price_default.price_id = pm.price_id)
28. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=69.81..542.99 rows=220 width=82) (never executed)

  • Output: ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.price_id, price_default.price, suomc.product_id, suomc.rate, pro.product_id
  • Inner Unique: true
  • Hash Cond: (suomc.product_id = pro.product_id)
29. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=42.85..515.45 rows=220 width=66) (never executed)

  • Output: ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.price_id, price_default.price, suomc.product_id, suomc.rate
  • Inner Unique: true
  • Hash Cond: ((price_default.product_id = suomc.product_id) AND (price_default.uom_type_id = suomc.uom_type_id))
30. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..395.62 rows=14,662 width=61) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_stonyfield.price_default (cost=0.00..9.42 rows=342 width=58) (never executed)

  • Output: 'DEFAULT'::enum.price_tier, price_default.product_id, price_default.uom_type_id, price_default.price_id, price_default.price
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_stonyfield.price_sale (cost=0.00..343.03 rows=12,903 width=60) (never executed)

  • Output: 'SALE'::enum.price_tier, price_sale.product_id, price_sale.uom_type_id, price_sale.price_id, price_sale.price
33. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_stonyfield.price_company (cost=0.00..14.20 rows=420 width=84) (never executed)

  • Output: 'COMPANY'::enum.price_tier, price_company.product_id, price_company.uom_type_id, price_company.price_id, price_company.price
34. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_stonyfield.price_list (cost=0.00..14.60 rows=460 width=84) (never executed)

  • Output: 'LIST'::enum.price_tier, price_list.product_id, price_list.uom_type_id, price_list.price_id, price_list.price
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_stonyfield.price_class (cost=0.00..14.37 rows=537 width=58) (never executed)

  • Output: 'CLASS'::enum.price_tier, price_class.product_id, price_class.uom_type_id, price_class.price_id, price_class.price
36. 0.000 0.000 ↓ 0.0 0

Hash (cost=23.74..23.74 rows=1,274 width=37) (never executed)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_stonyfield.product_uom_conversion suomc (cost=0.00..23.74 rows=1,274 width=37) (never executed)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
38. 0.000 0.000 ↓ 0.0 0

Hash (cost=21.65..21.65 rows=425 width=16) (never executed)

  • Output: pro.product_id
39. 0.000 0.000 ↓ 0.0 0

Index Only Scan using product_pkey on client_stonyfield.product pro (cost=0.27..21.65 rows=425 width=16) (never executed)

  • Output: pro.product_id
  • Heap Fetches: 0
40. 0.000 0.000 ↓ 0.0 0

Hash (cost=19.00..19.00 rows=240 width=52) (never executed)

  • Output: pm.price_id, pm.math_operation_value, pm.math_operation
41. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_stonyfield.price_modified pm (cost=0.00..19.00 rows=240 width=52) (never executed)

  • Output: pm.price_id, pm.math_operation_value, pm.math_operation
  • Filter: upper_inf(pm.active_range)
42. 0.124 1.053 ↑ 1.0 425 1

Hash (cost=39.67..39.67 rows=425 width=48) (actual time=1.053..1.053 rows=425 loops=1)

  • Output: product_uom_conversions.uom_type_ids, product_uom_conversions.product_id
  • Buckets: 1024 Batches: 1 Memory Usage: 57kB
  • Buffers: shared hit=11
43. 0.074 0.929 ↑ 1.0 425 1

Subquery Scan on product_uom_conversions (cost=30.11..39.67 rows=425 width=48) (actual time=0.694..0.929 rows=425 loops=1)

  • Output: product_uom_conversions.uom_type_ids, product_uom_conversions.product_id
  • Buffers: shared hit=11
44. 0.722 0.855 ↑ 1.0 425 1

HashAggregate (cost=30.11..35.42 rows=425 width=48) (actual time=0.693..0.855 rows=425 loops=1)

  • Output: product_uom_conversion.product_id, array_agg(product_uom_conversion.uom_type_id)
  • Group Key: product_uom_conversion.product_id
  • Buffers: shared hit=11
45. 0.133 0.133 ↑ 1.0 1,274 1

Seq Scan on client_stonyfield.product_uom_conversion (cost=0.00..23.74 rows=1,274 width=32) (actual time=0.007..0.133 rows=1,274 loops=1)

  • Output: product_uom_conversion.product_id, product_uom_conversion.uom_type_id, product_uom_conversion.rate
  • Buffers: shared hit=11
46. 1.203 3.496 ↓ 1.0 3,824 1

Hash (cost=139.90..139.90 rows=3,819 width=58) (actual time=3.496..3.496 rows=3,824 loops=1)

  • Output: pro_1.product_id, suomc_1.rate, suomc_1.uom_type_id, tuomc_1.rate, tuomc_1.uom_type_id
  • Buckets: 4096 Batches: 1 Memory Usage: 372kB
  • Buffers: shared hit=30
47. 1.062 2.293 ↓ 1.0 3,824 1

Hash Join (cost=70.00..139.90 rows=3,819 width=58) (actual time=1.117..2.293 rows=3,824 loops=1)

  • Output: pro_1.product_id, suomc_1.rate, suomc_1.uom_type_id, tuomc_1.rate, tuomc_1.uom_type_id
  • Hash Cond: (tuomc_1.product_id = pro_1.product_id)
  • Buffers: shared hit=30
48. 0.128 0.128 ↑ 1.0 1,274 1

Seq Scan on client_stonyfield.product_uom_conversion tuomc_1 (cost=0.00..23.74 rows=1,274 width=37) (actual time=0.006..0.128 rows=1,274 loops=1)

  • Output: tuomc_1.product_id, tuomc_1.uom_type_id, tuomc_1.rate
  • Buffers: shared hit=11
49. 0.342 1.103 ↑ 1.0 1,274 1

Hash (cost=54.08..54.08 rows=1,274 width=53) (actual time=1.103..1.103 rows=1,274 loops=1)

  • Output: pro_1.product_id, suomc_1.rate, suomc_1.product_id, suomc_1.uom_type_id
  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=19
50. 0.466 0.761 ↑ 1.0 1,274 1

Hash Join (cost=26.96..54.08 rows=1,274 width=53) (actual time=0.171..0.761 rows=1,274 loops=1)

  • Output: pro_1.product_id, suomc_1.rate, suomc_1.product_id, suomc_1.uom_type_id
  • Inner Unique: true
  • Hash Cond: (suomc_1.product_id = pro_1.product_id)
  • Buffers: shared hit=19
51. 0.131 0.131 ↑ 1.0 1,274 1

Seq Scan on client_stonyfield.product_uom_conversion suomc_1 (cost=0.00..23.74 rows=1,274 width=37) (actual time=0.002..0.131 rows=1,274 loops=1)

  • Output: suomc_1.product_id, suomc_1.uom_type_id, suomc_1.rate
  • Buffers: shared hit=11
52. 0.078 0.164 ↑ 1.0 425 1

Hash (cost=21.65..21.65 rows=425 width=16) (actual time=0.164..0.164 rows=425 loops=1)

  • Output: pro_1.product_id
  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
  • Buffers: shared hit=8
53. 0.086 0.086 ↑ 1.0 425 1

Index Only Scan using product_pkey on client_stonyfield.product pro_1 (cost=0.27..21.65 rows=425 width=16) (actual time=0.011..0.086 rows=425 loops=1)

  • Output: pro_1.product_id
  • Heap Fetches: 0
  • Buffers: shared hit=8
54. 0.000 0.009 ↓ 0.0 0 1

Hash (cost=5.30..5.30 rows=100 width=40) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: claim_product_order.claim_id, cpo.display_order, cpo.claim_product_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2
55. 0.001 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.28..5.30 rows=100 width=40) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: claim_product_order.claim_id, cpo.display_order, cpo.claim_product_id
  • Buffers: shared hit=2
56. 0.008 0.008 ↓ 0.0 0 1

Index Scan using claim_product_order_pkey on client_stonyfield.claim_product_order (cost=0.28..3.29 rows=1 width=60) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: claim_product_order.claim_id, claim_product_order.item_ids
  • Index Cond: (claim_product_order.claim_id = '0d2047b6-df31-4607-a081-a886c0e45a32'::uuid)
  • Buffers: shared hit=2
57. 0.000 0.000 ↓ 0.0 0

Function Scan on pg_catalog.unnest cpo (cost=0.00..1.00 rows=100 width=24) (never executed)

  • Output: cpo.claim_product_id, cpo.display_order
  • Function Call: unnest(claim_product_order.item_ids)
58. 0.000 0.009 ↓ 0.0 0 1

Hash (cost=24.25..24.25 rows=200 width=48) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: cpl.distributor_invoice_number, cpl.claim_product_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
59. 0.002 0.009 ↓ 0.0 0 1

Subquery Scan on cpl (cost=19.75..24.25 rows=200 width=48) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: cpl.distributor_invoice_number, cpl.claim_product_id
60. 0.003 0.007 ↓ 0.0 0 1

HashAggregate (cost=19.75..22.25 rows=200 width=48) (actual time=0.007..0.007 rows=0 loops=1)

  • Output: claim_product_location.claim_product_id, string_agg(claim_product_location.distributor_invoice_number, ', '::text)
  • Group Key: claim_product_location.claim_product_id
61. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on client_stonyfield.claim_product_location (cost=0.00..16.50 rows=650 width=48) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: claim_product_location.claim_product_id, claim_product_location.location_id, claim_product_location.quantity_ratio, claim_product_location.distributor_invoice_number