explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4UNE

Settings
# exclusive inclusive rows x rows loops node
1. 0.379 2,228.883 ↓ 25.0 25 1

Result (cost=254,288.18..254,288.47 rows=1 width=385) (actual time=2,228.610..2,228.883 rows=25 loops=1)

2.          

CTE policiespremiums

3. 470.207 1,847.085 ↑ 1.3 271,883 1

HashAggregate (cost=162,059.13..167,376.54 rows=354,494 width=41) (actual time=1,617.868..1,847.085 rows=271,883 loops=1)

  • Group Key: rp.idpolicyversion, p_1.showannualpremium
4. 143.314 1,376.878 ↓ 1.0 430,679 1

Hash Join (cost=136,886.05..156,841.50 rows=417,410 width=27) (actual time=646.643..1,376.878 rows=430,679 loops=1)

  • Hash Cond: (pv_1.idpolicy = p_1.id)
5. 175.713 1,043.370 ↓ 1.0 430,679 1

Hash Join (cost=97,871.75..116,731.47 rows=417,410 width=34) (actual time=455.946..1,043.370 rows=430,679 loops=1)

  • Hash Cond: (rp.idpolicyversion = pv_1.id)
6. 308.419 735.164 ↑ 1.0 536,931 1

WindowAgg (cost=63,808.05..75,888.99 rows=536,931 width=40) (actual time=323.011..735.164 rows=536,931 loops=1)

7. 352.414 426.745 ↑ 1.0 536,931 1

Sort (cost=63,808.05..65,150.37 rows=536,931 width=32) (actual time=322.995..426.745 rows=536,931 loops=1)

  • Sort Key: rp.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 71781kB
8. 74.331 74.331 ↑ 1.0 536,931 1

Seq Scan on tblriskpremium rp (cost=0.00..12,707.31 rows=536,931 width=32) (actual time=0.012..74.331 rows=536,931 loops=1)

9. 50.975 132.493 ↑ 1.0 271,963 1

