explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 98c

Settings
# exclusive inclusive rows x rows loops node
1. 2,489.203 18,549.788 ↓ 980.0 141,120 1

GroupAggregate (cost=10,766.53..10,788.85 rows=144 width=446) (actual time=15,040.211..18,549.788 rows=141,120 loops=1)

  • Output: crv.contract_renew_version_id, i.claimant_company_id, col.contract_lumpsum_id, clf.date_range, sum(cl.allowed_amount) FILTER (WHERE ((cri.claim_product_contract_rebate_type = 'DIRECT'::enum.claim_product_contract_rebate_type) AND (clf.date_range @> lower(cl.event_date_range)))), sum(cl.allowed_amount) FILTER (WHERE ((cri.claim_product_contract_rebate_type = 'DEVIATED'::enum.claim_product_contract_rebate_type) AND (clf.date_range @> lower(cl.event_date_range)))), sum(cl.allowed_amount) FILTER (WHERE (clf.date_range @> lower(cl.event_date_range))), sum(cl.allowed_amount) FILTER (WHERE ((cri.claim_product_contract_rebate_type = 'DIRECT'::enum.claim_product_contract_rebate_type) AND (clf.date_range @> (i.create_date)::date))), sum(cl.allowed_amount) FILTER (WHERE ((cri.claim_product_contract_rebate_type = 'DEVIATED'::enum.claim_product_contract_rebate_type) AND (clf.date_range @> (i.create_date)::date))), sum(cl.allowed_amount) FILTER (WHERE (clf.date_range @> (i.create_date)::date)), sum(cl.allowed_amount) FILTER (WHERE ((cri.claim_product_contract_rebate_type = 'DIRECT'::enum.claim_product_contract_rebate_type) AND (clf.date_range @> (ct.transaction_date)::date))), sum(cl.allowed_amount) FILTER (WHERE ((cri.claim_product_contract_rebate_type = 'DEVIATED'::enum.claim_product_contract_rebate_type) AND (clf.date_range @> (ct.transaction_date)::date))), sum(cl.allowed_amount) FILTER (WHERE (clf.date_range @> (ct.transaction_date)::date)), sum(cl.allowed_amount) FILTER (WHERE ((cri.claim_product_contract_rebate_type = 'DIRECT'::enum.claim_product_contract_rebate_type) AND (clf.date_range @> dc.deduction_match_date))), sum(cl.allowed_amount) FILTER (WHERE ((cri.claim_product_contract_rebate_type = 'DEVIATED'::enum.claim_product_contract_rebate_type) AND (clf.date_range @> dc.deduction_match_date))), sum(cl.allowed_amount) FILTER (WHERE (clf.date_range @> dc.deduction_match_date))
  • Group Key: crv.contract_renew_version_id, i.claimant_company_id, col.contract_lumpsum_id, clf.date_range
  • Buffers: shared hit=3324033 read=17, local hit=8851, temp read=16796 written=16820
2. 4,469.037 16,060.585 ↓ 8,465.0 1,218,960 1

Sort (cost=10,766.53..10,766.89 rows=144 width=106) (actual time=15,040.184..16,060.585 rows=1,218,960 loops=1)

  • Output: crv.contract_renew_version_id, i.claimant_company_id, col.contract_lumpsum_id, clf.date_range, cl.allowed_amount, cri.claim_product_contract_rebate_type, cl.event_date_range, i.create_date, ct.transaction_date, dc.deduction_match_date
  • Sort Key: crv.contract_renew_version_id, i.claimant_company_id, col.contract_lumpsum_id, clf.date_range
  • Sort Method: external merge Disk: 134368kB
  • Buffers: shared hit=3324033 read=17, local hit=8851, temp read=16796 written=16820
3. 247.705 11,591.548 ↓ 8,465.0 1,218,960 1

