explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gps

Settings
# exclusive inclusive rows x rows loops node
1. 95,428.286 329,937.045 ↑ 3.9 5,370,199 1

Hash Join (cost=1,527,418.36..8,209,702.9 rows=20,945,417 width=1,135) (actual time=95,723.544..329,937.045 rows=5,370,199 loops=1)

  • Buffers: shared hit=22176980 read=867554 dirtied=8431, temp read=66890 written=66892
2. 5,204.328 234,504.096 ↓ 1.0 5,370,199 1

Hash Join (cost=1,527,272.33..2,723,786.8 rows=5,328,220 width=577) (actual time=95,718.589..234,504.096 rows=5,370,199 loops=1)

  • Buffers: shared hit=22173180 read=867537 dirtied=8431, temp read=66890 written=66892
3. 7,859.636 229,202.726 ↓ 1.0 5,370,199 1

Hash Join (cost=1,521,713.26..2,704,240.92 rows=5,328,220 width=561) (actual time=95,621.474..229,202.726 rows=5,370,199 loops=1)

  • Buffers: shared hit=22171296 read=866780 dirtied=8430, temp read=66890 written=66892
4. 5,981.202 203,567.875 ↓ 1.0 5,370,199 1

Hash Join (cost=1,380,534.63..2,519,093.24 rows=5,328,220 width=497) (actual time=77,841.207..203,567.875 rows=5,370,199 loops=1)

  • Buffers: shared hit=17039648 read=827480 dirtied=8430, temp read=66890 written=66892
5. 6,196.163 197,029.168 ↓ 1.0 5,370,199 1

Hash Join (cost=1,347,952.45..2,472,524.23 rows=5,328,220 width=489) (actual time=77,282.324..197,029.168 rows=5,370,199 loops=1)

  • Buffers: shared hit=17027516 read=827480 dirtied=8430, temp read=66890 written=66892
6. 5,165.860 190,358.618 ↓ 1.0 5,370,199 1

Hash Join (cost=1,316,455.92..2,427,040.88 rows=5,328,220 width=481) (actual time=76,806.581..190,358.618 rows=5,370,199 loops=1)

  • Buffers: shared hit=17015384 read=827480 dirtied=8430, temp read=66890 written=66892
7. 9,814.870 184,852.393 ↓ 1.0 5,370,199 1

Nested Loop (cost=1,288,728.95..2,385,327.09 rows=5,328,220 width=473) (actual time=76,465.87..184,852.393 rows=5,370,199 loops=1)

  • Buffers: shared hit=17015384 read=815348 dirtied=8427, temp read=66890 written=66892
8. 6,100.719 164,297.125 ↓ 1.0 5,370,199 1

Hash Join (cost=1,288,728.95..1,852,505.09 rows=5,328,220 width=477) (actual time=76,465.722..164,297.125 rows=5,370,199 loops=1)

  • Buffers: shared hit=17015256 read=815348 dirtied=8427, temp read=66890 written=66892
9. 7,061.968 157,677.317 ↓ 1.0 5,370,199 1

Hash Join (cost=1,270,492.29..1,820,281.6 rows=5,328,220 width=429) (actual time=75,945.256..157,677.317 rows=5,370,199 loops=1)

  • Buffers: shared hit=17008723 read=815348 dirtied=8427, temp read=66890 written=66892
10. 4,890.824 149,782.089 ↓ 1.0 5,370,199 1

Hash Join (cost=1,214,605.27..1,750,407.99 rows=5,328,220 width=413) (actual time=75,111.256..149,782.089 rows=5,370,199 loops=1)

  • Buffers: shared hit=17008633 read=786956 dirtied=8427, temp read=66890 written=66892
11. 6,377.969 144,830.208 ↓ 1.0 5,370,199 1

Hash Join (cost=1,205,716.61..1,727,532.51 rows=5,328,220 width=405) (actual time=75,049.968..144,830.208 rows=5,370,199 loops=1)

  • Buffers: shared hit=17002025 read=786956 dirtied=8427, temp read=66890 written=66892
12. 5,662.598 136,271.147 ↓ 1.0 5,370,199 1

Hash Join (cost=1,048,090.31..1,555,919.63 rows=5,328,220 width=369) (actual time=72,863.073..136,271.147 rows=5,370,199 loops=1)

  • Buffers: shared hit=16346230 read=768320 dirtied=2, temp read=66890 written=66892
13. 6,365.318 130,415.542 ↓ 1.0 5,370,199 1

Hash Join (cost=1,039,546.81..1,533,389.3 rows=5,328,220 width=357) (actual time=72,668.699..130,415.542 rows=5,370,199 loops=1)

  • Buffers: shared hit=16346230 read=765178 dirtied=2, temp read=66890 written=66892
14. 4,656.729 123,360.925 ↓ 1.0 5,370,199 1

Hash Join (cost=1,020,995.03..1,441,574.5 rows=5,328,220 width=297) (actual time=71,978.058..123,360.925 rows=5,370,199 loops=1)

  • Buffers: shared hit=16343650 read=759456 dirtied=2, temp read=66890 written=66892
15. 5,279.388 118,686.417 ↓ 1.0 5,370,199 1

Hash Join (cost=1,020,478.88..1,427,066.78 rows=5,328,220 width=297) (actual time=71,960.161..118,686.417 rows=5,370,199 loops=1)

  • Buffers: shared hit=16343500 read=759333, temp read=66890 written=66892
