explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nOv3

Settings
# exclusive inclusive rows x rows loops node
1. 101,508.868 577,822.788 ↑ 3.8 5,464,665 1

Nested Loop (cost=1,364,823.78..57,362,451.5 rows=21,016,285 width=1,135) (actual time=84,522.715..577,822.788 rows=5,464,665 loops=1)

  • Buffers: shared hit=59567613 read=871886, temp read=66733 written=66735
2. 5,160.771 383,414.615 ↑ 3.8 5,464,665 1

Hash Join (cost=1,364,821.97..13,490,956.56 rows=21,016,285 width=609) (actual time=84,522.025..383,414.615 rows=5,464,665 loops=1)

  • Buffers: shared hit=43304608 read=871073, temp read=66733 written=66735
3. 8,765.449 378,248.138 ↓ 1.0 5,464,665 1

Hash Join (cost=1,364,675.94..13,240,550.82 rows=5,346,248 width=561) (actual time=84,516.291..378,248.138 rows=5,464,665 loops=1)

  • Buffers: shared hit=43304606 read=871073, temp read=66733 written=66735
4. 6,734.911 353,754.290 ↓ 1.0 5,464,665 1

Hash Join (cost=1,223,347.13..13,055,109.09 rows=5,346,248 width=497) (actual time=68,782.811..353,754.29 rows=5,464,665 loops=1)

  • Buffers: shared hit=38175411 read=825794, temp read=66733 written=66735
5. 6,916.216 346,497.562 ↓ 1.0 5,464,665 1

Hash Join (cost=1,190,721.89..13,008,449.71 rows=5,346,248 width=489) (actual time=68,260.619..346,497.562 rows=5,464,665 loops=1)

  • Buffers: shared hit=38163288 read=825794, temp read=66733 written=66735
6. 6,103.784 339,121.537 ↓ 1.0 5,464,665 1

Hash Join (cost=1,159,236.43..12,962,930.1 rows=5,346,248 width=481) (actual time=67,800.432..339,121.537 rows=5,464,665 loops=1)

  • Buffers: shared hit=38151165 read=825794, temp read=66733 written=66735
7. 8,957.256 332,731.649 ↓ 1.0 5,464,665 1

Nested Loop (cost=1,131,547.65..12,921,207.17 rows=5,346,248 width=473) (actual time=67,514.219..332,731.649 rows=5,464,665 loops=1)

  • Buffers: shared hit=38151165 read=813671, temp read=66733 written=66735
8. 6,613.402 181,693.103 ↓ 1.0 5,464,665 1

Nested Loop (cost=1,131,545.68..2,255,442.41 rows=5,346,248 width=437) (actual time=67,513.006..181,693.103 rows=5,464,665 loops=1)

  • Buffers: shared hit=16336477 read=780660, temp read=66733 written=66735
9. 5,017.178 158,685.706 ↓ 1.0 5,464,665 1

Hash Join (cost=1,131,545.68..1,720,817.61 rows=5,346,248 width=441) (actual time=67,512.93..158,685.706 rows=5,464,665 loops=1)

  • Buffers: shared hit=16336378 read=780660, temp read=66733 written=66735
10. 6,504.404 153,612.299 ↓ 1.0 5,464,665 1

Hash Join (cost=1,122,665.68..1,697,903.46 rows=5,346,248 width=433) (actual time=67,456.674..153,612.299 rows=5,464,665 loops=1)

  • Buffers: shared hit=16329848 read=780660, temp read=66733 written=66735
11. 7,196.357 146,630.858 ↓ 1.0 5,464,665 1

Hash Join (cost=1,104,429.17..1,665,632.81 rows=5,346,248 width=385) (actual time=66,979.195..146,630.858 rows=5,464,665 loops=1)

  • Buffers: shared hit=16323318 read=780660, temp read=66733 written=66735
12. 6,076.181 138,580.967 ↓ 1.0 5,464,665 1

Hash Join (cost=1,049,719.16..1,596,888.89 rows=5,346,248 width=369) (actual time=66,125.458..138,580.967 rows=5,464,665 loops=1)

  • Buffers: shared hit=16323317 read=752666, temp read=66733 written=66735
13. 4,993.181 132,359.054 ↓ 1.0 5,464,665 1