Nested Loop (cost=5,870.43..10,761.36 rows=144 width=106) (actual time=11,153.335..11,591.548 rows=1,218,960 loops=1)

  • Output: crv.contract_renew_version_id, i.claimant_company_id, col.contract_lumpsum_id, clf.date_range, cl.allowed_amount, cri.claim_product_contract_rebate_type, cl.event_date_range, i.create_date, ct.transaction_date, dc.deduction_match_date
  • Buffers: shared hit=3324033 read=17, local hit=8851
4. 40.984 11,208.403 ↓ 8,465.0 8,465 1

Hash Right Join (cost=5,870.43..10,757.48 rows=1 width=92) (actual time=11,153.324..11,208.403 rows=8,465 loops=1)

  • Output: crv.contract_renew_version_id, dc.deduction_match_date, cl.allowed_amount, cl.event_date_range, col.contract_lumpsum_id, cri.claim_product_contract_rebate_type, i.claimant_company_id, i.create_date, ct.transaction_date
  • Hash Cond: (ct.claim_id = c.claim_id)
  • Filter: (ct.void_date IS NULL)
  • Buffers: shared hit=3324033 read=17, local hit=386
5. 14.440 14.440 ↑ 1.0 117,603 1

Seq Scan on client_stonyfield.claim_transaction ct (cost=0.00..4,446.03 rows=117,603 width=32) (actual time=0.009..14.440 rows=117,603 loops=1)

  • Output: ct.claim_transaction_id, ct.claim_id, ct.transaction_type, ct.payment_transaction_state, ct.payment_transaction_type, ct.transaction_date, ct.transaction_amount, ct.transaction_number, ct.cleared_date, ct.payee_name, ct.payee_address_1, ct.payee_address_2, ct.payee_address_3, ct.payee_address_4, ct.payee_city, ct.payee_region, ct.payee_postal_code, ct.payee_country, ct.void_date, ct.contract_sequence, ct.invoice_id, ct.invoice_sequence, ct.claimant_company_id, ct.invoice_number, ct.invoice_date, ct.start_date, ct.end_date, ct.payee_number
  • Buffers: shared hit=3270
6. 3.607 11,152.979 ↓ 8,465.0 8,465 1

Hash (cost=5,870.42..5,870.42 rows=1 width=100) (actual time=11,152.979..11,152.979 rows=8,465 loops=1)

  • Output: crv.contract_renew_version_id, c.claim_id, dc.deduction_match_date, cl.allowed_amount, cl.event_date_range, col.contract_lumpsum_id, cri.claim_product_contract_rebate_type, i.claimant_company_id, i.create_date
  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1175kB
  • Buffers: shared hit=3320763 read=17, local hit=386
7. 3.965 11,149.372 ↓ 8,465.0 8,465 1

Nested Loop (cost=4,695.58..5,870.42 rows=1 width=100) (actual time=11,099.228..11,149.372 rows=8,465 loops=1)

  • Output: crv.contract_renew_version_id, c.claim_id, dc.deduction_match_date, cl.allowed_amount, cl.event_date_range, col.contract_lumpsum_id, cri.claim_product_contract_rebate_type, i.claimant_company_id, i.create_date
  • Inner Unique: true
  • Buffers: shared hit=3320763 read=17, local hit=386
8. 14.211 11,120.012 ↓ 8,465.0 8,465 1

Hash Join (cost=4,695.16..5,869.92 rows=1 width=92) (actual time=11,099.207..11,120.012 rows=8,465 loops=1)

  • Output: crv.contract_renew_version_id, c.invoice_id, c.claim_id, dc.deduction_match_date, cl.allowed_amount, cl.event_date_range, col.contract_lumpsum_id, cri.claim_product_contract_rebate_type
  • Hash Cond: (cri.claim_id = cl.claim_id)
  • Buffers: shared hit=3286903 read=17, local hit=386
9. 6.622 6.622 ↑ 1.0 58,382 1

Seq Scan on pg_temp_45.t_claim_rebate_info cri (cost=0.00..955.82 rows=58,382 width=20) (actual time=0.015..6.622 rows=58,382 loops=1)

  • Output: cri.claim_id, cri.claim_product_contract_rebate_type
  • Buffers: local hit=372
