explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8x1U

Settings
# exclusive inclusive rows x rows loops node
1. 87,915.642 318,459.054 ↑ 3.8 5,464,665 1

Hash Join (cost=1,524,689.28..8,267,200.39 rows=21,016,285 width=1,135) (actual time=100,716.659..318,459.054 rows=5,464,665 loops=1)

  • Buffers: shared hit=22097132 read=874004 written=9, temp read=66733 written=66735
2. 5,023.152 230,536.085 ↓ 1.0 5,464,665 1

Hash Join (cost=1,524,543.25..2,762,723.4 rows=5,346,248 width=577) (actual time=100,709.207..230,536.085 rows=5,464,665 loops=1)

  • Buffers: shared hit=22095304 read=873914 written=9, temp read=66733 written=66735
3. 7,387.373 225,424.518 ↓ 1.0 5,464,665 1

Hash Join (cost=1,518,992.39..2,743,138.39 rows=5,346,248 width=561) (actual time=100,620.708..225,424.518 rows=5,464,665 loops=1)

  • Buffers: shared hit=22095280 read=873159 written=9, temp read=66733 written=66735
4. 5,701.470 201,124.892 ↓ 1.0 5,464,665 1

Hash Join (cost=1,377,663.58..2,557,696.67 rows=5,346,248 width=497) (actual time=83,703.277..201,124.892 rows=5,464,665 loops=1)

  • Buffers: shared hit=16966111 read=827854 written=9, temp read=66733 written=66735
5. 5,720.231 194,850.341 ↓ 1.0 5,464,665 1

Hash Join (cost=1,345,038.34..2,511,037.28 rows=5,346,248 width=489) (actual time=83,128.812..194,850.341 rows=5,464,665 loops=1)

  • Buffers: shared hit=16953988 read=827854 written=9, temp read=66733 written=66735
6. 5,091.994 188,619.134 ↓ 1.0 5,464,665 1

Hash Join (cost=1,313,552.88..2,465,517.67 rows=5,346,248 width=481) (actual time=82,616.499..188,619.134 rows=5,464,665 loops=1)

  • Buffers: shared hit=16941865 read=827854 written=9, temp read=66733 written=66735
7. 6,050.356 183,233.308 ↓ 1.0 5,464,665 1

Hash Join (cost=1,285,864.1..2,423,794.74 rows=5,346,248 width=473) (actual time=82,322.326..183,233.308 rows=5,464,665 loops=1)

  • Buffers: shared hit=16941865 read=815731 written=9, temp read=66733 written=66735
8. 7,231.864 175,012.820 ↓ 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=80,150.753..175,012.82 rows=5,464,665 loops=1)

  • Buffers: shared hit=16318748 read=798341 written=9, temp read=66733 written=66735
9. 4,208.000 156,851.626 ↓ 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=80,150.489..156,851.626 rows=5,464,665 loops=1)

  • Buffers: shared hit=16318705 read=798341 written=9, temp read=66733 written=66735
10. 5,775.957 152,585.023 ↓ 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=80,091.862..152,585.023 rows=5,464,665 loops=1)

  • Buffers: shared hit=16312175 read=798341 written=9, temp read=66733 written=66735
11. 6,030.743 146,313.258 ↓ 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=79,595.665..146,313.258 rows=5,464,665 loops=1)

  • Buffers: shared hit=16305645 read=798341 written=9, temp read=66733 written=66735
12. 5,448.461 139,527.274 ↓ 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=78,840.221..139,527.274 rows=5,464,665 loops=1)

  • Buffers: shared hit=16305645 read=770346 written=9, temp read=66733 written=66735
13. 3,979.136 133,912.516 ↓ 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=78,673.538..133,912.516 rows=5,464,665 loops=1)

  • Buffers: shared hit=16305645 read=767204 written=9, temp read=66733 written=66735
14. 5,687.960 129,922.411 ↓ 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=78,662.506..129,922.411 rows=5,360,208 loops=1)

  • Buffers: shared hit=16305645 read=767006 written=9, temp read=66733 written=66735