Hash (cost=30,662.76..30,662.76 rows=272,075 width=16) (actual time=132.492..132.493 rows=271,963 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 16845kB
10. 81.518 81.518 ↑ 1.0 271,963 1

Seq Scan on tblpolicyversion pv_1 (cost=0.00..30,662.76 rows=272,075 width=16) (actual time=0.018..81.518 rows=271,963 loops=1)

  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 78018
11. 54.108 190.194 ↑ 1.0 271,914 1

Hash (cost=35,602.47..35,602.47 rows=272,947 width=9) (actual time=190.194..190.194 rows=271,914 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 16842kB
12. 136.086 136.086 ↑ 1.0 271,914 1

Seq Scan on tblpolicy p_1 (cost=0.00..35,602.47 rows=272,947 width=9) (actual time=0.016..136.086 rows=271,914 loops=1)

13.          

CTE policyversionwindow

14. 0.009 2,225.927 ↑ 1.0 25 1

Limit (cost=86,350.93..86,350.99 rows=25 width=48) (actual time=2,225.919..2,225.927 rows=25 loops=1)

15. 1.791 2,225.918 ↑ 797.0 25 1

Sort (cost=86,350.93..86,400.74 rows=19,925 width=48) (actual time=2,225.916..2,225.918 rows=25 loops=1)

  • Sort Key: policiespremiums.premium, pv_2.id
  • Sort Method: top-N heapsort Memory: 28kB
16. 6.188 2,224.127 ↑ 1.6 12,506 1

WindowAgg (cost=76,921.12..85,788.66 rows=19,925 width=48) (actual time=2,220.811..2,224.127 rows=12,506 loops=1)

17. 38.075 2,217.939 ↑ 1.6 12,506 1

Hash Join (cost=76,921.12..85,539.60 rows=19,925 width=40) (actual time=1,888.366..2,217.939 rows=12,506 loops=1)

  • Hash Cond: (policiespremiums.idpolicyversion = pv_2.id)
18. 1,909.420 1,909.420 ↑ 1.3 271,883 1

CTE Scan on policiespremiums (cost=0.00..7,089.88 rows=354,494 width=40) (actual time=1,617.872..1,909.420 rows=271,883 loops=1)

19. 2.086 270.444 ↑ 1.6 12,506 1

Hash (cost=76,675.23..76,675.23 rows=19,671 width=8) (actual time=270.444..270.444 rows=12,506 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 745kB
20. 20.865 268.358 ↑ 1.6 12,506 1

Hash Join (cost=37,024.88..76,675.23 rows=19,671 width=8) (actual time=94.816..268.358 rows=12,506 loops=1)

  • Hash Cond: (pv_2.idpolicy = p_2.id)
21. 152.787 152.787 ↑ 1.0 88,544 1

Seq Scan on tblpolicyversion pv_2 (cost=0.00..39,412.29 rows=90,692 width=16) (actual time=0.032..152.787 rows=88,544 loops=1)

  • Filter: ((policyversionstatus = 'CURRENT'::text) AND ((enddate - (((((NOT isenddateclosed))::integer)::text || ' day'::text))::interval) >= (now())::date))
  • Rows Removed by Filter: 261437
22. 7.256 94.706 ↑ 1.2 51,327 1

Hash (cost=36,284.84..36,284.84 rows=59,203 width=8) (actual time=94.706..94.706 rows=51,327 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2517kB
23. 87.450 87.450 ↑ 1.2 51,327 1

Seq Scan on tblpolicy p_2 (cost=0.00..36,284.84 rows=59,203 width=8) (actual time=0.010..87.450 rows=51,327 loops=1)

  • Filter: ((NOT test) AND (canceldate IS NULL) AND (policytype = 'POLICY'::text))
  • Rows Removed by Filter: 220587
24.          

CTE premiums

25. 0.038 2,226.182 ↑ 1.0 25 1

GroupAggregate (cost=301.46..305.76 rows=25 width=136) (actual time=2,226.143..2,226.182 rows=25 loops=1)

  • Group Key: rp_1.idpolicyversion
26. 0.024 2,226.144 ↑ 3.0 25 1

WindowAgg (cost=301.46..303.17 rows=76 width=50) (actual time=2,226.130..2,226.144 rows=25 loops=1)

27. 0.014 2,226.120 ↑ 3.0 25 1

Sort (cost=301.46..301.65 rows=76 width=42) (actual time=2,226.119..2,226.120 rows=25 loops=1)

  • Sort Key: rp_1.idpolicyversion, rp_1.riskname, rp_1.validfrom DESC
  • Sort Method: quicksort Memory: 26kB
28. 0.014 2,226.106 ↑ 3.0 25 1

Nested Loop (cost=0.42..299.09 rows=76 width=42) (actual time=2,225.955..2,226.106 rows=25 loops=1)

29. 2,225.942 2,225.942 ↑ 1.0 25 1

CTE Scan on policyversionwindow w (cost=0.00..0.50 rows=25 width=8) (actual time=2,225.921..2,225.942 rows=25 loops=1)

30. 0.150 0.150 ↑ 3.0 1 25

Index Scan using tblriskpremium_idpolicyversion_index on tblriskpremium rp_1 (cost=0.42..11.91 rows=3 width=42) (actual time=0.006..0.006 rows=1 loops=25)

  • Index Cond: (idpolicyversion = w.idpolicyversion)
31. 0.036 2,228.504 ↓ 25.0 25 1

Sort (cost=254.89..254.90 rows=1 width=361) (actual time=2,228.502..2,228.504 rows=25 loops=1)

  • Sort Key: (CASE WHEN p.showannualpremium THEN premiums.annualpremium ELSE premiums.premium END), pv.id
  • Sort Method: quicksort Memory: 37kB
32. 0.069 2,228.468 ↓ 25.0 25 1

Nested Loop Left Join (cost=6.47..254.88 rows=1 width=361) (actual time=2,226.512..2,228.468 rows=25 loops=1)

  • Join Filter: (p.fallbacklocale ~~* (cd_label_fallback.locale || '%'::text))
  • Rows Removed by Join Filter: 30
33. 0.003 2,228.374 ↓ 25.0 25 1

Nested Loop Left Join (cost=6.05..254.19 rows=1 width=546) (actual time=2,226.496..2,228.374 rows=25 loops=1)

34. 0.024 2,228.196 ↓ 25.0 25 1

Nested Loop Left Join (cost=5.62..253.53 rows=1 width=457) (actual time=2,226.478..2,228.196 rows=25 loops=1)

35. 0.027 2,228.072 ↓ 25.0 25 1

Nested Loop Left Join (cost=5.33..253.18 rows=1 width=433) (actual time=2,226.461..2,228.072 rows=25 loops=1)

36. 0.030 2,227.945 ↓ 25.0 25 1

Nested Loop Left Join (cost=5.04..252.86 rows=1 width=425) (actual time=2,226.449..2,227.945 rows=25 loops=1)

37. 0.033 2,227.890 ↓ 25.0 25 1

Nested Loop Left Join (cost=4.90..252.70 rows=1 width=393) (actual time=2,226.435..2,227.890 rows=25 loops=1)

  • Join Filter: (p.idinsurancetype = it_label.id)
  • Rows Removed by Join Filter: 200
38. 0.059 2,227.582 ↓ 25.0 25 1

Nested Loop Left Join (cost=4.90..251.30 rows=1 width=361) (actual time=2,226.414..2,227.582 rows=25 loops=1)

  • Join Filter: (pl_label.id = pl.id)
  • Rows Removed by Join Filter: 375
39. 0.024 2,227.173 ↓ 25.0 25 1

Nested Loop (cost=4.90..249.81 rows=1 width=329) (actual time=2,226.372..2,227.173 rows=25 loops=1)

40. 0.017 2,226.963 ↓ 62.0 62 1

Nested Loop (cost=4.48..249.27 rows=1 width=337) (actual time=2,226.351..2,226.963 rows=62 loops=1)

41. 0.028 2,226.796 ↓ 25.0 25 1

Nested Loop (cost=4.05..248.64 rows=1 width=312) (actual time=2,226.338..2,226.796 rows=25 loops=1)

42. 0.018 2,226.743 ↓ 25.0 25 1

Hash Join (cost=3.77..248.34 rows=1 width=296) (actual time=2,226.322..2,226.743 rows=25 loops=1)

  • Hash Cond: (pv.id = premiums.idpolicyversion)
43. 0.014 0.523 ↓ 1.3 25 1

Nested Loop (cost=2.96..247.44 rows=19 width=224) (actual time=0.111..0.523 rows=25 loops=1)

44. 0.021 0.484 ↓ 1.3 25 1

Nested Loop (cost=2.67..241.71 rows=19 width=206) (actual time=0.104..0.484 rows=25 loops=1)

45. 0.023 0.438 ↓ 1.3 25 1

Hash Join (cost=2.39..235.98 rows=19 width=186) (actual time=0.092..0.438 rows=25 loops=1)

  • Hash Cond: (ga.idproductline = pl.id)
46. 0.004 0.404 ↓ 1.3 25 1

Nested Loop (cost=0.99..234.52 rows=19 width=160) (actual time=0.066..0.404 rows=25 loops=1)

47. 0.003 0.325 ↓ 1.3 25 1

Nested Loop (cost=0.84..231.47 rows=19 width=154) (actual time=0.049..0.325 rows=25 loops=1)

  • Join Filter: (p.idinsurancecompany = cu.id)
  • Rows Removed by Join Filter: 25
48. 0.024 0.297 ↓ 1.3 25 1

Nested Loop (cost=0.84..229.94 rows=19 width=130) (actual time=0.032..0.297 rows=25 loops=1)

49. 0.017 0.148 ↓ 1.3 25 1

Nested Loop (cost=0.42..211.56 rows=19 width=44) (actual time=0.017..0.148 rows=25 loops=1)

50. 0.006 0.006 ↑ 1.0 25 1

CTE Scan on policyversionwindow pvw (cost=0.00..0.50 rows=25 width=16) (actual time=0.001..0.006 rows=25 loops=1)

51. 0.125 0.125 ↑ 1.0 1 25

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

  • Index Cond: (id = pvw.idpolicyversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
52. 0.125 0.125 ↑ 1.0 1 25

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

  • Index Cond: (id = pv.idpolicy)
53. 0.013 0.025 ↑ 1.0 2 25

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

54. 0.012 0.012 ↑ 1.0 2 1

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

55. 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)
56. 0.003 0.011 ↑ 1.0 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 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)

58. 0.025 0.025 ↑ 1.0 1 25

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

  • Index Cond: (id = p.idagency)
59. 0.025 0.025 ↑ 1.0 1 25

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

  • Index Cond: (id = p.idagencyunit)
60. 0.009 2,226.202 ↑ 1.0 25 1

Hash (cost=0.50..0.50 rows=25 width=72) (actual time=2,226.201..2,226.202 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
61. 2,226.193 2,226.193 ↑ 1.0 25 1

CTE Scan on premiums (cost=0.00..0.50 rows=25 width=72) (actual time=2,226.146..2,226.193 rows=25 loops=1)

62. 0.025 0.025 ↑ 1.0 1 25

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

  • Index Cond: (id = p.idemployee)
63. 0.150 0.150 ↓ 2.0 2 25

Index Scan using tblpolicyclient_idpolicyversion on tblpolicyclient pc_ubezpieczony (cost=0.42..0.62 rows=1 width=49) (actual time=0.005..0.006 rows=2 loops=25)

  • Index Cond: (idpolicyversion = pv.id)
64. 0.186 0.186 ↓ 0.0 0 62

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=0 loops=62)

  • Index Cond: ((idpolicyclient = pc_ubezpieczony.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 25
65. 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
66. 0.275 0.275 ↓ 9.0 9 25

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

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

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

  • Index Cond: (key = p.keyintegration)
68. 0.100 0.100 ↑ 1.0 1 25

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

  • Index Cond: (pc_ubezpieczony.idclient = id)
69. 0.100 0.100 ↑ 1.0 1 25

Index Scan using tblnumber_idpolicy_indx on tblnumber n (cost=0.29..0.34 rows=1 width=40) (actual time=0.004..0.004 rows=1 loops=25)

  • Index Cond: (p.id = idpolicy)
70. 0.175 0.175 ↑ 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.006..0.007 rows=1 loops=25)

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

  • Index Cond: (idpolicyversion = pv.id)
Planning time : 11.347 ms
Execution time : 2,250.535 ms