explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fWWe : Optimization for: Optimization for: Optimization for: plan #6dr9; plan #LiOX; plan #L3ao

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.369 8,327.392 ↓ 10.0 10 1

Result (cost=50,111.83..50,112.50 rows=1 width=460) (actual time=8,327.191..8,327.392 rows=10 loops=1)

2.          

CTE datawindow

3. 0.009 2,753.834 ↓ 10.0 10 1

Limit (cost=25,400.57..25,400.57 rows=1 width=24) (actual time=2,753.827..2,753.834 rows=10 loops=1)

4. 1.062 2,753.825 ↓ 10.0 10 1

Sort (cost=25,400.57..25,400.57 rows=1 width=24) (actual time=2,753.825..2,753.825 rows=10 loops=1)

  • Sort Key: pv_1.createdtime DESC
  • Sort Method: top-N heapsort Memory: 26kB
5. 5.274 2,752.763 ↓ 12,159.0 12,159 1

WindowAgg (cost=23,921.26..25,400.56 rows=1 width=24) (actual time=2,751.357..2,752.763 rows=12,159 loops=1)

6. 75.760 2,747.489 ↓ 12,159.0 12,159 1

Nested Loop (cost=23,921.26..25,400.54 rows=1 width=16) (actual time=393.393..2,747.489 rows=12,159 loops=1)

7. 100.158 2,475.145 ↓ 954.3 196,584 1

Nested Loop (cost=23,920.98..25,335.72 rows=206 width=24) (actual time=393.273..2,475.145 rows=196,584 loops=1)

8. 118.948 1,785.229 ↓ 720.1 196,586 1

Nested Loop (cost=23,920.56..25,062.47 rows=273 width=24) (actual time=393.256..1,785.229 rows=196,586 loops=1)

  • Join Filter: (od_2.id = pv_1.id)
9. 0.000 1,054.935 ↓ 437.3 203,782 1

Nested Loop (cost=23,920.14..24,660.01 rows=466 width=16) (actual time=393.236..1,054.935 rows=203,782 loops=1)

10. 128.638 461.805 ↓ 147.1 205,531 1

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

  • Group Key: od_3.id
11. 119.647 333.167 ↓ 147.1 205,531 1

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

  • Hash Cond: ((od_3.offernumber = tblofferdata_1.offernumber) AND ((od_3.offerversion)::bigint = (max((tblofferdata_1.offerversion)::bigint))))
12. 28.659 28.659 ↓ 1.0 279,388 1

Seq Scan on tblofferdata od_3 (cost=0.00..7,115.85 rows=279,385 width=21) (actual time=0.011..28.659 rows=279,388 loops=1)

13. 34.635 184.861 ↓ 1.3 205,512 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 13287kB
14. 120.663 150.226 ↓ 1.3 205,512 1

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

  • Group Key: tblofferdata_1.offernumber
15. 29.563 29.563 ↓ 1.0 279,388 1

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

16. 616.593 616.593 ↑ 1.0 1 205,531

Index Scan using tblofferdata_pkey on tblofferdata od_2 (cost=0.42..0.52 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=205,531)

  • Index Cond: (id = od_3.id)
  • Filter: ((validstartdate + validdays) < now())
  • Rows Removed by Filter: 0
17. 611.346 611.346 ↑ 1.0 1 203,782

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv_1 (cost=0.42..0.85 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=203,782)

  • Index Cond: (id = od_3.id)
  • Filter: ((canceldate IS NULL) AND (policyversionstatus = 'CURRENT'::text))
  • Rows Removed by Filter: 0
18. 589.758 589.758 ↑ 1.0 1 196,586

Index Scan using pk_tblinsurance on tblpolicy p_1 (cost=0.42..1.00 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=196,586)

  • Index Cond: (id = pv_1.idpolicy)
  • Filter: (policytype = 'OFFER'::text)
  • Rows Removed by Filter: 0
19. 196.584 196.584 ↓ 0.0 0 196,584

Index Scan using tblagencyunit_pkey on tblagencyunit agency_1 (cost=0.28..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=196,584)

  • Index Cond: (id = p_1.idagency)
  • Filter: ((ltree2text(subltree(path, 0, 1)))::integer = 2375)
  • Rows Removed by Filter: 1
20.          

CTE premiums

21. 0.015 0.207 ↓ 10.0 10 1