15. 4,540.374 123,680.623 ↓ 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=78,108.305..123,680.623 rows=5,360,208 loops=1)

  • Buffers: shared hit=16304721 read=759628 written=9, temp read=66733 written=66735
16. 4,791.563 119,129.043 ↓ 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=78,097.037..119,129.043 rows=5,360,208 loops=1)

  • Buffers: shared hit=16304717 read=759505 written=9, temp read=66733 written=66735
17. 7,373.440 114,328.471 ↓ 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=78,087.955..114,328.471 rows=5,360,208 loops=1)

  • Buffers: shared hit=16303881 read=759505 written=9, temp read=66733 written=66735
18. 4,665.884 57,689.003 ↓ 1.0 5,360,208 1

Hash Join (cost=514,817.61..893,869.55 rows=5,346,248 width=249) (actual time=28,821.881..57,689.003 rows=5,360,208 loops=1)

  • Buffers: shared hit=6369 read=506089 written=2, temp read=28557 written=28558
19. 4,325.320 52,968.429 ↓ 1.0 5,360,208 1

Hash Join (cost=512,793.05..870,997.1 rows=5,346,248 width=245) (actual time=28,767.169..52,968.429 rows=5,360,208 loops=1)

  • Buffers: shared hit=6255 read=505160 written=2, temp read=28557 written=28558
20. 4,184.296 48,419.495 ↓ 1.0 5,360,208 1

Hash Join (cost=501,230.91..845,400.82 rows=5,346,248 width=241) (actual time=28,542.193..48,419.495 rows=5,360,208 loops=1)

  • Buffers: shared hit=6255 read=498630, temp read=28557 written=28558
21. 4,510.942 44,231.414 ↓ 1.0 5,360,208 1

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

  • Buffers: shared hit=6252 read=498524, temp read=28557 written=28558
22. 7,779.501 39,543.825 ↓ 1.0 5,360,208 1

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

  • Buffers: shared hit=3137 read=498524, temp read=28557 written=28558
23. 3,403.162 3,403.162 ↓ 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.125..3,403.162 rows=5,360,208 loops=1)

  • Buffers: shared read=248595
24. 1,837.025 28,361.162 ↓ 467.3 5,152,835 1

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

  • Buffers: shared hit=3137 read=249929, temp read=28557 written=28558
25. 1,141.813 26,524.137 ↓ 467.3 5,152,835 1

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

  • Buffers: shared hit=3137 read=249929, temp read=28557 written=28558
26. 1,273.232 25,382.324 ↓ 467.3 5,152,835 1

Unique (cost=492,179.49..492,234.63 rows=11,027 width=33) (actual time=22,994.874..25,382.324 rows=5,152,835 loops=1)

  • Buffers: shared hit=3137 read=249929, temp read=28557 written=28558
27. 6,971.531 24,109.092 ↓ 492.4 5,429,559 1

Sort (cost=492,179.49..492,207.06 rows=11,027 width=33) (actual time=22,994.871..24,109.092 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=3137 read=249929, temp read=28557 written=28558
28. 10,559.739 17,137.561 ↓ 492.4 5,429,559 1

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

  • Buffers: shared hit=3132 read=249929
29. 2,861.933 6,538.481 ↓ 1.0 5,360,208 1

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

  • Buffers: shared hit=3120 read=248590
30. 3,542.958 3,542.958 ↓ 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.008..3,542.958 rows=5,360,208 loops=1)

  • Buffers: shared hit=5 read=248590
31. 71.894 133.590 ↑ 1.0 240,067 1

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

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

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

  • Buffers: shared hit=3115
33. 3.852 39.341 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=9 read=1339
34. 6.262 35.489 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=9 read=1339
35. 6.206 25.563 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=6 read=1233
36. 10.374 10.374 ↓ 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.161..10.374 rows=11,727 loops=1)

  • Filter: (cav.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED,TERMINATED}'::enum.contract_state[]))
  • Buffers: shared hit=3 read=833
