explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tbVe

Settings
# exclusive inclusive rows x rows loops node
1. 737.350 817,614.892 ↑ 3.9 5,383,810 1

Append (cost=84,360,537.5..87,478,116.16 rows=21,039,886 width=1,268) (actual time=788,310.132..817,614.892 rows=5,383,810 loops=1)

  • Buffers: shared hit=103692975 read=953165 dirtied=1 written=937, temp read=3503669 written=3503728
2. 6,739.259 805,249.185 ↑ 3.9 5,360,208 1

Unique (cost=84,360,537.5..86,724,869.56 rows=21,016,285 width=1,135) (actual time=788,310.131..805,249.185 rows=5,360,208 loops=1)

  • Buffers: shared hit=99593329 read=909030 written=812, temp read=3499871 written=3499927
3. 89,127.070 798,509.926 ↑ 3.8 5,464,665 1

Sort (cost=84,360,537.5..84,413,078.21 rows=21,016,285 width=1,135) (actual time=788,310.127..798,509.926 rows=5,464,665 loops=1)

  • Sort Key: (NULLIF(btrim((cx.claim_product_id)::text), 'Unknown'::text)), i.client_id, (NULLIF(btrim((i.claimant_company_id)::text), 'Unknown'::text)), (NULLIF(btrim((cx.product_id)::text), 'Unknown'::text)), (NULLIF(btrim(((conx.contract_id)::text || (conx.product_id)::text)), 'Unknown'::text)), (NULLIF(btrim((cx.uom_type_id)::text), 'Unknown'::text)), (NULLIF(btrim((c.claim_sequence)::text), 'Unknown'::text)), (NULLIF(btrim((c.claim_state)::text), 'Unknown'::text)), cx.rebate_requested_rate, cx.rebate_requested_quantity, ((cx.rebate_requested_quantity * cx.rebate_requested_rate)), cx.rebate_allowed_rate, cx.rebate_allowed_quantity, ((cx.rebate_allowed_quantity * cx.rebate_allowed_rate)), cx.rebate_calculated_rate, cx.resolve_date, ct.transaction_date, ct.cleared_date, ct_void.void_date, ("left"(COALESCE(NULLIF(btrim(c.claim_tag), ''::text), 'Unknown'::text), 250)), ("left"(COALESCE(NULLIF(btrim((cpri.claim_product_contract_rebate_type)::text), ''::text), 'Unknown'::text), 250)), ("left"(COALESCE(NULLIF(btrim((cpri.claim_product_contract_eligibility_type)::text), ''::text), 'Unknown'::text), 250)), (NULLIF(btrim((claim_product_reason_code.claim_reason_type)::text), 'Unknown'::text)), (NULLIF(btrim(((claim_product_reason_code.claim_reason_code)::text)), 'Unknown'::text)), cshpend.record_timestamp, cshcomp.record_timestamp, (NULLIF(btrim((c.invoice_id)::text), 'Unknown'::text)), (NULLIF(((CASE WHEN (pm.math_operation_value IS NULL) THEN price_default_1.price WHEN (pm.math_operation IS NULL) THEN price_default_1.price WHEN (pm.math_operation = 'ADD'::enum.math_operation) THEN (price_default_1.price + pm.math_operation_value) WHEN (pm.math_operation = 'SUBTRACT'::enum.math_operation) THEN (price_default_1.price - pm.math_operation_value) WHEN (pm.math_operation = 'MULTIPLY'::enum.math_operation) THEN (price_default_1.price * pm.math_operation_value) ELSE price_default_1.price END / (tuomc.rate / suomc.rate))), '0'::numeric)), (NULLIF(btrim((CASE WHEN (price_default.price_id IS NOT NULL) THEN 'PRICE_DEFAULT'::text WHEN (price_list.price_id IS NOT NULL) THEN 'PRICE_LIST'::text WHEN (price_company.price_id IS NOT NULL) THEN 'PRICE_COMPANY'::text WHEN (price_sale.price_id IS NOT NULL) THEN 'PRICE_SALE'::text WHEN (price_class.price_id IS NOT NULL) THEN 'PRICE_CLASS'::text ELSE NULL::text END)), 'Unknown'::text)), (NULLIF(btrim((cx.claim_id)::text), 'Unknown'::text)), (NULLIF(btrim((cx.distributor_company_id)::text), 'Unknown'::text)), (NULLIF(btrim((c.contract_sequence)::text), 'Unknown'::text)), ((contract_family.contract_family_id)::text), ((contract_renew_version.contract_renew_version_id)::text), ((COALESCE(conx.contract_id, ccb.contract_id))::text), import_file.received_date, ct.transaction_amount, ("left"(COALESCE(NULLIF(btrim(((account_foreign.first_name || ' '::text) || account_foreign.last_name)), ''::text), 'Unknown'::text), 250)), (CASE WHEN (ii.invoice_id IS NULL) THEN 'MANUAL'::text ELSE 'ECLAIM'::text END), cshsubmitted.record_timestamp, ("left"(COALESCE(NULLIF(btrim((claim_transaction.transaction_type)::text), ''::text), 'Unknown'::text), 250)), (CASE WHEN ((claim_amounts_materialized.product_count > 0) AND (claim_amounts_materialized.lumpsum_count = 0)) THEN 'product'::text WHEN ((claim_amounts_materialized.product_count > 0) AND (claim_amounts_materialized.lumpsum_count > 0)) THEN 'product and lumpsum'::text WHEN ((claim_amounts_materialized.product_count = 0) AND (claim_amounts_materialized.lumpsum_count > 0)) THEN 'lumpsum'::text WHEN (claim_amounts_materialized.adhoc_product_count > 0) THEN 'product'::text ELSE NULL::text END), ("left"(COALESCE(NULLIF(btrim((cx.claimant_contract_name)::text), ''::text), 'Unknown'::text), 250)), ("left"(COALESCE(NULLIF(btrim(((claim_reason_code.claim_reason_code)::text)), ''::text), 'Unknown'::text), 250))
  • Sort Method: external merge Disk: 3895928kB
  • Buffers: shared hit=99593329 read=909030 written=812, temp read=3499871 written=3499927
4. 96,839.712 709,382.856 ↑ 3.8 5,464,665 1

Nested Loop (cost=3,003,766.16..61,716,843.13 rows=21,016,285 width=1,135) (actual time=232,894.819..709,382.856 rows=5,464,665 loops=1)

  • Buffers: shared hit=99593317 read=909030 written=812, temp read=1944826 written=1944169
5. 4,577.318 508,714.509 ↑ 3.8 5,464,665 1

Hash Join (cost=3,003,764.34..17,845,348.19 rows=21,016,285 width=609) (actual time=232,893.95..508,714.509 rows=5,464,665 loops=1)

  • Buffers: shared hit=83329898 read=908213 written=812, temp read=1944826 written=1944169
6. 31,008.026 504,124.306 ↓ 1.0 5,464,665 1

Hash Join (cost=3,003,618.31..17,594,942.45 rows=5,346,248 width=561) (actual time=232,881.05..504,124.306 rows=5,464,665 loops=1)

  • Buffers: shared hit=83329898 read=908211 written=812, temp read=1944826 written=1944169
7. 6,634.456 455,554.986 ↓ 1.0 5,464,665 1

Hash Join (cost=2,850,450.51..16,696,656.73 rows=5,346,248 width=497) (actual time=213,683.121..455,554.986 rows=5,464,665 loops=1)

  • Buffers: shared hit=78200727 read=862895 written=649, temp read=1618501 written=1617875
8. 4,092.999 448,687.062 ↓ 1.0 5,464,665 1

Nested Loop (cost=2,822,761.73..16,654,933.8 rows=5,346,248 width=489) (actual time=213,449.199..448,687.062 rows=5,464,665 loops=1)

  • Buffers: shared hit=78188604 read=862895 written=649, temp read=1618501 written=1617875
9. 12,926.913 313,442.103 ↓ 1.0 5,464,665 1

Hash Join (cost=2,822,759.76..5,989,169.04 rows=5,346,248 width=453) (actual time=213,448.113..313,442.103 rows=5,464,665 loops=1)

  • Buffers: shared hit=55930576 read=861848 written=648, temp read=1618501 written=1617875
10. 12,128.686 299,371.183 ↓ 1.0 5,464,665 1

Hash Join (cost=2,767,929.76..5,303,203.9 rows=5,346,248 width=445) (actual time=212,303.368..299,371.183 rows=5,464,665 loops=1)

  • Buffers: shared hit=55111753 read=861848 written=648, temp read=1479564 written=1478940
11. 6,761.047 284,992.789 ↓ 1.0 5,464,665 1

Nested Loop (cost=2,714,382.54..4,629,106.53 rows=5,346,248 width=437) (actual time=210,052.101..284,992.789 rows=5,464,665 loops=1)

  • Buffers: shared hit=54311402 read=843234 written=437, temp read=1343653 written=1343031
12. 4,542.045 267,302.412 ↓ 1.0 5,464,665 1

Hash Join (cost=2,714,382.54..4,094,481.73 rows=5,346,248 width=441) (actual time=210,031.99..267,302.412 rows=5,464,665 loops=1)

  • Buffers: shared hit=54309418 read=843094 written=437, temp read=1343653 written=1343031
13. 5,208.616 262,760.183 ↓ 1.0 5,464,665 1

Hash Join (cost=2,714,381.47..4,060,621.09 rows=5,346,248 width=453) (actual time=210,031.777..262,760.183 rows=5,464,665 loops=1)

  • Buffers: shared hit=54309418 read=843093 written=437, temp read=1343653 written=1343031
14. 5,526.052 257,517.830 ↓ 1.0 5,464,665 1

Hash Join (cost=2,712,574.72..4,044,778.92 rows=5,346,248 width=437) (actual time=209,997.613..257,517.83 rows=5,464,665 loops=1)

  • Buffers: shared hit=54309418 read=842170 written=437, temp read=1343653 written=1343031
15. 13,097.536 251,861.048 ↓ 1.0 5,464,665 1

Hash Join (cost=2,705,881.71..4,002,993.28 rows=5,346,248 width=429) (actual time=209,866.521..251,861.048 rows=5,464,665 loops=1)

  • Buffers: shared hit=54309418 read=838040 written=437, temp read=1343653 written=1343031
16. 4,174.114 238,573.806 ↓ 1.0 5,464,665 1

Hash Join (cost=2,697,038.77..3,446,006.11 rows=5,346,248 width=381) (actual time=209,672.954..238,573.806 rows=5,464,665 loops=1)

  • Buffers: shared hit=54309418 read=834792 written=437, temp read=1226417 written=1225797
17. 14,916.901 234,334.500 ↓ 1.0 5,464,665 1

Hash Join (cost=2,688,158.77..3,423,091.97 rows=5,346,248 width=373) (actual time=209,607.713..234,334.5 rows=5,464,665 loops=1)

  • Buffers: shared hit=54302907 read=834773 written=437, temp read=1226417 written=1225797
18. 9,276.080 218,313.972 ↓ 1.0 5,464,665 1

Hash Join (cost=2,661,001.31..2,920,066.36 rows=5,346,248 width=325) (actual time=208,498.745..218,313.972 rows=5,464,665 loops=1)

  • Buffers: shared hit=54087081 read=826814 written=437, temp read=1052676 written=1052062
19. 602.511 3,332.563 ↓ 4.8 248,996 1

Bitmap Heap Scan on claim_product_reason_code cprc_override (cost=1,717.48..39,809.75 rows=52,201 width=32) (actual time=2,767.7..3,332.563 rows=248,996 loops=1)

  • Buffers: shared read=57750
20. 2,730.052 2,730.052 ↓ 4.8 248,996 1

Bitmap Index Scan on claim_product_reason_code_active_range_excl (cost=0..1,704.42 rows=52,201 width=0) (actual time=2,730.052..2,730.052 rows=248,996 loops=1)

  • Index Cond: ((cprc_override.claim_reason_type = 'OVERRIDE'::enum.claim_reason_type) AND (cprc_override.active_range @> now()))
  • Buffers: shared read=32259
21. 6,831.952 205,705.329 ↓ 1.0 5,464,665 1

Hash (cost=2,373,175.74..2,373,175.74 rows=5,346,248 width=309) (actual time=205,705.329..205,705.329 rows=5,464,665 loops=1)

  • Buffers: shared hit=54087081 read=769064 written=437, temp read=825113 written=1050074
22. 3,651.204 198,873.377 ↓ 1.0 5,464,665 1

Hash Join (cost=1,246,615.56..2,373,175.74 rows=5,346,248 width=309) (actual time=130,747.181..198,873.377 rows=5,464,665 loops=1)

  • Buffers: shared hit=54087081 read=769064 written=437, temp read=825113 written=825009
23. 10,940.456 195,210.635 ↓ 1.0 5,360,208 1

Hash Join (cost=1,245,907.1..2,334,004.43 rows=5,346,248 width=309) (actual time=130,735.579..195,210.635 rows=5,360,208 loops=1)

  • Buffers: shared hit=54087081 read=768866 written=437, temp read=825113 written=825009
24. 3,814.713 183,999.749 ↓ 1.0 5,360,208 1

Hash Join (cost=1,235,723.99..1,880,028.17 rows=5,346,248 width=297) (actual time=130,464.885..183,999.749 rows=5,360,208 loops=1)

  • Buffers: shared hit=54087081 read=765724 written=437, temp read=697007 written=696909
25. 4,090.062 180,171.526 ↓ 1.0 5,360,208 1