16. 5,027.985 113,186.464 ↓ 1.0 5,370,199 1

Hash Join (cost=1,008,914.18..1,401,515.27 rows=5,328,220 width=293) (actual time=71,738.19..113,186.464 rows=5,370,199 loops=1)

  • Buffers: shared hit=16343465 read=752835, temp read=66890 written=66892
17. 8,040.367 108,145.368 ↓ 1.0 5,370,199 1

Hash Join (cost=1,007,654.3..1,386,265.66 rows=5,328,220 width=277) (actual time=71,724.837..108,145.368 rows=5,370,199 loops=1)

  • Buffers: shared hit=16342629 read=752835, temp read=66890 written=66892
18. 4,831.662 72,454.316 ↓ 1.0 5,370,199 1

Hash Join (cost=515,628.26..880,253.03 rows=5,328,220 width=261) (actual time=44,074.098..72,454.316 rows=5,370,199 loops=1)

  • Buffers: shared hit=16337175 read=505231, temp read=38267 written=38268
19. 4,584.240 67,588.939 ↓ 1.0 5,370,199 1

Hash Join (cost=513,603.62..857,451.26 rows=5,328,220 width=257) (actual time=44,040.352..67,588.939 rows=5,370,199 loops=1)

  • Buffers: shared hit=16337062 read=504301, temp read=38267 written=38268
20. 5,145.410 63,001.898 ↓ 1.0 5,370,199 1

Hash Join (cost=513,375.69..843,226.3 rows=5,328,220 width=237) (actual time=44,037.52..63,001.898 rows=5,370,199 loops=1)

  • Buffers: shared hit=16336953 read=504301, temp read=38267 written=38268
21. 8,807.943 57,681.612 ↓ 1.0 5,370,199 1

Hash Join (cost=504,859.18..820,722.97 rows=5,328,220 width=168) (actual time=43,861.236..57,681.612 rows=5,370,199 loops=1)

  • Buffers: shared hit=16336947 read=501192, temp read=38267 written=38268
22. 5,012.493 5,012.493 ↓ 1.0 5,370,199 1

Seq Scan on claim_product cx (cost=0..301,877.2 rows=5,328,220 width=136) (actual time=0.012..5,012.493 rows=5,370,199 loops=1)

  • Buffers: shared read=248595
23. 1,703.358 43,861.176 ↓ 1,430.5 5,028,093 1

Hash (cost=504,815.24..504,815.24 rows=3,515 width=48) (actual time=43,861.176..43,861.176 rows=5,028,093 loops=1)

  • Buffers: shared hit=16336947 read=252597, temp read=38267 written=38268
24. 1,072.007 42,157.818 ↓ 1,430.5 5,028,093 1

Subquery Scan on conx (cost=504,762.52..504,815.24 rows=3,515 width=48) (actual time=38,867.762..42,157.818 rows=5,028,093 loops=1)

  • Buffers: shared hit=16336947 read=252597, temp read=38267 written=38268
25. 1,200.030 41,085.811 ↓ 1,430.5 5,028,093 1

Unique (cost=504,762.52..504,780.09 rows=3,515 width=49) (actual time=38,867.759..41,085.811 rows=5,028,093 loops=1)

  • Buffers: shared hit=16336947 read=252597, temp read=38267 written=38268
26. 7,222.914 39,885.781 ↓ 1,508.6 5,302,772 1

Sort (cost=504,762.52..504,771.31 rows=3,515 width=49) (actual time=38,867.756..39,885.781 rows=5,302,772 loops=1)

  • Sort Key: clp.claim_product_id, ((cav.contract_state = 'TERMINATED'::enum.contract_state))
  • Sort Method: external merge Disk: 306136kB
  • Buffers: shared hit=16336947 read=252597, temp read=38267 written=38268
27. 5,657.747 32,662.867 ↓ 1,508.6 5,302,772 1

Nested Loop (cost=10,742.07..504,555.5 rows=3,515 width=49) (actual time=184.566..32,662.867 rows=5,302,772 loops=1)

  • Buffers: shared hit=16336942 read=252597
28. 10,811.524 16,120.824 ↓ 494.0 5,442,148 1

Hash Join (cost=10,741.65..490,955.88 rows=11,017 width=52) (actual time=184.207..16,120.824 rows=5,442,148 loops=1)

  • Buffers: shared hit=5320 read=247741
29. 3,105.809 5,277.523 ↓ 1.0 5,370,199 1

Hash Join (cost=8,516.51..324,380.53 rows=5,328,220 width=40) (actual time=152.318..5,277.523 rows=5,370,199 loops=1)

  • Buffers: shared hit=4074 read=247636
30. 2,020.742 2,020.742 ↓ 1.0 5,370,199 1

Seq Scan on claim_product clp (cost=0..301,877.2 rows=5,328,220 width=52) (actual time=0.014..2,020.742 rows=5,370,199 loops=1)

  • Buffers: shared hit=959 read=247636
31. 85.477 150.972 ↓ 1.0 240,276 1

Hash (cost=5,515.67..5,515.67 rows=240,067 width=20) (actual time=150.971..150.972 rows=240,276 loops=1)

  • Buffers: shared hit=3115
32. 65.495 65.495 ↓ 1.0 240,276 1

Seq Scan on claim cl (cost=0..5,515.67 rows=240,067 width=20) (actual time=0.009..65.495 rows=240,276 loops=1)

  • Buffers: shared hit=3115