Hash Join (cost=1,041,177.05..1,574,312.63 rows=5,346,248 width=357) (actual time=65,979.349..132,359.054 rows=5,464,665 loops=1)

  • Buffers: shared hit=16323315 read=749526, temp read=66733 written=66735
14. 6,440.177 127,353.581 ↓ 1.0 5,360,208 1

Hash Join (cost=1,040,468.59..1,535,141.32 rows=5,346,248 width=357) (actual time=65,966.988..127,353.581 rows=5,360,208 loops=1)

  • Buffers: shared hit=16323315 read=749328, temp read=66733 written=66735
15. 5,055.678 120,216.539 ↓ 1.0 5,360,208 1

Hash Join (cost=1,021,921.42..1,443,083.25 rows=5,346,248 width=297) (actual time=65,269.745..120,216.539 rows=5,360,208 loops=1)

  • Buffers: shared hit=16319814 read=744527, temp read=66733 written=66735
16. 5,669.220 115,150.117 ↓ 1.0 5,360,208 1

Hash Join (cost=1,021,405.44..1,428,528.36 rows=5,346,248 width=297) (actual time=65,258.938..115,150.117 rows=5,360,208 loops=1)

  • Buffers: shared hit=16319809 read=744405, temp read=66733 written=66735
17. 8,478.416 109,472.008 ↓ 1.0 5,360,208 1

Hash Join (cost=1,020,145.76..1,413,231.62 rows=5,346,248 width=281) (actual time=65,249.977..109,472.008 rows=5,360,208 loops=1)

  • Buffers: shared hit=16318973 read=744405, temp read=66733 written=66735
18. 5,463.596 60,410.734 ↓ 1.0 5,360,208 1

Hash Join (cost=514,817.61..893,869.55 rows=5,346,248 width=249) (actual time=24,667.071..60,410.734 rows=5,360,208 loops=1)

  • Buffers: shared hit=16240 read=496210, temp read=28557 written=28558
19. 5,219.200 54,918.336 ↓ 1.0 5,360,208 1

Hash Join (cost=512,793.05..870,997.1 rows=5,346,248 width=245) (actual time=24,638.244..54,918.336 rows=5,360,208 loops=1)

  • Buffers: shared hit=15197 read=496210, temp read=28557 written=28558
20. 4,856.052 49,547.679 ↓ 1.0 5,360,208 1

Hash Join (cost=501,230.91..845,400.82 rows=5,346,248 width=241) (actual time=24,485.468..49,547.679 rows=5,360,208 loops=1)

  • Buffers: shared hit=8667 read=496210, temp read=28557 written=28558
21. 5,317.682 44,688.672 ↓ 1.0 5,360,208 1

Hash Join (cost=501,003.04..831,128.58 rows=5,346,248 width=221) (actual time=24,482.479..44,688.672 rows=5,360,208 loops=1)

  • Buffers: shared hit=8558 read=496210, temp read=28557 written=28558
22. 9,059.520 39,215.563 ↓ 1.0 5,360,208 1

Hash Join (cost=492,482.73..808,574.13 rows=5,346,248 width=152) (actual time=24,326.605..39,215.563 rows=5,360,208 loops=1)

  • Buffers: shared hit=5443 read=496210, temp read=28557 written=28558
23. 5,829.500 5,829.500 ↓ 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.012..5,829.5 rows=5,360,208 loops=1)

  • Buffers: shared hit=15 read=248580
24. 1,679.867 24,326.543 ↓ 467.3 5,152,835 1

Hash (cost=492,344.9..492,344.9 rows=11,027 width=32) (actual time=24,326.542..24,326.543 rows=5,152,835 loops=1)

  • Buffers: shared hit=5428 read=247630, temp read=28557 written=28558
25. 1,022.740 22,646.676 ↓ 467.3 5,152,835 1

Subquery Scan on ccb (cost=492,179.49..492,344.9 rows=11,027 width=32) (actual time=19,466.718..22,646.676 rows=5,152,835 loops=1)

  • Buffers: shared hit=5428 read=247630, temp read=28557 written=28558
26. 1,170.189 21,623.936 ↓ 467.3 5,152,835 1

Unique (cost=492,179.49..492,234.63 rows=11,027 width=33) (actual time=19,466.715..21,623.936 rows=5,152,835 loops=1)

  • Buffers: shared hit=5428 read=247630, temp read=28557 written=28558