Hash Join (cost=1,235,208..1,865,473.28 rows=5,346,248 width=297) (actual time=130,451.304..180,171.526 rows=5,360,208 loops=1)

  • Buffers: shared hit=54087081 read=765597 written=437, temp read=697007 written=696909
26. 16,256.094 176,062.076 ↓ 1.0 5,360,208 1

Hash Join (cost=1,233,948.33..1,850,176.54 rows=5,346,248 width=281) (actual time=130,431.834..176,062.076 rows=5,360,208 loops=1)

  • Buffers: shared hit=54086757 read=765085 written=437, temp read=697007 written=696909
27. 15,887.062 129,494.968 ↓ 1.0 5,360,208 1

Hash Join (cost=655,113.6..1,257,307.9 rows=5,346,248 width=265) (actual time=100,120.78..129,494.968 rows=5,360,208 loops=1)

  • Buffers: shared hit=54085513 read=513271 written=437, temp read=399120 written=399081
28. 8,021.963 14,580.299 ↓ 1.0 5,360,208 1

Hash Join (cost=36,702.75..624,863.14 rows=5,346,248 width=233) (actual time=1,090.773..14,580.299 rows=5,360,208 loops=1)

  • Buffers: shared hit=122 read=259270, temp read=78947 written=78927
29. 5,468.599 5,468.599 ↓ 1.0 5,360,208 1

Seq Scan on claim_product cx (cost=0..302,057.48 rows=5,346,248 width=136) (actual time=0.188..5,468.599 rows=5,360,208 loops=1)

  • Buffers: shared read=248595
30. 140.143 1,089.737 ↑ 1.0 240,067 1

Hash (cost=29,945.8..29,945.8 rows=240,236 width=97) (actual time=1,089.737..1,089.737 rows=240,067 loops=1)

  • Buffers: shared hit=122 read=10675, temp read=2803 written=5787
31. 134.187 949.594 ↑ 1.0 240,067 1

Hash Join (cost=13,588.74..29,945.8 rows=240,236 width=97) (actual time=313.003..949.594 rows=240,067 loops=1)

  • Buffers: shared hit=122 read=10675, temp read=2803 written=2797
32. 116.229 771.215 ↑ 1.0 240,067 1

Hash Join (cost=11,564.18..26,984.43 rows=240,236 width=93) (actual time=268.777..771.215 rows=240,067 loops=1)

  • Buffers: shared hit=9 read=9745, temp read=2803 written=2797
33. 262.769 649.725 ↑ 1.0 240,067 1

Hash Join (cost=11,336.31..26,125.48 rows=240,236 width=73) (actual time=263.45..649.725 rows=240,067 loops=1)

  • Buffers: shared hit=6 read=9639, temp read=2803 written=2797
34. 124.696 124.696 ↑ 1.0 222,371 1

Seq Scan on claim_transaction claim_transaction (cost=0..8,781.15 rows=222,479 width=20) (actual time=0.136..124.696 rows=222,371 loops=1)

  • Filter: (claim_transaction.void_date IS NULL)
  • Buffers: shared read=6530
35. 149.444 262.260 ↑ 1.0 240,067 1

Hash (cost=5,517.36..5,517.36 rows=240,236 width=69) (actual time=262.26..262.26 rows=240,067 loops=1)

  • Buffers: shared hit=6 read=3109, temp written=1977
36. 112.816 112.816 ↑ 1.0 240,067 1

Seq Scan on claim c (cost=0..5,517.36 rows=240,236 width=69) (actual time=0.142..112.816 rows=240,067 loops=1)

  • Buffers: shared hit=6 read=3109
37. 2.133 5.261 ↓ 1.0 5,286 1

Hash (cost=161.83..161.83 rows=5,283 width=24) (actual time=5.261..5.261 rows=5,286 loops=1)

  • Buffers: shared hit=3 read=106
38. 3.128 3.128 ↓ 1.0 5,286 1

Seq Scan on contract_family contract_family (cost=0..161.83 rows=5,283 width=24) (actual time=0.146..3.128 rows=5,286 loops=1)

  • Buffers: shared hit=3 read=106
39. 1.939 44.192 ↓ 66.9 5,286 1

Hash (cost=2,023.57..2,023.57 rows=79 width=20) (actual time=44.192..44.192 rows=5,286 loops=1)

  • Buffers: shared hit=113 read=930
40. 2.587 42.253 ↓ 66.9 5,286 1

Hash Join (cost=1,819.85..2,023.57 rows=79 width=20) (actual time=33.436..42.253 rows=5,286 loops=1)

  • Buffers: shared hit=113 read=930
41. 1.566 39.650 ↓ 1.0 5,286 1

Hash Join (cost=1,818.78..2,008.35 rows=5,283 width=48) (actual time=33.396..39.65 rows=5,286 loops=1)

  • Buffers: shared hit=112 read=930
42. 4.269 37.653 ↓ 1.0 5,286 1

Hash Join (cost=1,806.76..1,982.45 rows=5,283 width=48) (actual time=32.929..37.653 rows=5,286 loops=1)

  • Buffers: shared hit=111 read=921
43. 0.933 0.933 ↓ 1.0 5,286 1

Seq Scan on contract_family c_1 (cost=0..161.83 rows=5,283 width=48) (actual time=0.003..0.933 rows=5,286 loops=1)

  • Buffers: shared hit=109
44. 14.735 32.451 ↓ 1.0 39,279 1

Hash (cost=1,315.78..1,315.78 rows=39,278 width=32) (actual time=32.451..32.451 rows=39,279 loops=1)

  • Buffers: shared hit=2 read=921
45. 17.716 17.716 ↓ 1.0 39,279 1

Seq Scan on company cmp (cost=0..1,315.78 rows=39,278 width=32) (actual time=0.147..17.716 rows=39,279 loops=1)

  • Buffers: shared hit=2 read=921
46. 0.047 0.431 ↑ 1.0 90 1

Hash (cost=10.9..10.9 rows=90 width=32) (actual time=0.431..0.431 rows=90 loops=1)

  • Buffers: shared hit=1 read=9
47. 0.384 0.384 ↑ 1.0 90 1

Seq Scan on grouping_company gc (cost=0..10.9 rows=90 width=32) (actual time=0.103..0.384 rows=90 loops=1)

  • Buffers: shared hit=1 read=9
48. 0.006 0.016 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=20) (actual time=0.016..0.016 rows=3 loops=1)

  • Buffers: shared hit=1
49. 0.010 0.010 ↑ 1.0 3 1

Seq Scan on company_type ct_1 (cost=0..1.03 rows=3 width=20) (actual time=0.008..0.01 rows=3 loops=1)

  • Buffers: shared hit=1
50. 2,118.567 99,027.607 ↓ 1,427.1 5,017,750 1

Hash (cost=618,366.9..618,366.9 rows=3,516 width=48) (actual time=99,027.607..99,027.607 rows=5,017,750 loops=1)

  • Buffers: shared hit=54085391 read=254001 written=437, temp read=92898 written=133973
51. 1,246.148 96,909.040 ↓ 1,427.1 5,017,750 1

Subquery Scan on conx (cost=618,314.16..618,366.9 rows=3,516 width=48) (actual time=91,924.018..96,909.04 rows=5,017,750 loops=1)

  • Buffers: shared hit=54085391 read=254001 written=437, temp read=92898 written=93005
52. 1,352.793 95,662.892 ↓ 1,427.1 5,017,750 1

Unique (cost=618,314.16..618,331.74 rows=3,516 width=49) (actual time=91,924.015..95,662.892 rows=5,017,750 loops=1)

  • Buffers: shared hit=54085391 read=254001 written=437, temp read=92898 written=93005
53. 10,535.478 94,310.099 ↓ 1,504.6 5,290,190 1

Sort (cost=618,314.16..618,322.95 rows=3,516 width=49) (actual time=91,924.012..94,310.099 rows=5,290,190 loops=1)

  • Sort Key: clp.claim_product_id, ((cav.contract_state = 'TERMINATED'::enum.contract_state))
  • Sort Method: external merge Disk: 305488kB
  • Buffers: shared hit=54085391 read=254001 written=437, temp read=92898 written=93005
54. 6,201.079 83,774.621 ↓ 1,504.6 5,290,190 1

Nested Loop (cost=10,858.39..618,107.07 rows=3,516 width=49) (actual time=157.761..83,774.621 rows=5,290,190 loops=1)

  • Buffers: shared hit=54085386 read=254001 written=437, temp read=19151 written=19149
55. 2,606.374 61,284.865 ↓ 492.4 5,429,559 1

Nested Loop (cost=10,857.97..604,494.22 rows=11,027 width=52) (actual time=157.257..61,284.865 rows=5,429,559 loops=1)

  • Buffers: shared hit=37792677 read=248840 written=16, temp read=19151 written=19149
56. 11,924.728 26,090.079 ↓ 486.0 5,431,402 1

Hash Join (cost=10,857.68..599,210.18 rows=11,176 width=48) (actual time=156.408..26,090.079 rows=5,431,402 loops=1)

  • Buffers: shared hit=3634 read=248591 written=16, temp read=19151 written=19149
57. 4,946.806 14,146.048 ↓ 1.0 5,360,208 1

Hash Join (cost=9,928.31..431,847.94 rows=5,346,248 width=40) (actual time=136.86..14,146.048 rows=5,360,208 loops=1)

  • Buffers: shared hit=3119 read=248591 written=16, temp read=19151 written=19149
58. 9,063.175 9,063.175 ↓ 1.0 5,360,208 1

Seq Scan on claim_product clp (cost=0..302,057.48 rows=5,346,248 width=52) (actual time=0.012..9,063.175 rows=5,360,208 loops=1)

  • Buffers: shared hit=4 read=248591 written=16
59. 75.645 136.067 ↑ 1.0 240,067 1

Hash (cost=5,517.36..5,517.36 rows=240,236 width=20) (actual time=136.067..136.067 rows=240,067 loops=1)

  • Buffers: shared hit=3115, temp written=582
60. 60.422 60.422 ↑ 1.0 240,067 1

Seq Scan on claim cl (cost=0..5,517.36 rows=240,236 width=20) (actual time=0.01..60.422 rows=240,067 loops=1)

  • Buffers: shared hit=3115
61. 4.851 19.303 ↓ 1.0 11,886 1

Hash (cost=780.87..780.87 rows=11,880 width=34) (actual time=19.303..19.303 rows=11,886 loops=1)

  • Buffers: shared hit=512
62. 7.884 14.452 ↓ 1.0 11,886 1

Hash Join (cost=227.87..780.87 rows=11,880 width=34) (actual time=4.438..14.452 rows=11,886 loops=1)

  • Buffers: shared hit=512
63. 2.213 2.213 ↓ 1.0 11,886 1

Seq Scan on contract_renew_version crv (cost=0..521.8 rows=11,880 width=46) (actual time=0.014..2.213 rows=11,886 loops=1)

  • Buffers: shared hit=403
64. 2.139 4.355 ↓ 1.0 5,286 1

Hash (cost=161.83..161.83 rows=5,283 width=20) (actual time=4.355..4.355 rows=5,286 loops=1)

  • Buffers: shared hit=109
65. 2.216 2.216 ↓ 1.0 5,286 1

Seq Scan on contract_family cf (cost=0..161.83 rows=5,283 width=20) (actual time=0.01..2.216 rows=5,286 loops=1)

  • Buffers: shared hit=109
66. 32,588.412 32,588.412 ↑ 1.0 1 5,431,402

Index Scan using contract_amend_version_contract_renew_version_id_idx1 on contract_amend_version cav (cost=0.29..0.46 rows=1 width=36) (actual time=0.004..0.006 rows=1 loops=5,431,402)

  • Index Cond: (cav.contract_renew_version_id = crv.contract_renew_version_id)
  • Filter: (cav.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED,TERMINATED}'::enum.contract_state[]))
  • Buffers: shared hit=37789043 read=249
67. 16,288.677 16,288.677 ↑ 1.0 1 5,429,559

Index Only Scan using contract_product_materialized_pkey on contract_product_materialized contract_product_materialized (cost=0.42..1.23 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=5,429,559)

  • Index Cond: ((contract_product_materialized.contract_id = cav.contract_amend_version_id) AND (contract_product_materialized.product_id = clp.product_id))
  • Buffers: shared hit=16292709 read=5161 written=421
68. 2,082.674 30,311.014 ↓ 467.3 5,152,835 1

Hash (cost=578,696.9..578,696.9 rows=11,027 width=32) (actual time=30,311.014..30,311.014 rows=5,152,835 loops=1)

  • Buffers: shared hit=1244 read=251814, temp read=60563 written=92798
69. 1,271.870 28,228.340 ↓ 467.3 5,152,835 1

Subquery Scan on ccb (cost=578,531.49..578,696.9 rows=11,027 width=32) (actual time=23,431.941..28,228.34 rows=5,152,835 loops=1)

  • Buffers: shared hit=1244 read=251814, temp read=60563 written=60630
70. 1,379.580 26,956.470 ↓ 467.3 5,152,835 1

Unique (cost=578,531.49..578,586.63 rows=11,027 width=33) (actual time=23,431.94..26,956.47 rows=5,152,835 loops=1)

  • Buffers: shared hit=1244 read=251814, temp read=60563 written=60630
71. 8,018.227 25,576.890 ↓ 492.4 5,429,559 1

Sort (cost=578,531.49..578,559.06 rows=11,027 width=33) (actual time=23,431.938..25,576.89 rows=5,429,559 loops=1)

  • Sort Key: clp_1.claim_product_id, ((cav_1.contract_state = 'TERMINATED'::enum.contract_state))
  • Sort Method: external merge Disk: 228464kB
  • Buffers: shared hit=1244 read=251814, temp read=60563 written=60630