33. 4.334 31.777 ↓ 1.0 11,731 1

Hash (cost=2,078.56..2,078.56 rows=11,727 width=38) (actual time=31.777..31.777 rows=11,731 loops=1)

  • Buffers: shared hit=1243 read=105
34. 5.907 27.443 ↓ 1.0 11,731 1

Hash Join (cost=898.37..2,078.56 rows=11,727 width=38) (actual time=9.646..27.443 rows=11,731 loops=1)

  • Buffers: shared hit=1243 read=105
35. 6.255 18.710 ↓ 1.0 11,731 1

Hash Join (cost=670.44..1,819.81 rows=11,727 width=50) (actual time=6.769..18.71 rows=11,731 loops=1)

  • Buffers: shared hit=1239
36. 5.798 5.798 ↓ 1.0 11,731 1

Seq Scan on contract_amend_version cav (cost=0..1,118.59 rows=11,727 width=36) (actual time=0.012..5.798 rows=11,731 loops=1)

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

Hash (cost=521.86..521.86 rows=11,886 width=46) (actual time=6.657..6.657 rows=11,889 loops=1)

  • Buffers: shared hit=403
38. 2.815 2.815 ↓ 1.0 11,889 1

Seq Scan on contract_renew_version crv (cost=0..521.86 rows=11,886 width=46) (actual time=0.008..2.815 rows=11,889 loops=1)

  • Buffers: shared hit=403
39. 1.280 2.826 ↓ 1.0 5,287 1

Hash (cost=161.86..161.86 rows=5,286 width=20) (actual time=2.826..2.826 rows=5,287 loops=1)

  • Buffers: shared hit=4 read=105
40. 1.546 1.546 ↓ 1.0 5,287 1

Seq Scan on contract_family cf (cost=0..161.86 rows=5,286 width=20) (actual time=0.011..1.546 rows=5,287 loops=1)

  • Buffers: shared hit=4 read=105
41. 10,884.296 10,884.296 ↑ 1.0 1 5,442,148

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,442,148)

  • Index Cond: ((contract_product_materialized.contract_id = cav.contract_amend_version_id) AND (contract_product_materialized.product_id = clp.product_id))
  • Buffers: shared hit=16331622 read=4856
42. 101.408 174.876 ↓ 1.0 240,276 1

Hash (cost=5,515.67..5,515.67 rows=240,067 width=69) (actual time=174.876..174.876 rows=240,276 loops=1)

  • Buffers: shared hit=6 read=3109
43. 73.468 73.468 ↓ 1.0 240,276 1

Seq Scan on claim c (cost=0..5,515.67 rows=240,067 width=69) (actual time=0.024..73.468 rows=240,276 loops=1)

  • Buffers: shared hit=6 read=3109
44. 1.228 2.801 ↓ 1.0 5,287 1

Hash (cost=161.86..161.86 rows=5,286 width=24) (actual time=2.8..2.801 rows=5,287 loops=1)

  • Buffers: shared hit=109
45. 1.573 1.573 ↓ 1.0 5,287 1

Seq Scan on contract_family contract_family (cost=0..161.86 rows=5,286 width=24) (actual time=0.012..1.573 rows=5,287 loops=1)

  • Buffers: shared hit=109
46. 1.420 33.715 ↓ 66.9 5,287 1

Hash (cost=2,023.65..2,023.65 rows=79 width=20) (actual time=33.715..33.715 rows=5,287 loops=1)

  • Buffers: shared hit=113 read=930
47. 2.299 32.295 ↓ 66.9 5,287 1

Hash Join (cost=1,819.87..2,023.65 rows=79 width=20) (actual time=24.913..32.295 rows=5,287 loops=1)

  • Buffers: shared hit=113 read=930
48. 2.653 29.985 ↓ 1.0 5,287 1

Hash Join (cost=1,818.8..2,008.41 rows=5,286 width=48) (actual time=24.888..29.985 rows=5,287 loops=1)

  • Buffers: shared hit=112 read=930
49. 1.773 2.652 ↓ 1.0 5,287 1

Hash Join (cost=12.03..187.76 rows=5,286 width=48) (actual time=0.114..2.652 rows=5,287 loops=1)

  • Buffers: shared hit=110 read=9
50. 0.776 0.776 ↓ 1.0 5,287 1

Seq Scan on contract_family c_1 (cost=0..161.86 rows=5,286 width=48) (actual time=0.005..0.776 rows=5,287 loops=1)

  • Buffers: shared hit=109
51. 0.021 0.103 ↑ 1.0 90 1

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

  • Buffers: shared hit=1 read=9
52. 0.082 0.082 ↑ 1.0 90 1

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

  • Buffers: shared hit=1 read=9
53. 11.326 24.680 ↑ 1.0 39,279 1

Hash (cost=1,315.79..1,315.79 rows=39,279 width=32) (actual time=24.68..24.68 rows=39,279 loops=1)

  • Buffers: shared hit=2 read=921
54. 13.354 13.354 ↑ 1.0 39,279 1

Seq Scan on company cmp (cost=0..1,315.79 rows=39,279 width=32) (actual time=0.011..13.354 rows=39,279 loops=1)

  • Buffers: shared hit=2 read=921
55. 0.002 0.011 ↑ 1.0 3 1

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

  • Buffers: shared hit=1
56. 0.009 0.009 ↑ 1.0 3 1

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

  • Buffers: shared hit=1
57. 2,018.443 27,650.685 ↓ 468.7 5,163,167 1