37. 3.104 8.983 ↓ 1.0 11,886 1

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

  • Buffers: shared hit=3 read=400
38. 5.879 5.879 ↓ 1.0 11,886 1

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

  • Buffers: shared hit=3 read=400
39. 1.410 3.664 ↓ 1.0 5,286 1

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

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

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

  • Buffers: shared hit=3 read=106
41. 99.874 176.647 ↑ 1.0 240,067 1

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

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

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

  • Buffers: shared hit=3115
43. 1.409 3.785 ↓ 1.0 5,286 1

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

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

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

  • Buffers: shared hit=3 read=106
45. 87.043 223.614 ↑ 1.0 222,371 1

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

  • Buffers: shared read=6530 written=2
46. 136.571 136.571 ↑ 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.504..136.571 rows=222,371 loops=1)

  • Filter: (claim_transaction.void_date IS NULL)
  • Buffers: shared read=6530 written=2
47. 2.334 54.690 ↓ 66.9 5,286 1

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

  • Buffers: shared hit=114 read=929
48. 3.104 52.356 ↓ 66.9 5,286 1

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

  • Buffers: shared hit=114 read=929
49. 1.893 49.240 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=113 read=929
50. 4.761 46.791 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=111 read=921
51. 1.101 1.101 ↓ 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..1.101 rows=5,286 loops=1)

  • Buffers: shared hit=109
52. 18.531 40.929 ↓ 1.0 39,279 1

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

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

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

  • Buffers: shared hit=2 read=921
54. 0.070 0.556 ↑ 1.0 90 1

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

  • Buffers: shared hit=2 read=8
55. 0.486 0.486 ↑ 1.0 90 1

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

  • Buffers: shared hit=2 read=8
56. 0.005 0.012 ↑ 1.0 3 1

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

  • Buffers: shared hit=1
57. 0.007 0.007 ↑ 1.0 3 1

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

  • Buffers: shared hit=1
58. 1,821.159 49,266.028 ↓ 1,427.1 5,017,750 1

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

  • Buffers: shared hit=16297512 read=253416 written=7, temp read=38176 written=38177
59. 1,144.487 47,444.869 ↓ 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=43,888.723..47,444.869 rows=5,017,750 loops=1)

  • Buffers: shared hit=16297512 read=253416 written=7, temp read=38176 written=38177
60. 1,311.248 46,300.382 ↓ 1,427.1 5,017,750 1

Unique (cost=505,231.47..505,249.05 rows=3,516 width=49) (actual time=43,888.72..46,300.382 rows=5,017,750 loops=1)

  • Buffers: shared hit=16297512 read=253416 written=7, temp read=38176 written=38177
61. 7,777.178 44,989.134 ↓ 1,504.6 5,290,190 1

Sort (cost=505,231.47..505,240.26 rows=3,516 width=49) (actual time=43,888.717..44,989.134 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=16297512 read=253416 written=7, temp read=38176 written=38177
62. 1,478.805 37,211.956 ↓ 1,504.6 5,290,190 1

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

  • Buffers: shared hit=16297512 read=253416 written=7
63. 11,304.254 19,444.474 ↓ 492.4 5,429,559 1

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

  • Buffers: shared hit=4499 read=248559
64. 3,247.385 8,111.022 ↓ 1.0 5,360,208 1

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

  • Buffers: shared hit=3151 read=248559
65. 4,712.418 4,712.418 ↓ 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.01..4,712.418 rows=5,360,208 loops=1)

  • Buffers: shared hit=36 read=248559
66. 84.665 151.219 ↑ 1.0 240,067 1

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

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

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

  • Buffers: shared hit=3115
68. 3.905 29.198 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=1348
69. 5.356 25.293 ↓ 1.0 11,727 1

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

  • Buffers: shared hit=1348
70. 5.730 17.307 ↓ 1.0 11,727 1

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

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

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

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

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

  • Buffers: shared hit=403
