explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VKOJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 8.100 ↓ 0.0 0 1

Subquery Scan on foo (cost=850,544.68..4,630,221.21 rows=44 width=58) (actual time=8.099..8.100 rows=0 loops=1)

  • Filter: ((SubPlan 1) OR (SubPlan 2))
2. 0.000 8.098 ↓ 0.0 0 1

Finalize GroupAggregate (cost=850,544.68..850,552.63 rows=59 width=117) (actual time=8.098..8.098 rows=0 loops=1)

  • Group Key: rxa.file_name, rsd.claim_id, rsd.payment_reference, rxa.record_count
3. 8.541 8.702 ↓ 0.0 0 1

Gather Merge (cost=850,544.68..850,551.14 rows=50 width=117) (actual time=8.096..8.702 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.001 0.161 ↓ 0.0 0 3 / 3

Partial GroupAggregate (cost=849,544.66..849,545.34 rows=25 width=117) (actual time=0.161..0.161 rows=0 loops=3)

  • Group Key: rxa.file_name, rsd.claim_id, rsd.payment_reference, rxa.record_count
5. 0.043 0.160 ↓ 0.0 0 3 / 3

Sort (cost=849,544.66..849,544.72 rows=25 width=89) (actual time=0.160..0.160 rows=0 loops=3)

  • Sort Key: rxa.file_name, rsd.claim_id, rsd.payment_reference, rxa.record_count
  • Sort Method: quicksort Memory: 25kB
6. 0.039 0.117 ↓ 0.0 0 3 / 3

Hash Join (cost=8.46..849,544.08 rows=25 width=89) (actual time=0.117..0.117 rows=0 loops=3)

  • Hash Cond: ((rsd.file_name)::text = (rxa.file_name)::text)
7. 0.014 0.014 ↑ 7,735,394.0 1 3 / 3

Parallel Seq Scan on ra_stg_details rsd (cost=0.00..829,229.94 rows=7,735,394 width=83) (actual time=0.014..0.014 rows=1 loops=3)

8. 0.000 0.064 ↓ 0.0 0 3 / 3

Hash (cost=8.45..8.45 rows=1 width=62) (actual time=0.064..0.064 rows=0 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
9. 0.064 0.064 ↓ 0.0 0 3 / 3

Index Scan using file_id_unique on ra_xml_file rxa (cost=0.42..8.45 rows=1 width=62) (actual time=0.064..0.064 rows=0 loops=3)

  • Index Cond: ((file_id)::text = 'T00000000001039'::text)
  • Filter: ((is_recovery = 'Y'::bpchar) AND ((created_by)::text = 'ETL'::text))
  • Rows Removed by Filter: 1
10.          

SubPlan (for Subquery Scan)

11. 0.000 0.000 ↓ 0.0 0

Subquery Scan on f (cost=129.99..130.02 rows=1 width=0) (never executed)

12. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=129.99..130.01 rows=1 width=50) (never executed)

  • Group Key: ipa.remittance_id, ipa.claim_id
  • Filter: (sum(ipa.amount) = foo.amount)
13. 0.000 0.000 ↓ 0.0 0

Sort (cost=129.99..129.99 rows=1 width=22) (never executed)

  • Sort Key: ipa.remittance_id
14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.55..129.98 rows=1 width=22) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.99..21.05 rows=1 width=8) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_claim_remittance_claim_id on insurance_claim_remittance icr (cost=0.56..12.60 rows=1 width=4) (never executed)

  • Index Cond: ((claim_id)::text = (foo.claim_id)::text)
  • Filter: ((payment_reference)::text = (foo.payment_reference)::text)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_remittance_pkey on insurance_remittance ir (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: (remittance_id = icr.remittance_id)
  • Filter: ((job_status = ANY ('{S,C}'::bpchar[])) AND (is_recovery = 'Y'::bpchar))
18. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=0.56..108.92 rows=1 width=22) (never executed)

  • Index Cond: (remittance_id = ir.remittance_id)
  • Filter: (((claim_id)::text = (foo.claim_id)::text) AND ((payment_reference)::text = (foo.payment_reference)::text))
19. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ifoo (cost=63,932.11..63,932.15 rows=1 width=0) (never executed)

20. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=63,932.11..63,932.14 rows=1 width=116) (never executed)

  • Group Key: rxf.file_name, rsd_1.claim_id, rxf.payment_reference, rxf.record_count
  • Filter: (sum(rsd_1.payment_amount) = foo.amount)
21. 0.000 0.000 ↓ 0.0 0

Sort (cost=63,932.11..63,932.11 rows=1 width=88) (never executed)

  • Sort Key: rxf.file_name
22. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..63,932.10 rows=1 width=88) (never executed)

  • Join Filter: ((rxf.file_name)::text = (rsd_1.file_name)::text)
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on ra_xml_file rxf (cost=0.00..63,012.50 rows=1 width=70) (never executed)

  • Filter: ((status = 'V'::bpchar) AND ((payment_reference)::text = (foo.payment_reference)::text) AND (is_recovery = 'Y'::bpchar) AND (foo.record_count = record_count))
24. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ra_stg_details_claim_id_idx on ra_stg_details rsd_1 (cost=0.56..916.74 rows=229 width=74) (never executed)

  • Index Cond: ((foo.claim_id)::text = (claim_id)::text)
Planning time : 3.080 ms
Execution time : 8.871 ms