Hash (cost=491,888.33..491,888.33 rows=11,017 width=32) (actual time=27,650.685..27,650.685 rows=5,163,167 loops=1)

  • Buffers: shared hit=5454 read=247604, temp read=28623 written=28624
58. 1,246.816 25,632.242 ↓ 468.7 5,163,167 1

Subquery Scan on ccb (cost=491,723.08..491,888.33 rows=11,017 width=32) (actual time=21,739.35..25,632.242 rows=5,163,167 loops=1)

  • Buffers: shared hit=5454 read=247604, temp read=28623 written=28624
59. 1,397.966 24,385.426 ↓ 468.7 5,163,167 1

Unique (cost=491,723.08..491,778.16 rows=11,017 width=33) (actual time=21,739.347..24,385.426 rows=5,163,167 loops=1)

  • Buffers: shared hit=5454 read=247604, temp read=28623 written=28624
60. 7,361.746 22,987.460 ↓ 494.0 5,442,148 1

Sort (cost=491,723.08..491,750.62 rows=11,017 width=33) (actual time=21,739.343..22,987.46 rows=5,442,148 loops=1)

  • Sort Key: clp_1.claim_product_id, ((cav_1.contract_state = 'TERMINATED'::enum.contract_state))
  • Sort Method: external merge Disk: 228984kB
  • Buffers: shared hit=5454 read=247604, temp read=28623 written=28624
61. 10,511.833 15,625.714 ↓ 494.0 5,442,148 1

Hash Join (cost=10,741.65..490,983.43 rows=11,017 width=33) (actual time=174.052..15,625.714 rows=5,442,148 loops=1)

  • Buffers: shared hit=5454 read=247604
62. 2,848.297 5,085.123 ↓ 1.0 5,370,199 1

Hash Join (cost=8,516.51..324,380.53 rows=5,328,220 width=24) (actual time=145.252..5,085.123 rows=5,370,199 loops=1)

  • Buffers: shared hit=4106 read=247604
63. 2,091.979 2,091.979 ↓ 1.0 5,370,199 1

Seq Scan on claim_product clp_1 (cost=0..301,877.2 rows=5,328,220 width=36) (actual time=0.022..2,091.979 rows=5,370,199 loops=1)

  • Buffers: shared hit=991 read=247604
64. 79.718 144.847 ↓ 1.0 240,276 1

Hash (cost=5,515.67..5,515.67 rows=240,067 width=20) (actual time=144.847..144.847 rows=240,276 loops=1)

  • Buffers: shared hit=3115
65. 65.129 65.129 ↓ 1.0 240,276 1

Seq Scan on claim cl_1 (cost=0..5,515.67 rows=240,067 width=20) (actual time=0.007..65.129 rows=240,276 loops=1)

  • Buffers: shared hit=3115
66. 3.334 28.758 ↓ 1.0 11,731 1

Hash (cost=2,078.56..2,078.56 rows=11,727 width=38) (actual time=28.758..28.758 rows=11,731 loops=1)

  • Buffers: shared hit=1348
67. 5.955 25.424 ↓ 1.0 11,731 1

Hash Join (cost=898.37..2,078.56 rows=11,727 width=38) (actual time=8.677..25.424 rows=11,731 loops=1)

  • Buffers: shared hit=1348
68. 5.508 16.875 ↓ 1.0 11,731 1

Hash Join (cost=670.44..1,819.81 rows=11,727 width=50) (actual time=6.065..16.875 rows=11,731 loops=1)

  • Buffers: shared hit=1239
69. 5.348 5.348 ↓ 1.0 11,731 1

Seq Scan on contract_amend_version cav_1 (cost=0..1,118.59 rows=11,727 width=36) (actual time=0.012..5.348 rows=11,731 loops=1)

  • Filter: (cav_1.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED,TERMINATED}'::enum.contract_state[]))
  • Buffers: shared hit=836
70. 3.186 6.019 ↓ 1.0 11,889 1

Hash (cost=521.86..521.86 rows=11,886 width=46) (actual time=6.019..6.019 rows=11,889 loops=1)

  • Buffers: shared hit=403
71. 2.833 2.833 ↓ 1.0 11,889 1

Seq Scan on contract_renew_version crv_1 (cost=0..521.86 rows=11,886 width=46) (actual time=0.007..2.833 rows=11,889 loops=1)

  • Buffers: shared hit=403
72. 1.218 2.594 ↓ 1.0 5,287 1

Hash (cost=161.86..161.86 rows=5,286 width=20) (actual time=2.593..2.594 rows=5,287 loops=1)

  • Buffers: shared hit=109
73. 1.376 1.376 ↓ 1.0 5,287 1

Seq Scan on contract_family cf_1 (cost=0..161.86 rows=5,286 width=20) (actual time=0.008..1.376 rows=5,287 loops=1)

  • Buffers: shared hit=109
74. 6.986 13.111 ↓ 1.0 18,846 1

Hash (cost=1,024.39..1,024.39 rows=18,839 width=32) (actual time=13.111..13.111 rows=18,846 loops=1)

  • Buffers: shared hit=836
75. 6.125 6.125 ↓ 1.0 18,846 1

Seq Scan on contract_amend_version contract_amend_version (cost=0..1,024.39 rows=18,839 width=32) (actual time=0.023..6.125 rows=18,846 loops=1)

  • Buffers: shared hit=836
