explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.411 7,724.983 ↓ 10.0 10 1

Result (cost=81,943.04..81,943.72 rows=1 width=460) (actual time=7,724.753..7,724.983 rows=10 loops=1)

2.          

CTE datawindow

3. 0.009 1,711.686 ↑ 1.0 10 1

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

4. 1.169 1,711.677 ↑ 14.5 10 1

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

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

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

6. 20.289 1,707.017 ↓ 83.9 12,159 1

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

  • Hash Cond: (od_2.id = pv_1.id)
7. 28.282 1,633.052 ↓ 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,538.029..1,633.052 rows=203,763 loops=1)

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

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

9. 1,519.093 1,566.085 ↓ 1.0 279,388 1

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

  • Sort Key: tblofferdata_1.offernumber, tblofferdata_1.offerversion DESC
  • Sort Method: quicksort Memory: 34116kB
10. 46.992 46.992 ↓ 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.011..46.992 rows=279,388 loops=1)

11. 1.936 53.676 ↓ 12.4 12,263 1

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

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

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

13. 0.972 17.755 ↓ 11.7 12,290 1

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

14. 0.971 0.971 ↑ 5.5 4 1

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

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

Bitmap Heap Scan on tblpolicy p_1 (cost=18.09..862.24 rows=182 width=16) (actual time=0.443..3.953 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.204 1.204 ↓ 20.4 4,922 4

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

  • Index Cond: (idagency = agency_1.id)
17. 36.870 36.870 ↑ 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.003 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.141 ↓ 10.0 10 1

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

20. 0.010 0.118 ↓ 10.0 10 1

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

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

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

22. 0.021 0.086 ↑ 1.0 10 1

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

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

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

24. 0.008 0.052 ↑ 3.0 10 1

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

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

Nested Loop (cost=0.42..119.64 rows=30 width=42) (actual time=0.016..0.044 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.003..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.002 0.004 ↑ 1.0 10 1

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=0.004..0.004 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.037 7,724.572 ↓ 10.0 10 1

Sort (cost=24,776.02..24,776.02 rows=1 width=404) (actual time=7,724.570..7,724.572 rows=10 loops=1)

  • Sort Key: pv.createdtime DESC
  • Sort Method: quicksort Memory: 30kB
33. 0.099 7,724.535 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,020.71..24,776.01 rows=1 width=404) (actual time=2,170.920..7,724.535 rows=10 loops=1)

34. 0.039 7,724.376 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,020.42..24,775.68 rows=1 width=607) (actual time=2,170.896..7,724.376 rows=10 loops=1)

35. 0.055 7,724.327 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,020.14..24,775.38 rows=1 width=589) (actual time=2,170.891..7,724.327 rows=10 loops=1)

36. 0.041 7,724.262 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,019.86..24,775.07 rows=1 width=569) (actual time=2,170.885..7,724.262 rows=10 loops=1)

37. 0.050 7,724.151 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,019.57..24,774.67 rows=1 width=569) (actual time=2,170.866..7,724.151 rows=10 loops=1)

38. 0.046 7,724.011 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,019.28..24,774.35 rows=1 width=561) (actual time=2,170.833..7,724.011 rows=10 loops=1)

39. 0.039 7,723.515 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,018.43..24,773.19 rows=1 width=528) (actual time=2,170.785..7,723.515 rows=10 loops=1)

40. 0.043 7,723.306 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,017.86..24,766.03 rows=1 width=528) (actual time=2,170.761..7,723.306 rows=10 loops=1)

41. 0.053 7,723.203 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,017.72..24,765.87 rows=1 width=496) (actual time=2,170.751..7,723.203 rows=10 loops=1)

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

Nested Loop Left Join (cost=24,017.29..24,765.21 rows=1 width=410) (actual time=2,170.741..7,723.130 rows=10 loops=1)

43. 0.034 7,722.914 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,016.87..24,764.55 rows=1 width=321) (actual time=2,170.698..7,722.914 rows=10 loops=1)

44. 0.044 7,722.800 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,016.73..24,764.38 rows=1 width=289) (actual time=2,170.686..7,722.800 rows=10 loops=1)

45. 110.235 7,722.556 ↓ 10.0 10 1

Nested Loop (cost=24,016.59..24,763.54 rows=1 width=257) (actual time=2,170.654..7,722.556 rows=10 loops=1)

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

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

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

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

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

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

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

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

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

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

51. 0.005 1,711.803 ↓ 1.2 10 1

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

52. 0.016 1,711.748 ↓ 1.2 10 1

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

53. 1,711.692 1,711.692 ↑ 1.0 10 1

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

54. 0.040 0.040 ↑ 1.0 1 10

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv (cost=0.42..8.44 rows=1 width=36) (actual time=0.004..0.004 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.020 ↑ 1.0 18 1

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

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

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

59. 0.001 0.050 ↑ 1.0 2 10

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

60. 0.049 0.049 ↑ 1.0 2 1

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

61. 0.017 0.163 ↓ 10.0 10 1

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

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

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

63. 809.490 5,900.150 ↓ 147.1 205,531 10

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

64. 629.644 980.040 ↓ 147.1 205,531 10

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

  • Group Key: od_1.id
65. 126.675 350.396 ↓ 147.1 205,531 1

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

  • Hash Cond: ((od_1.offernumber = tblofferdata.offernumber) AND ((od_1.offerversion)::bigint = (max((tblofferdata.offerversion)::bigint))))
66. 29.208 29.208 ↓ 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.008..29.208 rows=279,388 loops=1)

67. 36.478 194.513 ↓ 1.3 205,512 1

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

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

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

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

Seq Scan on tblofferdata (cost=0.00..7,115.85 rows=279,385 width=13) (actual time=0.008..29.108 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.200 0.200 ↑ 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.020 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.007..0.008 rows=1 loops=10)

  • Index Cond: (p.idinsurancetype = id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 2
73. 0.180 0.180 ↑ 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.018..0.018 rows=1 loops=10)

  • Index Cond: (idpolicyversion = pv.id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 1
74. 0.020 0.020 ↑ 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.002..0.002 rows=2 loops=10)

  • Index Cond: (idpolicyversion = pv.id)
75. 0.060 0.060 ↑ 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.005..0.006 rows=1 loops=10)

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

Nested Loop (cost=0.57..7.15 rows=1 width=8) (actual time=0.017..0.017 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.070 0.450 ↑ 1.0 1 10

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

80. 0.170 0.170 ↑ 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.017..0.017 rows=1 loops=10)

  • Index Cond: (pv.id = idpolicyversion)
81. 0.210 0.210 ↑ 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.021..0.021 rows=1 loops=10)

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

Index Scan using tblclient_pkey on tblclient c (cost=0.29..0.32 rows=1 width=16) (actual time=0.009..0.009 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 : 11.417 ms
Execution time : 7,726.327 ms