explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Roms

Settings
# exclusive inclusive rows x rows loops node
1. 6,738.679 119,252.347 ↓ 1.0 14,188,493 1

GroupAggregate (cost=53,347.16..1,823,903.63 rows=14,177,105 width=73) (actual time=1,115.656..119,252.347 rows=14,188,493 loops=1)

  • Output: fc.claim_sk, fc.claim_id, ("left"(string_agg((dd_x.deduction_number)::text, ', '::text), 250))::dw_text
  • Group Key: fc.claim_sk
  • Buffers: shared hit=4840940 read=4631681 dirtied=9 written=57
  • Functions: 26
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 4.628 ms, Inlining 82.974 ms, Optimization 243.945 ms, Emission 159.698 ms, Total 491.245 ms
2. 6,199.401 112,513.668 ↓ 1.0 14,190,193 1

Merge Left Join (cost=53,347.16..1,504,918.76 rows=14,177,105 width=54) (actual time=1,115.098..112,513.668 rows=14,190,193 loops=1)

  • Output: fc.claim_sk, fc.claim_id, dd_x.deduction_number
  • Merge Cond: (fc.claim_sk = dc.claim_sk)
  • Buffers: shared hit=4840940 read=4631681 dirtied=9 written=57
3. 105,592.117 105,592.117 ↓ 1.0 14,188,493 1

Index Scan using fact_claim_pkey on public.fact_claim fc (cost=0.43..1,410,825.21 rows=14,177,105 width=41) (actual time=0.021..105,592.117 rows=14,188,493 loops=1)

  • Output: fc.claim_sk, fc.source_id, fc.claim_detail_id, fc.client_sk, fc.claimant_company_sk, fc.product_sk, fc.lumpsum_sk, fc.contract_sk, fc.uom_sk, fc.claim_sequence, fc.claim_state, fc.requested_rate, fc.requested_quantity, fc.requested_amount, fc.allowed_rate, fc.allowed_quantity, fc.allowed_amount, fc.calculated_rate, fc.price, fc.delivery_date, fc.pended_date, fc.completed_date, fc.transaction_date, fc.cleared_date, fc.voided_date, fc.price_type, fc.rebate_type, fc.eligibility_type, fc.reason_type, fc.reason_code, fc.claim_tag, fc.hint_contract_lumpsum_description, fc.invoice_sk, fc.event_start_date, fc.event_end_date, fc.distributor_company_sk, fc.claim_id, fc.contract_id, fc.contract_family_sk, fc.contract_amend_version_sk, fc.contract_renew_version_sk, fc.authority, fc.claim_type, fc.received_date, fc.submitted_date, fc.transaction_type, fc.transaction_amount, fc.claim_claimline_type, fc.claimant_contract_name, fc.claim_header_reason, fc.claim_calculated_amount
  • Buffers: shared hit=4840938 read=4619798 dirtied=2 written=57
4. 238.747 722.150 ↑ 1.0 353,552 1

Sort (cost=53,346.73..54,230.74 rows=353,604 width=17) (actual time=627.955..722.150 rows=353,552 loops=1)

  • Output: dc.claim_sk, dd_x.deduction_number
  • Sort Key: dc.claim_sk
  • Sort Method: quicksort Memory: 29159kB
  • Buffers: shared hit=2 read=11883 dirtied=7
5. 137.146 483.403 ↑ 1.0 353,552 1

Hash Left Join (cost=9,554.71..20,758.98 rows=353,604 width=17) (actual time=217.090..483.403 rows=353,552 loops=1)

  • Output: dc.claim_sk, dd_x.deduction_number
  • Inner Unique: true
  • Hash Cond: (dc.deduction_sk = dd_x.deduction_sk)
  • Buffers: shared hit=2 read=11883 dirtied=7
6. 131.163 131.163 ↑ 1.0 353,552 1

Seq Scan on public.fact_deduction_claim dc (cost=0.00..10,276.04 rows=353,604 width=8) (actual time=0.668..131.163 rows=353,552 loops=1)

  • Output: dc.deduction_claim_sk, dc.source_id, dc.deduction_claim_id, dc.client_sk, dc.deduction_sk, dc.claim_sk, dc.date_matched, dc.inoperative, dc.deduction_amount_allotted, dc.claim_deduction_amount_cleared, dc.claim_deduction_amount_ratio, dc.claim_id
  • Buffers: shared read=6740 dirtied=4
7. 76.567 215.094 ↑ 1.0 195,949 1

Hash (cost=7,104.87..7,104.87 rows=195,987 width=17) (actual time=215.093..215.094 rows=195,949 loops=1)

  • Output: dd_x.deduction_number, dd_x.deduction_sk
  • Buckets: 262144 Batches: 1 Memory Usage: 11750kB
  • Buffers: shared hit=2 read=5143 dirtied=3
8. 138.527 138.527 ↑ 1.0 195,949 1

Seq Scan on public.dim_deduction dd_x (cost=0.00..7,104.87 rows=195,987 width=17) (actual time=0.030..138.527 rows=195,949 loops=1)

  • Output: dd_x.deduction_number, dd_x.deduction_sk
  • Buffers: shared hit=2 read=5143 dirtied=3