76. 85.882 220.565 ↓ 1.0 222,530 1

Hash (cost=8,784.71..8,784.71 rows=222,399 width=20) (actual time=220.565..220.565 rows=222,530 loops=1)

  • Buffers: shared hit=35 read=6498
77. 134.683 134.683 ↓ 1.0 222,530 1

Seq Scan on claim_transaction claim_transaction (cost=0..8,784.71 rows=222,399 width=20) (actual time=0.22..134.683 rows=222,530 loops=1)

  • Filter: (claim_transaction.void_date IS NULL)
  • Buffers: shared hit=35 read=6498
78. 3.636 17.779 ↓ 1.0 11,889 1

Hash (cost=367.58..367.58 rows=11,886 width=16) (actual time=17.779..17.779 rows=11,889 loops=1)

  • Buffers: shared hit=150 read=123 dirtied=2
79. 14.143 14.143 ↓ 1.0 11,889 1

Index Only Scan using contract_version_pkey on contract_renew_version contract_renew_version (cost=0.29..367.58 rows=11,886 width=16) (actual time=0.266..14.143 rows=11,889 loops=1)

  • Buffers: shared hit=150 read=123 dirtied=2
80. 111.955 689.299 ↓ 1.0 178,977 1

Hash (cost=16,316.23..16,316.23 rows=178,844 width=76) (actual time=689.299..689.299 rows=178,977 loops=1)

  • Buffers: shared hit=2580 read=5722
81. 91.728 577.344 ↓ 1.0 178,977 1

Hash Join (cost=8,503.49..16,316.23 rows=178,844 width=76) (actual time=169.225..577.344 rows=178,977 loops=1)

  • Buffers: shared hit=2580 read=5722
82. 105.264 485.600 ↓ 1.0 178,977 1

Hash Join (cost=8,502.42..15,182.48 rows=178,844 width=88) (actual time=169.178..485.6 rows=178,977 loops=1)

  • Buffers: shared hit=2579 read=5722
83. 147.083 352.905 ↓ 1.0 178,977 1

Hash Join (cost=6,695.64..12,906.18 rows=178,844 width=72) (actual time=141.397..352.905 rows=178,977 loops=1)

  • Buffers: shared hit=2577 read=4801
84. 64.995 64.995 ↓ 1.0 178,977 1

Seq Scan on invoice i (cost=0..5,036.44 rows=178,844 width=48) (actual time=0.185..64.995 rows=178,977 loops=1)

  • Buffers: shared hit=2 read=3246
85. 24.405 140.827 ↑ 1.0 50,454 1

Hash (cost=6,061.56..6,061.56 rows=50,727 width=24) (actual time=140.827..140.827 rows=50,454 loops=1)

  • Buffers: shared hit=2575 read=1555
86. 29.315 116.422 ↓ 1.0 50,793 1

Hash Join (cost=3,866.12..6,061.56 rows=50,727 width=24) (actual time=48.836..116.422 rows=50,793 loops=1)

  • Buffers: shared hit=2575 read=1555
87. 38.873 38.873 ↓ 1.0 50,793 1

Seq Scan on import_invoice ii (cost=0..2,062.27 rows=50,727 width=32) (actual time=0.223..38.873 rows=50,793 loops=1)

  • Buffers: shared read=1555
88. 22.790 48.234 ↓ 1.0 57,405 1

Hash (cost=3,148.83..3,148.83 rows=57,383 width=24) (actual time=48.234..48.234 rows=57,405 loops=1)

  • Buffers: shared hit=2575
89. 25.444 25.444 ↓ 1.0 57,405 1

Seq Scan on import_file import_file (cost=0..3,148.83 rows=57,383 width=24) (actual time=0.008..25.444 rows=57,405 loops=1)

  • Buffers: shared hit=2575
90. 13.573 27.431 ↑ 1.0 39,279 1

Hash (cost=1,315.79..1,315.79 rows=39,279 width=32) (actual time=27.431..27.431 rows=39,279 loops=1)

  • Buffers: shared hit=2 read=921
91. 13.858 13.858 ↑ 1.0 39,279 1

Seq Scan on company clac (cost=0..1,315.79 rows=39,279 width=32) (actual time=0.021..13.858 rows=39,279 loops=1)

  • Buffers: shared hit=2 read=921
92. 0.008 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
93. 0.008 0.008 ↑ 1.0 3 1

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

  • Buffers: shared hit=1
94. 92.933 193.007 ↓ 1.0 240,276 1

Hash (cost=5,542.67..5,542.67 rows=240,067 width=28) (actual time=193.007..193.007 rows=240,276 loops=1)

  • Buffers: shared read=3142
95. 100.074 100.074 ↓ 1.0 240,276 1

Seq Scan on claim_amounts_materialized claim_amounts_materialized (cost=0..5,542.67 rows=240,067 width=28) (actual time=0.193..100.074 rows=240,276 loops=1)

  • Buffers: shared read=3142
96. 207.195 2,181.092 ↑ 1.3 448,478 1

Hash (cost=150,590.13..150,590.13 rows=562,893 width=52) (actual time=2,181.092..2,181.092 rows=448,478 loops=1)

  • Buffers: shared hit=655795 read=18636 dirtied=8425
97. 110.035 1,973.897 ↑ 1.3 448,478 1

Subquery Scan on cprc (cost=141,354.47..150,590.13 rows=562,893 width=52) (actual time=1,676.634..1,973.897 rows=448,478 loops=1)

  • Buffers: shared hit=655795 read=18636 dirtied=8425
