explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jhuP : Optimization for: Optimization for: Optimization for: Optimization for: plan #8DIR; plan #zSmQ; plan #6rVc; plan #xrdZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.584 8,029.285 ↓ 10.0 10 1

Result (cost=81,943.04..81,943.72 rows=1 width=460) (actual time=8,027.886..8,029.285 rows=10 loops=1)

2.          

CTE datawindow

3. 0.008 1,637.935 ↑ 1.0 10 1

Limit (cost=56,959.07..56,959.10 rows=10 width=24) (actual time=1,637.929..1,637.935 rows=10 loops=1)

4. 1.135 1,637.927 ↑ 14.5 10 1

Sort (cost=56,959.07..56,959.43 rows=145 width=24) (actual time=1,637.927..1,637.927 rows=10 loops=1)

  • Sort Key: pv_1.createdtime DESC
  • Sort Method: top-N heapsort Memory: 25kB
5. 3.375 1,636.792 ↓ 83.9 12,159 1

WindowAgg (cost=52,687.51..56,955.94 rows=145 width=24) (actual time=1,635.452..1,636.792 rows=12,159 loops=1)

6. 20.538 1,633.417 ↓ 83.9 12,159 1

Hash Join (cost=52,687.51..56,954.13 rows=145 width=16) (actual time=1,524.985..1,633.417 rows=12,159 loops=1)

  • Hash Cond: (od_2.id = pv_1.id)
7. 27.072 1,562.096 ↓ 4.0 203,763 1

Subquery Scan on od_2 (cost=32,388.87..36,462.82 rows=50,991 width=8) (actual time=1,468.934..1,562.096 rows=203,763 loops=1)

  • Filter: ((od_2.validstartdate + od_2.validdays) < now())
  • Rows Removed by Filter: 1749
8. 37.710 1,535.024 ↓ 1.3 205,512 1

Unique (cost=32,388.87..33,785.80 rows=152,973 width=173) (actual time=1,468.926..1,535.024 rows=205,512 loops=1)

9. 1,451.931 1,497.314 ↓ 1.0 279,388 1

Sort (cost=32,388.87..33,087.33 rows=279,385 width=173) (actual time=1,468.922..1,497.314 rows=279,388 loops=1)

  • Sort Key: tblofferdata_1.offernumber, tblofferdata_1.offerversion DESC
  • Sort Method: quicksort Memory: 34116kB
10. 45.383 45.383 ↓ 1.0 279,388 1

Seq Scan on tblofferdata tblofferdata_1 (cost=0.00..7,115.85 rows=279,385 width=173) (actual time=0.030..45.383 rows=279,388 loops=1)

11. 1.709 50.783 ↓ 12.4 12,263 1