Nested Loop (cost=12.83..21.61 rows=1 width=72) (actual time=0.093..0.207 rows=10 loops=1)

  • Join Filter: (pv_2.id = rp.idpolicyversion)
  • Rows Removed by Join Filter: 45
22. 0.010 0.042 ↓ 10.0 10 1

Nested Loop (cost=0.84..9.43 rows=1 width=17) (actual time=0.011..0.042 rows=10 loops=1)

23. 0.010 0.022 ↓ 10.0 10 1

Nested Loop (cost=0.42..8.46 rows=1 width=24) (actual time=0.006..0.022 rows=10 loops=1)

24. 0.002 0.002 ↓ 10.0 10 1

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

25. 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 = w_1.idpolicyversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
26. 0.010 0.010 ↑ 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.001..0.001 rows=1 loops=10)

  • Index Cond: (id = pv_2.idpolicy)
27. 0.060 0.150 ↓ 6.0 6 10

GroupAggregate (cost=11.99..12.16 rows=1 width=136) (actual time=0.008..0.015 rows=6 loops=10)

  • Group Key: rp.idpolicyversion
28. 0.040 0.090 ↓ 2.0 6 10

WindowAgg (cost=11.99..12.05 rows=3 width=50) (actual time=0.006..0.009 rows=6 loops=10)

29. 0.011 0.050 ↓ 2.3 7 10

Sort (cost=11.99..11.99 rows=3 width=42) (actual time=0.005..0.005 rows=7 loops=10)

  • Sort Key: rp.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 25kB
30. 0.007 0.039 ↓ 3.3 10 1

Nested Loop (cost=0.42..11.96 rows=3 width=42) (actual time=0.013..0.039 rows=10 loops=1)

31. 0.002 0.002 ↓ 10.0 10 1

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

32. 0.030 0.030 ↑ 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.003 rows=1 loops=10)

  • Index Cond: (idpolicyversion = w_2.idpolicyversion)
33. 0.044 8,327.023 ↓ 10.0 10 1

Sort (cost=24,689.65..24,689.65 rows=1 width=404) (actual time=8,327.022..8,327.023 rows=10 loops=1)

  • Sort Key: pv.createdtime DESC
  • Sort Method: quicksort Memory: 30kB
34. 0.090 8,326.979 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,934.37..24,689.64 rows=1 width=404) (actual time=3,193.296..8,326.979 rows=10 loops=1)

35. 0.035 8,326.829 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,934.08..24,689.30 rows=1 width=607) (actual time=3,193.273..8,326.829 rows=10 loops=1)

36. 0.040 8,326.784 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,933.80..24,689.00 rows=1 width=589) (actual time=3,193.268..8,326.784 rows=10 loops=1)

37. 0.044 8,326.734 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,933.52..24,688.70 rows=1 width=569) (actual time=3,193.262..8,326.734 rows=10 loops=1)

38. 0.055 8,326.630 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,933.23..24,688.30 rows=1 width=569) (actual time=3,193.251..8,326.630 rows=10 loops=1)

39. 0.033 8,326.505 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,932.94..24,687.98 rows=1 width=561) (actual time=3,193.238..8,326.505 rows=10 loops=1)

40. 0.042 8,326.222 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,932.09..24,686.82 rows=1 width=528) (actual time=3,193.209..8,326.222 rows=10 loops=1)

41. 0.048 8,325.990 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,931.52..24,679.66 rows=1 width=528) (actual time=3,193.186..8,325.990 rows=10 loops=1)

42. 0.045 8,325.892 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,931.38..24,679.50 rows=1 width=496) (actual time=3,193.174..8,325.892 rows=10 loops=1)

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

Nested Loop Left Join (cost=23,930.95..24,678.83 rows=1 width=410) (actual time=3,193.166..8,325.827 rows=10 loops=1)

44. 0.031 8,325.666 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,930.53..24,678.17 rows=1 width=321) (actual time=3,193.147..8,325.666 rows=10 loops=1)

45. 0.044 8,325.555 ↓ 10.0 10 1

Nested Loop Left Join (cost=23,930.39..24,678.00 rows=1 width=289) (actual time=3,193.135..8,325.555 rows=10 loops=1)

46. 101.679 8,325.291 ↓ 10.0 10 1

