explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WI2t : Optimization for: Optimization for: plan #VWvn; plan #jLt2

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.053 339.496 ↓ 12.5 25 1

Sort (cost=83,541.83..83,541.83 rows=2 width=625) (actual time=339.494..339.496 rows=25 loops=1)

  • Sort Key: pv.createdtime DESC NULLS LAST, pv.id
  • Sort Method: quicksort Memory: 37kB
2.          

CTE datawindow

3. 0.008 336.077 ↑ 1.0 25 1

Limit (cost=82,961.02..82,961.08 rows=25 width=41) (actual time=336.069..336.077 rows=25 loops=1)

4. 0.196 336.069 ↑ 1,754.6 25 1

Sort (cost=82,961.02..83,070.68 rows=43,865 width=41) (actual time=336.068..336.069 rows=25 loops=1)

  • Sort Key: pv_1.createdtime DESC NULLS LAST, pv_1.id
  • Sort Method: top-N heapsort Memory: 28kB
5. 0.379 335.873 ↑ 35.6 1,233 1

WindowAgg (cost=43,047.44..81,723.18 rows=43,865 width=41) (actual time=335.709..335.873 rows=1,233 loops=1)

6. 0.073 335.494 ↑ 35.6 1,233 1

Append (cost=43,047.44..81,174.87 rows=43,865 width=33) (actual time=204.674..335.494 rows=1,233 loops=1)

7. 17.326 335.409 ↑ 35.6 1,233 1

Hash Join (cost=43,047.44..80,507.23 rows=43,864 width=33) (actual time=204.673..335.409 rows=1,233 loops=1)

  • Hash Cond: (p_1.id = pv_1.idpolicy)
8. 113.478 113.478 ↓ 1.0 196,487 1

Seq Scan on tblpolicy p_1 (cost=0.00..36,284.84 rows=196,352 width=9) (actual time=0.010..113.478 rows=196,487 loops=1)

  • Filter: ((NOT test) AND (policytype = 'OFFER'::text))
  • Rows Removed by Filter: 75427
9. 0.251 204.605 ↑ 47.5 1,283 1

Hash (cost=42,285.25..42,285.25 rows=60,975 width=24) (actual time=204.605..204.605 rows=1,283 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 583kB
10. 25.907 204.354 ↑ 47.5 1,283 1

Hash Join (cost=10,948.23..42,285.25 rows=60,975 width=24) (actual time=54.491..204.354 rows=1,283 loops=1)

  • Hash Cond: (pv_1.id = od_1.id)
11. 124.072 124.072 ↑ 1.0 252,684 1

Seq Scan on tblpolicyversion pv_1 (cost=0.00..30,662.76 rows=256,857 width=24) (actual time=0.007..124.072 rows=252,684 loops=1)

  • Filter: ((canceldate IS NULL) AND (policyversionstatus = 'CURRENT'::text))
  • Rows Removed by Filter: 97297
12. 0.356 54.375 ↑ 52.0 1,597 1

Hash (cost=9,909.70..9,909.70 rows=83,082 width=8) (actual time=54.374..54.375 rows=1,597 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1087kB
13. 54.019 54.019 ↑ 52.0 1,597 1

Seq Scan on tblofferdata od_1 (cost=0.00..9,909.70 rows=83,082 width=8) (actual time=49.014..54.019 rows=1,597 loops=1)

  • Filter: (((validmarked IS NULL) OR validmarked) AND (offerstatus = ANY ('{HIDDEN,VISIBLE}'::text[])) AND ((validstartdate + validdays) >= now()))
  • Rows Removed by Filter: 277791
14. 0.000 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.57..9.66 rows=1 width=33) (actual time=0.012..0.012 rows=0 loops=1)

15. 0.001 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.29..9.32 rows=1 width=24) (actual time=0.012..0.012 rows=0 loops=1)

16. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on tblapplicationinprogress aip_1 (cost=0.00..1.02 rows=1 width=24) (actual time=0.011..0.011 rows=0 loops=1)

  • Filter: ((removed IS NULL) AND (status = ANY ('{OPEN_BROKER,OPEN_CLIENT,SUBMITTED,CLOSED,INTERRUPTED,QUOTE_NOT_SAVED}'::text[])))
  • Rows Removed by Filter: 1
17. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployee_pkey on tblemployee employee_2 (cost=0.29..8.30 rows=1 width=16) (never executed)

  • Index Cond: (id = aip_1.idemployee)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using tblagencyunit_pkey on tblagencyunit division_2 (cost=0.28..0.34 rows=1 width=8) (never executed)

  • Index Cond: (id = employee_2.idagencyunit)
  • Filter: (NOT test)