72. 10,768.696 17,558.663 ↓ 492.4 5,429,559 1

Hash Join (cost=12,153.03..577,791.1 rows=11,027 width=33) (actual time=225.893..17,558.663 rows=5,429,559 loops=1)

  • Buffers: shared hit=1244 read=251814, temp read=15026 written=15024
73. 3,175.768 6,755.956 ↓ 1.0 5,360,208 1

Hash Join (cost=9,928.31..410,963.94 rows=5,346,248 width=24) (actual time=191.639..6,755.956 rows=5,360,208 loops=1)

  • Buffers: shared hit=5 read=251705, temp read=15026 written=15024
74. 3,388.963 3,388.963 ↓ 1.0 5,360,208 1

Seq Scan on claim_product clp_1 (cost=0..302,057.48 rows=5,346,248 width=36) (actual time=0.182..3,388.963 rows=5,360,208 loops=1)

  • Buffers: shared hit=5 read=248590
75. 97.987 191.225 ↑ 1.0 240,067 1

Hash (cost=5,517.36..5,517.36 rows=240,236 width=20) (actual time=191.225..191.225 rows=240,067 loops=1)

  • Buffers: shared read=3115, temp written=582
76. 93.238 93.238 ↑ 1.0 240,067 1

Seq Scan on claim cl_1 (cost=0..5,517.36 rows=240,236 width=20) (actual time=0.226..93.238 rows=240,067 loops=1)

  • Buffers: shared read=3115
77. 4.532 34.011 ↓ 1.0 11,727 1

Hash (cost=2,078.19..2,078.19 rows=11,722 width=38) (actual time=34.011..34.011 rows=11,727 loops=1)

  • Buffers: shared hit=1239 read=109
78. 6.683 29.479 ↓ 1.0 11,727 1

Hash Join (cost=898.17..2,078.19 rows=11,722 width=38) (actual time=9.332..29.479 rows=11,727 loops=1)

  • Buffers: shared hit=1239 read=109
79. 6.632 19.169 ↓ 1.0 11,727 1

Hash Join (cost=670.3..1,819.53 rows=11,722 width=50) (actual time=5.686..19.169 rows=11,727 loops=1)

  • Buffers: shared hit=1239
80. 6.903 6.903 ↓ 1.0 11,727 1

Seq Scan on contract_amend_version cav_1 (cost=0..1,118.45 rows=11,722 width=36) (actual time=0.013..6.903 rows=11,727 loops=1)

  • Filter: (cav_1.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED,TERMINATED}'::enum.contract_state[]))
  • Buffers: shared hit=836
81. 2.865 5.634 ↓ 1.0 11,886 1

Hash (cost=521.8..521.8 rows=11,880 width=46) (actual time=5.634..5.634 rows=11,886 loops=1)

  • Buffers: shared hit=403
82. 2.769 2.769 ↓ 1.0 11,886 1

Seq Scan on contract_renew_version crv_1 (cost=0..521.8 rows=11,880 width=46) (actual time=0.007..2.769 rows=11,886 loops=1)

  • Buffers: shared hit=403
83. 1.378 3.627 ↓ 1.0 5,286 1

Hash (cost=161.83..161.83 rows=5,283 width=20) (actual time=3.627..3.627 rows=5,286 loops=1)

  • Buffers: shared read=109
84. 2.249 2.249 ↓ 1.0 5,286 1

Seq Scan on contract_family cf_1 (cost=0..161.83 rows=5,283 width=20) (actual time=0.114..2.249 rows=5,286 loops=1)

  • Buffers: shared read=109
85. 7.333 19.388 ↓ 1.0 18,839 1

Hash (cost=1,024.3..1,024.3 rows=18,830 width=32) (actual time=19.388..19.388 rows=18,839 loops=1)

  • Buffers: shared hit=324 read=512
86. 12.055 12.055 ↓ 1.0 18,839 1

Seq Scan on contract_amend_version contract_amend_version (cost=0..1,024.3 rows=18,830 width=32) (actual time=0.022..12.055 rows=18,839 loops=1)

  • Buffers: shared hit=324 read=512
87. 3.302 13.510 ↓ 1.0 11,886 1

Hash (cost=367.49..367.49 rows=11,880 width=16) (actual time=13.51..13.51 rows=11,886 loops=1)

  • Buffers: shared read=127
88. 10.208 10.208 ↓ 1.0 11,886 1

Index Only Scan using contract_version_pkey on contract_renew_version contract_renew_version (cost=0.29..367.49 rows=11,880 width=16) (actual time=0.651..10.208 rows=11,886 loops=1)

  • Buffers: shared read=127
89. 94.030 270.430 ↓ 1.0 240,067 1

Hash (cost=5,542.05..5,542.05 rows=240,005 width=28) (actual time=270.43..270.43 rows=240,067 loops=1)

  • Buffers: shared read=3142, temp written=1052
90. 176.400 176.400 ↓ 1.0 240,067 1

Seq Scan on claim_amounts_materialized claim_amounts_materialized (cost=0..5,542.05 rows=240,005 width=28) (actual time=0.312..176.4 rows=240,067 loops=1)

  • Buffers: shared read=3142
91. 5.683 11.538 ↑ 1.0 22,634 1

Hash (cost=424.87..424.87 rows=22,687 width=16) (actual time=11.538..11.538 rows=22,634 loops=1)

  • Buffers: shared read=198
92. 5.855 5.855 ↑ 1.0 22,634 1

Seq Scan on claim_pend_reason_tolerance_passed_check_log cprtpcl (cost=0..424.87 rows=22,687 width=16) (actual time=0.357..5.855 rows=22,634 loops=1)

  • Buffers: shared read=198
93. 91.995 1,103.627 ↑ 1.0 222,371 1

Hash (cost=21,986.47..21,986.47 rows=222,479 width=64) (actual time=1,103.627..1,103.627 rows=222,371 loops=1)

  • Buffers: shared hit=215826 read=7959, temp written=1045
94. 47.492 1,011.632 ↑ 1.0 222,371 1

Subquery Scan on ct (cost=0.42..21,986.47 rows=222,479 width=64) (actual time=0.916..1,011.632 rows=222,371 loops=1)

  • Buffers: shared hit=215826 read=7959
95. 215.962 964.140 ↑ 1.0 222,371 1

Aggregate (cost=0.42..19,761.68 rows=222,479 width=64) (actual time=0.914..964.14 rows=222,371 loops=1)

  • Buffers: shared hit=215826 read=7959
96. 748.178 748.178 ↑ 1.0 222,371 1

Index Scan using claim_transaction_claim_id_idx on claim_transaction claim_transaction_1 (cost=0.42..15,312.1 rows=222,479 width=39) (actual time=0.817..748.178 rows=222,371 loops=1)

  • Buffers: shared hit=215826 read=7959
97. 0.685 65.192 ↑ 1.0 2,575 1

Hash (cost=8,847.05..8,847.05 rows=2,636 width=24) (actual time=65.192..65.192 rows=2,575 loops=1)

  • Buffers: shared hit=6511 read=19
98. 0.459 64.507 ↑ 1.0 2,575 1

Subquery Scan on ct_void (cost=8,794.33..8,847.05 rows=2,636 width=24) (actual time=63.408..64.507 rows=2,575 loops=1)

  • Buffers: shared hit=6511 read=19
99. 2.330 64.048 ↑ 1.0 2,575 1

Aggregate (cost=8,794.33..8,820.69 rows=2,636 width=24) (actual time=63.406..64.048 rows=2,575 loops=1)

  • Buffers: shared hit=6511 read=19
100. 61.718 61.718 ↓ 1.0 2,697 1

Seq Scan on claim_transaction claim_transaction_2 (cost=0..8,781.15 rows=2,636 width=24) (actual time=0.066..61.718 rows=2,697 loops=1)

  • Filter: (claim_transaction_2.void_date IS NOT NULL)
  • Buffers: shared hit=6511 read=19
101. 94.237 189.706 ↓ 1.0 178,844 1

Hash (cost=5,035.97..5,035.97 rows=178,797 width=48) (actual time=189.706..189.706 rows=178,844 loops=1)

  • Buffers: shared read=3248, temp written=739
102. 95.469 95.469 ↓ 1.0 178,844 1

Seq Scan on invoice i (cost=0..5,035.97 rows=178,797 width=48) (actual time=0.406..95.469 rows=178,844 loops=1)

  • Buffers: shared read=3248
103. 19.834 130.730 ↑ 1.0 50,343 1

Hash (cost=6,059.35..6,059.35 rows=50,693 width=24) (actual time=130.73..130.73 rows=50,343 loops=1)

  • Buffers: shared read=4130
104. 24.785 110.896 ↓ 1.0 50,727 1

Hash Join (cost=3,864.34..6,059.35 rows=50,693 width=24) (actual time=54.793..110.896 rows=50,727 loops=1)

  • Buffers: shared read=4130
105. 31.927 31.927 ↓ 1.0 50,727 1

Seq Scan on import_invoice ii (cost=0..2,061.93 rows=50,693 width=32) (actual time=0.266..31.927 rows=50,727 loops=1)

  • Buffers: shared read=1555
106. 18.037 54.184 ↓ 1.0 57,383 1

Hash (cost=3,148.04..3,148.04 rows=57,304 width=24) (actual time=54.184..54.184 rows=57,383 loops=1)

  • Buffers: shared read=2575
107. 36.147 36.147 ↓ 1.0 57,383 1

Seq Scan on import_file import_file (cost=0..3,148.04 rows=57,304 width=24) (actual time=0.232..36.147 rows=57,383 loops=1)

  • Buffers: shared read=2575
108. 14.850 33.737 ↓ 1.0 39,279 1

Hash (cost=1,315.78..1,315.78 rows=39,278 width=32) (actual time=33.737..33.737 rows=39,279 loops=1)

  • Buffers: shared read=923
109. 18.887 18.887 ↓ 1.0 39,279 1

Seq Scan on company clac (cost=0..1,315.78 rows=39,278 width=32) (actual time=0.36..18.887 rows=39,279 loops=1)

  • Buffers: shared read=923
110. 0.013 0.184 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=20) (actual time=0.184..0.184 rows=3 loops=1)

  • Buffers: shared read=1
111. 0.171 0.171 ↑ 1.0 3 1

Seq Scan on company_type clact (cost=0..1.03 rows=3 width=20) (actual time=0.17..0.171 rows=3 loops=1)

  • Buffers: shared read=1
112. 0.000 10,929.330 ↑ 1.0 1 5,464,665

Subquery Scan on cpri (cost=0..0.09 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=5,464,665)

  • Filter: (cx.claim_product_id = cpri.claim_product_id)
  • Buffers: shared hit=1984 read=140
113. 10,929.330 10,929.330 ↑ 1.0 1 5,464,665

Result (cost=0..0.08 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=5,464,665)

  • Buffers: shared hit=1984 read=140
114. 98.081 2,249.708 ↓ 1.5 223,920 1

Hash (cost=50,773.11..50,773.11 rows=151,049 width=24) (actual time=2,249.708..2,249.708 rows=223,920 loops=1)

  • Buffers: shared hit=800215 read=18609 written=211, temp written=603
115. 46.088 2,151.627 ↓ 1.5 223,920 1

Subquery Scan on cshcomp (cost=0.43..50,773.11 rows=151,049 width=24) (actual time=1.371..2,151.627 rows=223,920 loops=1)

  • Buffers: shared hit=800215 read=18609 written=211
116. 123.850 2,105.539 ↓ 1.5 223,920 1

Aggregate (cost=0.43..49,262.62 rows=151,049 width=24) (actual time=1.37..2,105.539 rows=223,920 loops=1)

  • Buffers: shared hit=800215 read=18609 written=211
117. 1,981.689 1,981.689 ↓ 1.0 225,673 1

Index Scan using claim_state_history_claim_id_idx on claim_state_history claim_state_history (cost=0.43..46,633.49 rows=223,727 width=24) (actual time=0.937..1,981.689 rows=225,673 loops=1)

  • Filter: (claim_state_history.claim_state = 'COMPLETE'::enum.claim_state)
  • Buffers: shared hit=800215 read=18609 written=211
118. 90.562 1,144.007 ↓ 1.4 238,639 1

Hash (cost=51,607.31..51,607.31 rows=175,495 width=24) (actual time=1,144.007..1,144.007 rows=238,639 loops=1)

  • Buffers: shared hit=818823, temp written=642
119. 44.463 1,053.445 ↓ 1.4 238,639 1

Subquery Scan on cshsubmitted (cost=0.43..51,607.31 rows=175,495 width=24) (actual time=0.036..1,053.445 rows=238,639 loops=1)

  • Buffers: shared hit=818823
120. 133.654 1,008.982 ↓ 1.4 238,639 1

Aggregate (cost=0.43..49,852.36 rows=175,495 width=24) (actual time=0.034..1,008.982 rows=238,639 loops=1)

  • Buffers: shared hit=818823
121. 875.328 875.328 ↑ 1.0 292,071 1

Index Scan using claim_state_history_claim_id_idx on claim_state_history claim_state_history_1 (cost=0.43..46,633.49 rows=292,783 width=24) (actual time=0.025..875.328 rows=292,071 loops=1)

  • Filter: (claim_state_history_1.claim_state = 'SUBMITTED'::enum.claim_state)
  • Buffers: shared hit=818823
122. 5,464.665 131,151.960 ↓ 0.0 0 5,464,665