98. 133.381 1,863.862 ↑ 1.3 448,478 1

Unique (cost=141,354.47..144,961.2 rows=562,893 width=60) (actual time=1,676.632..1,863.862 rows=448,478 loops=1)

  • Buffers: shared hit=655795 read=18636 dirtied=8425
99. 281.908 1,730.481 ↑ 1.6 448,478 1

Sort (cost=141,354.47..143,157.84 rows=721,346 width=60) (actual time=1,676.629..1,730.481 rows=448,478 loops=1)

  • Sort Key: claim_product_reason_code.claim_product_id, (lower(claim_product_reason_code.active_range)) DESC
  • Sort Method: quicksort Memory: 66274kB
  • Buffers: shared hit=655795 read=18636 dirtied=8425
100. 1,448.573 1,448.573 ↑ 1.6 448,478 1

Index Only Scan using claim_product_reason_code_key on claim_product_reason_code claim_product_reason_code (cost=0.56..71,166.31 rows=721,346 width=60) (actual time=0.702..1,448.573 rows=448,478 loops=1)

  • Filter: upper_inf(claim_product_reason_code.active_range)
  • Buffers: shared hit=654000 read=18624 dirtied=8425
101. 0.722 61.057 ↑ 1.1 2,578 1

Hash (cost=8,854.01..8,854.01 rows=2,772 width=24) (actual time=61.057..61.057 rows=2,578 loops=1)

  • Buffers: shared hit=6533
102. 0.452 60.335 ↑ 1.1 2,578 1

Subquery Scan on ct_void (cost=8,798.57..8,854.01 rows=2,772 width=24) (actual time=59.261..60.335 rows=2,578 loops=1)

  • Buffers: shared hit=6533
103. 1.986 59.883 ↑ 1.1 2,578 1

Aggregate (cost=8,798.57..8,826.29 rows=2,772 width=24) (actual time=59.26..59.883 rows=2,578 loops=1)

  • Buffers: shared hit=6533
104. 57.897 57.897 ↑ 1.0 2,700 1

Seq Scan on claim_transaction claim_transaction_1 (cost=0..8,784.71 rows=2,772 width=24) (actual time=0.075..57.897 rows=2,700 loops=1)

  • Filter: (claim_transaction_1.void_date IS NOT NULL)
  • Buffers: shared hit=6533
105. 144.507 833.260 ↓ 2.9 249,033 1

Hash (cost=54,812.72..54,812.72 rows=85,944 width=32) (actual time=833.26..833.26 rows=249,033 loops=1)

  • Buffers: shared hit=90 read=28392
106. 607.581 688.753 ↓ 2.9 249,033 1

Bitmap Heap Scan on claim_product_reason_code cprc_override (cost=6,184.59..54,812.72 rows=85,944 width=32) (actual time=86.902..688.753 rows=249,033 loops=1)

  • Buffers: shared hit=90 read=28392
107. 81.172 81.172 ↓ 2.9 249,033 1

Bitmap Index Scan on claim_product_reason_code_current_idx (cost=0..6,163.1 rows=85,944 width=0) (actual time=81.172..81.172 rows=249,033 loops=1)

  • Index Cond: (cprc_override.claim_reason_type = 'OVERRIDE'::enum.claim_reason_type)
  • Buffers: shared read=2980
108. 88.777 519.089 ↓ 1.0 222,530 1

Hash (cost=15,456.68..15,456.68 rows=222,399 width=64) (actual time=519.089..519.089 rows=222,530 loops=1)

  • Buffers: shared hit=6533
109. 49.011 430.312 ↓ 1.0 222,530 1

Subquery Scan on ct (cost=11,008.7..15,456.68 rows=222,399 width=64) (actual time=248.473..430.312 rows=222,530 loops=1)

  • Buffers: shared hit=6533
110. 296.332 381.301 ↓ 1.0 222,530 1

Aggregate (cost=11,008.7..13,232.69 rows=222,399 width=64) (actual time=248.471..381.301 rows=222,530 loops=1)

  • Buffers: shared hit=6533
111. 84.969 84.969 ↓ 1.0 222,530 1

Seq Scan on claim_transaction claim_transaction_2 (cost=0..8,784.71 rows=222,399 width=39) (actual time=0.018..84.969 rows=222,530 loops=1)

  • Filter: (claim_transaction_2.void_date IS NULL)
  • Buffers: shared hit=6533
112. 0.000 10,740.398 ↑ 1.0 1 5,370,199

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

  • Filter: (cx.claim_product_id = cpri.claim_product_id)
  • Buffers: shared hit=128
113. 10,740.398 10,740.398 ↑ 1.0 1 5,370,199

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

  • Buffers: shared hit=128
114. 13.974 340.365 ↑ 1.3 46,140 1

Hash (cost=26,989.04..26,989.04 rows=59,034 width=24) (actual time=340.365..340.365 rows=46,140 loops=1)

  • Buffers: shared read=12132 dirtied=3
115. 8.356 326.391 ↑ 1.3 46,140 1

Subquery Scan on cshpend (cost=25,808.36..26,989.04 rows=59,034 width=24) (actual time=303.139..326.391 rows=46,140 loops=1)

  • Buffers: shared read=12132 dirtied=3
116. 56.859 318.035 ↑ 1.3 46,140 1

