explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.308 1,575.051 ↓ 10.0 10 1

Result (cost=57,190.42..57,191.09 rows=1 width=460) (actual time=1,574.844..1,575.051 rows=10 loops=1)

2.          

CTE datawindow

3. 0.007 1,573.809 ↑ 1.0 10 1

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

4. 1.125 1,573.802 ↑ 14.5 10 1

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

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

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

6. 21.582 1,569.322 ↓ 83.9 12,159 1

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

  • Hash Cond: (od_1.id = pv_1.id)
7. 28.010 1,497.986 ↓ 4.0 203,763 1

Subquery Scan on od_1 (cost=32,388.87..36,462.82 rows=50,991 width=8) (actual time=1,400.572..1,497.986 rows=203,763 loops=1)

  • Filter: ((od_1.validstartdate + od_1.validdays) < now())
  • Rows Removed by Filter: 1749
8. 39.968 1,469.976 ↓ 1.3 205,512 1

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

9. 1,386.594 1,430.008 ↓ 1.0 279,388 1

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

  • Sort Key: tblofferdata.offernumber, tblofferdata.offerversion DESC
  • Sort Method: quicksort Memory: 34116kB
10. 43.414 43.414 ↓ 1.0 279,388 1

Seq Scan on tblofferdata (cost=0.00..7,115.85 rows=279,385 width=173) (actual time=0.009..43.414 rows=279,388 loops=1)

11. 1.749 49.754 ↓ 12.4 12,263 1

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

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

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

13. 0.916 17.219 ↓ 11.7 12,290 1

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

14. 0.931 0.931 ↑ 5.5 4 1

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

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

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

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

  • Index Cond: (idagency = agency_1.id)
17. 24.580 24.580 ↑ 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.002 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.006 1,574.031 ↓ 10.0 10 1

Nested Loop (cost=121.54..207.93 rows=1 width=72) (actual time=1,573.951..1,574.031 rows=10 loops=1)

20. 0.013 1,573.985 ↓ 10.0 10 1

Hash Join (cost=121.12..206.96 rows=1 width=144) (actual time=1,573.934..1,573.985 rows=10 loops=1)

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

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

22. 0.025 0.113 ↑ 1.0 10 1

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

  • Group Key: rp.idpolicyversion
23. 0.015 0.088 ↑ 3.0 10 1

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

24. 0.019 0.073 ↑ 3.0 10 1

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

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

Nested Loop (cost=0.42..119.64 rows=30 width=42) (actual time=0.023..0.054 rows=10 loops=1)

26. 0.003 0.003 ↑ 1.0 10 1

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

27. 0.050 0.050 ↑ 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.004..0.005 rows=1 loops=10)

  • Index Cond: (idpolicyversion = w_2.idpolicyversion)
28. 0.040 0.040 ↑ 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.003..0.004 rows=1 loops=10)

  • Index Cond: (id = rp.idpolicyversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
29. 0.005 1,573.818 ↑ 1.0 10 1

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=1,573.818..1,573.818 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 1,573.813 1,573.813 ↑ 1.0 10 1

CTE Scan on datawindow w_1 (cost=0.00..0.20 rows=10 width=8) (actual time=1,573.805..1,573.813 rows=10 loops=1)

31. 0.040 0.040 ↑ 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.004..0.004 rows=1 loops=10)

  • Index Cond: (id = pv_2.idpolicy)
32. 0.015 1,574.743 ↓ 10.0 10 1

Sort (cost=23.39..23.40 rows=1 width=404) (actual time=1,574.742..1,574.743 rows=10 loops=1)

  • Sort Key: pv.createdtime DESC
  • Sort Method: quicksort Memory: 30kB
33. 0.029 1,574.728 ↓ 10.0 10 1

Nested Loop Left Join (cost=5.79..23.38 rows=1 width=404) (actual time=1,574.202..1,574.728 rows=10 loops=1)

34. 0.009 1,574.689 ↓ 10.0 10 1

Nested Loop Left Join (cost=5.51..23.05 rows=1 width=607) (actual time=1,574.181..1,574.689 rows=10 loops=1)

35. 0.006 1,574.670 ↓ 10.0 10 1

Nested Loop Left Join (cost=5.22..22.75 rows=1 width=589) (actual time=1,574.175..1,574.670 rows=10 loops=1)

36. 0.006 1,574.654 ↓ 10.0 10 1

Nested Loop Left Join (cost=4.94..22.45 rows=1 width=569) (actual time=1,574.170..1,574.654 rows=10 loops=1)

37. 0.009 1,574.628 ↓ 10.0 10 1

Nested Loop Left Join (cost=4.65..22.05 rows=1 width=569) (actual time=1,574.158..1,574.628 rows=10 loops=1)

38. 0.004 1,574.599 ↓ 10.0 10 1

Nested Loop Left Join (cost=4.36..21.73 rows=1 width=561) (actual time=1,574.144..1,574.599 rows=10 loops=1)

39. 0.005 1,574.545 ↓ 10.0 10 1

Nested Loop Left Join (cost=3.79..14.57 rows=1 width=561) (actual time=1,574.121..1,574.545 rows=10 loops=1)

40. 0.025 1,574.520 ↓ 10.0 10 1