Limit (cost=1.97..1.98 rows=1 width=60) (actual time=0.024..0.024 rows=0 loops=5,464,665)

  • Buffers: shared hit=22258028 read=1047 written=1
123. 10,929.330 125,687.295 ↓ 0.0 0 5,464,665

Sort (cost=1.97..1.98 rows=1 width=60) (actual time=0.023..0.023 rows=0 loops=5,464,665)

  • Sort Key: (lower(claim_product_reason_code.active_range)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=22258028 read=1047 written=1
124. 114,757.965 114,757.965 ↓ 0.0 0 5,464,665

Index Only Scan using claim_product_reason_code_active_range_excl on claim_product_reason_code claim_product_reason_code (cost=0.41..1.96 rows=1 width=60) (actual time=0.021..0.021 rows=0 loops=5,464,665)

  • Index Cond: (claim_product_reason_code.claim_product_id = cx.claim_product_id)
  • Filter: (upper(claim_product_reason_code.active_range) IS NULL)
  • Buffers: shared hit=22257977 read=1047 written=1
125. 14.654 233.468 ↑ 1.3 46,097 1

Hash (cost=26,957.08..26,957.08 rows=58,536 width=24) (actual time=233.468..233.468 rows=46,097 loops=1)

  • Buffers: shared hit=12123
126. 8.565 218.814 ↑ 1.3 46,097 1

Subquery Scan on cshpend (cost=25,786.36..26,957.08 rows=58,536 width=24) (actual time=195.748..218.814 rows=46,097 loops=1)

  • Buffers: shared hit=12123
127. 50.676 210.249 ↑ 1.3 46,097 1

Aggregate (cost=25,786.36..26,371.72 rows=58,536 width=24) (actual time=195.746..210.249 rows=46,097 loops=1)

  • Buffers: shared hit=12123
128. 159.573 159.573 ↓ 1.0 66,166 1

Seq Scan on claim_state_history claim_state_history_2 (cost=0..25,457.74 rows=65,725 width=24) (actual time=0.018..159.573 rows=66,166 loops=1)

  • Filter: (claim_state_history_2.claim_state = 'PENDED'::enum.claim_state)
  • Buffers: shared hit=12123
129. 1,350.215 17,561.294 ↓ 2.8 2,265,951 1

Hash (cost=129,206.35..129,206.35 rows=808,164 width=96) (actual time=17,561.294..17,561.294 rows=2,265,951 loops=1)

  • Buffers: shared hit=5129171 read=45316 written=163, temp read=17802 written=38372
130. 2,865.408 16,211.079 ↓ 2.8 2,265,951 1

Hash Join (cost=51,035.29..129,206.35 rows=808,164 width=96) (actual time=3,776.283..16,211.079 rows=2,265,951 loops=1)

  • Buffers: shared hit=5129171 read=45316 written=163, temp read=17802 written=17833
131. 519.817 13,342.926 ↓ 2.2 895,582 1

Hash Join (cost=50,860.41..118,395.2 rows=408,740 width=119) (actual time=3,773.48..13,342.926 rows=895,582 loops=1)

  • Buffers: shared hit=5129171 read=45268 written=163, temp read=17802 written=17833
132. 567.686 12,820.704 ↓ 2.2 895,582 1

Merge Join (cost=50,713.6..117,175.32 rows=408,740 width=167) (actual time=3,771.042..12,820.704 rows=895,582 loops=1)

  • Buffers: shared hit=5129171 read=45248 written=163, temp read=17802 written=17833
133. 286.690 11,505.739 ↓ 2.2 895,582 1

Merge Join (cost=50,646.53..72,152.43 rows=408,740 width=151) (actual time=3,770.517..11,505.739 rows=895,582 loops=1)

  • Buffers: shared hit=5125619 read=36702 written=163, temp read=17802 written=17833
134. 286.726 11,218.945 ↓ 2.2 895,582 1

Merge Join (cost=50,646.38..71,055.41 rows=408,740 width=135) (actual time=3,770.411..11,218.945 rows=895,582 loops=1)

  • Buffers: shared hit=5125619 read=36701 written=163, temp read=17802 written=17833
135. 345.874 10,932.091 ↓ 2.2 895,582 1

Merge Join (cost=50,646.24..69,952.82 rows=408,740 width=119) (actual time=3,770.281..10,932.091 rows=895,582 loops=1)

  • Buffers: shared hit=5125619 read=36700 written=163, temp read=17802 written=17833
136. 775.864 10,585.017 ↓ 2.2 895,582 1

Nested Loop (cost=50,645.93..68,839.74 rows=408,740 width=103) (actual time=3,769.699..10,585.017 rows=895,582 loops=1)

  • Buffers: shared hit=5125619 read=36694 written=163, temp read=17802 written=17833
137. 574.821 5,165.501 ↓ 101.1 1,547,884 1

Merge Join (cost=50,645.51..50,798.67 rows=15,311 width=87) (actual time=3,769.13..5,165.501 rows=1,547,884 loops=1)

  • Buffers: shared hit=56 read=25535 written=163, temp read=17802 written=17833
138. 2,367.364 4,586.695 ↓ 101.1 1,547,884 1

Sort (cost=50,521.62..50,559.9 rows=15,311 width=76) (actual time=3,766.43..4,586.695 rows=1,547,884 loops=1)

  • Sort Key: price_default_1.price_id
  • Sort Method: external merge Disk: 131288kB
  • Buffers: shared hit=56 read=25505 written=163, temp read=17802 written=17833
139. 635.874 2,219.331 ↓ 101.1 1,547,884 1

Hash Join (cost=287.19..49,457.33 rows=15,311 width=76) (actual time=4.653..2,219.331 rows=1,547,884 loops=1)

  • Buffers: shared hit=48 read=25505 written=163
140. 751.077 1,581.774 ↓ 99.4 1,547,884 1

Hash Join (cost=188.97..49,318.21 rows=15,567 width=60) (actual time=2.934..1,581.774 rows=1,547,884 loops=1)

  • Buffers: shared hit=48 read=25491 written=163
141. 213.913 828.013 ↑ 1.0 1,547,884 1

Append (cost=0..40,998.34 rows=1,548,734 width=55) (actual time=0.19..828.013 rows=1,547,884 loops=1)

  • Buffers: shared read=25491 written=163
142. 0.409 0.409 ↑ 1.0 599 1

Seq Scan on price_default price_default_1 (cost=0..13.99 rows=599 width=54) (actual time=0.189..0.409 rows=599 loops=1)

  • Buffers: shared read=8
143. 611.863 611.863 ↓ 1.0 1,542,993 1

Seq Scan on price_sale price_sale_1 (cost=0..40,841.63 rows=1,542,963 width=55) (actual time=2.451..611.863 rows=1,542,993 loops=1)

  • Buffers: shared read=25412 written=163
144. 0.029 0.029 ↓ 0.0 0 1

Seq Scan on price_company price_company_1 (cost=0..14.2 rows=420 width=80) (actual time=0.029..0.029 rows=0 loops=1)

145. 0.012 0.012 ↓ 0.0 0 1

Seq Scan on price_list price_list_1 (cost=0..14.6 rows=460 width=80) (actual time=0.012..0.012 rows=0 loops=1)

146. 1.787 1.787 ↑ 1.0 4,292 1

Seq Scan on price_class price_class_1 (cost=0..113.92 rows=4,292 width=54) (actual time=0.16..1.787 rows=4,292 loops=1)

  • Buffers: shared read=71
147. 1.974 2.684 ↑ 1.0 5,639 1

Hash (cost=104.39..104.39 rows=5,639 width=37) (actual time=2.684..2.684 rows=5,639 loops=1)

  • Buffers: shared hit=48
148. 0.710 0.710 ↑ 1.0 5,639 1

Seq Scan on product_uom_conversion suomc (cost=0..104.39 rows=5,639 width=37) (actual time=0.005..0.71 rows=5,639 loops=1)

  • Buffers: shared hit=48
149. 0.669 1.683 ↑ 1.0 2,852 1

Hash (cost=62.56..62.56 rows=2,852 width=16) (actual time=1.683..1.683 rows=2,852 loops=1)

  • Buffers: shared read=14
150. 1.014 1.014 ↑ 1.0 2,852 1

Index Only Scan using product_pkey on product pro (cost=0.28..62.56 rows=2,852 width=16) (actual time=0.449..1.014 rows=2,852 loops=1)

  • Buffers: shared read=14
151. 2.626 3.985 ↓ 3.0 3,205 1

Sort (cost=123.88..126.55 rows=1,069 width=27) (actual time=2.692..3.985 rows=3,205 loops=1)

  • Sort Key: pm.price_id
  • Sort Method: quicksort Memory: 347kB
  • Buffers: shared read=30
152. 1.359 1.359 ↓ 3.0 3,205 1

Seq Scan on price_modified pm (cost=0..70.1 rows=1,069 width=27) (actual time=0.259..1.359 rows=3,205 loops=1)

  • Filter: upper_inf(pm.active_range)
  • Buffers: shared read=30
153. 4,643.652 4,643.652 ↑ 27.0 1 1,547,884

Index Scan using idx_claim_product_price_price_id on claim_product_price claim_product_price (cost=0.42..0.91 rows=27 width=32) (actual time=0.002..0.003 rows=1 loops=1,547,884)

  • Index Cond: (claim_product_price.price_id = price_default_1.price_id)
  • Buffers: shared hit=5125563 read=11159
154. 1.200 1.200 ↑ 1.0 599 1

Index Only Scan using price_default_pkey on price_default price_default (cost=0.28..16.76 rows=599 width=16) (actual time=0.578..1.2 rows=599 loops=1)

  • Buffers: shared read=6
155. 0.128 0.128 ↓ 0.0 0 1

Index Only Scan using price_list_pkey on price_list price_list (cost=0.15..23.55 rows=460 width=16) (actual time=0.127..0.128 rows=0 loops=1)

  • Buffers: shared read=1
156. 0.104 0.104 ↓ 0.0 0 1

Index Only Scan using price_company_pkey on price_company price_company (cost=0.15..22.95 rows=420 width=16) (actual time=0.104..0.104 rows=0 loops=1)

  • Buffers: shared read=1
157. 747.279 747.279 ↓ 1.0 1,542,971 1

Index Only Scan using price_generated_pkey on price_sale price_sale (cost=0.43..36,122.87 rows=1,542,963 width=16) (actual time=0.511..747.279 rows=1,542,971 loops=1)

  • Buffers: shared hit=3552 read=8546
158. 0.952 2.405 ↑ 1.0 4,292 1

Hash (cost=93.16..93.16 rows=4,292 width=16) (actual time=2.405..2.405 rows=4,292 loops=1)

  • Buffers: shared read=20
159. 1.453 1.453 ↑ 1.0 4,292 1

Index Only Scan using price_class_pkey on price_class price_class (cost=0.28..93.16 rows=4,292 width=16) (actual time=0.59..1.453 rows=4,292 loops=1)

  • Buffers: shared read=20
160. 1.442 2.745 ↑ 1.0 5,639 1

Hash (cost=104.39..104.39 rows=5,639 width=37) (actual time=2.745..2.745 rows=5,639 loops=1)

  • Buffers: shared read=48
161. 1.303 1.303 ↑ 1.0 5,639 1

Seq Scan on product_uom_conversion tuomc (cost=0..104.39 rows=5,639 width=37) (actual time=0.241..1.303 rows=5,639 loops=1)

  • Buffers: shared read=48
162. 0.062 12.885 ↑ 4.0 115 1

Hash (cost=140.33..140.33 rows=456 width=80) (actual time=12.885..12.885 rows=115 loops=1)

  • Buffers: shared read=2
163. 0.390 12.823 ↑ 4.0 115 1

Hash Join (cost=104.61..140.33 rows=456 width=80) (actual time=12.304..12.823 rows=115 loops=1)

  • Buffers: shared read=2
164. 12.336 12.336 ↓ 1.6 1,226 1

Foreign Scan (cost=100..133.61 rows=787 width=80) (actual time=12.173..12.336 rows=1,226 loops=1)

165. 0.030 0.097 ↑ 1.0 116 1

Hash (cost=3.16..3.16 rows=116 width=32) (actual time=0.097..0.097 rows=116 loops=1)

  • Buffers: shared read=2
166. 0.067 0.067 ↑ 1.0 116 1

Seq Scan on account_client account_client (cost=0..3.16 rows=116 width=32) (actual time=0.037..0.067 rows=116 loops=1)

  • Buffers: shared read=2
167. 0.000 103,828.635 ↓ 0.0 0 5,464,665

Limit (cost=1.81..1.82 rows=1 width=56) (actual time=0.019..0.019 rows=0 loops=5,464,665)

  • Buffers: shared hit=16261280 read=751
168. 16,393.995 103,828.635 ↓ 0.0 0 5,464,665

Sort (cost=1.81..1.82 rows=1 width=56) (actual time=0.019..0.019 rows=0 loops=5,464,665)

  • Sort Key: (lower(claim_reason_code.active_range)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=16261280 read=751
169. 87,434.640 87,434.640 ↓ 0.0 0 5,464,665

Index Only Scan using claim_reason_code_active_range_excl on claim_reason_code claim_reason_code (cost=0.28..1.8 rows=1 width=56) (actual time=0.016..0.016 rows=0 loops=5,464,665)

  • Index Cond: (claim_reason_code.claim_id = c.claim_id)
  • Buffers: shared hit=16261238 read=750
170. 26.236 7,343.037 ↑ 1.0 19,365 1

Subquery Scan on *SELECT* 2 (cost=304,707.53..306,692.44 rows=19,365 width=1,156) (actual time=7,285.889..7,343.037 rows=19,365 loops=1)

  • Buffers: shared hit=2236467 read=40608 written=125, temp read=2654 written=2655
171. 21.672 7,316.801 ↑ 1.0 19,365 1

Unique (cost=304,707.53..306,353.56 rows=19,365 width=1,096) (actual time=7,285.876..7,316.801 rows=19,365 loops=1)

  • Buffers: shared hit=2236467 read=40608 written=125, temp read=2654 written=2655
172. 192.107 7,295.129 ↑ 1.0 19,365 1

Sort (cost=304,707.53..304,755.94 rows=19,365 width=1,096) (actual time=7,285.873..7,295.129 rows=19,365 loops=1)

  • Sort Key: (NULLIF(btrim((cx_1.claim_adhoc_product_id)::text), 'Unknown'::text)), i_1.client_id, (NULLIF(btrim((i_1.claimant_company_id)::text), 'Unknown'::text)), (NULLIF(btrim((c_2.claim_sequence)::text), 'Unknown'::text)), (NULLIF(btrim((c_2.claim_state)::text), 'Unknown'::text)), cx_1.rebate_requested_rate, cx_1.rebate_requested_quantity, ((cx_1.rebate_requested_quantity * cx_1.rebate_requested_rate)), cx_1.rebate_allowed_rate, cx_1.rebate_allowed_quantity, ((cx_1.rebate_allowed_quantity * cx_1.rebate_allowed_rate)), cx_1.resolve_date, (max(claim_transaction_3.transaction_date)), (max(claim_transaction_3.cleared_date)), (max(claim_transaction_3.void_date)), ("left"(COALESCE(NULLIF(btrim(c_2.claim_tag), ''::text), 'Unknown'::text), 250)), (NULLIF(btrim((claim_adhoc_product_reason_code.claim_reason_type)::text), 'Unknown'::text)), (NULLIF(btrim((claim_adhoc_product_reason_code.claim_reason_code)::text), 'Unknown'::text)), cshpend_1.record_timestamp, (max(claim_state_history_4.record_timestamp)), (NULLIF(btrim((c_2.invoice_id)::text), 'Unknown'::text)), (NULLIF(btrim((cx_1.claim_id)::text), 'Unknown'::text)), (NULLIF(btrim((cx_1.distributor_company_id)::text), 'Unknown'::text)), ((c_2.contract_sequence)::text), ((contract_family_1.contract_family_id)::text), ((contract_renew_version_1.contract_renew_version_id)::text), ((ccb_1.contract_id)::text), (CASE WHEN (ii_1.invoice_id IS NULL) THEN 'MANUAL'::text ELSE 'ECLAIM'::text END), (min(claim_state_history_3.record_timestamp)), ("left"(COALESCE(NULLIF(btrim((c_2.transaction_type)::text), ''::text), 'Unknown'::text), 250)), (CASE WHEN ((claim_amounts_materialized_1.product_count > 0) AND (claim_amounts_materialized_1.lumpsum_count = 0)) THEN 'product'::text WHEN ((claim_amounts_materialized_1.product_count > 0) AND (claim_amounts_materialized_1.lumpsum_count > 0)) THEN 'product and lumpsum'::text WHEN ((claim_amounts_materialized_1.product_count = 0) AND (claim_amounts_materialized_1.lumpsum_count > 0)) THEN 'lumpsum'::text WHEN (claim_amounts_materialized_1.adhoc_product_count > 0) THEN 'product'::text ELSE NULL::text END), ("left"(COALESCE(NULLIF(btrim((cx_1.claimant_contract_name)::text), ''::text), 'Unknown'::text), 250)), ("left"(COALESCE(NULLIF(btrim(((claim_reason_code_1.claim_reason_code)::text)), ''::text), 'Unknown'::text), 250))
  • Sort Method: external merge Disk: 8864kB
  • Buffers: shared hit=2236467 read=40608 written=125, temp read=2654 written=2655
173. 223.318 7,103.022 ↑ 1.0 19,365 1

Nested Loop (cost=205,818.41..297,370.63 rows=19,365 width=1,096) (actual time=5,169.257..7,103.022 rows=19,365 loops=1)

  • Buffers: shared hit=2234476 read=40497 written=125, temp read=1546 written=1546
174. 16.079 6,473.039 ↑ 1.0 19,365 1

Hash Join (cost=205,816.6..258,350.15 rows=19,365 width=307) (actual time=5,168.969..6,473.039 rows=19,365 loops=1)

  • Buffers: shared hit=2176294 read=40497 written=125, temp read=1546 written=1546
175. 97.228 6,408.508 ↑ 1.0 19,365 1

Hash Join (cost=203,121.01..255,527.45 rows=19,365 width=307) (actual time=5,120.148..6,408.508 rows=19,365 loops=1)

  • Buffers: shared hit=2176294 read=38942 written=56, temp read=1546 written=1546
176. 156.466 1,193.016 ↓ 1.4 238,639 1

Aggregate (cost=0.43..49,852.36 rows=175,495 width=24) (actual time=0.029..1,193.016 rows=238,639 loops=1)

  • Buffers: shared hit=818817 read=6
177. 1,036.550 1,036.550 ↑ 1.0 292,071 1

Index Scan using claim_state_history_claim_id_idx on claim_state_history claim_state_history_3 (cost=0.43..46,633.49 rows=292,783 width=24) (actual time=0.02..1,036.55 rows=292,071 loops=1)

  • Filter: (claim_state_history_3.claim_state = 'SUBMITTED'::enum.claim_state)
  • Buffers: shared hit=818817 read=6
178. 22.766 5,118.264 ↑ 1.0 19,365 1

Hash (cost=202,878.52..202,878.52 rows=19,365 width=299) (actual time=5,118.264..5,118.264 rows=19,365 loops=1)

  • Buffers: shared hit=1357424 read=38936 written=56, temp read=1546 written=1546
179. 22.468 5,095.498 ↑ 1.0 19,365 1

Nested Loop (cost=160,107.67..202,878.52 rows=19,365 width=299) (actual time=4,475.311..5,095.498 rows=19,365 loops=1)

  • Buffers: shared hit=1357424 read=38936 written=56, temp read=1546 written=1546
180. 12.439 4,627.635 ↑ 1.0 19,365 1

Hash Join (cost=160,105.87..167,440.57 rows=19,365 width=291) (actual time=4,473.395..4,627.635 rows=19,365 loops=1)

  • Buffers: shared hit=1299551 read=38651 written=53, temp read=1546 written=1546
181. 15.883 4,581.653 ↑ 1.0 19,365 1

Hash Join (cost=158,280.75..165,349.19 rows=19,365 width=275) (actual time=4,439.627..4,581.653 rows=19,365 loops=1)

  • Buffers: shared hit=1298710 read=38528 written=51, temp read=1546 written=1546
182. 72.425 4,303.199 ↑ 1.0 19,365 1

Hash Join (cost=144,401.56..151,419.15 rows=19,365 width=259) (actual time=4,177.033..4,303.199 rows=19,365 loops=1)

  • Buffers: shared hit=1286394 read=38324 written=32, temp read=951 written=953
183. 54.202 54.202 ↓ 1.0 178,844 1

Seq Scan on invoice i_1 (cost=0..5,035.97 rows=178,797 width=48) (actual time=0.37..54.202 rows=178,844 loops=1)

  • Buffers: shared read=3248 written=21
184. 19.760 4,176.572 ↑ 1.0 19,365 1

Hash (cost=144,159.5..144,159.5 rows=19,365 width=211) (actual time=4,176.572..4,176.572 rows=19,365 loops=1)

  • Buffers: shared hit=1286394 read=35076 written=11, temp read=951 written=953
185. 12.832 4,156.812 ↑ 1.0 19,365 1

Hash Join (cost=60,707.69..144,159.5 rows=19,365 width=211) (actual time=957.672..4,156.812 rows=19,365 loops=1)

  • Buffers: shared hit=1286394 read=35076 written=11, temp read=951 written=953
186. 13.337 4,138.513 ↑ 1.0 19,365 1

Hash Join (cost=60,479.82..143,880.76 rows=19,365 width=195) (actual time=952.099..4,138.513 rows=19,365 loops=1)

  • Buffers: shared hit=1286379 read=34967 written=11, temp read=951 written=953
187. 40.040 3,777.766 ↑ 1.0 19,365 1

Merge Join (cost=32,791.04..116,141.15 rows=19,365 width=187) (actual time=604.574..3,777.766 rows=19,365 loops=1)

  • Buffers: shared hit=1285354 read=23869 written=11, temp read=951 written=953
188. 42.075 3,096.609 ↑ 1.0 19,365 1

Merge Join (cost=39.07..75,346.26 rows=19,365 width=163) (actual time=187.775..3,096.609 rows=19,365 loops=1)

  • Buffers: shared hit=1285354 read=17339 written=11
189. 53.305 1,374.808 ↑ 1.0 19,365 1

Merge Join (cost=38.65..24,025.36 rows=19,365 width=155) (actual time=70.52..1,374.808 rows=19,365 loops=1)

  • Buffers: shared hit=474819 read=9834 written=2
190. 47.973 727.296 ↑ 1.0 19,365 1

Merge Join (cost=19.5..12,554.98 rows=19,365 width=82) (actual time=34.81..727.296 rows=19,365 loops=1)

  • Buffers: shared hit=238170 read=5146
191. 27.339 27.339 ↑ 1.0 19,365 1

Index Scan using claim_adhoc_product_claim_id_idx on claim_adhoc_product cx_1 (cost=0.29..1,055.72 rows=19,365 width=70) (actual time=0.887..27.339 rows=19,365 loops=1)

  • Buffers: shared hit=1553 read=436
192. 651.984 651.984 ↑ 1.0 239,838 1

Index Scan using claim_amounts_materialized_pkey on claim_amounts_materialized claim_amounts_materialized_1 (cost=0.42..10,675.98 rows=240,005 width=28) (actual time=1.161..651.984 rows=239,838 loops=1)

  • Buffers: shared hit=236617 read=4710
193. 594.207 594.207 ↑ 1.0 239,838 1

Index Scan using claim_pkey on claim c_2 (cost=0.42..10,646.46 rows=240,236 width=73) (actual time=0.942..594.207 rows=239,838 loops=1)

  • Buffers: shared hit=236649 read=4688 written=2
194. 140.852 1,679.726 ↓ 1.5 223,702 1

Aggregate (cost=0.43..49,262.62 rows=151,049 width=24) (actual time=1.758..1,679.726 rows=223,702 loops=1)

  • Buffers: shared hit=810535 read=7505 written=9
195. 1,538.874 1,538.874 ↓ 1.0 225,454 1

Index Scan using claim_state_history_claim_id_idx on claim_state_history claim_state_history_4 (cost=0.43..46,633.49 rows=223,727 width=24) (actual time=1.21..1,538.874 rows=225,454 loops=1)

  • Filter: (claim_state_history_4.claim_state = 'COMPLETE'::enum.claim_state)
  • Buffers: shared hit=810535 read=7505 written=9
196. 160.550 641.117 ↓ 1.0 223,071 1

Aggregate (cost=32,751.96..37,788.74 rows=222,284 width=40) (actual time=416.457..641.117 rows=223,071 loops=1)

  • Buffers: shared read=6530, temp read=951 written=953
197. 336.443 480.567 ↑ 1.0 224,849 1

Sort (cost=32,751.96..33,314.75 rows=225,115 width=40) (actual time=416.447..480.567 rows=224,849 loops=1)

  • Sort Key: claim_transaction_3.claim_id
  • Sort Method: external merge Disk: 7608kB
  • Buffers: shared read=6530, temp read=951 written=953
198. 144.124 144.124 ↑ 1.0 225,068 1

Seq Scan on claim_transaction claim_transaction_3 (cost=0..8,781.15 rows=225,115 width=40) (actual time=0.446..144.124 rows=225,068 loops=1)

  • Buffers: shared read=6530
199. 16.934 347.410 ↑ 1.3 46,097 1

Hash (cost=26,957.08..26,957.08 rows=58,536 width=24) (actual time=347.41..347.41 rows=46,097 loops=1)

  • Buffers: shared hit=1025 read=11098
200. 10.833 330.476 ↑ 1.3 46,097 1

Subquery Scan on cshpend_1 (cost=25,786.36..26,957.08 rows=58,536 width=24) (actual time=301.568..330.476 rows=46,097 loops=1)

  • Buffers: shared hit=1025 read=11098
201. 60.467 319.643 ↑ 1.3 46,097 1

Aggregate (cost=25,786.36..26,371.72 rows=58,536 width=24) (actual time=301.566..319.643 rows=46,097 loops=1)

  • Buffers: shared hit=1025 read=11098
202. 259.176 259.176 ↓ 1.0 66,166 1

Seq Scan on claim_state_history claim_state_history_5 (cost=0..25,457.74 rows=65,725 width=24) (actual time=0.203..259.176 rows=66,166 loops=1)

  • Filter: (claim_state_history_5.claim_state = 'PENDED'::enum.claim_state)
  • Buffers: shared hit=1025 read=11098
203. 2.007 5.467 ↓ 1.0 5,286 1

Hash (cost=161.83..161.83 rows=5,283 width=20) (actual time=5.467..5.467 rows=5,286 loops=1)

  • Buffers: shared read=109
204. 3.460 3.460 ↓ 1.0 5,286 1

Seq Scan on contract_family contract_family_1 (cost=0..161.83 rows=5,283 width=20) (actual time=0.726..3.46 rows=5,286 loops=1)

  • Buffers: shared read=109
205. 0.514 262.571 ↓ 37.1 1,486 1

Hash (cost=13,878.69..13,878.69 rows=40 width=32) (actual time=262.571..262.571 rows=1,486 loops=1)

  • Buffers: shared hit=12316 read=204 written=19, temp read=595 written=593
206. 0.354 262.057 ↓ 37.1 1,486 1

Subquery Scan on ccb_1 (cost=13,878.09..13,878.69 rows=40 width=32) (actual time=261.052..262.057 rows=1,486 loops=1)

  • Buffers: shared hit=12316 read=204 written=19, temp read=595 written=593
207. 0.439 261.703 ↓ 37.1 1,486 1

Unique (cost=13,878.09..13,878.29 rows=40 width=33) (actual time=261.05..261.703 rows=1,486 loops=1)

  • Buffers: shared hit=12316 read=204 written=19, temp read=595 written=593
208. 1.578 261.264 ↓ 37.9 1,516 1

Sort (cost=13,878.09..13,878.19 rows=40 width=33) (actual time=261.048..261.264 rows=1,516 loops=1)

  • Sort Key: clp_2.claim_adhoc_product_id, ((cav_2.contract_state = 'TERMINATED'::enum.contract_state))
  • Sort Method: quicksort Memory: 167kB
  • Buffers: shared hit=12316 read=204 written=19, temp read=595 written=593
209. 1.647 259.686 ↓ 37.9 1,516 1

Nested Loop (cost=10,857.97..13,877.03 rows=40 width=33) (actual time=182.078..259.686 rows=1,516 loops=1)

  • Buffers: shared hit=12308 read=204 written=19, temp read=595 written=593
210. 6.775 224.687 ↓ 37.9 1,516 1

Hash Join (cost=10,857.68..13,858.02 rows=40 width=32) (actual time=181.108..224.687 rows=1,516 loops=1)

  • Buffers: shared hit=4062 read=86 written=8, temp read=595 written=593
211. 29.284 199.522 ↑ 1.0 19,365 1

Hash Join (cost=9,928.31..12,325.79 rows=19,365 width=24) (actual time=162.449..199.522 rows=19,365 loops=1)

  • Buffers: shared hit=3550 read=86 written=8, temp read=595 written=593
212. 9.108 9.108 ↑ 1.0 19,365 1

Seq Scan on claim_adhoc_product clp_2 (cost=0..634.65 rows=19,365 width=36) (actual time=0.01..9.108 rows=19,365 loops=1)

  • Buffers: shared hit=359 read=82 written=8
213. 91.105 161.130 ↑ 1.0 240,067 1

Hash (cost=5,517.36..5,517.36 rows=240,236 width=20) (actual time=161.13..161.13 rows=240,067 loops=1)

  • Buffers: shared hit=3115, temp written=583
214. 70.025 70.025 ↑ 1.0 240,067 1

Seq Scan on claim cl_2 (cost=0..5,517.36 rows=240,236 width=20) (actual time=0.011..70.025 rows=240,067 loops=1)

  • Buffers: shared hit=3115
215. 4.794 18.390 ↓ 1.0 11,886 1

Hash (cost=780.87..780.87 rows=11,880 width=34) (actual time=18.39..18.39 rows=11,886 loops=1)

  • Buffers: shared hit=512
216. 8.054 13.596 ↓ 1.0 11,886 1

Hash Join (cost=227.87..780.87 rows=11,880 width=34) (actual time=3.391..13.596 rows=11,886 loops=1)

  • Buffers: shared hit=512
217. 2.410 2.410 ↓ 1.0 11,886 1

Seq Scan on contract_renew_version crv_2 (cost=0..521.8 rows=11,880 width=46) (actual time=0.23..2.41 rows=11,886 loops=1)

  • Buffers: shared hit=403
218. 1.467 3.132 ↓ 1.0 5,286 1

Hash (cost=161.83..161.83 rows=5,283 width=20) (actual time=3.132..3.132 rows=5,286 loops=1)

  • Buffers: shared hit=109
219. 1.665 1.665 ↓ 1.0 5,286 1

Seq Scan on contract_family cf_2 (cost=0..161.83 rows=5,283 width=20) (actual time=0.006..1.665 rows=5,286 loops=1)

  • Buffers: shared hit=109
220. 33.352 33.352 ↑ 1.0 1 1,516

Index Scan using contract_amend_version_contract_renew_version_id_idx1 on contract_amend_version cav_2 (cost=0.29..0.46 rows=1 width=36) (actual time=0.02..0.022 rows=1 loops=1,516)

  • Index Cond: (cav_2.contract_renew_version_id = crv_2.contract_renew_version_id)
  • Filter: (cav_2.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED,TERMINATED}'::enum.contract_state[]))
  • Buffers: shared hit=8246 read=118 written=11
221. 5.813 33.543 ↓ 1.0 18,839 1

Hash (cost=1,589.74..1,589.74 rows=18,830 width=32) (actual time=33.543..33.543 rows=18,839 loops=1)

  • Buffers: shared hit=841 read=123 written=2
222. 9.669 27.730 ↓ 1.0 18,839 1

Hash Join (cost=515.99..1,589.74 rows=18,830 width=32) (actual time=15.404..27.73 rows=18,839 loops=1)

  • Buffers: shared hit=841 read=123 written=2
223. 2.838 2.838 ↓ 1.0 18,839 1

Seq Scan on contract_amend_version contract_amend_version_1 (cost=0..1,024.3 rows=18,830 width=32) (actual time=0.063..2.838 rows=18,839 loops=1)

  • Buffers: shared hit=836
224. 4.669 15.223 ↓ 1.0 11,886 1

Hash (cost=367.49..367.49 rows=11,880 width=16) (actual time=15.223..15.223 rows=11,886 loops=1)

  • Buffers: shared hit=5 read=123 written=2
225. 10.554 10.554 ↓ 1.0 11,886 1

Index Only Scan using contract_version_pkey on contract_renew_version contract_renew_version_1 (cost=0.29..367.49 rows=11,880 width=16) (actual time=0.511..10.554 rows=11,886 loops=1)

  • Buffers: shared hit=5 read=123 written=2
226. 19.365 445.395 ↑ 1.0 1 19,365

Limit (cost=1.8..1.81 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=19,365)

  • Buffers: shared hit=57873 read=285 written=3
227. 58.095 426.030 ↑ 1.0 1 19,365

Sort (cost=1.8..1.81 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=19,365)

  • Sort Key: (upper(claim_adhoc_product_reason_code.active_range)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=57873 read=285 written=3
228. 367.935 367.935 ↑ 1.0 1 19,365

Index Only Scan using claim_adhoc_product_reason_code_active_range_excl on claim_adhoc_product_reason_code claim_adhoc_product_reason_code (cost=0.28..1.79 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=19,365)

  • Index Cond: (claim_adhoc_product_reason_code.claim_adhoc_product_id = cx_1.claim_adhoc_product_id)
  • Buffers: shared hit=57868 read=285 written=3
229. 17.805 48.452 ↑ 1.0 50,343 1

Hash (cost=2,061.93..2,061.93 rows=50,693 width=16) (actual time=48.452..48.452 rows=50,343 loops=1)

  • Buffers: shared read=1555 written=69
230. 30.647 30.647 ↓ 1.0 50,727 1

Seq Scan on import_invoice ii_1 (cost=0..2,061.93 rows=50,693 width=16) (actual time=0.509..30.647 rows=50,727 loops=1)

  • Buffers: shared read=1555 written=69
231. 19.365 406.665 ↑ 1.0 1 19,365

Limit (cost=1.81..1.82 rows=1 width=56) (actual time=0.02..0.021 rows=1 loops=19,365)

  • Buffers: shared hit=58143
232. 77.460 387.300 ↑ 1.0 1 19,365

Sort (cost=1.81..1.82 rows=1 width=56) (actual time=0.02..0.02 rows=1 loops=19,365)

  • Sort Key: (lower(claim_reason_code_1.active_range)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=58143
233. 309.840 309.840 ↑ 1.0 1 19,365

Index Only Scan using claim_reason_code_active_range_excl on claim_reason_code claim_reason_code_1 (cost=0.28..1.8 rows=1 width=56) (actual time=0.016..0.016 rows=1 loops=19,365)

  • Index Cond: (claim_reason_code_1.claim_id = c_2.claim_id)
  • Buffers: shared hit=58143
234. 6.107 4,285.320 ↓ 1.0 4,237 1

Subquery Scan on *SELECT* 3 (cost=235,904.17..236,391.31 rows=4,236 width=1,214) (actual time=4,274.533..4,285.32 rows=4,237 loops=1)

  • Buffers: shared hit=1863179 read=3527 dirtied=1, temp read=1144 written=1146
235. 4.007 4,279.213 ↓ 1.0 4,237 1

Unique (cost=235,904.17..236,285.41 rows=4,236 width=1,046) (actual time=4,274.524..4,279.213 rows=4,237 loops=1)

  • Buffers: shared hit=1863179 read=3527 dirtied=1, temp read=1144 written=1146
236. 24.725 4,275.206 ↓ 1.0 4,290 1

Sort (cost=235,904.17..235,914.76 rows=4,236 width=1,046) (actual time=4,274.521..4,275.206 rows=4,290 loops=1)

  • Sort Key: (NULLIF(btrim((cx_2.claim_lumpsum_id)::text), 'Unknown'::text)), i_2.client_id, (NULLIF(btrim((i_2.claimant_company_id)::text), 'Unknown'::text)), (NULLIF(btrim((cx_2.lumpsum_id)::text), 'Unknown'::text)), ((cx_2.contract_lumpsum_id)::text), (NULLIF(btrim((c_3.claim_sequence)::text), 'Unknown'::text)), (NULLIF(btrim((c_3.claim_state)::text), 'Unknown'::text)), cx_2.requested_amount, cx_2.allowed_amount, ((upper(cx_2.event_date_range) - 1)), (max(claim_transaction_5.transaction_date)), (max(claim_transaction_5.cleared_date)), (max(claim_transaction_5.void_date)), ("left"(COALESCE(NULLIF(btrim(c_3.claim_tag), ''::text), 'Unknown'::text), 250)), ("left"(COALESCE(NULLIF(btrim((cx_2.hint_contract_lumpsum_description)::text), ''::text), 'Unknown'::text), 250)), (lower(cx_2.event_date_range)), (NULLIF(btrim((claim_lumpsum_reason_code.claim_reason_type)::text), 'Unknown'::text)), (NULLIF(btrim(((claim_lumpsum_reason_code.claim_reason_code)::text)), 'Unknown'::text)), cshpend_2.record_timestamp, (max(claim_state_history_7.record_timestamp)), (NULLIF(btrim((c_3.invoice_id)::text), 'Unknown'::text)), (NULLIF(btrim((cx_2.claim_id)::text), 'Unknown'::text)), ((c_3.contract_sequence)::text), ((contract_family_2.contract_family_id)::text), ((contract_renew_version_2.contract_renew_version_id)::text), ((COALESCE(conx_1.contract_id, ccb_2.contract_id))::text), import_file_1.received_date, (max(claim_transaction_5.transaction_amount)), ("left"(COALESCE(NULLIF(btrim(((account_foreign_1.first_name || ' '::text) || account_foreign_1.last_name)), ''::text), 'Unknown'::text), 250)), (CASE WHEN (ii_2.invoice_id IS NULL) THEN 'MANUAL'::text ELSE 'ECLAIM'::text END), (min(claim_state_history_8.record_timestamp)), ("left"(COALESCE(NULLIF(btrim((claim_transaction_4.transaction_type)::text), ''::text), 'Unknown'::text), 250)), (CASE WHEN ((claim_amounts_materialized_2.product_count > 0) AND (claim_amounts_materialized_2.lumpsum_count = 0)) THEN 'product'::text WHEN ((claim_amounts_materialized_2.product_count > 0) AND (claim_amounts_materialized_2.lumpsum_count > 0)) THEN 'product and lumpsum'::text WHEN ((claim_amounts_materialized_2.product_count = 0) AND (claim_amounts_materialized_2.lumpsum_count > 0)) THEN 'lumpsum'::text WHEN (claim_amounts_materialized_2.adhoc_product_count > 0) THEN 'product'::text ELSE NULL::text END), ("left"(COALESCE(NULLIF(btrim((cx_2.claimant_contract_name)::text), ''::text), 'Unknown'::text), 250)), ("left"(COALESCE(NULLIF(btrim(((claim_reason_code_2.claim_reason_code)::text)), ''::text), 'Unknown'::text), 250))
  • Sort Method: quicksort Memory: 4323kB
  • Buffers: shared hit=1863179 read=3527 dirtied=1, temp read=1144 written=1146
237. 69.869 4,250.481 ↓ 1.0 4,290 1

Nested Loop (cost=121,239.09..235,648.98 rows=4,236 width=1,046) (actual time=1,952.402..4,250.481 rows=4,290 loops=1)

  • Buffers: shared hit=1863179 read=3527 dirtied=1, temp read=1144 written=1146
238. 7.214 4,090.522 ↓ 1.0 4,290 1

Nested Loop (cost=121,237.28..227,039.31 rows=4,236 width=536) (actual time=1,952.212..4,090.522 rows=4,290 loops=1)

  • Buffers: shared hit=1850368 read=3527 dirtied=1, temp read=1144 written=1146
239. 6.618 3,915.998 ↓ 1.0 4,290 1

Hash Join (cost=121,236.87..224,821.31 rows=4,236 width=544) (actual time=1,952.192..3,915.998 rows=4,290 loops=1)

  • Buffers: shared hit=1847829 read=3204 dirtied=1, temp read=1144 written=1146
240. 4.811 3,850.259 ↓ 1.0 4,290 1

Hash Join (cost=118,541.27..222,097.91 rows=4,236 width=528) (actual time=1,892.938..3,850.259 rows=4,290 loops=1)

  • Buffers: shared hit=1846274 read=3204 dirtied=1, temp read=1144 written=1146
241. 6.029 3,835.393 ↓ 1.0 4,290 1

Hash Join (cost=118,340.5..221,656.24 rows=4,236 width=464) (actual time=1,882.852..3,835.393 rows=4,290 loops=1)

  • Buffers: shared hit=1846234 read=3171 dirtied=1, temp read=1144 written=1146
242. 35.483 3,816.760 ↓ 1.0 4,237 1

Merge Join (cost=117,632.04..220,917.3 rows=4,236 width=464) (actual time=1,870.18..3,816.76 rows=4,237 loops=1)

  • Buffers: shared hit=1846234 read=2973 dirtied=1, temp read=1144 written=1146
243. 36.611 3,005.672 ↓ 1.0 4,237 1

Merge Join (cost=117,631.62..168,829.73 rows=4,236 width=456) (actual time=1,870.116..3,005.672 rows=4,237 loops=1)

  • Buffers: shared hit=1028209 read=2973 dirtied=1, temp read=1144 written=1146
244. 14.419 1,870.993 ↓ 1.0 4,237 1

Sort (cost=117,631.19..117,641.78 rows=4,236 width=448) (actual time=1,870.021..1,870.993 rows=4,237 loops=1)

  • Sort Key: cx_2.claim_id
  • Sort Method: quicksort Memory: 2309kB
  • Buffers: shared hit=210182 read=2973 dirtied=1, temp read=1144 written=1146
245. 7.444 1,856.574 ↓ 1.0 4,237 1

Hash Join (cost=81,257.2..117,376 rows=4,236 width=448) (actual time=1,040.834..1,856.574 rows=4,237 loops=1)

  • Buffers: shared hit=210182 read=2973 dirtied=1, temp read=1144 written=1146
246. 7.511 1,595.479 ↓ 1.0 4,237 1

Nested Loop (cost=53,568.42..89,676.1 rows=4,236 width=440) (actual time=786.824..1,595.479 rows=4,237 loops=1)

  • Buffers: shared hit=198059 read=2973 dirtied=1, temp read=1144 written=1146
247. 6.647 1,486.280 ↓ 1.0 4,237 1

Nested Loop (cost=53,566.72..82,358.41 rows=4,236 width=404) (actual time=786.358..1,486.28 rows=4,237 loops=1)

  • Buffers: shared hit=189548 read=2930 dirtied=1, temp read=1144 written=1146
248. 6.186 1,454.211 ↓ 1.0 4,237 1

Hash Join (cost=53,566.3..74,346.16 rows=4,236 width=392) (actual time=786.333..1,454.211 rows=4,237 loops=1)

  • Buffers: shared hit=172576 read=2923 dirtied=1, temp read=1144 written=1146
249. 7.317 1,442.739 ↓ 1.0 4,237 1

Hash Join (cost=53,050.31..73,819.06 rows=4,236 width=392) (actual time=780.939..1,442.739 rows=4,237 loops=1)

  • Buffers: shared hit=172449 read=2923 dirtied=1, temp read=1144 written=1146
250. 10.174 1,424.912 ↓ 1.0 4,237 1

Nested Loop (cost=51,790.64..72,548.26 rows=4,236 width=376) (actual time=770.364..1,424.912 rows=4,237 loops=1)

  • Buffers: shared hit=171613 read=2923 dirtied=1, temp read=1144 written=1146
251. 5.554 1,287.628 ↓ 1.0 4,237 1

Hash Join (cost=51,790.22..70,578.4 rows=4,236 width=328) (actual time=769.686..1,287.628 rows=4,237 loops=1)

  • Buffers: shared hit=155743 read=1805 dirtied=1, temp read=1144 written=1146
252. 6.039 1,278.661 ↓ 1.0 4,237 1

Hash Join (cost=51,562.35..70,339.4 rows=4,236 width=312) (actual time=766.238..1,278.661 rows=4,237 loops=1)

  • Buffers: shared hit=155634 read=1805 dirtied=1, temp read=1144 written=1146
253. 7.375 1,044.589 ↓ 1.0 4,237 1

Hash Join (cost=42,394.28..61,160.2 rows=4,236 width=296) (actual time=538.171..1,044.589 rows=4,237 loops=1)

  • Buffers: shared hit=92378 read=1587 dirtied=1, temp read=1144 written=1146
254. 8.964 938.504 ↓ 1.0 4,237 1

Nested Loop (cost=33,248.09..52,002.88 rows=4,236 width=280) (actual time=439.433..938.504 rows=4,237 loops=1)

  • Buffers: shared hit=42204 read=1395, temp read=1144 written=1146
255. 38.919 819.378 ↓ 1.0 4,237 1

Merge Join (cost=33,247.67..49,938.43 rows=4,236 width=276) (actual time=438.548..819.378 rows=4,237 loops=1)

  • Buffers: shared hit=27343 read=21, temp read=1144 written=1146
256. 9.763 77.949 ↓ 1.0 4,237 1

Nested Loop (cost=0.7..8,263.57 rows=4,236 width=220) (actual time=38.116..77.949 rows=4,237 loops=1)

  • Buffers: shared hit=20813 read=21
257. 47.001 47.001 ↓ 1.0 4,237 1

Index Scan using claim_lumpsum_claim_id_idx on claim_lumpsum cx_2 (cost=0.28..257.32 rows=4,236 width=151) (actual time=38.088..47.001 rows=4,237 loops=1)

  • Buffers: shared hit=3829 read=21
258. 21.185 21.185 ↑ 1.0 1 4,237

Index Scan using claim_pkey on claim c_3 (cost=0.42..1.89 rows=1 width=69) (actual time=0.005..0.005 rows=1 loops=4,237)

  • Index Cond: (c_3.claim_id = cx_2.claim_id)
  • Buffers: shared hit=16984
259. 229.053 702.510 ↓ 1.0 223,068 1

Aggregate (cost=33,246.96..38,846.53 rows=222,284 width=72) (actual time=400.411..702.51 rows=223,068 loops=1)

  • Buffers: shared hit=6530, temp read=1144 written=1146
260. 363.243 473.457 ↑ 1.0 224,846 1

Sort (cost=33,246.96..33,809.75 rows=225,115 width=47) (actual time=400.398..473.457 rows=224,846 loops=1)

  • Sort Key: claim_transaction_5.claim_id
  • Sort Method: external merge Disk: 9152kB
  • Buffers: shared hit=6530, temp read=1144 written=1146
261. 110.214 110.214 ↑ 1.0 225,068 1

Seq Scan on claim_transaction claim_transaction_5 (cost=0..8,781.15 rows=225,115 width=47) (actual time=0.007..110.214 rows=225,068 loops=1)

  • Buffers: shared hit=6530
262. 110.162 110.162 ↑ 1.0 1 4,237

Index Scan using claim_transaction_claim_id_idx on claim_transaction claim_transaction_4 (cost=0.42..0.49 rows=1 width=20) (actual time=0.026..0.026 rows=1 loops=4,237)

  • Index Cond: (c_3.claim_id = claim_transaction_4.claim_id)
  • Buffers: shared hit=14861 read=1374
263. 1.543 98.710 ↓ 85.3 3,755 1

Hash (cost=9,145.65..9,145.65 rows=44 width=32) (actual time=98.71..98.71 rows=3,755 loops=1)

  • Buffers: shared hit=50174 read=192 dirtied=1
264. 1.015 97.167 ↓ 85.3 3,755 1

Subquery Scan on ccb_2 (cost=9,144.99..9,145.65 rows=44 width=32) (actual time=94.04..97.167 rows=3,755 loops=1)

  • Buffers: shared hit=50174 read=192 dirtied=1
265. 1.417 96.152 ↓ 85.3 3,755 1

Unique (cost=9,144.99..9,145.21 rows=44 width=33) (actual time=94.037..96.152 rows=3,755 loops=1)

  • Buffers: shared hit=50174 read=192 dirtied=1
266. 3.808 94.735 ↓ 92.1 4,052 1

Sort (cost=9,144.99..9,145.1 rows=44 width=33) (actual time=94.034..94.735 rows=4,052 loops=1)

  • Sort Key: clp_3.claim_lumpsum_id, ((cav_3.contract_state = 'TERMINATED'::enum.contract_state))
  • Sort Method: quicksort Memory: 413kB
  • Buffers: shared hit=50174 read=192 dirtied=1
267. 2.122 90.927 ↓ 92.1 4,052 1

Nested Loop (cost=8,446.12..9,143.78 rows=44 width=33) (actual time=29.934..90.927 rows=4,052 loops=1)

  • Buffers: shared hit=50174 read=192 dirtied=1
268. 16.376 48.285 ↓ 92.1 4,052 1

Hash Join (cost=8,445.83..9,122.87 rows=44 width=32) (actual time=29.916..48.285 rows=4,052 loops=1)

  • Buffers: shared hit=17490 read=115 dirtied=1
269. 2.063 2.063 ↓ 1.0 11,886 1

Seq Scan on contract_renew_version crv_3 (cost=0..521.8 rows=11,880 width=46) (actual time=0.013..2.063 rows=11,886 loops=1)

  • Buffers: shared hit=403
270. 1.559 29.846 ↑ 1.0 3,770 1

Hash (cost=8,396.61..8,396.61 rows=3,938 width=46) (actual time=29.846..29.846 rows=3,770 loops=1)

  • Buffers: shared hit=17087 read=115 dirtied=1
271. 2.035 28.287 ↑ 1.0 3,770 1

Hash Join (cost=228.29..8,396.61 rows=3,938 width=46) (actual time=3.664..28.287 rows=3,770 loops=1)

  • Buffers: shared hit=17087 read=115 dirtied=1
272. 3.960 23.015 ↓ 1.0 4,237 1

Nested Loop (cost=0.42..8,157.61 rows=4,236 width=34) (actual time=0.354..23.015 rows=4,237 loops=1)

  • Buffers: shared hit=16978 read=115 dirtied=1
273. 2.107 2.107 ↓ 1.0 4,237 1

Seq Scan on claim_lumpsum clp_3 (cost=0..151.36 rows=4,236 width=46) (actual time=0.182..2.107 rows=4,237 loops=1)

  • Buffers: shared hit=1 read=108 dirtied=1
274. 16.948 16.948 ↑ 1.0 1 4,237

Index Scan using claim_pkey on claim cl_3 (cost=0.42..1.89 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=4,237)

  • Index Cond: (cl_3.claim_id = clp_3.claim_id)
  • Buffers: shared hit=16977 read=7
275. 1.523 3.237 ↓ 1.0 5,286 1

Hash (cost=161.83..161.83 rows=5,283 width=20) (actual time=3.237..3.237 rows=5,286 loops=1)

  • Buffers: shared hit=109
276. 1.714 1.714 ↓ 1.0 5,286 1

Seq Scan on contract_family cf_3 (cost=0..161.83 rows=5,283 width=20) (actual time=0.008..1.714 rows=5,286 loops=1)

  • Buffers: shared hit=109
277. 40.520 40.520 ↑ 1.0 1 4,052

Index Scan using contract_amend_version_contract_renew_version_id_idx1 on contract_amend_version cav_3 (cost=0.29..0.46 rows=1 width=36) (actual time=0.008..0.01 rows=1 loops=4,052)

  • Index Cond: (cav_3.contract_renew_version_id = crv_3.contract_renew_version_id)
  • Filter: (cav_3.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED,TERMINATED}'::enum.contract_state[]))
  • Buffers: shared hit=32684 read=77
278. 0.948 228.033 ↓ 126.1 3,532 1

Hash (cost=9,167.72..9,167.72 rows=28 width=32) (actual time=228.033..228.033 rows=3,532 loops=1)

  • Buffers: shared hit=63256 read=218
279. 0.825 227.085 ↓ 126.1 3,532 1

Subquery Scan on conx_1 (cost=9,167.3..9,167.72 rows=28 width=32) (actual time=214.544..227.085 rows=3,532 loops=1)

  • Buffers: shared hit=63256 read=218
280. 6.876 226.260 ↓ 126.1 3,532 1

Unique (cost=9,167.3..9,167.44 rows=28 width=49) (actual time=214.541..226.26 rows=3,532 loops=1)

  • Buffers: shared hit=63256 read=218
281. 42.806 219.384 ↓ 1,525.3 42,709 1

Sort (cost=9,167.3..9,167.37 rows=28 width=49) (actual time=214.539..219.384 rows=42,709 loops=1)

  • Sort Key: clp_4.claim_lumpsum_id, ((cav_4.contract_state = 'TERMINATED'::enum.contract_state))
  • Sort Method: quicksort Memory: 4873kB
  • Buffers: shared hit=63256 read=218
282. 16.651 176.578 ↓ 1,525.3 42,709 1

Nested Loop (cost=8,446.53..9,166.63 rows=28 width=49) (actual time=34.889..176.578 rows=42,709 loops=1)

  • Buffers: shared hit=63256 read=218
283. 2.052 99.147 ↓ 92.1 4,052 1

Nested Loop (cost=8,446.12..9,143.67 rows=44 width=52) (actual time=33.039..99.147 rows=4,052 loops=1)

  • Buffers: shared hit=50366
284. 21.117 56.575 ↓ 92.1 4,052 1

Hash Join (cost=8,445.83..9,122.87 rows=44 width=48) (actual time=33.02..56.575 rows=4,052 loops=1)

  • Buffers: shared hit=17605
285. 2.517 2.517 ↓ 1.0 11,886 1

Seq Scan on contract_renew_version crv_4 (cost=0..521.8 rows=11,880 width=46) (actual time=0.018..2.517 rows=11,886 loops=1)

  • Buffers: shared hit=403
286. 2.159 32.941 ↑ 1.0 3,770 1

Hash (cost=8,396.61..8,396.61 rows=3,938 width=62) (actual time=32.941..32.941 rows=3,770 loops=1)

  • Buffers: shared hit=17202
287. 2.535 30.782 ↑ 1.0 3,770 1

Hash Join (cost=228.29..8,396.61 rows=3,938 width=62) (actual time=3.87..30.782 rows=3,770 loops=1)

  • Buffers: shared hit=17202
288. 2.477 24.490 ↓ 1.0 4,237 1

Nested Loop (cost=0.42..8,157.61 rows=4,236 width=50) (actual time=0.04..24.49 rows=4,237 loops=1)

  • Buffers: shared hit=17093
289. 0.828 0.828 ↓ 1.0 4,237 1

Seq Scan on claim_lumpsum clp_4 (cost=0..151.36 rows=4,236 width=62) (actual time=0.009..0.828 rows=4,237 loops=1)

  • Buffers: shared hit=109
290. 21.185 21.185 ↑ 1.0 1 4,237

Index Scan using claim_pkey on claim cl_4 (cost=0.42..1.89 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=4,237)

  • Index Cond: (cl_4.claim_id = clp_4.claim_id)
  • Buffers: shared hit=16984
291. 1.695 3.757 ↓ 1.0 5,286 1

Hash (cost=161.83..161.83 rows=5,283 width=20) (actual time=3.757..3.757 rows=5,286 loops=1)

  • Buffers: shared hit=109
292. 2.062 2.062 ↓ 1.0 5,286 1

Seq Scan on contract_family cf_4 (cost=0..161.83 rows=5,283 width=20) (actual time=0.009..2.062 rows=5,286 loops=1)

  • Buffers: shared hit=109
293. 40.520 40.520 ↑ 1.0 1 4,052

Index Scan using contract_amend_version_contract_renew_version_id_idx1 on contract_amend_version cav_4 (cost=0.29..0.46 rows=1 width=36) (actual time=0.007..0.01 rows=1 loops=4,052)

  • Index Cond: (cav_4.contract_renew_version_id = crv_4.contract_renew_version_id)
  • Filter: (cav_4.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED,TERMINATED}'::enum.contract_state[]))
  • Buffers: shared hit=32761
294. 60.780 60.780 ↓ 11.0 11 4,052

Index Only Scan using contract_lumpsum_contract_id_lumpsum_id_contract_lumpsum_de_key on contract_lumpsum cp (cost=0.41..0.51 rows=1 width=32) (actual time=0.012..0.015 rows=11 loops=4,052)

  • Index Cond: ((cp.contract_id = cav_4.contract_amend_version_id) AND (cp.lumpsum_id = clp_4.lumpsum_id))
  • Buffers: shared hit=12890 read=218
295. 1.410 3.413 ↓ 1.0 5,286 1

Hash (cost=161.83..161.83 rows=5,283 width=20) (actual time=3.413..3.413 rows=5,286 loops=1)

  • Buffers: shared hit=109
296. 2.003 2.003 ↓ 1.0 5,286 1

Seq Scan on contract_family contract_family_2 (cost=0..161.83 rows=5,283 width=20) (actual time=0.014..2.003 rows=5,286 loops=1)

  • Buffers: shared hit=109
297. 127.110 127.110 ↑ 1.0 1 4,237

Index Scan using invoice_pkey on invoice i_2 (cost=0.42..0.47 rows=1 width=48) (actual time=0.03..0.03 rows=1 loops=4,237)

  • Index Cond: (i_2.invoice_id = c_3.invoice_id)
  • Buffers: shared hit=15870 read=1118
298. 5.442 10.510 ↓ 1.0 18,839 1

Hash (cost=1,024.3..1,024.3 rows=18,830 width=32) (actual time=10.51..10.51 rows=18,839 loops=1)

  • Buffers: shared hit=836
299. 5.068 5.068 ↓ 1.0 18,839 1

Seq Scan on contract_amend_version contract_amend_version_2 (cost=0..1,024.3 rows=18,830 width=32) (actual time=0.008..5.068 rows=18,839 loops=1)

  • Buffers: shared hit=836
300. 2.918 5.286 ↓ 1.0 11,886 1

Hash (cost=367.49..367.49 rows=11,880 width=16) (actual time=5.286..5.286 rows=11,886 loops=1)

  • Buffers: shared hit=127
301. 2.368 2.368 ↓ 1.0 11,886 1

Index Only Scan using contract_version_pkey on contract_renew_version contract_renew_version_2 (cost=0.29..367.49 rows=11,880 width=16) (actual time=0.02..2.368 rows=11,886 loops=1)

  • Buffers: shared hit=127
302. 25.422 25.422 ↑ 1.0 1 4,237

Index Scan using claim_amounts_materialized_pkey on claim_amounts_materialized claim_amounts_materialized_2 (cost=0.42..1.89 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=4,237)

  • Index Cond: (cx_2.claim_id = claim_amounts_materialized_2.claim_id)
  • Buffers: shared hit=16972 read=7
303. 8.474 101.688 ↓ 0.0 0 4,237

Limit (cost=1.7..1.71 rows=1 width=60) (actual time=0.024..0.024 rows=0 loops=4,237)

  • Buffers: shared hit=8511 read=43
304. 21.185 93.214 ↓ 0.0 0 4,237

Sort (cost=1.7..1.71 rows=2 width=60) (actual time=0.022..0.022 rows=0 loops=4,237)

  • Sort Key: (upper(claim_lumpsum_reason_code.active_range)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=8511 read=43
305. 72.029 72.029 ↑ 2.0 1 4,237

Index Only Scan using claim_lumpsum_reason_code_active_range_excl on claim_lumpsum_reason_code claim_lumpsum_reason_code (cost=0.14..1.69 rows=2 width=60) (actual time=0.016..0.017 rows=1 loops=4,237)

  • Index Cond: (claim_lumpsum_reason_code.claim_lumpsum_id = cx_2.claim_lumpsum_id)
  • Buffers: shared hit=8511 read=43
306. 18.780 253.651 ↑ 1.3 46,097 1

Hash (cost=26,957.08..26,957.08 rows=58,536 width=24) (actual time=253.651..253.651 rows=46,097 loops=1)

  • Buffers: shared hit=12123
307. 10.353 234.871 ↑ 1.3 46,097 1

Subquery Scan on cshpend_2 (cost=25,786.36..26,957.08 rows=58,536 width=24) (actual time=206.213..234.871 rows=46,097 loops=1)

  • Buffers: shared hit=12123
308. 54.847 224.518 ↑ 1.3 46,097 1

Aggregate (cost=25,786.36..26,371.72 rows=58,536 width=24) (actual time=206.211..224.518 rows=46,097 loops=1)

  • Buffers: shared hit=12123
309. 169.671 169.671 ↓ 1.0 66,166 1

Seq Scan on claim_state_history claim_state_history_6 (cost=0..25,457.74 rows=65,725 width=24) (actual time=0.01..169.671 rows=66,166 loops=1)

  • Filter: (claim_state_history_6.claim_state = 'PENDED'::enum.claim_state)
  • Buffers: shared hit=12123
310. 124.041 1,098.068 ↓ 1.5 223,699 1

Aggregate (cost=0.43..49,262.62 rows=151,049 width=24) (actual time=0.033..1,098.068 rows=223,699 loops=1)

  • Buffers: shared hit=818027
311. 974.027 974.027 ↓ 1.0 225,451 1

Index Scan using claim_state_history_claim_id_idx on claim_state_history claim_state_history_7 (cost=0.43..46,633.49 rows=223,727 width=24) (actual time=0.015..974.027 rows=225,451 loops=1)

  • Filter: (claim_state_history_7.claim_state = 'COMPLETE'::enum.claim_state)
  • Buffers: shared hit=818027
312. 142.078 775.605 ↓ 1.4 238,410 1

Aggregate (cost=0.43..49,852.36 rows=175,495 width=24) (actual time=0.011..775.605 rows=238,410 loops=1)

  • Buffers: shared hit=818025
313. 633.527 633.527 ↑ 1.0 291,776 1

Index Scan using claim_state_history_claim_id_idx on claim_state_history claim_state_history_8 (cost=0.43..46,633.49 rows=292,783 width=24) (actual time=0.007..633.527 rows=291,776 loops=1)

  • Filter: (claim_state_history_8.claim_state = 'SUBMITTED'::enum.claim_state)
  • Buffers: shared hit=818025
314. 6.319 12.604 ↑ 1.0 22,634 1

Hash (cost=424.87..424.87 rows=22,687 width=16) (actual time=12.604..12.604 rows=22,634 loops=1)

  • Buffers: shared read=198
315. 6.285 6.285 ↑ 1.0 22,634 1

Seq Scan on claim_pend_reason_tolerance_passed_check_log cprtpcl_1 (cost=0..424.87 rows=22,687 width=16) (actual time=0.52..6.285 rows=22,634 loops=1)

  • Buffers: shared read=198
316. 0.114 10.055 ↑ 1.4 184 1

Hash (cost=197.48..197.48 rows=263 width=80) (actual time=10.055..10.055 rows=184 loops=1)

  • Buffers: shared hit=40 read=33
317. 0.160 9.941 ↑ 1.4 184 1

Hash Join (cost=148.36..197.48 rows=263 width=80) (actual time=8.776..9.941 rows=184 loops=1)

  • Buffers: shared hit=40 read=33
318. 1.765 2.103 ↓ 2.7 184 1

Bitmap Heap Scan on claim_lumpsum_reason_code cprc_override_1 (cost=2.33..48.32 rows=67 width=32) (actual time=1.08..2.103 rows=184 loops=1)

  • Buffers: shared hit=40 read=31
319. 0.338 0.338 ↓ 2.7 184 1

Bitmap Index Scan on claim_lumpsum_reason_code_active_range_excl (cost=0..2.32 rows=67 width=0) (actual time=0.338..0.338 rows=184 loops=1)

  • Index Cond: ((cprc_override_1.claim_reason_type = 'OVERRIDE'::enum.claim_reason_type) AND (cprc_override_1.active_range @> now()))
  • Buffers: shared hit=38
320. 0.064 7.678 ↑ 4.0 115 1

Hash (cost=140.33..140.33 rows=456 width=80) (actual time=7.678..7.678 rows=115 loops=1)

  • Buffers: shared read=2
321. 0.445 7.614 ↑ 4.0 115 1

Hash Join (cost=104.61..140.33 rows=456 width=80) (actual time=7.047..7.614 rows=115 loops=1)

  • Buffers: shared read=2
322. 6.862 6.862 ↓ 1.6 1,226 1

Foreign Scan (cost=100..133.61 rows=787 width=80) (actual time=6.706..6.862 rows=1,226 loops=1)

323. 0.038 0.307 ↑ 1.0 116 1

Hash (cost=3.16..3.16 rows=116 width=32) (actual time=0.307..0.307 rows=116 loops=1)

  • Buffers: shared read=2
324. 0.269 0.269 ↑ 1.0 116 1

Seq Scan on account_client account_client_1 (cost=0..3.16 rows=116 width=32) (actual time=0.233..0.269 rows=116 loops=1)

  • Buffers: shared read=2
325. 25.296 59.121 ↑ 1.0 50,343 1

Hash (cost=2,061.93..2,061.93 rows=50,693 width=32) (actual time=59.121..59.121 rows=50,343 loops=1)

  • Buffers: shared hit=1555
326. 33.825 33.825 ↓ 1.0 50,727 1

Seq Scan on import_invoice ii_2 (cost=0..2,061.93 rows=50,693 width=32) (actual time=0.011..33.825 rows=50,727 loops=1)

  • Buffers: shared hit=1555
327. 167.310 167.310 ↓ 0.0 0 4,290

Index Scan using import_file_new_pkey on import_file import_file_1 (cost=0.41..0.52 rows=1 width=24) (actual time=0.039..0.039 rows=0 loops=4,290)

  • Index Cond: (ii_2.file_id = import_file_1.file_id)
  • Buffers: shared hit=2539 read=323
328. 4.290 90.090 ↓ 0.0 0 4,290

Limit (cost=1.81..1.82 rows=1 width=56) (actual time=0.021..0.021 rows=0 loops=4,290)

  • Buffers: shared hit=12796
329. 17.160 85.800 ↓ 0.0 0 4,290

Sort (cost=1.81..1.82 rows=1 width=56) (actual time=0.02..0.02 rows=0 loops=4,290)

  • Sort Key: (lower(claim_reason_code_2.active_range)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=12796
330. 68.640 68.640 ↓ 0.0 0 4,290

Index Only Scan using claim_reason_code_active_range_excl on claim_reason_code claim_reason_code_2 (cost=0.28..1.8 rows=1 width=56) (actual time=0.016..0.016 rows=0 loops=4,290)

  • Index Cond: (claim_reason_code_2.claim_id = c_3.claim_id)
  • Buffers: shared hit=12796