19.          

CTE premiums

20. 0.053 0.221 ↑ 1.0 25 1

GroupAggregate (cost=301.46..305.76 rows=25 width=136) (actual time=0.152..0.221 rows=25 loops=1)

  • Group Key: rp.idpolicyversion
21. 0.038 0.168 ↑ 1.3 57 1

WindowAgg (cost=301.46..303.17 rows=76 width=50) (actual time=0.134..0.168 rows=57 loops=1)

22. 0.035 0.130 ↑ 1.3 57 1

Sort (cost=301.46..301.65 rows=76 width=42) (actual time=0.127..0.130 rows=57 loops=1)

  • Sort Key: rp.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 30kB
23. 0.008 0.095 ↑ 1.3 57 1

Nested Loop (cost=0.42..299.09 rows=76 width=42) (actual time=0.015..0.095 rows=57 loops=1)

24. 0.012 0.012 ↑ 1.0 25 1

CTE Scan on datawindow w (cost=0.00..0.50 rows=25 width=8) (actual time=0.000..0.012 rows=25 loops=1)

25. 0.075 0.075 ↑ 1.5 2 25

Index Scan using tblriskpremium_idpolicyversion_index on tblriskpremium rp (cost=0.42..11.91 rows=3 width=42) (actual time=0.002..0.003 rows=2 loops=25)

  • Index Cond: (idpolicyversion = w.idpolicyversion)
26.          

CTE ppremium

27. 0.015 336.324 ↑ 1.0 25 1

Hash Join (cost=0.81..1.66 rows=25 width=72) (actual time=336.317..336.324 rows=25 loops=1)

  • Hash Cond: (w_1.idpolicyversion = premiums.idpolicyversion)
28. 336.072 336.072 ↑ 1.0 25 1

CTE Scan on datawindow w_1 (cost=0.00..0.50 rows=25 width=9) (actual time=336.070..336.072 rows=25 loops=1)

29. 0.006 0.237 ↑ 1.0 25 1