74. 1.282 2.630 ↓ 1.0 5,286 1

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

  • Buffers: shared hit=109
75. 1.348 1.348 ↓ 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.348 rows=5,286 loops=1)

  • Buffers: shared hit=109
76. 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_1.contract_amend_version_id) AND (contract_product_materialized.product_id = clp_1.product_id))
  • Buffers: shared hit=16293013 read=4857 written=7
77. 4.325 9.009 ↓ 1.0 18,839 1

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

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

  • Buffers: shared hit=836
79. 2.749 11.206 ↓ 1.0 11,886 1

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

  • Buffers: shared hit=4 read=123
80. 8.457 8.457 ↓ 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.29..8.457 rows=11,886 loops=1)

  • Buffers: shared hit=4 read=123
81. 82.715 553.828 ↓ 1.0 178,844 1

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

  • Buffers: shared hit=924 read=7378
82. 79.756 471.113 ↓ 1.0 178,844 1

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

  • Buffers: shared hit=924 read=7378
83. 90.092 391.344 ↓ 1.0 178,844 1

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

  • Buffers: shared hit=923 read=7378
84. 118.970 279.721 ↓ 1.0 178,844 1

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

  • Buffers: shared read=7378
85. 56.873 56.873 ↓ 1.0 178,844 1

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

  • Buffers: shared read=3248
86. 15.189 103.878 ↑ 1.0 50,343 1

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

  • Buffers: shared read=4130
87. 21.497 88.689 ↓ 1.0 50,727 1

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

  • Buffers: shared read=4130
88. 27.166 27.166 ↓ 1.0 50,727 1

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

  • Buffers: shared read=1555
89. 14.781 40.026 ↓ 1.0 57,383 1

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

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

  • Buffers: shared read=2575
91. 10.548 21.531 ↓ 1.0 39,279 1

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

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

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

  • Buffers: shared hit=923
93. 0.003 0.013 ↑ 1.0 3 1

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

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

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

  • Buffers: shared hit=1
95. 5.400 10.969 ↑ 1.0 22,634 1

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

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

  • Buffers: shared read=198
97. 73.376 166.297 ↓ 1.0 240,067 1

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

  • Buffers: shared read=3142
98. 92.921 92.921 ↓ 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.157..92.921 rows=240,067 loops=1)

  • Buffers: shared read=3142
99. 119.932 755.241 ↓ 3.0 248,996 1

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

  • Buffers: shared read=27995
100. 538.968 635.309 ↓ 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=102.027..635.309 rows=248,996 loops=1)

  • Buffers: shared read=27995
101. 96.341 96.341 ↓ 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=96.341..96.341 rows=248,996 loops=1)

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

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

  • Buffers: shared hit=6530
103. 47.731 414.780 ↑ 1.0 222,371 1

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

  • Buffers: shared hit=6530
104. 283.236 367.049 ↑ 1.0 222,371 1

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

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

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

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

  • Buffers: shared hit=6530
107. 0.513 58.007 ↑ 1.0 2,575 1

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

  • Buffers: shared hit=6530
108. 1.815 57.494 ↑ 1.0 2,575 1

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

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

  • Filter: (claim_transaction_2.void_date IS NOT NULL)
  • Buffers: shared hit=6530
110. 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=43
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.001..0.002 rows=1 loops=5,464,665)

  • Buffers: shared hit=43
112. 181.524 2,170.132 ↑ 1.2 448,432 1

Hash (cost=147,421.11..147,421.11 rows=551,785 width=52) (actual time=2,170.132..2,170.132 rows=448,432 loops=1)

  • Buffers: shared hit=623117 read=17390
113. 97.491 1,988.608 ↑ 1.2 448,432 1

Subquery Scan on cprc (cost=138,338.91..147,421.11 rows=551,785 width=52) (actual time=1,735.934..1,988.608 rows=448,432 loops=1)

  • Buffers: shared hit=623117 read=17390