Hash (cost=20,286.24..20,286.24 rows=992 width=16) (actual time=50.782..50.783 rows=12,263 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 703kB
12. 7.480 49.074 ↓ 12.4 12,263 1

Nested Loop (cost=18.52..20,286.24 rows=992 width=16) (actual time=1.939..49.074 rows=12,263 loops=1)

13. 0.898 17.014 ↓ 11.7 12,290 1

Nested Loop (cost=18.09..19,204.02 rows=1,054 width=8) (actual time=1.912..17.014 rows=12,290 loops=1)

14. 0.952 0.952 ↑ 5.5 4 1

Seq Scan on tblagencyunit agency_1 (cost=0.00..194.77 rows=22 width=8) (actual time=0.028..0.952 rows=4 loops=1)

  • Filter: ((ltree2text(subltree(path, 0, 1)))::integer = 2375)
  • Rows Removed by Filter: 4301
15. 13.796 15.164 ↓ 16.9 3,072 4

Bitmap Heap Scan on tblpolicy p_1 (cost=18.09..862.24 rows=182 width=16) (actual time=0.478..3.791 rows=3,072 loops=4)

  • Recheck Cond: (idagency = agency_1.id)
  • Filter: (policytype = 'OFFER'::text)
  • Rows Removed by Filter: 1850
  • Heap Blocks: exact=5465
16. 1.368 1.368 ↓ 20.4 4,922 4

Bitmap Index Scan on tblpolicy_idagency (cost=0.00..18.05 rows=241 width=0) (actual time=0.341..0.342 rows=4,922 loops=4)

  • Index Cond: (idagency = agency_1.id)
17. 24.580 24.580 ↑ 1.0 1 12,290

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv_1 (cost=0.42..1.02 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=12,290)

  • Index Cond: (idpolicy = p_1.id)
  • Filter: ((canceldate IS NULL) AND (policyversionstatus = 'CURRENT'::text))
  • Rows Removed by Filter: 0
18.          

CTE premiums

19. 0.003 0.144 ↓ 10.0 10 1

Nested Loop (cost=121.54..207.93 rows=1 width=72) (actual time=0.097..0.144 rows=10 loops=1)

20. 0.008 0.121 ↓ 10.0 10 1

Hash Join (cost=121.12..206.96 rows=1 width=144) (actual time=0.089..0.121 rows=10 loops=1)

  • Hash Cond: (pv_2.id = w_1.idpolicyversion)
21. 0.008 0.108 ↓ 1.2 10 1

Nested Loop (cost=120.79..206.60 rows=8 width=152) (actual time=0.079..0.108 rows=10 loops=1)

22. 0.024 0.090 ↑ 1.0 10 1

GroupAggregate (cost=120.37..122.07 rows=10 width=136) (actual time=0.073..0.090 rows=10 loops=1)

  • Group Key: rp.idpolicyversion
23. 0.013 0.066 ↑ 3.0 10 1

WindowAgg (cost=120.37..121.05 rows=30 width=50) (actual time=0.059..0.066 rows=10 loops=1)

24. 0.008 0.053 ↑ 3.0 10 1

Sort (cost=120.37..120.45 rows=30 width=42) (actual time=0.052..0.053 rows=10 loops=1)

  • Sort Key: rp.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 25kB
25. 0.003 0.045 ↑ 3.0 10 1

Nested Loop (cost=0.42..119.64 rows=30 width=42) (actual time=0.015..0.045 rows=10 loops=1)

26. 0.002 0.002 ↑ 1.0 10 1

CTE Scan on datawindow w_2 (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.002 rows=10 loops=1)

27. 0.040 0.040 ↑ 3.0 1 10

Index Scan using tblriskpremium_idpolicyversion_index on tblriskpremium rp (cost=0.42..11.91 rows=3 width=42) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: (idpolicyversion = w_2.idpolicyversion)
28. 0.010 0.010 ↑ 1.0 1 10

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv_2 (cost=0.42..8.44 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=10)

  • Index Cond: (id = rp.idpolicyversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
29. 0.003 0.005 ↑ 1.0 10 1

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=0.005..0.005 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.002 0.002 ↑ 1.0 10 1

CTE Scan on datawindow w_1 (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.002 rows=10 loops=1)

31. 0.020 0.020 ↑ 1.0 1 10

Index Scan using pk_tblinsurance on tblpolicy p_2 (cost=0.42..0.97 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: (id = pv_2.idpolicy)
32. 0.038 8,027.701 ↓ 10.0 10 1

Sort (cost=24,776.02..24,776.02 rows=1 width=404) (actual time=8,027.699..8,027.701 rows=10 loops=1)

  • Sort Key: pv.createdtime DESC
  • Sort Method: quicksort Memory: 30kB
33. 0.088 8,027.663 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,020.71..24,776.01 rows=1 width=404) (actual time=2,086.978..8,027.663 rows=10 loops=1)

34. 0.038 8,027.515 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,020.42..24,775.68 rows=1 width=607) (actual time=2,086.957..8,027.515 rows=10 loops=1)

35. 0.036 8,027.467 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,020.14..24,775.38 rows=1 width=589) (actual time=2,086.952..8,027.467 rows=10 loops=1)