10. 7.088 11,099.179 ↓ 8,465.0 8,465 1

Hash (cost=4,695.15..4,695.15 rows=1 width=104) (actual time=11,099.179..11,099.179 rows=8,465 loops=1)

  • Output: crv.contract_renew_version_id, c.invoice_id, c.claim_id, dc.deduction_match_date, cl.allowed_amount, cl.event_date_range, cl.claim_id, col.contract_lumpsum_id
  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1094kB
  • Buffers: shared hit=3286903 read=17, local hit=14
11. 9.646 11,092.091 ↓ 8,465.0 8,465 1

Nested Loop Left Join (cost=2,135.10..4,695.15 rows=1 width=104) (actual time=29.393..11,092.091 rows=8,465 loops=1)

  • Output: crv.contract_renew_version_id, c.invoice_id, c.claim_id, dc.deduction_match_date, cl.allowed_amount, cl.event_date_range, cl.claim_id, col.contract_lumpsum_id
  • Inner Unique: true
  • Buffers: shared hit=3286903 read=17, local hit=14
12. 7.133 11,082.445 ↓ 8,465.0 8,465 1

Nested Loop Left Join (cost=2,134.81..4,694.83 rows=1 width=104) (actual time=29.383..11,082.445 rows=8,465 loops=1)

  • Output: crv.contract_renew_version_id, c.invoice_id, c.claim_id, dc.deduction_match_date, cl.allowed_amount, cl.event_date_range, cl.claim_id, cl.contract_lumpsum_id
  • Buffers: shared hit=3286875 read=4, local hit=14
13. 12.331 10,990.662 ↓ 8,465.0 8,465 1

Nested Loop Left Join (cost=2,134.54..4,694.52 rows=1 width=120) (actual time=29.350..10,990.662 rows=8,465 loops=1)

  • Output: crv.contract_renew_version_id, c.invoice_id, c.claim_id, dc.deduction_match_date, cl.allowed_amount, cl.event_date_range, cl.claim_id, cl.claim_lumpsum_id, cl.contract_lumpsum_id
  • Buffers: shared hit=3261584 read=4, local hit=14
14. 31.163 66.946 ↓ 8,465.0 8,465 1

Hash Join (cost=2,134.13..4,327.31 rows=1 width=120) (actual time=27.618..66.946 rows=8,465 loops=1)

  • Output: crv.contract_renew_version_id, c.invoice_id, c.claim_id, dc.deduction_match_date, cl.allowed_amount, cl.event_date_range, cl.claim_id, cl.claim_lumpsum_id, cl.contract_lumpsum_id
  • Hash Cond: (cl.claim_id = c.claim_id)
  • Join Filter: (crv.date_range @> lower(cl.event_date_range))
  • Rows Removed by Join Filter: 2
  • Buffers: shared hit=19488 read=4, local hit=14
15. 8.199 8.199 ↑ 1.1 49,988 1

Seq Scan on client_stonyfield.claim_lumpsum cl (cost=0.00..1,969.08 rows=54,808 width=68) (actual time=0.004..8.199 rows=49,988 loops=1)

  • Output: cl.claim_lumpsum_id, cl.claim_id, cl.lumpsum_id, cl.requested_amount, cl.allowed_amount, cl.contract_lumpsum_id, cl.event_date_range, cl.claimant_contract_name, cl.hint_contract_lumpsum_description
  • Buffers: shared hit=1421
16. 2.171 27.584 ↓ 2.5 6,694 1

Hash (cost=2,100.49..2,100.49 rows=2,691 width=66) (actual time=27.584..27.584 rows=6,694 loops=1)

  • Output: crv.contract_renew_version_id, crv.date_range, c.invoice_id, c.claim_id, dc.deduction_match_date
  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 679kB
  • Buffers: shared hit=18067 read=4, local hit=14
17. 2.477 25.413 ↓ 2.5 6,694 1