27. 6,371.762 20,453.747 ↓ 492.4 5,429,559 1

Sort (cost=492,179.49..492,207.06 rows=11,027 width=33) (actual time=19,466.713..20,453.747 rows=5,429,559 loops=1)

  • Sort Key: clp.claim_product_id, ((cav.contract_state = 'TERMINATED'::enum.contract_state))
  • Sort Method: external merge Disk: 228456kB
  • Buffers: shared hit=5428 read=247630, temp read=28557 written=28558
28. 9,807.878 14,081.985 ↓ 492.4 5,429,559 1

Hash Join (cost=10,745.03..491,439.1 rows=11,027 width=33) (actual time=147.678..14,081.985 rows=5,429,559 loops=1)

  • Buffers: shared hit=5428 read=247630
29. 2,621.765 4,247.450 ↓ 1.0 5,360,208 1

Hash Join (cost=8,520.31..324,611.94 rows=5,346,248 width=24) (actual time=120.979..4,247.45 rows=5,360,208 loops=1)

  • Buffers: shared hit=4080 read=247630
30. 1,505.098 1,505.098 ↓ 1.0 5,360,208 1

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

  • Buffers: shared hit=965 read=247630
31. 63.692 120.587 ↑ 1.0 240,067 1

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

  • Buffers: shared hit=3115
32. 56.895 56.895 ↑ 1.0 240,067 1

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

  • Buffers: shared hit=3115
33. 3.136 26.657 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=1348
34. 5.465 23.521 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=1348
35. 4.916 15.697 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=1239
36. 5.220 5.220 ↓ 1.0 11,727 1

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

  • Filter: (cav.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED,TERMINATED}'::enum.contract_state[]))
  • Buffers: shared hit=836
37. 2.863 5.561 ↓ 1.0 11,886 1

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

  • Buffers: shared hit=403
38. 2.698 2.698 ↓ 1.0 11,886 1

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

  • Buffers: shared hit=403
39. 1.079 2.359 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=109
40. 1.280 1.280 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=109
41. 86.087 155.427 ↑ 1.0 240,067 1

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

  • Buffers: shared hit=3115
42. 69.340 69.340 ↑ 1.0 240,067 1

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

  • Buffers: shared hit=3115
43. 1.380 2.955 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=109
44. 1.575 1.575 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=109
45. 66.996 151.457 ↑ 1.0 222,371 1

Hash (cost=8,781.15..8,781.15 rows=222,479 width=20) (actual time=151.457..151.457 rows=222,371 loops=1)

  • Buffers: shared hit=6530
46. 84.461 84.461 ↑ 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.01..84.461 rows=222,371 loops=1)

  • Filter: (claim_transaction.void_date IS NULL)
  • Buffers: shared hit=6530
47. 1.385 28.802 ↓ 66.9 5,286 1

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

  • Buffers: shared hit=1043
48. 1.905 27.417 ↓ 66.9 5,286 1

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

  • Buffers: shared hit=1043
49. 1.157 25.496 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=1042
50. 2.908 24.271 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=1032
51. 0.689 0.689 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=109
52. 10.705 20.674 ↓ 1.0 39,279 1

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

  • Buffers: shared hit=923
53. 9.969 9.969 ↓ 1.0 39,279 1

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

  • Buffers: shared hit=923
54. 0.026 0.068 ↑ 1.0 90 1

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

  • Buffers: shared hit=10
55. 0.042 0.042 ↑ 1.0 90 1

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

  • Buffers: shared hit=10
56. 0.004 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
57. 0.012 0.012 ↑ 1.0 3 1

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

  • Buffers: shared hit=1
58. 1,667.021 40,582.858 ↓ 1,427.1 5,017,750 1

Hash (cost=505,284.21..505,284.21 rows=3,516 width=48) (actual time=40,582.858..40,582.858 rows=5,017,750 loops=1)

  • Buffers: shared hit=16302733 read=248195, temp read=38176 written=38177
59. 1,055.803 38,915.837 ↓ 1,427.1 5,017,750 1

Subquery Scan on conx (cost=505,231.47..505,284.21 rows=3,516 width=48) (actual time=35,684.485..38,915.837 rows=5,017,750 loops=1)

  • Buffers: shared hit=16302733 read=248195, temp read=38176 written=38177
