explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6dr9

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.328 8,447.432 ↓ 10.0 10 1

Result (cost=50,629.36..50,630.03 rows=1 width=460) (actual time=8,447.238..8,447.432 rows=10 loops=1)

2.          

CTE datawindow

3. 0.008 2,672.237 ↓ 10.0 10 1

Limit (cost=25,605.90..25,605.90 rows=1 width=24) (actual time=2,672.230..2,672.237 rows=10 loops=1)

4. 1.042 2,672.229 ↓ 10.0 10 1

Sort (cost=25,605.90..25,605.90 rows=1 width=24) (actual time=2,672.228..2,672.229 rows=10 loops=1)

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

WindowAgg (cost=24,227.76..25,605.89 rows=1 width=24) (actual time=2,669.892..2,671.187 rows=12,159 loops=1)

6. 84.267 2,665.721 ↓ 12,159.0 12,159 1

Nested Loop (cost=24,227.76..25,605.88 rows=1 width=16) (actual time=386.273..2,665.721 rows=12,159 loops=1)

7. 47.106 2,384.998 ↓ 953.7 196,456 1

Nested Loop (cost=24,227.48..25,541.05 rows=206 width=24) (actual time=386.159..2,384.998 rows=196,456 loops=1)

8. 77.095 1,748.518 ↓ 719.6 196,458 1

Nested Loop (cost=24,227.05..25,280.82 rows=273 width=24) (actual time=386.145..1,748.518 rows=196,458 loops=1)

  • Join Filter: (od_2.id = pv_1.id)
9. 0.000 1,060.470 ↓ 434.2 203,651 1

Nested Loop (cost=24,226.63..24,971.79 rows=469 width=16) (actual time=386.125..1,060.470 rows=203,651 loops=1)

10. 121.611 455.207 ↓ 147.3 207,270 1

HashAggregate (cost=24,226.21..24,240.28 rows=1,407 width=8) (actual time=386.096..455.207 rows=207,270 loops=1)

  • Group Key: od_3.id
11. 115.152 333.596 ↓ 147.3 207,270 1

Hash Join (cost=15,507.93..24,222.69 rows=1,407 width=8) (actual time=190.275..333.596 rows=207,270 loops=1)

  • Hash Cond: ((od_3.offernumber = tblofferdata_1.offernumber) AND ((od_3.offerversion)::bigint = (max((tblofferdata_1.offerversion)::bigint))))
12. 28.401 28.401 ↓ 1.0 281,405 1

Seq Scan on tblofferdata od_3 (cost=0.00..7,167.04 rows=281,404 width=21) (actual time=0.009..28.401 rows=281,405 loops=1)

13. 33.652 190.043 ↓ 1.3 207,251 1

Hash (cost=13,139.28..13,139.28 rows=157,910 width=19) (actual time=190.042..190.043 rows=207,251 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13383kB
14. 127.477 156.391 ↓ 1.3 207,251 1

HashAggregate (cost=9,981.08..11,560.18 rows=157,910 width=19) (actual time=107.708..156.391 rows=207,251 loops=1)

  • Group Key: tblofferdata_1.offernumber
15. 28.914 28.914 ↓ 1.0 281,405 1

Seq Scan on tblofferdata tblofferdata_1 (cost=0.00..7,167.04 rows=281,404 width=13) (actual time=0.009..28.914 rows=281,405 loops=1)

16. 621.810 621.810 ↑ 1.0 1 207,270

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=207,270)

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

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

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

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

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

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,456)

  • 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.227 ↓ 10.0 10 1

Nested Loop (cost=13.29..22.02 rows=1 width=72) (actual time=0.098..0.227 rows=10 loops=1)

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

Nested Loop (cost=0.84..9.38 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.000..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.020 0.020 ↑ 1.0 1 10

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

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

GroupAggregate (cost=12.44..12.61 rows=1 width=136) (actual time=0.008..0.017 rows=6 loops=10)

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

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

29. 0.012 0.050 ↓ 2.3 7 10

Sort (cost=12.44..12.45 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.006 0.038 ↓ 3.3 10 1

Nested Loop (cost=0.42..12.42 rows=3 width=42) (actual time=0.013..0.038 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..12.37 rows=3 width=42) (actual time=0.003..0.003 rows=1 loops=10)

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

Sort (cost=25,001.43..25,001.44 rows=1 width=404) (actual time=8,447.102..8,447.104 rows=10 loops=1)

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

Nested Loop Left Join (cost=24,240.82..25,001.42 rows=1 width=404) (actual time=3,132.244..8,447.059 rows=10 loops=1)

35. 0.036 8,446.928 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,240.53..25,001.09 rows=1 width=607) (actual time=3,132.226..8,446.928 rows=10 loops=1)

36. 0.031 8,446.882 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,240.25..25,000.79 rows=1 width=589) (actual time=3,132.220..8,446.882 rows=10 loops=1)

37. 0.039 8,446.841 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,239.97..25,000.49 rows=1 width=569) (actual time=3,132.215..8,446.841 rows=10 loops=1)

38. 0.045 8,446.742 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,239.68..25,000.09 rows=1 width=569) (actual time=3,132.204..8,446.742 rows=10 loops=1)