Nested Loop Left Join (cost=3.65..14.41 rows=1 width=529) (actual time=1,574.111..1,574.520 rows=10 loops=1)

  • Join Filter: (p.fallbacklocale ~~* (cd_label_fallback.locale || '%'::text))
  • Rows Removed by Join Filter: 10
41. 0.007 1,574.485 ↓ 10.0 10 1

Nested Loop Left Join (cost=3.22..13.74 rows=1 width=443) (actual time=1,574.102..1,574.485 rows=10 loops=1)

42. 0.009 1,574.418 ↓ 10.0 10 1

Nested Loop Left Join (cost=2.80..13.08 rows=1 width=354) (actual time=1,574.083..1,574.418 rows=10 loops=1)

43. 0.008 1,574.379 ↓ 10.0 10 1

Nested Loop Left Join (cost=2.66..12.91 rows=1 width=322) (actual time=1,574.072..1,574.379 rows=10 loops=1)

44. 0.009 1,574.331 ↓ 10.0 10 1

Nested Loop Left Join (cost=2.52..12.08 rows=1 width=290) (actual time=1,574.051..1,574.331 rows=10 loops=1)

45. 0.011 1,574.232 ↓ 10.0 10 1

Nested Loop (cost=1.67..10.91 rows=1 width=257) (actual time=1,574.021..1,574.232 rows=10 loops=1)

46. 0.010 1,574.191 ↓ 10.0 10 1

Nested Loop (cost=1.25..10.40 rows=1 width=239) (actual time=1,574.008..1,574.191 rows=10 loops=1)

47. 0.005 1,574.171 ↓ 10.0 10 1

Nested Loop (cost=1.12..10.24 rows=1 width=215) (actual time=1,573.997..1,574.171 rows=10 loops=1)

48. 0.009 1,574.146 ↓ 10.0 10 1

Nested Loop (cost=0.99..9.92 rows=1 width=189) (actual time=1,573.987..1,574.146 rows=10 loops=1)

49. 0.005 1,574.107 ↓ 10.0 10 1

Nested Loop (cost=0.84..9.75 rows=1 width=172) (actual time=1,573.973..1,574.107 rows=10 loops=1)

50. 0.010 1,574.082 ↓ 10.0 10 1

Nested Loop (cost=0.42..8.79 rows=1 width=116) (actual time=1,573.965..1,574.082 rows=10 loops=1)

  • Join Filter: (pv.id = w.idpolicyversion)
  • Rows Removed by Join Filter: 90
51. 0.015 1,574.062 ↓ 10.0 10 1

Nested Loop (cost=0.42..8.46 rows=1 width=108) (actual time=1,573.961..1,574.062 rows=10 loops=1)

52. 1,574.037 1,574.037 ↓ 10.0 10 1

CTE Scan on premiums (cost=0.00..0.02 rows=1 width=72) (actual time=1,573.954..1,574.037 rows=10 loops=1)

53. 0.010 0.010 ↑ 1.0 1 10

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

  • Index Cond: (id = premiums.idpolicyversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
54. 0.010 0.010 ↑ 1.0 10 10

CTE Scan on datawindow w (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.001 rows=10 loops=10)

55. 0.020 0.020 ↑ 1.0 1 10

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..0.97 rows=1 width=72) (actual time=0.002..0.002 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.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
58. 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)
59. 0.030 0.030 ↑ 1.0 1 10

Index Scan using tblofferdata_pkey on tblofferdata od (cost=0.42..0.51 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: (id = pv.id)
60. 0.010 0.090 ↑ 1.0 1 10

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

61. 0.040 0.040 ↑ 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.003..0.004 rows=1 loops=10)

  • Index Cond: (pv.id = idpolicyversion)
62. 0.040 0.040 ↑ 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.004..0.004 rows=1 loops=10)

  • Index Cond: ((idpolicyclient = pc_ubezpieczony.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 10
63. 0.040 0.040 ↑ 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.004..0.004 rows=1 loops=10)

  • Index Cond: (id = pl.id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 1
64. 0.030 0.030 ↑ 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.003..0.003 rows=1 loops=10)

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

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

  • Index Cond: (idpolicyversion = pv.id)
67. 0.020 0.020 ↑ 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.002..0.002 rows=1 loops=10)

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

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

69. 0.020 0.020 ↑ 1.0 1 10

Index Scan using tblagencyunit_pkey on tblagencyunit u2 (cost=0.28..0.31 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: (id = p.idagencyunit)
  • Filter: (nlevel(path) = 2)
70. 0.010 0.010 ↑ 1.0 1 10

Index Scan using tblagencyunit_pkey on tblagencyunit u1 (cost=0.29..6.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=10)

  • Index Cond: (id = (ltree2text(subpath(u2.path, 0, 1)))::bigint)
  • Filter: (nlevel(path) = 1)
71. 0.020 0.020 ↑ 1.0 1 10

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

  • Index Cond: (pc_ubezpieczony.idclient = id)
72. 0.020 0.020 ↑ 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.002..0.002 rows=1 loops=10)

  • Index Cond: (id = c.idmetaclient)
  • Heap Fetches: 10
73. 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)
74. 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)
75. 0.010 0.010 ↑ 1.0 1 10

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

  • Index Cond: (id = p.idemployee)
Planning time : 9.640 ms
Execution time : 1,575.688 ms