explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5q2a

Settings
# exclusive inclusive rows x rows loops node
1. 18.946 9,999.745 ↑ 5.4 26,424 1

Hash Left Join (cost=267,830.96..289,663.18 rows=141,740 width=349) (actual time=9,368.908..9,999.745 rows=26,424 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.contract_id, contract_family.contract_title, contract_item.won, COALESCE(claim_product.distributor_invoice_number, cpl.distributor_invoice_number)
  • Inner Unique: true
  • Hash Cond: ((claim_product.claim_id = cpl.claim_id) AND (claim_product.claim_product_id = cpl.claim_product_id))
  • Buffers: shared hit=210492, temp read=32548 written=32627
2. 10.180 9,827.189 ↑ 5.4 26,424 1

Hash Left Join (cost=189,846.50..210,934.58 rows=141,740 width=342) (actual time=9,215.235..9,827.189 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, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, ('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=83435, temp read=32548 written=32627
3. 13.780 9,814.434 ↑ 5.4 26,424 1

Hash Left Join (cost=189,487.70..210,203.46 rows=141,740 width=316) (actual time=9,212.632..9,814.434 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, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, claim.contract_sequence, company.company_name, company.company_number, contract_item.contract_id, contract_item.won, ('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=83204, temp read=32548 written=32627
4. 61.670 9,796.469 ↑ 5.4 26,424 1

Hash Left Join (cost=189,177.53..206,881.33 rows=141,740 width=332) (actual time=9,208.416..9,796.469 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, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, claim.contract_sequence, company.company_name, company.company_number, ('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=83141, temp read=32548 written=32627
5. 265.545 9,521.852 ↑ 5.4 26,424 1

Merge Left Join (cost=163,360.87..165,615.61 rows=141,740 width=299) (actual time=8,972.131..9,521.852 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, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, 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)))
  • Merge Cond: ((claim_product.uom_type_id = tuomc.uom_type_id) AND (claim_product_price.price_id = price_default.price_id))
  • Buffers: shared hit=69631, temp read=29559 written=29652
6. 204.305 1,230.522 ↑ 5.4 26,424 1

Sort (cost=98,255.69..98,610.04 rows=141,740 width=279) (actual time=1,218.891..1,230.522 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, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, claim.contract_sequence, company.company_name, company.company_number, claim_product_price.price_id
  • Sort Key: claim_product.uom_type_id, claim_product_price.price_id
  • Sort Method: external merge Disk: 7784kB
  • Buffers: shared hit=36409, temp read=973 written=974
7. 11.076 1,026.217 ↑ 5.4 26,424 1

Hash Join (cost=32,807.92..74,292.79 rows=141,740 width=279) (actual time=679.051..1,026.217 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, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, claim.contract_sequence, company.company_name, company.company_number, claim_product_price.price_id
  • Inner Unique: true
  • Hash Cond: (claim_product.product_id = product_uom_conversions.product_id)
  • Buffers: shared hit=36409
8. 11.617 1,009.413 ↑ 1.0 26,424 1

Hash Left Join (cost=32,643.68..74,055.65 rows=27,537 width=263) (actual time=673.305..1,009.413 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, product.product_name, product.product_number, product.product_id, claim.contract_sequence, company.company_name, company.company_number, claim_product_price.price_id
  • Inner Unique: true
  • Hash Cond: (claim_product.distributor_company_id = company.company_id)
  • Buffers: shared hit=36392
9. 10.206 992.884 ↑ 1.0 26,424 1

Hash Join (cost=32,152.04..73,491.68 rows=27,537 width=232) (actual time=668.341..992.884 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, product.product_name, product.product_number, product.product_id, claim.contract_sequence, claim_product_price.price_id
  • Inner Unique: true
  • Hash Cond: (claim_product.product_id = product.product_id)
  • Buffers: shared hit=36138
10. 5.489 980.268 ↑ 1.0 26,424 1

Nested Loop (cost=32,026.40..73,293.64 rows=27,537 width=178) (actual time=665.897..980.268 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, claim_product_price.price_id
  • Buffers: shared hit=36069
11. 0.008 0.058 ↑ 1.0 1 1

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

  • Output: claim.claim_id, claim.contract_sequence
  • Inner Unique: true
  • Buffers: shared hit=8
12. 0.034 0.034 ↑ 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.028..0.034 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
13. 0.016 0.016 ↑ 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.016..0.016 rows=1 loops=1)

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

Hash Right Join (cost=32,025.56..73,012.90 rows=27,537 width=174) (actual time=665.840..974.721 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_product_price.price_id
  • Inner Unique: true
  • Hash Cond: (claim_product_price.claim_product_id = claim_product.claim_product_id)
  • Buffers: shared hit=36061
15. 240.298 240.298 ↑ 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.024..240.298 rows=2,051,591 loops=1)

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

Hash (cost=31,681.35..31,681.35 rows=27,537 width=158) (actual time=93.164..93.164 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
  • Buckets: 32768 Batches: 1 Memory Usage: 5164kB
  • Buffers: shared hit=20975
17. 65.109 70.008 ↑ 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.883..70.008 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
18. 4.899 4.899 ↑ 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.899..4.899 rows=26,424 loops=1)

  • Index Cond: (claim_product.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
19. 1.325 2.410 ↑ 1.0 2,517 1

Hash (cost=94.17..94.17 rows=2,517 width=54) (actual time=2.410..2.410 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
20. 1.085 1.085 ↑ 1.0 2,517 1

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

  • Output: product.product_name, product.product_number, product.product_id
  • Buffers: shared hit=69
21. 2.465 4.912 ↑ 1.0 10,562 1

Hash (cost=359.62..359.62 rows=10,562 width=47) (actual time=4.912..4.912 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
22. 2.447 2.447 ↑ 1.0 10,562 1

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

  • Output: company.company_name, company.company_number, company.company_id
  • Buffers: shared hit=254
23. 1.198 5.728 ↑ 1.0 2,517 1

Hash (cost=132.77..132.77 rows=2,517 width=48) (actual time=5.728..5.728 rows=2,517 loops=1)

  • Output: product_uom_conversions.uom_type_ids, product_uom_conversions.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 241kB
  • Buffers: shared hit=17
24. 0.609 4.530 ↑ 1.0 2,517 1

Subquery Scan on product_uom_conversions (cost=0.28..132.77 rows=2,517 width=48) (actual time=0.035..4.530 rows=2,517 loops=1)

  • Output: product_uom_conversions.uom_type_ids, product_uom_conversions.product_id
  • Buffers: shared hit=17
25. 3.204 3.921 ↑ 1.0 2,517 1

GroupAggregate (cost=0.28..107.60 rows=2,517 width=48) (actual time=0.034..3.921 rows=2,517 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=17
26. 0.717 0.717 ↑ 1.0 2,518 1

Index Only Scan using product_uom_conversion_pkey on client_pinnacle.product_uom_conversion (cost=0.28..63.55 rows=2,518 width=32) (actual time=0.019..0.717 rows=2,518 loops=1)

  • Output: product_uom_conversion.product_id, product_uom_conversion.uom_type_id
  • Heap Fetches: 0
  • Buffers: shared hit=17
27. 3,828.013 8,025.785 ↓ 203.7 2,024,570 1

Sort (cost=65,105.11..65,129.95 rows=9,938 width=68) (actual time=7,735.728..8,025.785 rows=2,024,570 loops=1)

  • 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
  • Sort Method: external sort Disk: 114672kB
  • Buffers: shared hit=33222, temp read=28586 written=28678
28. 2,011.920 4,197.772 ↓ 202.0 2,007,730 1

Hash Join (cost=759.01..64,445.29 rows=9,938 width=68) (actual time=10.622..4,197.772 rows=2,007,730 loops=1)

  • 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: (suomc.product_id = pro.product_id)
  • Buffers: shared hit=33222
29. 678.124 2,183.449 ↓ 199.9 1,985,471 1

Hash Left Join (cost=573.74..63,924.62 rows=9,934 width=76) (actual time=8.194..2,183.449 rows=1,985,471 loops=1)

  • Output: ('DEFAULT'::enum.price_tier), price_default.product_id, price_default.price_id, price_default.price, suomc.product_id, suomc.rate, pm.math_operation_value, pm.math_operation
  • Hash Cond: (price_default.price_id = pm.price_id)
  • Buffers: shared hit=33180
30. 737.355 1,497.822 ↓ 199.9 1,985,471 1

Hash Join (cost=83.95..63,339.42 rows=9,934 width=65) (actual time=0.672..1,497.822 rows=1,985,471 loops=1)

  • 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))
  • Buffers: shared hit=32981
31. 235.579 759.819 ↑ 1.0 1,985,471 1

Append (cost=0.00..52,829.29 rows=1,985,929 width=60) (actual time=0.010..759.819 rows=1,985,471 loops=1)

  • Buffers: shared hit=32960
32. 1.011 1.011 ↑ 1.0 4,086 1

Seq Scan on client_pinnacle.price_default (cost=0.00..94.86 rows=4,086 width=58) (actual time=0.009..1.011 rows=4,086 loops=1)

  • Output: 'DEFAULT'::enum.price_tier, price_default.product_id, price_default.uom_type_id, price_default.price_id, price_default.price
  • Buffers: shared hit=54
33. 519.708 519.708 ↓ 1.0 1,966,314 1

Seq Scan on client_pinnacle.price_sale (cost=0.00..52,321.12 rows=1,966,312 width=60) (actual time=0.023..519.708 rows=1,966,314 loops=1)

  • Output: 'SALE'::enum.price_tier, price_sale.product_id, price_sale.uom_type_id, price_sale.price_id, price_sale.price
  • Buffers: shared hit=32658
34. 3.005 3.005 ↑ 1.0 13,050 1

Seq Scan on client_pinnacle.price_company (cost=0.00..344.50 rows=13,050 width=58) (actual time=0.014..3.005 rows=13,050 loops=1)

  • Output: 'COMPANY'::enum.price_tier, price_company.product_id, price_company.uom_type_id, price_company.price_id, price_company.price
  • Buffers: shared hit=214
35. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on client_pinnacle.price_list (cost=0.00..14.60 rows=460 width=84) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: 'LIST'::enum.price_tier, price_list.product_id, price_list.uom_type_id, price_list.price_id, price_list.price
36. 0.505 0.505 ↑ 1.0 2,021 1

Seq Scan on client_pinnacle.price_class (cost=0.00..54.21 rows=2,021 width=58) (actual time=0.007..0.505 rows=2,021 loops=1)

  • Output: 'CLASS'::enum.price_tier, price_class.product_id, price_class.uom_type_id, price_class.price_id, price_class.price
  • Buffers: shared hit=34
37. 0.422 0.648 ↑ 1.0 2,518 1

Hash (cost=46.18..46.18 rows=2,518 width=37) (actual time=0.648..0.648 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
38. 0.226 0.226 ↑ 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.003..0.226 rows=2,518 loops=1)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Buffers: shared hit=21
39. 3.384 7.503 ↓ 3.0 17,333 1

Hash (cost=417.09..417.09 rows=5,816 width=27) (actual time=7.503..7.503 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
40. 4.119 4.119 ↓ 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.009..4.119 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
41. 0.541 2.403 ↑ 1.0 2,518 1

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

  • Output: pro.product_id, tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Buckets: 4096 Batches: 1 Memory Usage: 242kB
  • Buffers: shared hit=42
42. 0.869 1.862 ↑ 1.0 2,518 1

Hash Join (cost=101.00..153.80 rows=2,518 width=53) (actual time=0.780..1.862 rows=2,518 loops=1)

  • Output: pro.product_id, tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Inner Unique: true
  • Hash Cond: (tuomc.product_id = pro.product_id)
  • Buffers: shared hit=42
43. 0.243 0.243 ↑ 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.016..0.243 rows=2,518 loops=1)

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Buffers: shared hit=21
44. 0.377 0.750 ↑ 1.0 2,517 1

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

  • Output: pro.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=21
45. 0.373 0.373 ↑ 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.373 rows=2,517 loops=1)

  • Output: pro.product_id
  • Heap Fetches: 0
  • Buffers: shared hit=21
46. 110.470 212.947 ↑ 1.0 381,407 1

Hash (cost=17,324.07..17,324.07 rows=381,407 width=49) (actual time=212.947..212.947 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
47. 102.477 102.477 ↑ 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.077..102.477 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
48. 0.584 4.185 ↓ 1.0 2,520 1

Hash (cost=266.09..266.09 rows=2,519 width=48) (actual time=4.185..4.185 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
49. 0.794 3.601 ↓ 1.0 2,520 1

Hash Join (cost=185.27..266.09 rows=2,519 width=48) (actual time=2.594..3.601 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
50. 0.244 0.244 ↑ 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.017..0.244 rows=2,518 loops=1)

  • Output: tuomc_1.product_id, tuomc_1.uom_type_id, tuomc_1.rate
  • Buffers: shared hit=21
51. 0.532 2.563 ↑ 1.0 2,518 1

Hash (cost=153.80..153.80 rows=2,518 width=48) (actual time=2.563..2.563 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
52. 0.917 2.031 ↑ 1.0 2,518 1

Hash Join (cost=101.00..153.80 rows=2,518 width=48) (actual time=0.907..2.031 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
53. 0.222 0.222 ↑ 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.222 rows=2,518 loops=1)

  • Output: suomc_1.product_id, suomc_1.uom_type_id, suomc_1.rate
  • Buffers: shared hit=21
54. 0.461 0.892 ↑ 1.0 2,517 1

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

  • Output: pro_1.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=21
55. 0.431 0.431 ↑ 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.021..0.431 rows=2,517 loops=1)

  • Output: pro_1.product_id
  • Heap Fetches: 0
  • Buffers: shared hit=21
56. 1.126 2.575 ↑ 1.0 5,680 1

Hash (cost=287.80..287.80 rows=5,680 width=34) (actual time=2.575..2.575 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
57. 1.449 1.449 ↑ 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.015..1.449 rows=5,680 loops=1)

  • Output: contract_family.contract_title, contract_family.contract_sequence
  • Buffers: shared hit=231
58. 6.627 153.610 ↓ 1.5 26,424 1

Hash (cost=77,713.62..77,713.62 rows=18,056 width=64) (actual time=153.610..153.610 rows=26,424 loops=1)

  • Output: cpl.distributor_invoice_number, cpl.claim_product_id, cpl.claim_id
  • Buckets: 32768 Batches: 1 Memory Usage: 1934kB
  • Buffers: shared hit=127057
59. 3.644 146.983 ↓ 1.5 26,424 1

Subquery Scan on cpl (cost=77,126.80..77,713.62 rows=18,056 width=64) (actual time=129.111..146.983 rows=26,424 loops=1)

  • Output: cpl.distributor_invoice_number, cpl.claim_product_id, cpl.claim_id
  • Buffers: shared hit=127057
60. 12.301 143.339 ↓ 1.5 26,424 1

GroupAggregate (cost=77,126.80..77,533.06 rows=18,056 width=64) (actual time=129.109..143.339 rows=26,424 loops=1)

  • Output: claim_product_1.claim_id, claim_product_location.claim_product_id, string_agg(claim_product_location.distributor_invoice_number, ', '::text)
  • Group Key: claim_product_1.claim_id, claim_product_location.claim_product_id
  • Buffers: shared hit=127057
61. 11.390 131.038 ↓ 1.5 26,424 1

Sort (cost=77,126.80..77,171.94 rows=18,056 width=33) (actual time=129.093..131.038 rows=26,424 loops=1)

  • Output: claim_product_1.claim_id, claim_product_location.claim_product_id, claim_product_location.distributor_invoice_number
  • Sort Key: claim_product_location.claim_product_id
  • Sort Method: quicksort Memory: 2833kB
  • Buffers: shared hit=127057
62. 0.780 119.648 ↓ 1.5 26,424 1

Nested Loop (cost=389.90..75,850.22 rows=18,056 width=33) (actual time=7.097..119.648 rows=26,424 loops=1)

  • Output: claim_product_1.claim_id, claim_product_location.claim_product_id, claim_product_location.distributor_invoice_number
  • Buffers: shared hit=127057
63. 36.141 39.596 ↑ 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=32) (actual time=7.073..39.596 rows=26,424 loops=1)

  • Output: claim_product_1.claim_id, claim_product_1.claim_product_id
  • Recheck Cond: (claim_product_1.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Heap Blocks: exact=20870
  • Buffers: shared hit=20975
64. 3.455 3.455 ↑ 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.454..3.455 rows=26,424 loops=1)

  • Index Cond: (claim_product_1.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
65. 79.272 79.272 ↑ 5.0 1 26,424

Index Only Scan using claim_product_location_pkey on client_pinnacle.claim_product_location (cost=0.56..1.55 rows=5 width=17) (actual time=0.002..0.003 rows=1 loops=26,424)

  • Output: claim_product_location.claim_product_id, claim_product_location.location_id, claim_product_location.distributor_invoice_number
  • Index Cond: (claim_product_location.claim_product_id = claim_product_1.claim_product_id)
  • Heap Fetches: 0
  • Buffers: shared hit=106082