39. 0.034 8,446.637 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,239.39..24,999.77 rows=1 width=561) (actual time=3,132.193..8,446.637 rows=10 loops=1)

40. 0.031 8,446.363 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,238.54..24,998.60 rows=1 width=528) (actual time=3,132.166..8,446.363 rows=10 loops=1)

41. 0.041 8,446.152 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,237.96..24,991.45 rows=1 width=528) (actual time=3,132.144..8,446.152 rows=10 loops=1)

42. 0.042 8,446.061 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,237.82..24,991.29 rows=1 width=496) (actual time=3,132.135..8,446.061 rows=10 loops=1)

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

Nested Loop Left Join (cost=24,237.40..24,990.62 rows=1 width=410) (actual time=3,132.127..8,445.999 rows=10 loops=1)

44. 0.040 8,445.838 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,236.97..24,989.96 rows=1 width=321) (actual time=3,132.108..8,445.838 rows=10 loops=1)

45. 0.046 8,445.728 ↓ 10.0 10 1

Nested Loop Left Join (cost=24,236.84..24,989.79 rows=1 width=289) (actual time=3,132.095..8,445.728 rows=10 loops=1)

46. 106.884 8,445.482 ↓ 10.0 10 1

Nested Loop (cost=24,236.70..24,988.95 rows=1 width=257) (actual time=3,132.068..8,445.482 rows=10 loops=1)

  • Join Filter: (pv.id = od.id)
  • Rows Removed by Join Filter: 2072690
47. 0.062 2,672.718 ↓ 10.0 10 1

Merge Join (cost=10.07..10.09 rows=1 width=239) (actual time=2,672.639..2,672.718 rows=10 loops=1)

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

Sort (cost=10.04..10.04 rows=1 width=167) (actual time=2,672.393..2,672.403 rows=10 loops=1)

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

Nested Loop (cost=1.25..10.03 rows=1 width=167) (actual time=2,672.289..2,672.385 rows=10 loops=1)

50. 0.003 2,672.370 ↓ 10.0 10 1

Nested Loop (cost=1.12..9.87 rows=1 width=143) (actual time=2,672.282..2,672.370 rows=10 loops=1)

51. 0.004 2,672.347 ↓ 10.0 10 1

Nested Loop (cost=0.99..9.54 rows=1 width=117) (actual time=2,672.271..2,672.347 rows=10 loops=1)

52. 0.007 2,672.313 ↓ 10.0 10 1

Nested Loop (cost=0.84..9.38 rows=1 width=100) (actual time=2,672.257..2,672.313 rows=10 loops=1)

53. 0.004 2,672.276 ↓ 10.0 10 1

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

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

CTE Scan on datawindow w (cost=0.00..0.02 rows=1 width=8) (actual time=2,672.232..2,672.242 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.92 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.021 0.253 ↓ 10.0 10 1

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

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

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

62. 568.820 5,665.880 ↓ 147.3 207,270 10

Nested Loop (cost=24,226.63..24,961.27 rows=1,407 width=50) (actual time=41.099..566.588 rows=207,270 loops=10)

63. 605.288 951.660 ↓ 147.3 207,270 10

HashAggregate (cost=24,226.21..24,240.28 rows=1,407 width=8) (actual time=41.094..95.166 rows=207,270 loops=10)

  • Group Key: od_1.id
64. 124.403 346.372 ↓ 147.3 207,270 1

Hash Join (cost=15,507.93..24,222.69 rows=1,407 width=8) (actual time=192.597..346.372 rows=207,270 loops=1)

  • Hash Cond: ((od_1.offernumber = tblofferdata.offernumber) AND ((od_1.offerversion)::bigint = (max((tblofferdata.offerversion)::bigint))))
65. 29.600 29.600 ↓ 1.0 281,405 1

Seq Scan on tblofferdata od_1 (cost=0.00..7,167.04 rows=281,404 width=21) (actual time=0.009..29.600 rows=281,405 loops=1)

66. 35.457 192.369 ↓ 1.3 207,251 1

Hash (cost=13,139.28..13,139.28 rows=157,910 width=19) (actual time=192.368..192.369 rows=207,251 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13383kB
67. 129.047 156.912 ↓ 1.3 207,251 1

HashAggregate (cost=9,981.08..11,560.18 rows=157,910 width=19) (actual time=107.966..156.912 rows=207,251 loops=1)

  • Group Key: tblofferdata.offernumber
68. 27.865 27.865 ↓ 1.0 281,405 1

Seq Scan on tblofferdata (cost=0.00..7,167.04 rows=281,404 width=13) (actual time=0.005..27.865 rows=281,405 loops=1)

69. 4,145.400 4,145.400 ↑ 1.0 1 2,072,700

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,072,700)

  • Index Cond: (id = od_1.id)
70. 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
71. 0.070 0.070 ↑ 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.007 rows=1 loops=10)

  • Index Cond: (p.idinsurancetype = id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 2
72. 0.130 0.130 ↑ 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.013 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.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)

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.240 ↑ 1.0 1 10

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

79. 0.090 0.090 ↑ 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.009 rows=1 loops=10)

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

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

Index Scan using tblclient_pkey on tblclient c (cost=0.29..0.32 rows=1 width=16) (actual time=0.006..0.006 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.050 0.050 ↑ 1.0 1 10

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

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