60. 1,180.298 37,860.034 ↓ 1,427.1 5,017,750 1

Unique (cost=505,231.47..505,249.05 rows=3,516 width=49) (actual time=35,684.481..37,860.034 rows=5,017,750 loops=1)

  • Buffers: shared hit=16302733 read=248195, temp read=38176 written=38177
61. 6,878.849 36,679.736 ↓ 1,504.6 5,290,190 1

Sort (cost=505,231.47..505,240.26 rows=3,516 width=49) (actual time=35,684.477..36,679.736 rows=5,290,190 loops=1)

  • Sort Key: clp_1.claim_product_id, ((cav_1.contract_state = 'TERMINATED'::enum.contract_state))
  • Sort Method: external merge Disk: 305408kB
  • Buffers: shared hit=16302733 read=248195, temp read=38176 written=38177
62. 3,934.714 29,800.887 ↓ 1,504.6 5,290,190 1

Nested Loop (cost=10,745.45..505,024.38 rows=3,516 width=49) (actual time=161.927..29,800.887 rows=5,290,190 loops=1)

  • Buffers: shared hit=16302733 read=248195
63. 10,268.471 15,007.055 ↓ 492.4 5,429,559 1

Hash Join (cost=10,745.03..491,411.53 rows=11,027 width=52) (actual time=161.89..15,007.055 rows=5,429,559 loops=1)

  • Buffers: shared hit=5460 read=247598
64. 2,911.146 4,708.962 ↓ 1.0 5,360,208 1

Hash Join (cost=8,520.31..324,611.94 rows=5,346,248 width=40) (actual time=132.154..4,708.962 rows=5,360,208 loops=1)

  • Buffers: shared hit=4112 read=247598
65. 1,667.230 1,667.230 ↓ 1.0 5,360,208 1

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

  • Buffers: shared hit=997 read=247598
66. 72.719 130.586 ↑ 1.0 240,067 1

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

  • Buffers: shared hit=3115
67. 57.867 57.867 ↑ 1.0 240,067 1

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

  • Buffers: shared hit=3115
68. 4.087 29.622 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=1348
69. 5.601 25.535 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=1348
70. 5.684 17.360 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=1239
71. 5.670 5.670 ↓ 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.012..5.67 rows=11,727 loops=1)

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

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

  • Buffers: shared hit=403
73. 2.638 2.638 ↓ 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.638 rows=11,886 loops=1)

  • Buffers: shared hit=403
74. 1.360 2.574 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=109
75. 1.214 1.214 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=109
76. 10,859.118 10,859.118 ↑ 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.002..0.002 rows=1 loops=5,429,559)

  • Index Cond: ((contract_product_materialized.contract_id = cav_1.contract_amend_version_id) AND (contract_product_materialized.product_id = clp_1.product_id))
  • Buffers: shared hit=16297273 read=597
77. 4.315 8.889 ↓ 1.0 18,839 1

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

  • Buffers: shared hit=836
78. 4.574 4.574 ↓ 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.02..4.574 rows=18,839 loops=1)

  • Buffers: shared hit=836
79. 2.686 10.744 ↓ 1.0 11,886 1

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

  • Buffers: shared hit=5 read=122
80. 8.058 8.058 ↓ 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.058..8.058 rows=11,886 loops=1)

  • Buffers: shared hit=5 read=122
81. 71.854 696.865 ↓ 1.0 178,844 1

Hash (cost=16,312.2..16,312.2 rows=178,797 width=76) (actual time=696.865..696.865 rows=178,844 loops=1)

  • Buffers: shared hit=3501 read=4801
82. 73.792 625.011 ↓ 1.0 178,844 1

Hash Join (cost=8,500.84..16,312.2 rows=178,797 width=76) (actual time=115.389..625.011 rows=178,844 loops=1)

  • Buffers: shared hit=3501 read=4801
83. 78.720 551.205 ↓ 1.0 178,844 1

Hash Join (cost=8,499.77..15,178.75 rows=178,797 width=88) (actual time=115.355..551.205 rows=178,844 loops=1)

  • Buffers: shared hit=3500 read=4801
84. 104.455 451.946 ↓ 1.0 178,844 1