Nested Loop (cost=23,930.25..24,677.17 rows=1 width=257) (actual time=3,193.107..8,325.291 rows=10 loops=1)

  • Join Filter: (pv.id = od.id)
  • Rows Removed by Join Filter: 2055300
47. 0.063 2,754.302 ↓ 10.0 10 1

Merge Join (cost=10.11..10.14 rows=1 width=239) (actual time=2,754.223..2,754.302 rows=10 loops=1)

  • Merge Cond: (pv.id = premiums.idpolicyversion)
48. 0.016 2,754.005 ↓ 10.0 10 1

Sort (cost=10.08..10.09 rows=1 width=167) (actual time=2,753.997..2,754.005 rows=10 loops=1)

  • Sort Key: w.idpolicyversion
  • Sort Method: quicksort Memory: 27kB
49. 0.008 2,753.989 ↓ 10.0 10 1

Nested Loop (cost=1.25..10.07 rows=1 width=167) (actual time=2,753.890..2,753.989 rows=10 loops=1)

50. 0.003 2,753.971 ↓ 10.0 10 1

Nested Loop (cost=1.12..9.92 rows=1 width=143) (actual time=2,753.881..2,753.971 rows=10 loops=1)

51. 0.005 2,753.948 ↓ 10.0 10 1

Nested Loop (cost=0.99..9.59 rows=1 width=117) (actual time=2,753.870..2,753.948 rows=10 loops=1)

52. 0.004 2,753.913 ↓ 10.0 10 1

Nested Loop (cost=0.84..9.43 rows=1 width=100) (actual time=2,753.856..2,753.913 rows=10 loops=1)

53. 0.009 2,753.879 ↓ 10.0 10 1

Nested Loop (cost=0.42..8.46 rows=1 width=44) (actual time=2,753.846..2,753.879 rows=10 loops=1)

54. 2,753.840 2,753.840 ↓ 10.0 10 1

CTE Scan on datawindow w (cost=0.00..0.02 rows=1 width=8) (actual time=2,753.829..2,753.840 rows=10 loops=1)

55. 0.030 0.030 ↑ 1.0 1 10

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

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

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

  • Index Cond: (id = pv.idpolicy)
57. 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)
58. 0.020 0.020 ↑ 1.0 1 10

Index Only Scan using tblproductline_pkey on tblproductline pl (cost=0.14..0.31 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: (id = ga.idproductline)
  • Heap Fetches: 10
59. 0.010 0.010 ↑ 1.0 1 10

Index Scan using tblinsurancecompanyunit_pkey on tblinsurancecompanyunit cu (cost=0.13..0.15 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=10)

  • Index Cond: (id = p.idinsurancecompany)
60. 0.022 0.234 ↓ 10.0 10 1

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

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

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

62. 444.410 5,469.310 ↓ 147.1 205,531 10

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

63. 581.091 914.280 ↓ 147.1 205,531 10

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

  • Group Key: od_1.id
64. 116.763 333.189 ↓ 147.1 205,531 1

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

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

66. 38.288 187.696 ↓ 1.3 205,512 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 13287kB
67. 122.388 149.408 ↓ 1.3 205,512 1

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

  • Group Key: tblofferdata.offernumber
68. 27.020 27.020 ↓ 1.0 279,388 1

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

69. 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)
70. 0.220 0.220 ↑ 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.021..0.022 rows=1 loops=10)

  • Index Cond: (id = pl.id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 1
71. 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
72. 0.120 0.120 ↑ 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.012..0.012 rows=1 loops=10)

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

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

  • Index Cond: (key = p.keyintegration)
75. 0.070 0.190 ↑ 1.0 1 10

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

76. 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)
77. 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)
78. 0.060 0.250 ↑ 1.0 1 10

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

79. 0.080 0.080 ↑ 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.008..0.008 rows=1 loops=10)

  • Index Cond: (pv.id = idpolicyversion)
80. 0.110 0.110 ↑ 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.011..0.011 rows=1 loops=10)

  • Index Cond: ((idpolicyclient = pc_ubezpieczony.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 10
81. 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)
82. 0.060 0.060 ↑ 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.006..0.006 rows=1 loops=10)

  • Index Cond: (id = c.idmetaclient)
  • Heap Fetches: 10
83. 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)
84. 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)
85. 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 : 13.870 ms
Execution time : 8,334.173 ms