36. 0.044 8,027.421 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,019.86..24,775.07 rows=1 width=569) (actual time=2,086.946..8,027.421 rows=10 loops=1)

37. 0.052 8,027.307 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,019.57..24,774.67 rows=1 width=569) (actual time=2,086.925..8,027.307 rows=10 loops=1)

38. 0.038 8,027.185 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,019.28..24,774.35 rows=1 width=561) (actual time=2,086.903..8,027.185 rows=10 loops=1)

39. 0.032 8,026.857 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,018.43..24,773.19 rows=1 width=528) (actual time=2,086.864..8,026.857 rows=10 loops=1)

40. 0.041 8,026.645 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,017.86..24,766.03 rows=1 width=528) (actual time=2,086.841..8,026.645 rows=10 loops=1)

41. 0.048 8,026.534 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,017.72..24,765.87 rows=1 width=496) (actual time=2,086.830..8,026.534 rows=10 loops=1)

  • Join Filter: (p.fallbacklocale ~~* (cd_label_fallback.locale || '%'::text))
  • Rows Removed by Join Filter: 10
42. 0.040 8,026.456 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,017.29..24,765.21 rows=1 width=410) (actual time=2,086.821..8,026.456 rows=10 loops=1)

43. 0.036 8,026.276 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,016.87..24,764.55 rows=1 width=321) (actual time=2,086.786..8,026.276 rows=10 loops=1)

44. 0.045 8,026.160 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,016.73..24,764.38 rows=1 width=289) (actual time=2,086.774..8,026.160 rows=10 loops=1)

45. 114.630 8,025.905 ↓ 10.0 10 1

Nested Loop (cost=24,016.59..24,763.54 rows=1 width=257) (actual time=2,086.743..8,025.905 rows=10 loops=1)

  • Join Filter: (pv.id = od.id)
  • Rows Removed by Join Filter: 2055300
46. 0.067 1,638.405 ↓ 10.0 10 1

Merge Join (cost=96.45..96.51 rows=1 width=239) (actual time=1,638.324..1,638.405 rows=10 loops=1)

  • Merge Cond: (pv.id = premiums.idpolicyversion)
47. 0.017 1,638.175 ↓ 1.2 10 1

Sort (cost=96.42..96.44 rows=8 width=167) (actual time=1,638.165..1,638.175 rows=10 loops=1)

  • Sort Key: w.idpolicyversion
  • Sort Method: quicksort Memory: 27kB
48. 0.007 1,638.158 ↓ 1.2 10 1

Nested Loop (cost=2.39..96.30 rows=8 width=167) (actual time=1,638.070..1,638.158 rows=10 loops=1)

  • Join Filter: (p.idinsurancecompany = cu.id)
  • Rows Removed by Join Filter: 10
49. 0.019 1,638.131 ↓ 1.2 10 1

Hash Join (cost=2.39..95.07 rows=8 width=143) (actual time=1,638.047..1,638.131 rows=10 loops=1)

  • Hash Cond: (ga.idproductline = pl.id)
50. 0.006 1,638.096 ↓ 1.2 10 1

Nested Loop (cost=0.99..93.64 rows=8 width=117) (actual time=1,638.015..1,638.096 rows=10 loops=1)

51. 0.011 1,638.060 ↓ 1.2 10 1

Nested Loop (cost=0.84..92.36 rows=8 width=100) (actual time=1,637.998..1,638.060 rows=10 loops=1)

52. 0.009 1,637.999 ↓ 1.2 10 1

Nested Loop (cost=0.42..84.63 rows=8 width=44) (actual time=1,637.964..1,637.999 rows=10 loops=1)

53. 1,637.940 1,637.940 ↑ 1.0 10 1

CTE Scan on datawindow w (cost=0.00..0.20 rows=10 width=8) (actual time=1,637.931..1,637.940 rows=10 loops=1)