Hash Left Join (cost=81.71..2,100.49 rows=2,691 width=66) (actual time=1.210..25.413 rows=6,694 loops=1)

  • Output: crv.contract_renew_version_id, crv.date_range, c.invoice_id, c.claim_id, dc.deduction_match_date
  • Inner Unique: true
  • Hash Cond: (c.claim_id = dc.claim_id)
  • Buffers: shared hit=18067 read=4, local hit=14
18. 2.710 22.506 ↓ 2.5 6,694 1

Nested Loop (cost=71.43..2,083.14 rows=2,691 width=62) (actual time=0.775..22.506 rows=6,694 loops=1)

  • Output: crv.contract_renew_version_id, crv.date_range, c.invoice_id, c.claim_id
  • Buffers: shared hit=18060, local hit=14
19. 1.718 12.012 ↓ 1.8 1,946 1

Nested Loop (cost=71.01..1,172.25 rows=1,061 width=34) (actual time=0.764..12.012 rows=1,946 loops=1)

  • Output: crv.contract_renew_version_id, crv.date_range, cf.contract_sequence
  • Inner Unique: true
  • Buffers: shared hit=6213, local hit=14
20. 2.067 6.402 ↓ 1.8 1,946 1

Hash Join (cost=70.72..723.04 rows=1,061 width=46) (actual time=0.753..6.402 rows=1,946 loops=1)

  • Output: crv.contract_renew_version_id, crv.contract_family_id, crv.date_range
  • Hash Cond: (crv.contract_renew_version_id = tcrv.contract_renew_version_id)
  • Buffers: shared hit=375, local hit=14
21. 3.633 3.633 ↓ 1.0 9,736 1

Seq Scan on client_stonyfield.contract_renew_version crv (cost=0.00..605.36 rows=9,693 width=46) (actual time=0.006..3.633 rows=9,736 loops=1)

  • Output: crv.contract_renew_version_id, crv.contract_family_id, crv.date_range, crv.create_timestamp, crv.dead
  • Filter: (NOT crv.dead)
  • Rows Removed by Filter: 13344
  • Buffers: shared hit=375
22. 0.403 0.702 ↑ 1.0 2,521 1

Hash (cost=39.21..39.21 rows=2,521 width=16) (actual time=0.702..0.702 rows=2,521 loops=1)

  • Output: tcrv.contract_renew_version_id
  • Buckets: 4096 Batches: 1 Memory Usage: 151kB
  • Buffers: local hit=14
23. 0.299 0.299 ↑ 1.0 2,521 1

Seq Scan on pg_temp_45.tmp_contract_renew_version tcrv (cost=0.00..39.21 rows=2,521 width=16) (actual time=0.007..0.299 rows=2,521 loops=1)

  • Output: tcrv.contract_renew_version_id
  • Buffers: local hit=14
24. 3.892 3.892 ↑ 1.0 1 1,946

Index Scan using contract_family_pkey on client_stonyfield.contract_family cf (cost=0.29..0.42 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=1,946)

  • Output: cf.contract_family_id, cf.client_id, cf.contractee_company_id, cf.contractee_grouping_id, cf.creator_id, cf.contract_sequence, cf.cost_basis_type, cf.contract_type, cf.contract_title, cf.contract_template_id, cf.payment_calendar_id, cf.catch_all, cf.accrual_quantity_source, cf.import_contract_number
  • Index Cond: (cf.contract_family_id = crv.contract_family_id)
  • Buffers: shared hit=5838
25. 7.784 7.784 ↑ 2.0 3 1,946

Index Scan using idx_claim_contract_sequence on client_stonyfield.claim c (cost=0.42..0.80 rows=6 width=36) (actual time=0.002..0.004 rows=3 loops=1,946)

  • Output: c.claim_id, c.invoice_id, c.claim_state, c.claim_sequence, c.transaction_type, c.contract_sequence, c.claim_tag
  • Index Cond: (c.contract_sequence = cf.contract_sequence)
  • Buffers: shared hit=11847
26. 0.048 0.430 ↓ 3.0 236 1