114. 112.032 1,891.117 ↑ 1.2 448,432 1

Unique (cost=138,338.91..141,903.26 rows=551,785 width=60) (actual time=1,735.932..1,891.117 rows=448,432 loops=1)

  • Buffers: shared hit=623117 read=17390
115. 203.437 1,779.085 ↑ 1.6 448,432 1

Sort (cost=138,338.91..140,121.08 rows=712,870 width=60) (actual time=1,735.929..1,779.085 rows=448,432 loops=1)

  • Sort Key: claim_product_reason_code.claim_product_id, (lower(claim_product_reason_code.active_range)) DESC
  • Sort Method: quicksort Memory: 66269kB
  • Buffers: shared hit=623117 read=17390
116. 1,575.648 1,575.648 ↑ 1.6 448,432 1

Index Only Scan using claim_product_reason_code_key on claim_product_reason_code claim_product_reason_code (cost=0.56..69,036.26 rows=712,870 width=60) (actual time=0.677..1,575.648 rows=448,432 loops=1)

  • Filter: upper_inf(claim_product_reason_code.active_range)
  • Buffers: shared hit=623114 read=17390
117. 14.991 293.832 ↑ 1.3 46,097 1

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

  • Buffers: shared read=12123
118. 8.982 278.841 ↑ 1.3 46,097 1

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

  • Buffers: shared read=12123
119. 50.643 269.859 ↑ 1.3 46,097 1

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

  • Buffers: shared read=12123
120. 219.216 219.216 ↓ 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.509..219.216 rows=66,166 loops=1)

  • Filter: (claim_state_history.claim_state = 'PENDED'::enum.claim_state)
  • Buffers: shared read=12123
121. 76.830 510.976 ↓ 1.5 223,920 1

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

  • Buffers: shared hit=12123
122. 43.557 434.146 ↓ 1.5 223,920 1

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

  • Buffers: shared hit=12123
123. 214.193 390.589 ↓ 1.5 223,920 1

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

  • Buffers: shared hit=12123
124. 176.396 176.396 ↓ 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.016..176.396 rows=225,673 loops=1)

  • Filter: (claim_state_history_1.claim_state = 'COMPLETE'::enum.claim_state)
  • Buffers: shared hit=12123
125. 87.782 573.081 ↓ 1.4 238,639 1

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

  • Buffers: shared hit=12123
126. 49.197 485.299 ↓ 1.4 238,639 1

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

  • Buffers: shared hit=12123
127. 259.991 436.102 ↓ 1.4 238,639 1

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

  • Buffers: shared hit=12123
128. 176.111 176.111 ↑ 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.015..176.111 rows=292,071 loops=1)

  • Filter: (claim_state_history_2.claim_state = 'SUBMITTED'::enum.claim_state)
  • Buffers: shared hit=12123
129. 1,469.423 16,912.253 ↓ 2.8 2,265,951 1

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

  • Buffers: shared hit=5129169 read=45305
130. 2,779.433 15,442.830 ↓ 2.8 2,265,951 1

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

  • Buffers: shared hit=5129169 read=45305
131. 502.777 12,660.548 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5129167 read=45259
132. 559.946 12,155.869 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5129167 read=45240
133. 285.316 10,899.171 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5125615 read=36695
134. 287.998 10,613.844 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5125615 read=36694
135. 348.607 10,325.834 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5125615 read=36693
136. 528.324 9,976.420 ↓ 2.2 895,582 1

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

  • Buffers: shared hit=5125615 read=36688
137. 686.179 4,804.444 ↓ 101.1 1,547,884 1

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

  • Buffers: shared hit=51 read=25531
138. 1,826.043 4,114.333 ↓ 101.1 1,547,884 1

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

  • Sort Key: price_default_1.price_id
  • Sort Method: quicksort Memory: 266824kB
  • Buffers: shared hit=51 read=25501
139. 646.046 2,288.290 ↓ 101.1 1,547,884 1

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

  • Buffers: shared hit=51 read=25501