Hash Join (cost=6,693.01..12,902.6 rows=178,797 width=72) (actual time=94.705..451.946 rows=178,844 loops=1)

  • Buffers: shared hit=2577 read=4801
85. 253.038 253.038 ↓ 1.0 178,844 1

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

  • Buffers: shared hit=2 read=3246
86. 14.805 94.453 ↑ 1.0 50,343 1

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

  • Buffers: shared hit=2575 read=1555
87. 20.943 79.648 ↓ 1.0 50,727 1

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

  • Buffers: shared hit=2575 read=1555
88. 26.456 26.456 ↓ 1.0 50,727 1

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

  • Buffers: shared read=1555
89. 13.638 32.249 ↓ 1.0 57,383 1

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

  • Buffers: shared hit=2575
90. 18.611 18.611 ↓ 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.01..18.611 rows=57,383 loops=1)

  • Buffers: shared hit=2575
91. 9.814 20.539 ↓ 1.0 39,279 1

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

  • Buffers: shared hit=923
92. 10.725 10.725 ↓ 1.0 39,279 1

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

  • Buffers: shared hit=923
93. 0.002 0.014 ↑ 1.0 3 1

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

  • Buffers: shared hit=1
94. 0.012 0.012 ↑ 1.0 3 1

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

  • Buffers: shared hit=1
95. 5.933 12.292 ↑ 1.0 22,634 1

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

  • Buffers: shared read=198
96. 6.359 6.359 ↑ 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.214..6.359 rows=22,634 loops=1)

  • Buffers: shared read=198
97. 62.839 145.732 ↓ 1.0 240,067 1

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

  • Buffers: shared hit=2 read=3140
98. 82.893 82.893 ↓ 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.167..82.893 rows=240,067 loops=1)

  • Buffers: shared hit=2 read=3140
99. 110.324 853.534 ↓ 3.0 248,996 1

Hash (cost=53,674.27..53,674.27 rows=82,859 width=32) (actual time=853.534..853.534 rows=248,996 loops=1)

  • Buffers: shared hit=1 read=27994
100. 532.853 743.210 ↓ 3.0 248,996 1

Bitmap Heap Scan on claim_product_reason_code cprc_override (cost=6,130.02..53,674.27 rows=82,859 width=32) (actual time=216.076..743.21 rows=248,996 loops=1)

  • Buffers: shared hit=1 read=27994
101. 210.357 210.357 ↓ 3.0 248,996 1

Bitmap Index Scan on claim_product_reason_code_current_idx (cost=0..6,109.31 rows=82,859 width=0) (actual time=210.357..210.357 rows=248,996 loops=1)

  • Index Cond: (cprc_override.claim_reason_type = 'OVERRIDE'::enum.claim_reason_type)
  • Buffers: shared read=2504
102. 83.313 477.037 ↑ 1.0 222,371 1

Hash (cost=15,455.52..15,455.52 rows=222,479 width=64) (actual time=477.037..477.037 rows=222,371 loops=1)

  • Buffers: shared hit=6530
103. 50.026 393.724 ↑ 1.0 222,371 1

Subquery Scan on ct (cost=11,005.94..15,455.52 rows=222,479 width=64) (actual time=216.307..393.724 rows=222,371 loops=1)

  • Buffers: shared hit=6530
104. 268.450 343.698 ↑ 1.0 222,371 1

Aggregate (cost=11,005.94..13,230.73 rows=222,479 width=64) (actual time=216.305..343.698 rows=222,371 loops=1)

  • Buffers: shared hit=6530
105. 75.248 75.248 ↑ 1.0 222,371 1

Seq Scan on claim_transaction claim_transaction_1 (cost=0..8,781.15 rows=222,479 width=39) (actual time=0.024..75.248 rows=222,371 loops=1)

  • Filter: (claim_transaction_1.void_date IS NULL)
  • Buffers: shared hit=6530
106. 0.600 56.229 ↑ 1.0 2,575 1

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

  • Buffers: shared hit=6530
107. 0.528 55.629 ↑ 1.0 2,575 1

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

  • Buffers: shared hit=6530
108. 1.773 55.101 ↑ 1.0 2,575 1

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

  • Buffers: shared hit=6530