Hash (cost=9.31..9.31 rows=78 width=20) (actual time=0.429..0.430 rows=236 loops=1)

  • Output: dc.deduction_match_date, dc.claim_id
  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
  • Buffers: shared hit=7 read=4
27. 0.038 0.382 ↓ 3.0 236 1

Subquery Scan on dc (cost=7.55..9.31 rows=78 width=20) (actual time=0.270..0.382 rows=236 loops=1)

  • Output: dc.deduction_match_date, dc.claim_id
  • Buffers: shared hit=7 read=4
28. 0.242 0.344 ↓ 3.0 236 1

HashAggregate (cost=7.55..8.53 rows=78 width=20) (actual time=0.270..0.344 rows=236 loops=1)

  • Output: dc_1.claim_id, (min(lower(dc_1.active_range)))::date
  • Group Key: dc_1.claim_id
  • Buffers: shared hit=7 read=4
29. 0.102 0.102 ↓ 3.2 251 1

Seq Scan on client_stonyfield.deduction_claim dc_1 (cost=0.00..6.96 rows=79 width=30) (actual time=0.019..0.102 rows=251 loops=1)

  • Output: dc_1.deduction_id, dc_1.claim_id, dc_1.deduction_amount_allotted, dc_1.active_range, dc_1.inoperative, dc_1.deduction_claim_id, dc_1.match_account_id, dc_1.unmatch_account_id
  • Filter: upper_inf(dc_1.active_range)
  • Rows Removed by Filter: 1
  • Buffers: shared read=4
30. 10,911.385 10,911.385 ↑ 1.0 1 8,465

Index Only Scan using claim_transaction_lumpsum_pkey on client_stonyfield.claim_transaction_lumpsum ctl (cost=0.41..367.21 rows=1 width=16) (actual time=0.640..1.289 rows=1 loops=8,465)

  • Output: ctl.claim_transaction_id, ctl.claim_lumpsum_id
  • Index Cond: (ctl.claim_lumpsum_id = cl.claim_lumpsum_id)
  • Heap Fetches: 0
  • Buffers: shared hit=3242096
31. 84.650 84.650 ↓ 0.0 0 8,465

Index Only Scan using claim_lumpsum_reason_code_excl on client_stonyfield.claim_lumpsum_reason_code clrc (cost=0.27..0.29 rows=1 width=16) (actual time=0.010..0.010 rows=0 loops=8,465)

  • Output: clrc.claim_lumpsum_id, clrc.claim_reason_type
  • Index Cond: (clrc.claim_lumpsum_id = cl.claim_lumpsum_id)
  • Heap Fetches: 6
  • Buffers: shared hit=25291
32. 0.000 0.000 ↓ 0.0 0 8,465

Index Only Scan using contract_lumpsum_pkey on client_stonyfield.contract_lumpsum col (cost=0.29..0.32 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=8,465)

  • Output: col.contract_lumpsum_id
  • Index Cond: (col.contract_lumpsum_id = cl.contract_lumpsum_id)
  • Heap Fetches: 0
  • Buffers: shared hit=28 read=13
33. 25.395 25.395 ↑ 1.0 1 8,465

Index Scan using invoice_pkey on client_stonyfield.invoice i (cost=0.42..0.50 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=8,465)

  • Output: i.invoice_id, i.client_id, i.claimant_company_id, i.invoice_number, i.invoice_date, i.start_date, i.end_date, i.amount_requested, i.creator_id, i.invoice_sequence, i.hint_deduction_number, i.create_date
  • Index Cond: (i.invoice_id = c.invoice_id)
  • Buffers: shared hit=33860
34. 135.440 135.440 ↑ 1.0 144 8,465

Seq Scan on pg_temp_45.tmp_client_fiscal clf (cost=0.00..2.44 rows=144 width=14) (actual time=0.003..0.016 rows=144 loops=8,465)

  • Output: clf.fiscal_month, clf.fiscal_year, clf.date_range
  • Buffers: local hit=8465