Aggregate (cost=25,808.36..26,398.7 rows=59,034 width=24) (actual time=303.138..318.035 rows=46,140 loops=1)

  • Buffers: shared read=12132 dirtied=3
117. 261.176 261.176 ↑ 1.0 66,229 1

Seq Scan on claim_state_history claim_state_history (cost=0..25,475.89 rows=66,494 width=24) (actual time=0.191..261.176 rows=66,229 loops=1)

  • Filter: (claim_state_history.claim_state = 'PENDED'::enum.claim_state)
  • Buffers: shared read=12132 dirtied=3
118. 74.581 474.387 ↓ 1.5 224,082 1

Hash (cost=29,613.33..29,613.33 rows=150,656 width=24) (actual time=474.387..474.387 rows=224,082 loops=1)

  • Buffers: shared hit=12132
119. 42.011 399.806 ↓ 1.5 224,082 1

Subquery Scan on cshcomp (cost=26,600.21..29,613.33 rows=150,656 width=24) (actual time=279.489..399.806 rows=224,082 loops=1)

  • Buffers: shared hit=12132
120. 205.749 357.795 ↓ 1.5 224,082 1

Aggregate (cost=26,600.21..28,106.77 rows=150,656 width=24) (actual time=279.488..357.795 rows=224,082 loops=1)

  • Buffers: shared hit=12132
121. 152.046 152.046 ↓ 1.0 225,835 1

Seq Scan on claim_state_history claim_state_history_1 (cost=0..25,475.89 rows=224,865 width=24) (actual time=0.018..152.046 rows=225,835 loops=1)

  • Filter: (claim_state_history_1.claim_state = 'COMPLETE'::enum.claim_state)
  • Buffers: shared hit=12132
122. 82.193 557.505 ↓ 1.4 238,745 1

Hash (cost=30,409.32..30,409.32 rows=173,829 width=24) (actual time=557.505..557.505 rows=238,745 loops=1)

  • Buffers: shared hit=12132
123. 45.543 475.312 ↓ 1.4 238,745 1

Subquery Scan on cshsubmitted (cost=26,932.74..30,409.32 rows=173,829 width=24) (actual time=342.95..475.312 rows=238,745 loops=1)

  • Buffers: shared hit=12132
124. 256.970 429.769 ↓ 1.4 238,745 1

Aggregate (cost=26,932.74..28,671.03 rows=173,829 width=24) (actual time=342.948..429.769 rows=238,745 loops=1)

  • Buffers: shared hit=12132
125. 172.799 172.799 ↓ 1.0 292,297 1

Seq Scan on claim_state_history claim_state_history_2 (cost=0..25,475.89 rows=291,371 width=24) (actual time=0.018..172.799 rows=292,297 loops=1)

  • Filter: (claim_state_history_2.claim_state = 'SUBMITTED'::enum.claim_state)
  • Buffers: shared hit=12132
126. 1,526.924 17,775.215 ↓ 2.8 2,266,138 1

Hash (cost=129,081.37..129,081.37 rows=806,484 width=96) (actual time=17,775.214..17,775.215 rows=2,266,138 loops=1)

  • Buffers: shared hit=5131648 read=39300
127. 2,886.508 16,248.291 ↓ 2.8 2,266,138 1

Hash Join (cost=51,035.61..129,081.37 rows=806,484 width=96) (actual time=3,522.585..16,248.291 rows=2,266,138 loops=1)

  • Buffers: shared hit=5131648 read=39300
128. 533.811 13,358.793 ↓ 2.2 895,649 1

Hash Join (cost=50,860.73..118,292.34 rows=407,890 width=119) (actual time=3,519.531..13,358.793 rows=895,649 loops=1)

  • Buffers: shared hit=5131646 read=39254
129. 587.044 12,822.186 ↓ 2.2 895,649 1

Merge Join (cost=50,713.92..117,074.68 rows=407,890 width=167) (actual time=3,516.701..12,822.186 rows=895,649 loops=1)

  • Buffers: shared hit=5131646 read=39234
130. 293.602 11,003.374 ↓ 2.2 895,649 1

Merge Join (cost=50,646.99..72,145.76 rows=407,890 width=151) (actual time=3,515.89..11,003.374 rows=895,649 loops=1)

  • Buffers: shared hit=5131646 read=30688
131. 293.761 10,709.672 ↓ 2.2 895,649 1

Merge Join (cost=50,646.84..71,051.08 rows=407,890 width=135) (actual time=3,515.787..10,709.672 rows=895,649 loops=1)

  • Buffers: shared hit=5131646 read=30687
132. 360.067 10,415.773 ↓ 2.2 895,649 1

Merge Join (cost=50,646.7..69,950.86 rows=407,890 width=119) (actual time=3,515.647..10,415.773 rows=895,649 loops=1)

  • Buffers: shared hit=5131646 read=30686
133. 612.360 10,054.326 ↓ 2.2 895,649 1

Nested Loop (cost=50,646.39..68,840.21 rows=407,890 width=103) (actual time=3,514.962..10,054.326 rows=895,649 loops=1)

  • Buffers: shared hit=5131646 read=30680
134. 712.791 4,798.314 ↓ 101.1 1,547,884 1

Merge Join (cost=50,645.97..50,799.13 rows=15,311 width=87) (actual time=3,514.569..4,798.314 rows=1,547,884 loops=1)

  • Buffers: shared hit=59 read=25532
135. 1,822.748 4,080.493 ↓ 101.1 1,547,884 1