109. 53.328 53.328 ↓ 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.071..53.328 rows=2,697 loops=1)

  • Filter: (claim_transaction_2.void_date IS NOT NULL)
  • Buffers: shared hit=6530
110. 5,464.665 16,393.995 ↑ 1.0 1 5,464,665

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

  • Filter: (cx.claim_product_id = cpri.claim_product_id)
  • Buffers: shared hit=99
111. 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=99
112. 0.000 142,081.290 ↓ 0.0 0 5,464,665

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

  • Buffers: shared hit=21814688 read=33011
113. 16,393.995 142,081.290 ↓ 0.0 0 5,464,665

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

  • Sort Key: (lower(claim_product_reason_code.active_range)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=21814688 read=33011
114. 125,687.295 125,687.295 ↓ 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.023..0.023 rows=0 loops=5,464,665)

  • Index Cond: (claim_product_reason_code.claim_product_id = cx.claim_product_id)
  • Filter: upper_inf(claim_product_reason_code.active_range)
  • Buffers: shared hit=21814600 read=33010
115. 12.181 286.104 ↑ 1.3 46,097 1

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

  • Buffers: shared read=12123
116. 8.616 273.923 ↑ 1.3 46,097 1

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

  • Buffers: shared read=12123
117. 47.672 265.307 ↑ 1.3 46,097 1

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

  • Buffers: shared read=12123
118. 217.635 217.635 ↓ 1.0 66,166 1

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

  • Filter: (claim_state_history.claim_state = 'PENDED'::enum.claim_state)
  • Buffers: shared read=12123
119. 67.155 459.809 ↓ 1.5 223,920 1

Hash (cost=29,597.35..29,597.35 rows=151,049 width=24) (actual time=459.809..459.809 rows=223,920 loops=1)

  • Buffers: shared hit=12123
120. 41.235 392.654 ↓ 1.5 223,920 1

Subquery Scan on cshcomp (cost=26,576.37..29,597.35 rows=151,049 width=24) (actual time=276.193..392.654 rows=223,920 loops=1)

  • Buffers: shared hit=12123
121. 195.447 351.419 ↓ 1.5 223,920 1

Aggregate (cost=26,576.37..28,086.86 rows=151,049 width=24) (actual time=276.192..351.419 rows=223,920 loops=1)

  • Buffers: shared hit=12123
122. 155.972 155.972 ↓ 1.0 225,673 1

Seq Scan on claim_state_history claim_state_history_1 (cost=0..25,457.74 rows=223,727 width=24) (actual time=0.02..155.972 rows=225,673 loops=1)

  • Filter: (claim_state_history_1.claim_state = 'COMPLETE'::enum.claim_state)
  • Buffers: shared hit=12123
123. 75.188 521.817 ↓ 1.4 238,639 1

Hash (cost=30,431.55..30,431.55 rows=175,495 width=24) (actual time=521.817..521.817 rows=238,639 loops=1)

  • Buffers: shared hit=12123
124. 43.549 446.629 ↓ 1.4 238,639 1

Subquery Scan on cshsubmitted (cost=26,921.65..30,431.55 rows=175,495 width=24) (actual time=322.379..446.629 rows=238,639 loops=1)

  • Buffers: shared hit=12123
125. 238.343 403.080 ↓ 1.4 238,639 1

Aggregate (cost=26,921.65..28,676.6 rows=175,495 width=24) (actual time=322.378..403.08 rows=238,639 loops=1)

  • Buffers: shared hit=12123
126. 164.737 164.737 ↑ 1.0 292,071 1

Seq Scan on claim_state_history claim_state_history_2 (cost=0..25,457.74 rows=292,783 width=24) (actual time=0.018..164.737 rows=292,071 loops=1)

  • Filter: (claim_state_history_2.claim_state = 'SUBMITTED'::enum.claim_state)
  • Buffers: shared hit=12123
127. 1,349.243 15,728.399 ↓ 2.8 2,265,951 1

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

  • Buffers: shared hit=5129195 read=45279
128. 2,604.865 14,379.156 ↓ 2.8 2,265,951 1

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

  • Buffers: shared hit=5129195 read=45279
