explain.depesz.com

PostgreSQL's explain analyze made readable

Result: feMt

Settings
# exclusive inclusive rows x rows loops node
1. 27.374 1,718.147 ↑ 1.0 26,424 1

GroupAggregate (cost=215,752.37..217,198.64 rows=27,548 width=353) (actual time=1,685.100..1,718.147 rows=26,424 loops=1)

  • Output: claim_product.claim_product_id, claim_product.product_id, product.product_name, product.product_number, array_agg(product_uom_conversion.uom_type_id), 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.contract_id, contract_family.contract_title, contract_item.won, COALESCE(claim_product.distributor_invoice_number, string_agg(cpl.distributor_invoice_number, ', '::text)), ((cpo.display_order)::integer)
  • Group Key: claim_product.claim_product_id, product.product_name, product.product_number, company.company_name, company.company_number, ((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), contract_item.contract_id, contract_family.contract_title, contract_item.won, ((cpo.display_order)::integer)
  • Buffers: shared hit=466806, temp read=3964 written=3951
2. 67.984 1,690.773 ↑ 1.0 27,472 1

Sort (cost=215,752.37..215,821.24 rows=27,548 width=315) (actual time=1,685.077..1,690.773 rows=27,472 loops=1)

  • Output: claim_product.claim_product_id, product.product_name, product.product_number, company.company_name, company.company_number, ((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), contract_item.contract_id, contract_family.contract_title, contract_item.won, ((cpo.display_order)::integer), claim_product.product_id, product_uom_conversion.uom_type_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, cpl.distributor_invoice_number
  • Sort Key: claim_product.claim_product_id, product.product_name, product.product_number, company.company_name, company.company_number, ((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), contract_item.contract_id, contract_family.contract_title, contract_item.won, ((cpo.display_order)::integer)
  • Sort Method: external merge Disk: 7912kB
  • Buffers: shared hit=466806, temp read=3964 written=3951
3. 30.181 1,622.789 ↑ 1.0 27,472 1

Nested Loop Left Join (cost=92,805.58..211,117.50 rows=27,548 width=315) (actual time=736.563..1,622.789 rows=27,472 loops=1)

  • Output: claim_product.claim_product_id, product.product_name, product.product_number, company.company_name, company.company_number, ((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), contract_item.contract_id, contract_family.contract_title, contract_item.won, (cpo.display_order)::integer, claim_product.product_id, product_uom_conversion.uom_type_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, cpl.distributor_invoice_number
  • Buffers: shared hit=466806, temp read=2975 written=2961
4. 14.169 1,482.720 ↑ 1.0 27,472 1

Hash Left Join (cost=92,805.03..166,862.12 rows=27,548 width=318) (actual time=736.543..1,482.720 rows=27,472 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, product.product_name, product.product_number, product_uom_conversion.uom_type_id, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, cpo.display_order, ('DEFAULT'::enum.price_tier), contract_family.contract_title, ((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.contract_sequence = contract_family.contract_sequence)
  • Buffers: shared hit=356514, temp read=2975 written=2961
5. 19.318 1,465.861 ↑ 1.0 27,472 1

Hash Left Join (cost=92,446.23..166,430.96 rows=27,548 width=292) (actual time=733.824..1,465.861 rows=27,472 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, product.product_name, product.product_number, product_uom_conversion.uom_type_id, claim.contract_sequence, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, cpo.display_order, ('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)))
  • 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=356283, temp read=2975 written=2961
6. 74.035 1,442.387 ↑ 1.0 27,472 1

Hash Left Join (cost=92,136.06..165,535.40 rows=27,548 width=308) (actual time=729.641..1,442.387 rows=27,472 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, product.product_name, product.product_number, product_uom_conversion.uom_type_id, claim.contract_sequence, company.company_name, company.company_number, cpo.display_order, ('DEFAULT'::enum.price_tier), contract_item.contract_id, contract_item.won, contract_item.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)))
  • Inner Unique: true
  • Hash Cond: (claim_product.contract_item_id = contract_item.contract_item_id)
  • Buffers: shared hit=356220, temp read=2975 written=2961
7. 29.364 1,145.020 ↑ 1.0 27,472 1

Hash Left Join (cost=66,319.40..133,875.43 rows=27,548 width=275) (actual time=505.901..1,145.020 rows=27,472 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, product.product_name, product.product_number, product_uom_conversion.uom_type_id, claim.contract_sequence, company.company_name, company.company_number, cpo.display_order, ('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)))
  • Hash Cond: ((claim_product.claim_id = claim_product_order.claim_id) AND (claim_product.claim_product_id = cpo.claim_product_id))
  • Buffers: shared hit=342710
8. 38.313 1,101.726 ↑ 1.0 27,472 1

Hash Right Join (cost=66,312.46..133,661.87 rows=27,548 width=283) (actual time=491.954..1,101.726 rows=27,472 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, product.product_name, product.product_number, product_uom_conversion.uom_type_id, claim.contract_sequence, company.company_name, company.company_number, ('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)))
  • Hash Cond: ((claim_product_1.claim_id = claim_product.claim_id) AND (claim_product_1.claim_product_id = claim_product.claim_product_id))
  • Buffers: shared hit=342649
9. 26.361 943.265 ↓ 4.3 25,909 1

Hash Left Join (cost=32,784.53..100,088.55 rows=6,002 width=68) (actual time=371.742..943.265 rows=25,909 loops=1)

  • Output: claim_product_1.claim_product_id, claim_product_1.claim_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))
  • Hash Cond: (price_default.price_id = pm.price_id)
  • Buffers: shared hit=321322
10. 15.074 909.023 ↓ 4.3 25,909 1

Hash Join (cost=32,294.74..99,421.07 rows=6,002 width=70) (actual time=363.819..909.023 rows=25,909 loops=1)

  • Output: claim_product_1.claim_product_id, claim_product_1.claim_id, ('DEFAULT'::enum.price_tier), price_default.price_id, price_default.price, suomc.rate, tuomc.rate
  • Inner Unique: true
  • Hash Cond: ((pro.product_id = tuomc.product_id) AND (claim_product_1.uom_type_id = tuomc.uom_type_id))
  • Buffers: shared hit=321123
11. 12.493 893.226 ↓ 4.3 25,909 1

Hash Join (cost=32,210.79..99,305.58 rows=6,002 width=129) (actual time=363.061..893.226 rows=25,909 loops=1)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id, ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.price_id, price_default.price, pro.product_id, suomc.product_id, suomc.rate
  • Inner Unique: true
  • Hash Cond: (suomc.product_id = pro.product_id)
  • Buffers: shared hit=321102
12. 15.057 879.926 ↓ 4.3 25,909 1

Hash Join (cost=32,109.79..99,188.81 rows=6,002 width=113) (actual time=362.238..879.926 rows=25,909 loops=1)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id, ('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))
  • Buffers: shared hit=321081
13. 11.996 864.192 ↑ 46.3 25,909 1

Nested Loop (cost=32,025.84..92,805.29 rows=1,199,914 width=108) (actual time=361.544..864.192 rows=25,909 loops=1)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id, ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.uom_type_id, price_default.price_id, price_default.price
  • Buffers: shared hit=321060
14. 401.050 619.015 ↓ 2.1 25,909 1

Hash Join (cost=32,025.56..73,012.90 rows=12,492 width=64) (actual time=361.509..619.015 rows=25,909 loops=1)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id, claim_product_price.price_id
  • Inner Unique: true
  • Hash Cond: (claim_product_price.claim_product_id = claim_product_1.claim_product_id)
  • Buffers: shared hit=36061
15. 171.304 171.304 ↑ 1.0 2,051,591 1

Seq Scan on client_pinnacle.claim_product_price (cost=0.00..35,601.91 rows=2,051,591 width=32) (actual time=0.006..171.304 rows=2,051,591 loops=1)

  • Output: claim_product_price.claim_product_id, claim_product_price.price_id
  • Buffers: shared hit=15086
16. 8.306 46.661 ↑ 1.0 26,424 1

Hash (cost=31,681.35..31,681.35 rows=27,537 width=48) (actual time=46.661..46.661 rows=26,424 loops=1)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id
  • Buckets: 32768 Batches: 1 Memory Usage: 2321kB
  • Buffers: shared hit=20975
17. 34.873 38.355 ↑ 1.0 26,424 1

Bitmap Heap Scan on client_pinnacle.claim_product claim_product_1 (cost=389.34..31,681.35 rows=27,537 width=48) (actual time=7.121..38.355 rows=26,424 loops=1)

  • Output: claim_product_1.claim_product_id, claim_product_1.uom_type_id, claim_product_1.claim_id
  • Recheck Cond: (claim_product_1.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Heap Blocks: exact=20870
  • Buffers: shared hit=20975
18. 3.482 3.482 ↑ 1.0 26,424 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..382.46 rows=27,537 width=0) (actual time=3.482..3.482 rows=26,424 loops=1)

  • Index Cond: (claim_product_1.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
19. 77.727 233.181 ↑ 5.0 1 25,909

Append (cost=0.28..1.53 rows=5 width=60) (actual time=0.002..0.009 rows=1 loops=25,909)

  • Buffers: shared hit=284999
20. 51.818 51.818 ↑ 1.0 1 25,909

Index Scan using price_default_pkey on client_pinnacle.price_default (cost=0.28..0.30 rows=1 width=58) (actual time=0.002..0.002 rows=1 loops=25,909)

  • Output: 'DEFAULT'::enum.price_tier, price_default.product_id, price_default.uom_type_id, price_default.price_id, price_default.price
  • Index Cond: (price_default.price_id = claim_product_price.price_id)
  • Buffers: shared hit=77727
21. 51.818 51.818 ↓ 0.0 0 25,909

Index Scan using price_generated_pkey on client_pinnacle.price_sale (cost=0.43..0.47 rows=1 width=60) (actual time=0.002..0.002 rows=0 loops=25,909)

  • Output: 'SALE'::enum.price_tier, price_sale.product_id, price_sale.uom_type_id, price_sale.price_id, price_sale.price
  • Index Cond: (price_sale.price_id = claim_product_price.price_id)
  • Buffers: shared hit=77727
22. 25.909 25.909 ↓ 0.0 0 25,909

Index Scan using price_company_pkey on client_pinnacle.price_company (cost=0.29..0.30 rows=1 width=58) (actual time=0.001..0.001 rows=0 loops=25,909)

  • Output: 'COMPANY'::enum.price_tier, price_company.product_id, price_company.uom_type_id, price_company.price_id, price_company.price
  • Index Cond: (price_company.price_id = claim_product_price.price_id)
  • Buffers: shared hit=51818
23. 0.000 0.000 ↓ 0.0 0 25,909

Index Scan using price_list_pkey on client_pinnacle.price_list (cost=0.15..0.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=25,909)

  • Output: 'LIST'::enum.price_tier, price_list.product_id, price_list.uom_type_id, price_list.price_id, price_list.price
  • Index Cond: (price_list.price_id = claim_product_price.price_id)
  • Buffers: shared hit=25909
24. 25.909 25.909 ↓ 0.0 0 25,909

Index Scan using price_class_pkey on client_pinnacle.price_class (cost=0.28..0.30 rows=1 width=58) (actual time=0.001..0.001 rows=0 loops=25,909)

  • Output: 'CLASS'::enum.price_tier, price_class.product_id, price_class.uom_type_id, price_class.price_id, price_class.price
  • Index Cond: (price_class.price_id = claim_product_price.price_id)
  • Buffers: shared hit=51818
25. 0.440 0.677 ↑ 1.0 2,518 1

Hash (cost=46.18..46.18 rows=2,518 width=37) (actual time=0.677..0.677 rows=2,518 loops=1)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Buckets: 4096 Batches: 1 Memory Usage: 202kB
  • Buffers: shared hit=21
26. 0.237 0.237 ↑ 1.0 2,518 1

Seq Scan on client_pinnacle.product_uom_conversion suomc (cost=0.00..46.18 rows=2,518 width=37) (actual time=0.005..0.237 rows=2,518 loops=1)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Buffers: shared hit=21
27. 0.423 0.807 ↑ 1.0 2,517 1

Hash (cost=69.53..69.53 rows=2,517 width=16) (actual time=0.807..0.807 rows=2,517 loops=1)

  • Output: pro.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=21
28. 0.384 0.384 ↑ 1.0 2,517 1

Index Only Scan using product_pkey on client_pinnacle.product pro (cost=0.28..69.53 rows=2,517 width=16) (actual time=0.018..0.384 rows=2,517 loops=1)

  • Output: pro.product_id
  • Heap Fetches: 0
  • Buffers: shared hit=21
29. 0.479 0.723 ↑ 1.0 2,518 1

Hash (cost=46.18..46.18 rows=2,518 width=37) (actual time=0.723..0.723 rows=2,518 loops=1)

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Buckets: 4096 Batches: 1 Memory Usage: 202kB
  • Buffers: shared hit=21
30. 0.244 0.244 ↑ 1.0 2,518 1

Seq Scan on client_pinnacle.product_uom_conversion tuomc (cost=0.00..46.18 rows=2,518 width=37) (actual time=0.013..0.244 rows=2,518 loops=1)

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Buffers: shared hit=21
31. 3.539 7.881 ↓ 3.0 17,333 1

Hash (cost=417.09..417.09 rows=5,816 width=27) (actual time=7.881..7.881 rows=17,333 loops=1)

  • Output: pm.price_id, pm.math_operation_value, pm.math_operation
  • Buckets: 32768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1272kB
  • Buffers: shared hit=199
32. 4.342 4.342 ↓ 3.0 17,333 1

Seq Scan on client_pinnacle.price_modified pm (cost=0.00..417.09 rows=5,816 width=27) (actual time=0.014..4.342 rows=17,333 loops=1)

  • Output: pm.price_id, pm.math_operation_value, pm.math_operation
  • Filter: upper_inf(pm.active_range)
  • Rows Removed by Filter: 114
  • Buffers: shared hit=199
33. 21.533 120.148 ↑ 1.0 27,472 1

Hash (cost=33,114.71..33,114.71 rows=27,548 width=247) (actual time=120.148..120.148 rows=27,472 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, product.product_name, product.product_number, product_uom_conversion.uom_type_id, claim.contract_sequence, company.company_name, company.company_number
  • Buckets: 32768 Batches: 1 Memory Usage: 7862kB
  • Buffers: shared hit=21327
34. 15.382 98.615 ↑ 1.0 27,472 1

Hash Join (cost=1,091.74..33,114.71 rows=27,548 width=247) (actual time=22.609..98.615 rows=27,472 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, product.product_name, product.product_number, product_uom_conversion.uom_type_id, claim.contract_sequence, company.company_name, company.company_number
  • Hash Cond: (claim_product.product_id = product.product_id)
  • Buffers: shared hit=21327
35. 11.841 78.170 ↑ 1.0 26,424 1

Hash Left Join (cost=881.83..32,526.06 rows=27,537 width=193) (actual time=17.529..78.170 rows=26,424 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.contract_sequence, company.company_name, company.company_number
  • Inner Unique: true
  • Hash Cond: (claim_product.distributor_company_id = company.company_id)
  • Buffers: shared hit=21237
36. 5.550 58.389 ↑ 1.0 26,424 1

Nested Loop (cost=390.18..31,962.10 rows=27,537 width=162) (actual time=9.538..58.389 rows=26,424 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.contract_sequence
  • Buffers: shared hit=20983
37. 0.004 0.042 ↑ 1.0 1 1

Nested Loop (cost=0.84..5.38 rows=1 width=20) (actual time=0.037..0.042 rows=1 loops=1)

  • Output: claim.claim_id, claim.contract_sequence
  • Inner Unique: true
  • Buffers: shared hit=8
38. 0.023 0.023 ↑ 1.0 1 1

Index Scan using claim_pkey on client_pinnacle.claim (cost=0.42..3.44 rows=1 width=36) (actual time=0.019..0.023 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 = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=4
39. 0.015 0.015 ↑ 1.0 1 1

Index Only Scan using invoice_pkey on client_pinnacle.invoice (cost=0.42..1.94 rows=1 width=16) (actual time=0.015..0.015 rows=1 loops=1)

  • Output: invoice.invoice_id
  • Index Cond: (invoice.invoice_id = claim.invoice_id)
  • Heap Fetches: 0
  • Buffers: shared hit=4
40. 48.032 52.797 ↑ 1.0 26,424 1

Bitmap Heap Scan on client_pinnacle.claim_product (cost=389.34..31,681.35 rows=27,537 width=158) (actual time=9.492..52.797 rows=26,424 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
  • Recheck Cond: (claim_product.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Heap Blocks: exact=20870
  • Buffers: shared hit=20975
41. 4.765 4.765 ↑ 1.0 26,424 1

Bitmap Index Scan on claim_product_claim_id_idx (cost=0.00..382.46 rows=27,537 width=0) (actual time=4.764..4.765 rows=26,424 loops=1)

  • Index Cond: (claim_product.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
42. 4.364 7.940 ↑ 1.0 10,562 1

Hash (cost=359.62..359.62 rows=10,562 width=47) (actual time=7.940..7.940 rows=10,562 loops=1)

  • Output: company.company_name, company.company_number, company.company_id
  • Buckets: 16384 Batches: 1 Memory Usage: 956kB
  • Buffers: shared hit=254
43. 3.576 3.576 ↑ 1.0 10,562 1

Seq Scan on client_pinnacle.company (cost=0.00..359.62 rows=10,562 width=47) (actual time=0.011..3.576 rows=10,562 loops=1)

  • Output: company.company_name, company.company_number, company.company_id
  • Buffers: shared hit=254
44. 1.181 5.063 ↑ 1.0 2,518 1

Hash (cost=178.43..178.43 rows=2,518 width=86) (actual time=5.062..5.063 rows=2,518 loops=1)

  • Output: product.product_name, product.product_number, product.product_id, product_uom_conversion.uom_type_id, product_uom_conversion.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 326kB
  • Buffers: shared hit=90
45. 1.528 3.882 ↑ 1.0 2,518 1

Hash Join (cost=125.63..178.43 rows=2,518 width=86) (actual time=1.990..3.882 rows=2,518 loops=1)

  • Output: product.product_name, product.product_number, product.product_id, product_uom_conversion.uom_type_id, product_uom_conversion.product_id
  • Inner Unique: true
  • Hash Cond: (product_uom_conversion.product_id = product.product_id)
  • Buffers: shared hit=90
46. 0.388 0.388 ↑ 1.0 2,518 1

Seq Scan on client_pinnacle.product_uom_conversion (cost=0.00..46.18 rows=2,518 width=32) (actual time=0.010..0.388 rows=2,518 loops=1)

  • Output: product_uom_conversion.product_id, product_uom_conversion.uom_type_id, product_uom_conversion.rate
  • Buffers: shared hit=21
47. 1.077 1.966 ↑ 1.0 2,517 1

Hash (cost=94.17..94.17 rows=2,517 width=54) (actual time=1.966..1.966 rows=2,517 loops=1)

  • Output: product.product_name, product.product_number, product.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 247kB
  • Buffers: shared hit=69
48. 0.889 0.889 ↑ 1.0 2,517 1

Seq Scan on client_pinnacle.product (cost=0.00..94.17 rows=2,517 width=54) (actual time=0.007..0.889 rows=2,517 loops=1)

  • Output: product.product_name, product.product_number, product.product_id
  • Buffers: shared hit=69
49. 5.580 13.930 ↓ 264.2 26,424 1

Hash (cost=5.44..5.44 rows=100 width=40) (actual time=13.930..13.930 rows=26,424 loops=1)

  • Output: claim_product_order.claim_id, cpo.display_order, cpo.claim_product_id
  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2114kB
  • Buffers: shared hit=61
50. 3.604 8.350 ↓ 264.2 26,424 1

Nested Loop (cost=0.42..5.44 rows=100 width=40) (actual time=2.191..8.350 rows=26,424 loops=1)

  • Output: claim_product_order.claim_id, cpo.display_order, cpo.claim_product_id
  • Buffers: shared hit=61
51. 0.020 0.020 ↑ 1.0 1 1

Index Scan using claim_product_order_pkey on client_pinnacle.claim_product_order (cost=0.42..3.44 rows=1 width=144) (actual time=0.015..0.020 rows=1 loops=1)

  • Output: claim_product_order.claim_id, claim_product_order.item_ids
  • Index Cond: (claim_product_order.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=4
52. 4.726 4.726 ↓ 264.2 26,424 1

Function Scan on pg_catalog.unnest cpo (cost=0.00..1.00 rows=100 width=24) (actual time=2.172..4.726 rows=26,424 loops=1)

  • Output: cpo.claim_product_id, cpo.display_order
  • Function Call: unnest(claim_product_order.item_ids)
  • Buffers: shared hit=57
53. 115.431 223.332 ↑ 1.0 381,407 1

Hash (cost=17,324.07..17,324.07 rows=381,407 width=49) (actual time=223.332..223.332 rows=381,407 loops=1)

  • Output: contract_item.contract_id, contract_item.won, contract_item.contract_item_id, contract_item.uom_type_id
  • Buckets: 131072 Batches: 8 Memory Usage: 4822kB
  • Buffers: shared hit=13510, temp written=2807
54. 107.901 107.901 ↑ 1.0 381,407 1

Seq Scan on client_pinnacle.contract_item (cost=0.00..17,324.07 rows=381,407 width=49) (actual time=4.491..107.901 rows=381,407 loops=1)

  • Output: contract_item.contract_id, contract_item.won, contract_item.contract_item_id, contract_item.uom_type_id
  • Buffers: shared hit=13510
55. 0.631 4.156 ↓ 1.0 2,520 1

Hash (cost=266.09..266.09 rows=2,519 width=48) (actual time=4.156..4.156 rows=2,520 loops=1)

  • Output: pro_1.product_id, suomc_1.uom_type_id, tuomc_1.uom_type_id
  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=63
56. 0.899 3.525 ↓ 1.0 2,520 1

Hash Join (cost=185.27..266.09 rows=2,519 width=48) (actual time=2.428..3.525 rows=2,520 loops=1)

  • Output: pro_1.product_id, suomc_1.uom_type_id, tuomc_1.uom_type_id
  • Hash Cond: (tuomc_1.product_id = pro_1.product_id)
  • Buffers: shared hit=63
57. 0.218 0.218 ↑ 1.0 2,518 1

Seq Scan on client_pinnacle.product_uom_conversion tuomc_1 (cost=0.00..46.18 rows=2,518 width=32) (actual time=0.007..0.218 rows=2,518 loops=1)

  • Output: tuomc_1.product_id, tuomc_1.uom_type_id, tuomc_1.rate
  • Buffers: shared hit=21
58. 0.491 2.408 ↑ 1.0 2,518 1

Hash (cost=153.80..153.80 rows=2,518 width=48) (actual time=2.408..2.408 rows=2,518 loops=1)

  • Output: pro_1.product_id, suomc_1.product_id, suomc_1.uom_type_id
  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=42
59. 0.874 1.917 ↑ 1.0 2,518 1

Hash Join (cost=101.00..153.80 rows=2,518 width=48) (actual time=0.832..1.917 rows=2,518 loops=1)

  • Output: pro_1.product_id, 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=42
60. 0.225 0.225 ↑ 1.0 2,518 1

Seq Scan on client_pinnacle.product_uom_conversion suomc_1 (cost=0.00..46.18 rows=2,518 width=32) (actual time=0.003..0.225 rows=2,518 loops=1)

  • Output: suomc_1.product_id, suomc_1.uom_type_id, suomc_1.rate
  • Buffers: shared hit=21
61. 0.425 0.818 ↑ 1.0 2,517 1

Hash (cost=69.53..69.53 rows=2,517 width=16) (actual time=0.818..0.818 rows=2,517 loops=1)

  • Output: pro_1.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=21
62. 0.393 0.393 ↑ 1.0 2,517 1

Index Only Scan using product_pkey on client_pinnacle.product pro_1 (cost=0.28..69.53 rows=2,517 width=16) (actual time=0.016..0.393 rows=2,517 loops=1)

  • Output: pro_1.product_id
  • Heap Fetches: 0
  • Buffers: shared hit=21
63. 1.123 2.690 ↑ 1.0 5,680 1

Hash (cost=287.80..287.80 rows=5,680 width=34) (actual time=2.690..2.690 rows=5,680 loops=1)

  • Output: contract_family.contract_title, contract_family.contract_sequence
  • Buckets: 8192 Batches: 1 Memory Usage: 439kB
  • Buffers: shared hit=231
64. 1.567 1.567 ↑ 1.0 5,680 1

Seq Scan on client_pinnacle.contract_family (cost=0.00..287.80 rows=5,680 width=34) (actual time=0.016..1.567 rows=5,680 loops=1)

  • Output: contract_family.contract_title, contract_family.contract_sequence
  • Buffers: shared hit=231
65. 109.888 109.888 ↑ 5.0 1 27,472

Index Only Scan using claim_product_location_pkey on client_pinnacle.claim_product_location cpl (cost=0.56..1.55 rows=5 width=17) (actual time=0.004..0.004 rows=1 loops=27,472)

  • Output: cpl.claim_product_id, cpl.location_id, cpl.distributor_invoice_number
  • Index Cond: (cpl.claim_product_id = claim_product.claim_product_id)
  • Heap Fetches: 0
  • Buffers: shared hit=110292
Planning time : 32.830 ms
Execution time : 1,722.629 ms