Hash (cost=0.50..0.50 rows=25 width=136) (actual time=0.236..0.237 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
30. 0.231 0.231 ↑ 1.0 25 1

CTE Scan on premiums (cost=0.00..0.50 rows=25 width=136) (actual time=0.154..0.231 rows=25 loops=1)

31. 0.005 339.443 ↓ 12.5 25 1

Append (cost=5.48..273.32 rows=2 width=624) (actual time=336.800..339.443 rows=25 loops=1)

32. 0.862 339.387 ↓ 25.0 25 1

Nested Loop Left Join (cost=5.48..252.04 rows=1 width=603) (actual time=336.799..339.387 rows=25 loops=1)

33. 0.094 338.475 ↓ 25.0 25 1

Nested Loop Left Join (cost=5.19..251.06 rows=1 width=662) (actual time=336.647..338.475 rows=25 loops=1)

  • Join Filter: (i.key = p.keyintegration)
  • Rows Removed by Join Filter: 975
34. 0.031 338.306 ↓ 25.0 25 1

Nested Loop Left Join (cost=5.19..249.20 rows=1 width=630) (actual time=336.633..338.306 rows=25 loops=1)

  • Join Filter: (p.fallbacklocale ~~* (cd_label_fallback.locale || '%'::text))
  • Rows Removed by Join Filter: 18
35. 0.019 338.225 ↓ 25.0 25 1

Nested Loop Left Join (cost=4.77..248.53 rows=1 width=544) (actual time=336.625..338.225 rows=25 loops=1)

36. 0.051 338.131 ↓ 25.0 25 1

Nested Loop Left Join (cost=4.34..247.87 rows=1 width=455) (actual time=336.612..338.131 rows=25 loops=1)

  • Join Filter: (p.idinsurancetype = it_label.id)
  • Rows Removed by Join Filter: 200
37. 0.076 337.780 ↓ 25.0 25 1

Nested Loop Left Join (cost=4.34..246.47 rows=1 width=423) (actual time=336.602..337.780 rows=25 loops=1)

  • Join Filter: (pl_label.id = pl.id)
  • Rows Removed by Join Filter: 375
38. 0.033 337.354 ↓ 25.0 25 1

Nested Loop (cost=4.34..244.98 rows=1 width=391) (actual time=336.582..337.354 rows=25 loops=1)

39. 0.024 337.237 ↓ 28.0 28 1

Nested Loop (cost=3.92..244.44 rows=1 width=399) (actual time=336.566..337.237 rows=28 loops=1)

  • Join Filter: (pv.id = pc_ubezpieczony.idpolicyversion)
40. 0.014 337.113 ↓ 25.0 25 1

Nested Loop (cost=3.50..243.82 rows=1 width=390) (actual time=336.537..337.113 rows=25 loops=1)

41. 0.017 337.024 ↓ 25.0 25 1

Nested Loop (cost=3.21..243.51 rows=1 width=374) (actual time=336.527..337.024 rows=25 loops=1)

42. 0.023 336.932 ↓ 25.0 25 1

Nested Loop (cost=2.93..243.21 rows=1 width=356) (actual time=336.503..336.932 rows=25 loops=1)

43. 0.024 336.809 ↓ 25.0 25 1

Hash Join (cost=2.65..242.91 rows=1 width=336) (actual time=336.484..336.809 rows=25 loops=1)

  • Hash Cond: (pv.id = ppremium.idpolicyversion)
44. 0.025 0.446 ↓ 2.8 25 1

Nested Loop (cost=1.83..242.05 rows=9 width=264) (actual time=0.138..0.446 rows=25 loops=1)

  • Join Filter: (p.idinsurancecompany = cu.id)
  • Rows Removed by Join Filter: 27
45. 0.071 0.421 ↓ 2.8 25 1

Nested Loop (cost=1.83..240.79 rows=9 width=240) (actual time=0.130..0.421 rows=25 loops=1)

  • Join Filter: (ga.idproductline = pl.id)
  • Rows Removed by Join Filter: 425
46. 0.008 0.008 ↑ 1.0 18 1

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

47. 0.031 0.342 ↓ 2.8 25 18

Materialize (cost=1.83..237.20 rows=9 width=214) (actual time=0.003..0.019 rows=25 loops=18)

48. 0.004 0.311 ↓ 2.8 25 1

Nested Loop (cost=1.83..237.15 rows=9 width=214) (actual time=0.054..0.311 rows=25 loops=1)

49. 0.015 0.232 ↓ 2.8 25 1

Nested Loop (cost=1.69..235.71 rows=9 width=197) (actual time=0.043..0.232 rows=25 loops=1)

50. 0.011 0.167 ↓ 2.8 25 1

Nested Loop (cost=1.27..227.01 rows=9 width=136) (actual time=0.030..0.167 rows=25 loops=1)

51. 0.007 0.131 ↓ 2.3 25 1

Nested Loop (cost=0.84..221.34 rows=11 width=60) (actual time=0.025..0.131 rows=25 loops=1)

52. 0.019 0.074 ↓ 1.3 25 1

Nested Loop (cost=0.42..211.56 rows=19 width=52) (actual time=0.013..0.074 rows=25 loops=1)

53. 0.005 0.005 ↑ 1.0 25 1

CTE Scan on datawindow dw (cost=0.00..0.50 rows=25 width=16) (actual time=0.000..0.005 rows=25 loops=1)

54. 0.050 0.050 ↑ 1.0 1 25

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

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

Index Scan using tblofferdata_pkey on tblofferdata (cost=0.42..0.51 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=25)

  • Index Cond: (id = pv.id)
  • Filter: (NOT archive)
56. 0.025 0.025 ↑ 1.0 1 25

Index Scan using tblofferdata_pkey on tblofferdata od (cost=0.42..0.51 rows=1 width=76) (actual time=0.001..0.001 rows=1 loops=25)

  • Index Cond: (id = pv.id)
57. 0.050 0.050 ↑ 1.0 1 25

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..0.97 rows=1 width=77) (actual time=0.002..0.002 rows=1 loops=25)

  • Index Cond: (id = pv.idpolicy)
58. 0.075 0.075 ↑ 1.0 1 25

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=25)

  • Index Cond: (id = p.idgeneralagreement)
59. 0.000 0.000 ↑ 1.0 2 25

Materialize (cost=0.00..1.03 rows=2 width=40) (actual time=0.000..0.000 rows=2 loops=25)

60. 0.005 0.005 ↓ 1.5 3 1

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

61. 0.006 336.339 ↑ 1.0 25 1

