explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XiM7

Settings
# exclusive inclusive rows x rows loops node
1. 173.351 1,910.764 ↑ 5.4 26,424 1

Merge Left Join (cost=76,904.39..184,158.23 rows=141,740 width=313) (actual time=151.388..1,910.764 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, 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(claim_product_location.distributor_invoice_number, ', '::text)))
  • Inner Unique: true
  • Merge Cond: (claim_product.claim_product_id = claim_product_location.claim_product_id)
  • Buffers: shared hit=149053, temp read=963 written=963
2. 58.421 161.954 ↑ 5.4 26,424 1

Sort (cost=76,903.84..77,258.19 rows=141,740 width=290) (actual time=151.273..161.954 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, contract_item.contract_id, contract_item.won, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, company.company_name, company.company_number, contract_family.contract_title
  • Sort Key: claim_product.claim_product_id
  • Sort Method: external sort Disk: 7704kB
  • Buffers: shared hit=127925, temp read=963 written=963
3. 13.996 103.533 ↑ 5.4 26,424 1

Hash Join (cost=2,482.57..52,317.68 rows=141,740 width=290) (actual time=30.205..103.533 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, contract_item.contract_id, contract_item.won, product.product_name, product.product_number, product_uom_conversions.uom_type_ids, company.company_name, company.company_number, contract_family.contract_title
  • Inner Unique: true
  • Hash Cond: (claim_product.product_id = product_uom_conversions.product_id)
  • Buffers: shared hit=127925
4. 6.543 83.792 ↑ 1.0 26,424 1

Nested Loop (cost=2,318.34..52,080.54 rows=27,537 width=274) (actual time=24.441..83.792 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, contract_item.contract_id, contract_item.won, product.product_name, product.product_number, product.product_id, company.company_name, company.company_number, contract_family.contract_title
  • Buffers: shared hit=127908
5. 0.004 0.062 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.12..8.68 rows=1 width=46) (actual time=0.058..0.062 rows=1 loops=1)

  • Output: claim.claim_id, contract_family.contract_title
  • Inner Unique: true
  • Buffers: shared hit=11
6. 0.006 0.046 ↑ 1.0 1 1

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

  • Output: claim.claim_id, claim.contract_sequence
  • Inner Unique: true
  • Buffers: shared hit=8
7. 0.026 0.026 ↑ 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.023..0.026 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
8. 0.014 0.014 ↑ 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.014..0.014 rows=1 loops=1)

  • Output: invoice.invoice_id
  • Index Cond: (invoice.invoice_id = claim.invoice_id)
  • Heap Fetches: 0
  • Buffers: shared hit=4
9. 0.012 0.012 ↑ 1.0 1 1

Index Scan using contract_family_unique on client_pinnacle.contract_family (cost=0.28..3.30 rows=1 width=34) (actual time=0.012..0.012 rows=1 loops=1)

  • Output: contract_family.contract_family_id, contract_family.client_id, contract_family.contractee_company_id, contract_family.contractee_grouping_id, contract_family.creator_id, contract_family.contract_sequence, contract_family.cost_basis_type, contract_family.contract_type, contract_family.contract_title, contract_family.contract_template_id, contract_family.payment_calendar_id, contract_family.catch_all, contract_family.import_contract_number, contract_family.accrual_quantity_source
  • Index Cond: (contract_family.contract_sequence = claim.contract_sequence)
  • Buffers: shared hit=3
10. 0.000 77.187 ↑ 1.0 26,424 1

Gather (cost=2,317.21..51,796.49 rows=27,537 width=260) (actual time=24.379..77.187 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, contract_item.contract_id, contract_item.won, product.product_name, product.product_number, product.product_id, company.company_name, company.company_number
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=127897
11. 5.840 91.262 ↑ 1.3 8,808 3

Hash Left Join (cost=1,317.21..48,042.79 rows=11,474 width=260) (actual time=19.783..91.262 rows=8,808 loops=3)

  • 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, contract_item.contract_id, contract_item.won, product.product_name, product.product_number, product.product_id, company.company_name, company.company_number
  • Inner Unique: true
  • Hash Cond: (claim_product.distributor_company_id = company.company_id)
  • Buffers: shared hit=127897
  • Worker 0: actual time=18.740..108.819 rows=12550 loops=1
  • Buffers: shared hit=60490
  • Worker 1: actual time=17.309..111.214 rows=11035 loops=1
  • Buffers: shared hit=53320
12. 5.038 78.247 ↑ 1.3 8,808 3

Hash Join (cost=825.57..47,521.02 rows=11,474 width=229) (actual time=12.506..78.247 rows=8,808 loops=3)

  • 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, contract_item.contract_id, contract_item.won, product.product_name, product.product_number, product.product_id
  • Inner Unique: true
  • Hash Cond: (claim_product.product_id = product.product_id)
  • Buffers: shared hit=127135
  • Worker 0: actual time=10.102..92.830 rows=12550 loops=1
  • Buffers: shared hit=60236
  • Worker 1: actual time=10.889..97.299 rows=11035 loops=1
  • Buffers: shared hit=53066
13. 6.713 71.344 ↑ 1.3 8,808 3

Hash Left Join (cost=699.94..47,365.21 rows=11,474 width=175) (actual time=10.547..71.344 rows=8,808 loops=3)

  • 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, contract_item.contract_id, contract_item.won
  • Hash Cond: ((claim_product.product_id = pro.product_id) AND (contract_item.uom_type_id = suomc.uom_type_id) AND (claim_product.uom_type_id = tuomc.uom_type_id))
  • Buffers: shared hit=126864
  • Worker 0: actual time=8.006..84.467 rows=12550 loops=1
  • Buffers: shared hit=60135
  • Worker 1: actual time=8.359..88.345 rows=11035 loops=1
  • Buffers: shared hit=52965
14. 33.098 58.169 ↑ 1.3 8,808 3

Nested Loop Left Join (cost=389.77..46,811.22 rows=11,474 width=191) (actual time=4.034..58.169 rows=8,808 loops=3)

  • 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, contract_item.contract_id, contract_item.won, contract_item.uom_type_id
  • Inner Unique: true
  • Buffers: shared hit=126673
  • Worker 0: actual time=0.878..68.847 rows=12550 loops=1
  • Buffers: shared hit=60071
  • Worker 1: actual time=0.412..71.667 rows=11035 loops=1
  • Buffers: shared hit=52901
15. 18.967 25.068 ↑ 1.3 8,808 3

Parallel Bitmap Heap Scan on client_pinnacle.claim_product (cost=389.34..31,480.56 rows=11,474 width=158) (actual time=4.003..25.068 rows=8,808 loops=3)

  • 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=2240
  • Buffers: shared hit=20975
  • Worker 0: actual time=0.847..27.820 rows=12550 loops=1
  • Buffers: shared hit=9870
  • Worker 1: actual time=0.386..27.700 rows=11035 loops=1
  • Buffers: shared hit=8760
16. 6.101 6.101 ↑ 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=6.101..6.101 rows=26,424 loops=1)

  • Index Cond: (claim_product.claim_id = '2c3c9316-267c-4439-b4d6-e0f137e32fdf'::uuid)
  • Buffers: shared hit=105
17. 0.003 0.003 ↑ 1.0 1 26,424

Index Scan using contract_item_pkey on client_pinnacle.contract_item (cost=0.42..1.34 rows=1 width=49) (actual time=0.003..0.003 rows=1 loops=26,424)

  • 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, contract_item.rebate_growth_type, contract_item.rebate_growth_decimal_model
  • Index Cond: (claim_product.contract_item_id = contract_item.contract_item_id)
  • Buffers: shared hit=105698
  • Worker 0: actual time=0.003..0.003 rows=1 loops=12550
  • Buffers: shared hit=50201
  • Worker 1: actual time=0.003..0.003 rows=1 loops=11035
  • Buffers: shared hit=44141
18. 1.075 6.462 ↓ 1.0 2,520 3

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

  • Output: pro.product_id, suomc.uom_type_id, tuomc.uom_type_id
  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=191
  • Worker 0: actual time=7.070..7.070 rows=2520 loops=1
  • Buffers: shared hit=64
  • Worker 1: actual time=7.885..7.885 rows=2520 loops=1
  • Buffers: shared hit=64
19. 1.297 5.387 ↓ 1.0 2,520 3

Hash Join (cost=185.27..266.09 rows=2,519 width=48) (actual time=3.789..5.387 rows=2,520 loops=3)

  • Output: pro.product_id, suomc.uom_type_id, tuomc.uom_type_id
  • Hash Cond: (tuomc.product_id = pro.product_id)
  • Buffers: shared hit=191
  • Worker 0: actual time=4.154..5.883 rows=2520 loops=1
  • Buffers: shared hit=64
  • Worker 1: actual time=4.466..6.483 rows=2520 loops=1
  • Buffers: shared hit=64
20. 0.340 0.340 ↑ 1.0 2,518 3

Seq Scan on client_pinnacle.product_uom_conversion tuomc (cost=0.00..46.18 rows=2,518 width=32) (actual time=0.013..0.340 rows=2,518 loops=3)

  • Output: tuomc.product_id, tuomc.uom_type_id, tuomc.rate
  • Buffers: shared hit=63
  • Worker 0: actual time=0.016..0.370 rows=2518 loops=1
  • Buffers: shared hit=21
  • Worker 1: actual time=0.012..0.430 rows=2518 loops=1
  • Buffers: shared hit=21
21. 0.903 3.750 ↑ 1.0 2,518 3

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

  • Output: pro.product_id, suomc.product_id, suomc.uom_type_id
  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=128
  • Worker 0: actual time=4.105..4.105 rows=2518 loops=1
  • Buffers: shared hit=43
  • Worker 1: actual time=4.422..4.422 rows=2518 loops=1
  • Buffers: shared hit=43
22. 1.249 2.847 ↑ 1.0 2,518 3

Hash Join (cost=101.00..153.80 rows=2,518 width=48) (actual time=1.304..2.847 rows=2,518 loops=3)

  • Output: pro.product_id, suomc.product_id, suomc.uom_type_id
  • Inner Unique: true
  • Hash Cond: (suomc.product_id = pro.product_id)
  • Buffers: shared hit=128
  • Worker 0: actual time=1.449..3.099 rows=2518 loops=1
  • Buffers: shared hit=43
  • Worker 1: actual time=1.385..3.241 rows=2518 loops=1
  • Buffers: shared hit=43
23. 0.327 0.327 ↑ 1.0 2,518 3

Seq Scan on client_pinnacle.product_uom_conversion suomc (cost=0.00..46.18 rows=2,518 width=32) (actual time=0.003..0.327 rows=2,518 loops=3)

  • Output: suomc.product_id, suomc.uom_type_id, suomc.rate
  • Buffers: shared hit=63
  • Worker 0: actual time=0.004..0.366 rows=2518 loops=1
  • Buffers: shared hit=21
  • Worker 1: actual time=0.002..0.396 rows=2518 loops=1
  • Buffers: shared hit=21
24. 0.717 1.271 ↑ 1.0 2,517 3

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

  • Output: pro.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=65
  • Worker 0: actual time=1.405..1.406 rows=2517 loops=1
  • Buffers: shared hit=22
  • Worker 1: actual time=1.350..1.350 rows=2517 loops=1
  • Buffers: shared hit=22
25. 0.554 0.554 ↑ 1.0 2,517 3

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

  • Output: pro.product_id
  • Heap Fetches: 0
  • Buffers: shared hit=65
  • Worker 0: actual time=0.064..0.598 rows=2517 loops=1
  • Buffers: shared hit=22
  • Worker 1: actual time=0.047..0.584 rows=2517 loops=1
  • Buffers: shared hit=22
26. 1.035 1.865 ↑ 1.0 2,517 3

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

  • Output: product.product_name, product.product_number, product.product_id
  • Buckets: 4096 Batches: 1 Memory Usage: 247kB
  • Buffers: shared hit=207
  • Worker 0: actual time=1.978..1.978 rows=2517 loops=1
  • Buffers: shared hit=69
  • Worker 1: actual time=2.388..2.388 rows=2517 loops=1
  • Buffers: shared hit=69
27. 0.830 0.830 ↑ 1.0 2,517 3

Seq Scan on client_pinnacle.product (cost=0.00..94.17 rows=2,517 width=54) (actual time=0.018..0.830 rows=2,517 loops=3)

  • Output: product.product_name, product.product_number, product.product_id
  • Buffers: shared hit=207
  • Worker 0: actual time=0.021..0.928 rows=2517 loops=1
  • Buffers: shared hit=69
  • Worker 1: actual time=0.019..0.955 rows=2517 loops=1
  • Buffers: shared hit=69
28. 4.045 7.175 ↑ 1.0 10,562 3

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

  • Output: company.company_name, company.company_number, company.company_id
  • Buckets: 16384 Batches: 1 Memory Usage: 956kB
  • Buffers: shared hit=762
  • Worker 0: actual time=8.509..8.509 rows=10562 loops=1
  • Buffers: shared hit=254
  • Worker 1: actual time=6.288..6.288 rows=10562 loops=1
  • Buffers: shared hit=254
29. 3.130 3.130 ↑ 1.0 10,562 3

Seq Scan on client_pinnacle.company (cost=0.00..359.62 rows=10,562 width=47) (actual time=0.023..3.130 rows=10,562 loops=3)

  • Output: company.company_name, company.company_number, company.company_id
  • Buffers: shared hit=762
  • Worker 0: actual time=0.035..3.529 rows=10562 loops=1
  • Buffers: shared hit=254
  • Worker 1: actual time=0.026..2.640 rows=10562 loops=1
  • Buffers: shared hit=254
30. 1.182 5.745 ↑ 1.0 2,517 1

Hash (cost=132.77..132.77 rows=2,517 width=48) (actual time=5.745..5.745 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
31. 0.608 4.563 ↑ 1.0 2,517 1

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

  • Output: product_uom_conversions.uom_type_ids, product_uom_conversions.product_id
  • Buffers: shared hit=17
32. 3.217 3.955 ↑ 1.0 2,517 1

GroupAggregate (cost=0.28..107.60 rows=2,517 width=48) (actual time=0.041..3.955 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
33. 0.738 0.738 ↑ 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.028..0.738 rows=2,518 loops=1)

  • Output: product_uom_conversion.product_id, product_uom_conversion.uom_type_id
  • Heap Fetches: 0
  • Buffers: shared hit=17
34. 1,154.816 1,575.459 ↓ 3.1 1,878,358 1

GroupAggregate (cost=0.56..98,824.53 rows=602,585 width=48) (actual time=0.049..1,575.459 rows=1,878,358 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
  • Buffers: shared hit=21128
35. 420.643 420.643 ↑ 1.0 2,965,119 1

Index Only Scan using claim_product_location_pkey on client_pinnacle.claim_product_location (cost=0.56..76,465.05 rows=2,965,433 width=17) (actual time=0.036..420.643 rows=2,965,119 loops=1)

  • Output: claim_product_location.claim_product_id, claim_product_location.location_id, claim_product_location.distributor_invoice_number
  • Heap Fetches: 0
  • Buffers: shared hit=21128