explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4GO

Settings
# exclusive inclusive rows x rows loops node
1. 99.596 1,118.371 ↑ 1.6 68,423 1

Hash Join (cost=51,775.83..63,700.21 rows=106,495 width=112) (actual time=987.721..1,118.371 rows=68,423 loops=1)

  • Output: xref.note_id, claim_data.invoice_id, '49d1d0aa-35e9-4e4b-8596-4dcde88d49ce'::uuid, ((((('Document type: '::text || xref.document_type) || ' - User: '::text) || xref.user_name) || ' - Note: '::text) || xref.note), NULL::text
  • Hash Cond: (claim_data.deduction_id = xref.id)
  • Buffers: shared hit=32042, temp read=2766 written=2762
2. 188.278 224.158 ↓ 1.2 101,877 1

HashAggregate (cost=29,536.90..30,406.99 rows=87,009 width=24) (actual time=191.931..224.158 rows=101,877 loops=1)

  • Output: claim_data.invoice_id, claim_data.deduction_id
  • Group Key: claim_data.invoice_id, claim_data.deduction_id
  • Buffers: shared hit=27235
3. 35.880 35.880 ↑ 1.0 153,460 1

Seq Scan on client_stonyfield_sandbox.claim_data (cost=0.00..28,769.60 rows=153,460 width=24) (actual time=0.015..35.880 rows=153,460 loops=1)

  • Output: claim_data.processor_name, claim_data.customer_name, claim_data.customer_number, claim_data.deduction_id, claim_data.deduction_number, claim_data.line, claim_data.deduction_type, claim_data.reason_code_description, claim_data.reason_code, claim_data.reason_category, claim_data.gl, claim_data.customer_claim_number, claim_data.external_reference, claim_data.deduction_create_date, claim_data.total_open_amount, claim_data.total_original_amount, claim_data.sap_channel, claim_data.company_code, claim_data.promo_customer_number, claim_data.promotion_number, claim_data.promotion_name, claim_data.commitment_number, claim_data.resolution_id, claim_data.commit_id, claim_data.resolution_type, claim_data.resolution_amount, claim_data.deduction_resolution_date, claim_data.approval, claim_data.resolution_status, claim_data.resolution_created_by, claim_data.resolution_updated_by, claim_data.invalid_customer_name, claim_data.invalid_identifier, claim_data.invalid_root_cause, claim_data.invalid_amount, claim_data.invalid_resolution, claim_data.correspondence, claim_data.resolution_comments, claim_data.owner_name, claim_data.days_outstanding, claim_data.date_to_pay, claim_data.fiscal_year, claim_data.source, claim_data.tactic_type, claim_data.promotion_type, claim_data.tier_1, claim_data.tier_2, claim_data.tier_3, claim_data.row_id, claim_data.invoice_id, claim_data.claim_id, claim_data.contract_sequence, claim_data.claimant_company_id, claim_data.distributor_company_id, claim_data.product_grouping_id, claim_data.lumpsum_id, claim_data.start_date, claim_data.end_date
  • Buffers: shared hit=27235
4. 59.538 794.617 ↑ 1.0 130,451 1

Hash (cost=19,206.91..19,206.91 rows=136,161 width=55) (actual time=794.617..794.617 rows=130,451 loops=1)

  • Output: xref.note_id, xref.document_type, xref.user_name, xref.note, xref.id
  • Buckets: 131072 Batches: 2 Memory Usage: 6668kB
  • Buffers: shared hit=4807, temp read=1869 written=2480
5. 639.430 735.079 ↑ 1.0 130,451 1

Hash Join (cost=4,895.28..19,206.91 rows=136,161 width=55) (actual time=59.772..735.079 rows=130,451 loops=1)

  • Output: xref.note_id, xref.document_type, xref.user_name, xref.note, xref.id
  • Hash Cond: ("right"(xref.file_name, ("position"(reverse(xref.file_name), '/'::text) - 1)) = ids.attachment_name)
  • Buffers: shared hit=4807, temp read=1869 written=1867
6. 36.622 36.622 ↑ 1.0 130,452 1

Seq Scan on client_stonyfield_sandbox.claim_attachment_xref xref (cost=0.00..4,682.61 rows=136,161 width=160) (actual time=0.010..36.622 rows=130,452 loops=1)

  • Output: xref.note_id, xref.document_type, xref.user_name, xref.note, xref.file_name, xref.id
  • Buffers: shared hit=3321
7. 38.844 59.027 ↑ 1.0 108,946 1

Hash (cost=2,575.46..2,575.46 rows=108,946 width=42) (actual time=59.027..59.027 rows=108,946 loops=1)

  • Output: ids.attachment_name
  • Buckets: 131072 Batches: 2 Memory Usage: 4988kB
  • Buffers: shared hit=1486, temp written=419
8. 20.183 20.183 ↑ 1.0 108,946 1

Seq Scan on client_stonyfield_sandbox.claim_attachment_ids ids (cost=0.00..2,575.46 rows=108,946 width=42) (actual time=0.006..20.183 rows=108,946 loops=1)

  • Output: ids.attachment_name
  • Buffers: shared hit=1486