Sort (cost=50,522.08..50,560.36 rows=15,311 width=76) (actual time=3,510.913..4,080.493 rows=1,547,884 loops=1)

  • Sort Key: price_default_1.price_id
  • Sort Method: quicksort Memory: 266824kB
  • Buffers: shared hit=59 read=25502
136. 635.731 2,257.745 ↓ 101.1 1,547,884 1

Hash Join (cost=287.19..49,457.79 rows=15,311 width=76) (actual time=4.608..2,257.745 rows=1,547,884 loops=1)

  • Buffers: shared hit=51 read=25502
137. 787.125 1,620.176 ↓ 99.4 1,547,884 1

Hash Join (cost=188.97..49,318.66 rows=15,568 width=60) (actual time=2.735..1,620.176 rows=1,547,884 loops=1)

  • Buffers: shared hit=48 read=25491
138. 219.848 830.562 ↑ 1.0 1,547,884 1

Append (cost=0..40,998.64 rows=1,548,764 width=55) (actual time=0.174..830.562 rows=1,547,884 loops=1)

  • Buffers: shared read=25491
139. 0.512 0.512 ↑ 1.0 599 1

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

  • Buffers: shared read=8
140. 606.180 606.180 ↑ 1.0 1,542,993 1

Seq Scan on price_sale price_sale_1 (cost=0..40,841.93 rows=1,542,993 width=55) (actual time=0.322..606.18 rows=1,542,993 loops=1)

  • Buffers: shared read=25412
141. 0.032 0.032 ↓ 0.0 0 1

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

142. 0.005 0.005 ↓ 0.0 0 1

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

143. 3.985 3.985 ↑ 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.226..3.985 rows=4,292 loops=1)

  • Buffers: shared read=71
144. 1.784 2.489 ↑ 1.0 5,639 1

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

  • Buffers: shared hit=48
145. 0.705 0.705 ↑ 1.0 5,639 1

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

  • Buffers: shared hit=48
146. 0.774 1.838 ↑ 1.0 2,852 1

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

  • Buffers: shared hit=3 read=11
147. 1.064 1.064 ↑ 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.343..1.064 rows=2,852 loops=1)

  • Buffers: shared hit=3 read=11
148. 3.110 5.030 ↓ 3.0 3,205 1

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

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

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

  • Filter: upper_inf(pm.active_range)
  • Buffers: shared read=30
150. 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=5131587 read=5148
151. 1.380 1.380 ↑ 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.679..1.38 rows=599 loops=1)

  • Buffers: shared read=6
152. 0.138 0.138 ↓ 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.137..0.138 rows=0 loops=1)

  • Buffers: shared read=1
153. 0.100 0.100 ↓ 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.1..0.1 rows=0 loops=1)

  • Buffers: shared read=1
154. 1,231.768 1,231.768 ↑ 1.0 1,542,971 1

Index Only Scan using price_generated_pkey on price_sale price_sale (cost=0.43..36,039.32 rows=1,542,993 width=16) (actual time=0.798..1,231.768 rows=1,542,971 loops=1)

  • Buffers: shared read=8546
155. 1.049 2.796 ↑ 1.0 4,292 1

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

  • Buffers: shared read=20
156. 1.747 1.747 ↑ 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.591..1.747 rows=4,292 loops=1)

  • Buffers: shared read=20
157. 1.559 2.990 ↑ 1.0 5,639 1

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

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

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

  • Buffers: shared hit=2 read=46
159. 8.653 97.042 ↑ 1.0 28,702 1

Hash (cost=5,199.9..5,199.9 rows=28,733 width=48) (actual time=97.042..97.042 rows=28,702 loops=1)

  • Buffers: shared hit=1884 read=757 dirtied=1
160. 6.662 88.389 ↑ 1.0 28,702 1

Subquery Scan on crc (cost=4,703.21..5,199.9 rows=28,733 width=48) (actual time=63.305..88.389 rows=28,702 loops=1)

  • Buffers: shared hit=1884 read=757 dirtied=1
161. 10.749 81.727 ↑ 1.0 28,702 1

Unique (cost=4,703.21..4,912.57 rows=28,733 width=56) (actual time=63.301..81.727 rows=28,702 loops=1)

  • Buffers: shared hit=1884 read=757 dirtied=1
162. 39.934 70.978 ↑ 1.0 41,831 1

Sort (cost=4,703.21..4,807.89 rows=41,872 width=56) (actual time=63.297..70.978 rows=41,831 loops=1)

  • Sort Key: claim_reason_code.claim_id, (lower(claim_reason_code.active_range)) DESC
  • Sort Method: quicksort Memory: 4871kB
  • Buffers: shared hit=1884 read=757 dirtied=1
163. 31.044 31.044 ↑ 1.0 41,831 1

Seq Scan on claim_reason_code claim_reason_code (cost=0..1,488.76 rows=41,872 width=56) (actual time=0.336..31.044 rows=41,831 loops=1)

  • Buffers: shared hit=51 read=756 dirtied=1
164. 0.045 4.663 ↑ 4.0 115 1

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

  • Buffers: shared hit=2
165. 0.252 4.618 ↑ 4.0 115 1

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

  • Buffers: shared hit=2
166. 4.279 4.279 ↓ 1.6 1,226 1

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

167. 0.027 0.087 ↑ 1.0 116 1

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

  • Buffers: shared hit=2
168. 0.060 0.060 ↑ 1.0 116 1

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

  • Buffers: shared hit=2