54. 0.050 0.050 ↑ 1.0 1 10

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv (cost=0.42..8.44 rows=1 width=36) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: (id = w.idpolicyversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
55. 0.050 0.050 ↑ 1.0 1 10

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..0.97 rows=1 width=72) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: (id = pv.idpolicy)
56. 0.030 0.030 ↑ 1.0 1 10

Index Scan using tblgeneralagreement_pkey on tblgeneralagreement ga (cost=0.14..0.16 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: (id = p.idgeneralagreement)
57. 0.004 0.016 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=32) (actual time=0.016..0.016 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
58. 0.012 0.012 ↑ 1.0 18 1

Seq Scan on tblproductline pl (cost=0.00..1.18 rows=18 width=32) (actual time=0.010..0.012 rows=18 loops=1)

59. 0.002 0.020 ↑ 1.0 2 10

Materialize (cost=0.00..1.03 rows=2 width=40) (actual time=0.002..0.002 rows=2 loops=10)

60. 0.018 0.018 ↑ 1.0 2 1

Seq Scan on tblinsurancecompanyunit cu (cost=0.00..1.02 rows=2 width=40) (actual time=0.018..0.018 rows=2 loops=1)

61. 0.015 0.163 ↓ 10.0 10 1

Sort (cost=0.03..0.04 rows=1 width=72) (actual time=0.153..0.163 rows=10 loops=1)

  • Sort Key: premiums.idpolicyversion
  • Sort Method: quicksort Memory: 25kB
62. 0.148 0.148 ↓ 10.0 10 1

CTE Scan on premiums (cost=0.00..0.02 rows=1 width=72) (actual time=0.099..0.148 rows=10 loops=1)

63. 1,166.410 6,272.870 ↓ 147.1 205,531 10

Nested Loop (cost=23,920.14..24,649.56 rows=1,397 width=50) (actual time=39.998..627.287 rows=205,531 loops=10)

64. 652.985 995.840 ↓ 147.1 205,531 10

HashAggregate (cost=23,919.71..23,933.68 rows=1,397 width=8) (actual time=39.994..99.584 rows=205,531 loops=10)

  • Group Key: od_1.id
65. 129.940 342.855 ↓ 147.1 205,531 1

Hash Join (cost=15,263.75..23,916.22 rows=1,397 width=8) (actual time=185.464..342.855 rows=205,531 loops=1)

  • Hash Cond: ((od_1.offernumber = tblofferdata.offernumber) AND ((od_1.offerversion)::bigint = (max((tblofferdata.offerversion)::bigint))))
66. 27.681 27.681 ↓ 1.0 279,388 1

Seq Scan on tblofferdata od_1 (cost=0.00..7,115.85 rows=279,385 width=21) (actual time=0.007..27.681 rows=279,388 loops=1)

67. 34.393 185.234 ↓ 1.3 205,512 1

Hash (cost=12,969.16..12,969.16 rows=152,973 width=19) (actual time=185.234..185.234 rows=205,512 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13287kB
68. 123.601 150.841 ↓ 1.3 205,512 1

HashAggregate (cost=9,909.70..11,439.43 rows=152,973 width=19) (actual time=104.236..150.841 rows=205,512 loops=1)

  • Group Key: tblofferdata.offernumber
69. 27.240 27.240 ↓ 1.0 279,388 1

Seq Scan on tblofferdata (cost=0.00..7,115.85 rows=279,385 width=13) (actual time=0.006..27.240 rows=279,388 loops=1)

70. 4,110.620 4,110.620 ↑ 1.0 1 2,055,310

Index Scan using tblofferdata_pkey on tblofferdata od (cost=0.42..0.51 rows=1 width=42) (actual time=0.002..0.002 rows=1 loops=2,055,310)

  • Index Cond: (id = od_1.id)
71. 0.210 0.210 ↑ 1.0 1 10

Index Scan using tblproductlinelabel_pkey on tblproductlinelabel pl_label (cost=0.14..0.83 rows=1 width=64) (actual time=0.020..0.021 rows=1 loops=10)

  • Index Cond: (id = pl.id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 1
72. 0.080 0.080 ↑ 1.0 1 10

Index Scan using tblinsurancetypelabel_pkey on tblinsurancetypelabel it_label (cost=0.14..0.16 rows=1 width=64) (actual time=0.008..0.008 rows=1 loops=10)

  • Index Cond: (p.idinsurancetype = id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 2
73. 0.140 0.140 ↑ 1.0 1 10

Index Scan using tblpolicycoveragedescription_unique on tblpolicycoveragedescription cd_label (cost=0.42..0.65 rows=1 width=97) (actual time=0.014..0.014 rows=1 loops=10)

  • Index Cond: (idpolicyversion = pv.id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 1
74. 0.030 0.030 ↑ 1.0 2 10

Index Scan using tblpolicycoveragedescription_unique on tblpolicycoveragedescription cd_label_fallback (cost=0.42..0.64 rows=2 width=100) (actual time=0.001..0.003 rows=2 loops=10)

  • Index Cond: (idpolicyversion = pv.id)
75. 0.070 0.070 ↑ 1.0 1 10

Index Scan using tblintegration_key_key on tblintegration i (cost=0.14..0.16 rows=1 width=64) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: (key = p.keyintegration)
76. 0.060 0.180 ↑ 1.0 1 10

Nested Loop (cost=0.57..7.15 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=10)

77. 0.090 0.090 ↑ 1.0 1 10

Index Scan using tblagencyunit_pkey on tblagencyunit u2 (cost=0.28..0.31 rows=1 width=25) (actual time=0.009..0.009 rows=1 loops=10)

  • Index Cond: (id = p.idagencyunit)
  • Filter: (nlevel(path) = 2)
78. 0.030 0.030 ↑ 1.0 1 10

Index Scan using tblagencyunit_pkey on tblagencyunit u1 (cost=0.29..6.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: (id = (ltree2text(subpath(u2.path, 0, 1)))::bigint)
  • Filter: (nlevel(path) = 1)
79. 0.050 0.290 ↑ 1.0 1 10

Nested Loop (cost=0.85..1.15 rows=1 width=41) (actual time=0.029..0.029 rows=1 loops=10)

80. 0.120 0.120 ↑ 1.0 1 10

Index Scan using tblpolicyclient_idpolicyversion on tblpolicyclient pc_ubezpieczony (cost=0.42..0.62 rows=1 width=49) (actual time=0.011..0.012 rows=1 loops=10)

  • Index Cond: (pv.id = idpolicyversion)
81. 0.120 0.120 ↑ 1.0 1 10

Index Only Scan using tblpolicyclientrole_role_unique on tblpolicyclientrole pcr_ubezpieczony (cost=0.42..0.54 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=10)

  • Index Cond: ((idpolicyclient = pc_ubezpieczony.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 10
82. 0.070 0.070 ↑ 1.0 1 10

Index Scan using tblclient_pkey on tblclient c (cost=0.29..0.32 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: (pc_ubezpieczony.idclient = id)
83. 0.070 0.070 ↑ 1.0 1 10

Index Only Scan using tblmetaclient_pkey on tblmetaclient mc (cost=0.29..0.40 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: (id = c.idmetaclient)
  • Heap Fetches: 10
84. 0.010 0.010 ↑ 1.0 1 10

Index Scan using tblagencyunit_pkey on tblagencyunit agency (cost=0.28..0.30 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=10)

  • Index Cond: (id = p.idagency)
85. 0.010 0.010 ↑ 1.0 1 10

Index Scan using tblagencyunit_pkey on tblagencyunit division (cost=0.28..0.30 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=10)

  • Index Cond: (id = p.idagencyunit)
86. 0.060 0.060 ↑ 1.0 1 10

Index Scan using tblemployee_pkey on tblemployee employee (cost=0.29..0.31 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (id = p.idemployee)
Planning time : 10.242 ms
Execution time : 8,030.578 ms