140. 797.405 1,640.802 ↓ 99.4 1,547,884 1

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

  • Buffers: shared hit=48 read=25491
141. 221.898 841.170 ↑ 1.0 1,547,884 1

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

  • Buffers: shared read=25491
142. 0.434 0.434 ↑ 1.0 599 1

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

  • Buffers: shared read=8
143. 617.143 617.143 ↓ 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.136..617.143 rows=1,542,993 loops=1)

  • Buffers: shared read=25412
144. 0.005 0.005 ↓ 0.0 0 1

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

145. 0.002 0.002 ↓ 0.0 0 1

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

146. 1.688 1.688 ↑ 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.123..1.688 rows=4,292 loops=1)

  • Buffers: shared read=71
147. 1.563 2.227 ↑ 1.0 5,639 1

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

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

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

  • Buffers: shared hit=48
149. 0.683 1.442 ↑ 1.0 2,852 1

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

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

  • Buffers: shared hit=3 read=10
151. 2.653 3.932 ↓ 3.0 3,205 1

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

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

Seq Scan on price_modified pm (cost=0..70.1 rows=1,069 width=27) (actual time=0.146..1.279 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=5125564 read=11157
154. 0.807 0.807 ↑ 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.807 rows=599 loops=1)

  • Buffers: shared read=5
155. 0.012 0.012 ↓ 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.012..0.012 rows=0 loops=1)

  • Buffers: shared read=1
156. 0.011 0.011 ↓ 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.011..0.011 rows=0 loops=1)

  • Buffers: shared read=1
157. 696.752 696.752 ↓ 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.175..696.752 rows=1,542,971 loops=1)

  • Buffers: shared hit=3552 read=8545
158. 0.907 1.902 ↑ 1.0 4,292 1

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

  • Buffers: shared read=19
159. 0.995 0.995 ↑ 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.155..0.995 rows=4,292 loops=1)

  • Buffers: shared read=19
160. 1.537 2.849 ↑ 1.0 5,639 1

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

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

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

  • Buffers: shared hit=2 read=46
162. 8.508 88.415 ↓ 1.0 28,695 1

Hash (cost=5,192.21..5,192.21 rows=28,692 width=48) (actual time=88.415..88.415 rows=28,695 loops=1)

  • Buffers: shared hit=24 read=755
163. 6.870 79.907 ↓ 1.0 28,695 1

Subquery Scan on crc (cost=4,696.22..5,192.21 rows=28,692 width=48) (actual time=54.868..79.907 rows=28,695 loops=1)

  • Buffers: shared hit=24 read=755
164. 10.776 73.037 ↓ 1.0 28,695 1

Unique (cost=4,696.22..4,905.29 rows=28,692 width=56) (actual time=54.866..73.037 rows=28,695 loops=1)

  • Buffers: shared hit=24 read=755
165. 34.715 62.261 ↓ 1.0 41,817 1

Sort (cost=4,696.22..4,800.76 rows=41,813 width=56) (actual time=54.862..62.261 rows=41,817 loops=1)

  • Sort Key: claim_reason_code.claim_id, (lower(claim_reason_code.active_range)) DESC
  • Sort Method: quicksort Memory: 4870kB
  • Buffers: shared hit=24 read=755
166. 27.546 27.546 ↓ 1.0 41,817 1

Seq Scan on claim_reason_code claim_reason_code (cost=0..1,486.73 rows=41,813 width=56) (actual time=0.183..27.546 rows=41,817 loops=1)

  • Buffers: shared hit=24 read=755
167. 0.046 7.327 ↑ 4.0 115 1

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

  • Buffers: shared read=2
168. 0.227 7.281 ↑ 4.0 115 1

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

  • Buffers: shared read=2
169. 6.976 6.976 ↓ 1.6 1,226 1

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

170. 0.028 0.078 ↑ 1.0 116 1

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

  • Buffers: shared read=2
171. 0.050 0.050 ↑ 1.0 116 1

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

  • Buffers: shared read=2