Hash (cost=0.50..0.50 rows=25 width=72) (actual time=336.339..336.339 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
62. 336.333 336.333 ↑ 1.0 25 1

CTE Scan on ppremium (cost=0.00..0.50 rows=25 width=72) (actual time=336.320..336.333 rows=25 loops=1)

63. 0.100 0.100 ↑ 1.0 1 25

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

  • Index Cond: (id = p.idagency)
64. 0.075 0.075 ↑ 1.0 1 25

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

  • Index Cond: (id = p.idagencyunit)
65. 0.075 0.075 ↑ 1.0 1 25

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

  • Index Cond: (id = p.idemployee)
66. 0.100 0.100 ↑ 1.0 1 25

Index Scan using tblpolicyclient_idpolicyversion on tblpolicyclient pc_ubezpieczony (cost=0.42..0.61 rows=1 width=49) (actual time=0.004..0.004 rows=1 loops=25)

  • Index Cond: (idpolicyversion = od.id)
67. 0.084 0.084 ↑ 1.0 1 28

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

  • Index Cond: ((idpolicyclient = pc_ubezpieczony.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 25
68. 0.350 0.350 ↓ 16.0 16 25

Seq Scan on tblproductlinelabel pl_label (cost=0.00..1.48 rows=1 width=64) (actual time=0.004..0.014 rows=16 loops=25)

  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 16
69. 0.300 0.300 ↓ 9.0 9 25

Seq Scan on tblinsurancetypelabel it_label (cost=0.00..1.39 rows=1 width=64) (actual time=0.002..0.012 rows=9 loops=25)

  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 17
70. 0.075 0.075 ↑ 1.0 1 25

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

  • Index Cond: (idpolicyversion = pv.id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 1
71. 0.050 0.050 ↑ 2.0 1 25

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

  • Index Cond: (idpolicyversion = pv.id)
72. 0.075 0.075 ↓ 1.0 39 25

Seq Scan on tblintegration i (cost=0.00..1.38 rows=38 width=64) (actual time=0.001..0.003 rows=39 loops=25)

73. 0.050 0.050 ↑ 1.0 1 25

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

  • Index Cond: (pc_ubezpieczony.idclient = id)
74. 0.000 0.051 ↓ 0.0 0 1

Nested Loop (cost=2.62..21.25 rows=1 width=646) (actual time=0.051..0.051 rows=0 loops=1)

75. 0.001 0.051 ↓ 0.0 0 1

Nested Loop (cost=2.47..20.35 rows=1 width=212) (actual time=0.050..0.051 rows=0 loops=1)

76. 0.001 0.050 ↓ 0.0 0 1

Nested Loop (cost=2.19..20.02 rows=1 width=177) (actual time=0.050..0.050 rows=0 loops=1)

77. 0.000 0.049 ↓ 0.0 0 1

Nested Loop (cost=1.90..11.71 rows=1 width=153) (actual time=0.049..0.049 rows=0 loops=1)

78. 0.017 0.049 ↓ 0.0 0 1

Hash Join (cost=1.61..3.41 rows=1 width=125) (actual time=0.049..0.049 rows=0 loops=1)

  • Hash Cond: (pa.id = aip.idpath)
79. 0.008 0.008 ↑ 57.0 1 1

Seq Scan on tblpath pa (cost=0.00..1.57 rows=57 width=27) (actual time=0.008..0.008 rows=1 loops=1)

80. 0.001 0.024 ↓ 0.0 0 1

Hash (cost=1.60..1.60 rows=1 width=108) (actual time=0.024..0.024 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
81. 0.012 0.023 ↓ 0.0 0 1

Hash Join (cost=1.02..1.60 rows=1 width=108) (actual time=0.023..0.023 rows=0 loops=1)

  • Hash Cond: (dw_1.idaip = aip.id)
82. 0.003 0.003 ↑ 1.0 25 1

CTE Scan on datawindow dw_1 (cost=0.00..0.50 rows=25 width=16) (actual time=0.001..0.003 rows=25 loops=1)

83. 0.002 0.008 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=100) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
84. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on tblapplicationinprogress aip (cost=0.00..1.01 rows=1 width=100) (actual time=0.006..0.006 rows=1 loops=1)

85. 0.000 0.000 ↓ 0.0 0

Index Scan using tblclient_pkey on tblclient c_1 (cost=0.29..8.31 rows=1 width=36) (never executed)

  • Index Cond: (id = aip.idclient)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployee_pkey on tblemployee employee_1 (cost=0.29..8.30 rows=1 width=32) (never executed)

  • Index Cond: (id = aip.idemployee)
87. 0.000 0.000 ↓ 0.0 0

Index Scan using tblagencyunit_pkey on tblagencyunit division_1 (cost=0.28..0.34 rows=1 width=51) (never executed)

  • Index Cond: (id = employee_1.idagencyunit)
88. 0.000 0.000 ↓ 0.0 0

Index Scan using tblagencyunit_path_ltree_gist on tblagencyunit agency_1 (cost=0.15..0.36 rows=1 width=45) (never executed)

  • Index Cond: (path @> division_1.path)
  • Filter: (nlevel(path) = 1)
Planning time : 14.173 ms
Execution time : 339.920 ms