129. 471.411 11,771.705 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5129193 read=45233
130. 520.481 11,297.800 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5129190 read=45217
131. 273.104 10,067.395 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5125634 read=36676
132. 268.935 9,794.288 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5125633 read=36676
133. 322.605 9,525.348 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5125632 read=36676
134. 199.788 9,201.762 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5125629 read=36674
135. 632.234 4,358.322 ↓ 101.1 1,547,884 1

Merge Join (cost=50,645.51..50,798.67 rows=15,311 width=87) (actual time=3,203.789..4,358.322 rows=1,547,884 loops=1)

  • Buffers: shared hit=57 read=25525
136. 1,633.596 3,708.787 ↓ 101.1 1,547,884 1

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

  • Sort Key: price_default_1.price_id
  • Sort Method: quicksort Memory: 266824kB
  • Buffers: shared hit=57 read=25495
137. 566.643 2,075.191 ↓ 101.1 1,547,884 1

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

  • Buffers: shared hit=57 read=25495
138. 700.866 1,506.977 ↓ 99.4 1,547,884 1

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

  • Buffers: shared hit=54 read=25485
139. 196.987 803.904 ↑ 1.0 1,547,884 1

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

  • Buffers: shared hit=6 read=25485
140. 0.557 0.557 ↑ 1.0 599 1

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

  • Buffers: shared hit=2 read=6
141. 604.549 604.549 ↓ 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=0.19..604.549 rows=1,542,993 loops=1)

  • Buffers: shared hit=2 read=25410
142. 0.007 0.007 ↓ 0.0 0 1

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

143. 0.003 0.003 ↓ 0.0 0 1

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

144. 1.801 1.801 ↑ 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.211..1.801 rows=4,292 loops=1)

  • Buffers: shared hit=2 read=69
145. 1.558 2.207 ↑ 1.0 5,639 1

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

  • Buffers: shared hit=48
146. 0.649 0.649 ↑ 1.0 5,639 1

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

  • Buffers: shared hit=48
147. 0.637 1.571 ↑ 1.0 2,852 1

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

  • Buffers: shared hit=3 read=10
148. 0.934 0.934 ↑ 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.23..0.934 rows=2,852 loops=1)

  • Buffers: shared hit=3 read=10
149. 2.444 17.301 ↓ 3.0 3,205 1

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

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

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

  • Filter: upper_inf(pm.active_range)
  • Buffers: shared read=30
151. 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=5125572 read=11149
152. 0.981 0.981 ↑ 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.223..0.981 rows=599 loops=1)

  • Buffers: shared hit=3 read=2
153. 0.005 0.005 ↓ 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.005..0.005 rows=0 loops=1)

  • Buffers: shared hit=1
154. 0.003 0.003 ↓ 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.003..0.003 rows=0 loops=1)

  • Buffers: shared hit=1
155. 709.924 709.924 ↓ 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.158..709.924 rows=1,542,971 loops=1)

  • Buffers: shared hit=3556 read=8541
156. 0.894 2.494 ↑ 1.0 4,292 1

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

  • Buffers: shared hit=3 read=16
157. 1.600 1.600 ↑ 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.214..1.6 rows=4,292 loops=1)

  • Buffers: shared hit=3 read=16
158. 1.314 2.586 ↑ 1.0 5,639 1

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

  • Buffers: shared hit=2 read=46
159. 1.272 1.272 ↑ 1.0 5,639 1

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

  • Buffers: shared hit=2 read=46
160. 0.059 5.706 ↑ 4.0 115 1

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

  • Buffers: shared hit=2
161. 0.346 5.647 ↑ 4.0 115 1

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

  • Buffers: shared hit=2
162. 5.192 5.192 ↓ 1.6 1,226 1

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

163. 0.028 0.109 ↑ 1.0 116 1

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

  • Buffers: shared hit=2
164. 0.081 0.081 ↑ 1.0 116 1

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

  • Buffers: shared hit=2
165. 5,464.665 92,899.305 ↓ 0.0 0 5,464,665

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

  • Buffers: shared hit=16261039 read=765
166. 10,929.330 87,434.640 ↓ 0.0 0 5,464,665

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

  • Sort Key: (lower(claim_reason_code.active_range)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=16261039 read=765
167. 76,505.310 76,505.310 ↓ 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.014..0.014 rows=0 loops=5,464,665)

  • Index Cond: (claim_reason_code.claim_id = c.claim_id)
  • Buffers: shared hit=16260997 read=764