explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S63d : Optimization for: plan #KQOB

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.076 94.635 ↓ 109.0 109 1

Sort (cost=1,640.31..1,640.31 rows=1 width=323) (actual time=94.627..94.635 rows=109 loops=1)

  • Sort Key: (CASE WHEN (paym.createdtime < '2017-04-01'::date) THEN (paym.paymentdate)::date ELSE (acc_entry.createdtime)::date END) DESC
  • Sort Method: quicksort Memory: 53kB
2.          

CTE datawindow

3. 0.011 87.722 ↓ 54.5 109 1

Append (cost=19.77..1,589.57 rows=2 width=16) (actual time=67.357..87.722 rows=109 loops=1)

4. 0.000 43.393 ↓ 0.0 0 1

Nested Loop (cost=19.77..788.48 rows=1 width=16) (actual time=43.393..43.393 rows=0 loops=1)

5. 0.001 43.393 ↓ 0.0 0 1

Nested Loop (cost=19.34..780.30 rows=1 width=8) (actual time=43.393..43.393 rows=0 loops=1)

6. 6.422 43.392 ↓ 0.0 0 1

Nested Loop (cost=18.92..779.39 rows=1 width=24) (actual time=43.392..43.392 rows=0 loops=1)

7. 1.418 17.281 ↓ 328.1 19,689 1

Nested Loop (cost=18.50..741.88 rows=60 width=8) (actual time=2.090..17.281 rows=19,689 loops=1)

8. 0.005 0.818 ↓ 3.0 3 1

Nested Loop (cost=4.73..66.26 rows=1 width=8) (actual time=0.440..0.818 rows=3 loops=1)

9. 0.014 0.014 ↑ 1.0 1 1

Index Scan using tblagencyunit_pkey on tblagencyunit u1_1 (cost=0.28..8.31 rows=1 width=8) (actual time=0.012..0.014 rows=1 loops=1)

  • Index Cond: (id = 2375)
  • Filter: (nlevel(path) = 1)
10. 0.722 0.799 ↓ 3.0 3 1

Bitmap Heap Scan on tblagencyunit u2_1 (cost=4.45..57.95 rows=1 width=25) (actual time=0.425..0.799 rows=3 loops=1)

  • Recheck Cond: (nlevel(path) = 2)
  • Filter: ((ltree2text(subpath(path, 0, 1)))::bigint = 2375)
  • Rows Removed by Filter: 2618
  • Heap Blocks: exact=97
11. 0.077 0.077 ↓ 119.1 2,621 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..4.45 rows=22 width=0) (actual time=0.077..0.077 rows=2,621 loops=1)

  • Index Cond: (nlevel(path) = 2)
12. 13.914 15.045 ↓ 39.8 6,563 3

Bitmap Heap Scan on tblpolicy p_1 (cost=13.77..673.97 rows=165 width=16) (actual time=0.553..5.015 rows=6,563 loops=3)

  • Recheck Cond: (idagencyunit = u2_1.id)
  • Filter: (NOT test)
  • Heap Blocks: exact=5465
13. 1.131 1.131 ↓ 37.7 6,563 3

Bitmap Index Scan on tblpolicy_idagencyunit (cost=0.00..13.73 rows=174 width=0) (actual time=0.377..0.377 rows=6,563 loops=3)

  • Index Cond: (idagencyunit = u2_1.id)
14. 19.689 19.689 ↓ 0.0 0 19,689

Index Scan using tblpayment_idpolicy on tblpayment paym_1 (cost=0.42..0.62 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=19,689)

  • Index Cond: (idpolicy = p_1.id)
  • Filter: ((createdtime < '2017-04-01'::date) AND (date_trunc('month'::text, paymentdate) = '2019-05-01 00:00:00+00'::timestamp with time zone) AND ((canceldate IS NULL) OR (date_trunc('month'::text, createdtime) <> date_trunc('month'::text, canceldate))))
  • Rows Removed by Filter: 0
15. 0.000 0.000 ↓ 0.0 0

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv_2 (cost=0.42..0.90 rows=1 width=8) (never executed)

  • Index Cond: (idpolicy = p_1.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using tblaccountingentry_idpayment on tblaccountingentry acc_entry_1 (cost=0.42..8.17 rows=1 width=24) (never executed)

  • Index Cond: (idpayment = paym_1.id)
  • Filter: (type = ANY ('{CREATE_PAYMENT,CANCEL_PAYMENT}'::text[]))
17. 0.019 44.318 ↓ 109.0 109 1

Nested Loop (cost=19.77..801.06 rows=1 width=16) (actual time=23.962..44.318 rows=109 loops=1)

18. 0.034 43.972 ↓ 109.0 109 1

Nested Loop (cost=19.34..800.15 rows=1 width=32) (actual time=23.948..43.972 rows=109 loops=1)

19. 3.741 38.613 ↓ 98.6 1,775 1

Nested Loop (cost=18.92..779.09 rows=18 width=24) (actual time=21.997..38.613 rows=1,775 loops=1)

20. 1.326 15.183 ↓ 328.1 19,689 1

Nested Loop (cost=18.50..741.88 rows=60 width=8) (actual time=1.994..15.183 rows=19,689 loops=1)

21. 0.005 0.765 ↓ 3.0 3 1

Nested Loop (cost=4.73..66.26 rows=1 width=8) (actual time=0.408..0.765 rows=3 loops=1)

22. 0.010 0.010 ↑ 1.0 1 1

Index Scan using tblagencyunit_pkey on tblagencyunit u1_2 (cost=0.28..8.31 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (id = 2375)
  • Filter: (nlevel(path) = 1)
23. 0.667 0.750 ↓ 3.0 3 1

Bitmap Heap Scan on tblagencyunit u2_2 (cost=4.45..57.95 rows=1 width=25) (actual time=0.395..0.750 rows=3 loops=1)

  • Recheck Cond: (nlevel(path) = 2)
  • Filter: ((ltree2text(subpath(path, 0, 1)))::bigint = 2375)
  • Rows Removed by Filter: 2618
  • Heap Blocks: exact=97
24. 0.083 0.083 ↓ 119.1 2,621 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..4.45 rows=22 width=0) (actual time=0.082..0.083 rows=2,621 loops=1)

  • Index Cond: (nlevel(path) = 2)
25. 12.009 13.092 ↓ 39.8 6,563 3

Bitmap Heap Scan on tblpolicy p_2 (cost=13.77..673.97 rows=165 width=16) (actual time=0.532..4.364 rows=6,563 loops=3)

  • Recheck Cond: (idagencyunit = u2_2.id)
  • Filter: (NOT test)
  • Heap Blocks: exact=5465
26. 1.083 1.083 ↓ 37.7 6,563 3

Bitmap Index Scan on tblpolicy_idagencyunit (cost=0.00..13.73 rows=174 width=0) (actual time=0.361..0.361 rows=6,563 loops=3)

  • Index Cond: (idagencyunit = u2_2.id)
27. 19.689 19.689 ↓ 0.0 0 19,689

Index Scan using tblpayment_idpolicy on tblpayment paym_2 (cost=0.42..0.59 rows=3 width=16) (actual time=0.001..0.001 rows=0 loops=19,689)

  • Index Cond: (idpolicy = p_2.id)
  • Filter: ((createdtime >= '2017-04-01'::date) AND ((canceldate IS NULL) OR (date_trunc('month'::text, createdtime) <> date_trunc('month'::text, canceldate))))
  • Rows Removed by Filter: 0
28. 5.325 5.325 ↓ 0.0 0 1,775

Index Scan using tblaccountingentry_idpayment on tblaccountingentry acc_entry_2 (cost=0.42..1.16 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=1,775)

  • Index Cond: (idpayment = paym_2.id)
  • Filter: ((type = ANY ('{CREATE_PAYMENT,CANCEL_PAYMENT}'::text[])) AND (date_trunc('month'::text, createdtime) = '2019-05-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 1
29. 0.327 0.327 ↑ 1.0 1 109

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv_3 (cost=0.42..0.90 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=109)

  • Index Cond: (idpolicy = p_2.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
30.          

CTE premiums

31. 0.147 88.721 ↓ 54.5 109 1

GroupAggregate (cost=24.00..24.34 rows=2 width=136) (actual time=88.502..88.721 rows=109 loops=1)

  • Group Key: rp.idpolicyversion
32. 0.084 88.574 ↓ 18.2 109 1

WindowAgg (cost=24.00..24.14 rows=6 width=50) (actual time=88.489..88.574 rows=109 loops=1)

33. 0.049 88.490 ↓ 18.2 109 1

Sort (cost=24.00..24.02 rows=6 width=42) (actual time=88.479..88.490 rows=109 loops=1)

  • Sort Key: rp.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 33kB
34. 0.040 88.441 ↓ 18.2 109 1

Nested Loop (cost=0.42..23.93 rows=6 width=42) (actual time=67.380..88.441 rows=109 loops=1)

35. 87.747 87.747 ↓ 54.5 109 1

CTE Scan on datawindow w_1 (cost=0.00..0.04 rows=2 width=8) (actual time=67.359..87.747 rows=109 loops=1)

36. 0.654 0.654 ↑ 3.0 1 109

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

  • Index Cond: (idpolicyversion = w_1.idpolicyversion)
37. 0.354 94.559 ↓ 109.0 109 1

Nested Loop (cost=4.66..26.38 rows=1 width=323) (actual time=90.074..94.559 rows=109 loops=1)

38. 0.106 93.713 ↓ 246.0 246 1

Nested Loop (cost=4.24..25.77 rows=1 width=228) (actual time=90.041..93.713 rows=246 loops=1)

39. 0.005 93.280 ↓ 109.0 109 1

Nested Loop (cost=3.81..25.18 rows=1 width=216) (actual time=90.028..93.280 rows=109 loops=1)

40. 0.044 93.057 ↓ 109.0 109 1

Nested Loop (cost=3.39..24.73 rows=1 width=208) (actual time=90.017..93.057 rows=109 loops=1)

  • Join Filter: (p.id = n.idpolicy)
41. 0.020 92.795 ↓ 109.0 109 1

Nested Loop Left Join (cost=3.10..24.40 rows=1 width=220) (actual time=90.005..92.795 rows=109 loops=1)

42. 1.187 92.012 ↓ 109.0 109 1

Nested Loop (cost=2.54..23.18 rows=1 width=215) (actual time=89.977..92.012 rows=109 loops=1)

  • Join Filter: (pv.id = premiums.idpolicyversion)
  • Rows Removed by Join Filter: 11772
43. 88.754 88.754 ↓ 54.5 109 1

CTE Scan on premiums (cost=0.00..0.04 rows=2 width=72) (actual time=88.503..88.754 rows=109 loops=1)

44. 0.455 2.071 ↓ 54.5 109 109

Materialize (cost=2.54..23.08 rows=2 width=143) (actual time=0.001..0.019 rows=109 loops=109)

45. 0.027 1.616 ↓ 54.5 109 1

Nested Loop (cost=2.54..23.07 rows=2 width=143) (actual time=0.074..1.616 rows=109 loops=1)

46. 0.009 1.480 ↓ 54.5 109 1

Nested Loop (cost=2.26..22.46 rows=2 width=125) (actual time=0.063..1.480 rows=109 loops=1)

47. 0.014 1.362 ↓ 54.5 109 1

Nested Loop (cost=1.97..21.85 rows=2 width=133) (actual time=0.057..1.362 rows=109 loops=1)

48. 0.061 1.239 ↓ 54.5 109 1

Nested Loop (cost=1.69..21.25 rows=2 width=116) (actual time=0.048..1.239 rows=109 loops=1)

49. 0.095 0.960 ↓ 54.5 109 1

Nested Loop (cost=1.27..19.53 rows=2 width=104) (actual time=0.039..0.960 rows=109 loops=1)

  • Join Filter: (p.id = pv_1.idpolicy)
50. 0.024 0.647 ↓ 54.5 109 1

Nested Loop (cost=0.85..18.24 rows=2 width=72) (actual time=0.027..0.647 rows=109 loops=1)

51. 0.063 0.296 ↓ 54.5 109 1

Nested Loop (cost=0.42..16.93 rows=2 width=29) (actual time=0.012..0.296 rows=109 loops=1)

52. 0.015 0.015 ↓ 54.5 109 1

CTE Scan on datawindow w (cost=0.00..0.04 rows=2 width=8) (actual time=0.001..0.015 rows=109 loops=1)

53. 0.218 0.218 ↑ 1.0 1 109

Index Scan using tblaccountingentry_pkey on tblaccountingentry acc_entry (cost=0.42..8.44 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=109)

  • Index Cond: (id = w.id)
54. 0.327 0.327 ↑ 1.0 1 109

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..0.66 rows=1 width=43) (actual time=0.003..0.003 rows=1 loops=109)

  • Index Cond: (id = acc_entry.idpolicy)
55. 0.218 0.218 ↑ 1.0 1 109

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv_1 (cost=0.42..0.63 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=109)

  • Index Cond: (idpolicy = acc_entry.idpolicy)
  • Filter: (policyversionstatus = 'CURRENT'::text)
56. 0.218 0.218 ↑ 1.0 1 109

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv (cost=0.42..0.86 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=109)

  • Index Cond: (id = pv_1.id)
57. 0.109 0.109 ↑ 1.0 1 109

Index Scan using tblagencyunit_pkey on tblagencyunit u1 (cost=0.28..0.30 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=109)

  • Index Cond: (id = p.idagency)
58. 0.109 0.109 ↑ 1.0 1 109

Index Only Scan using tblagencyunit_pkey on tblagencyunit u2 (cost=0.28..0.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=109)

  • Index Cond: (id = p.idagencyunit)
  • Heap Fetches: 109
59. 0.109 0.109 ↑ 1.0 1 109

Index Scan using tblemployee_pkey on tblemployee e (cost=0.29..0.31 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=109)

  • Index Cond: (id = p.idemployee)
60. 0.000 0.763 ↑ 1.0 1 109

Nested Loop (cost=0.56..1.21 rows=1 width=13) (actual time=0.005..0.007 rows=1 loops=109)

61. 0.436 0.436 ↑ 1.0 3 109

Index Scan using tbltechcommission_idpolicyversion on tbltechcommission tc (cost=0.43..0.75 rows=3 width=19) (actual time=0.003..0.004 rows=3 loops=109)

  • Index Cond: (pv_1.id = idpolicyversion)
62. 0.327 0.327 ↓ 0.0 0 327

Index Scan using tblcommissiontypedef_pkey on tblcommissiontypedef (cost=0.13..0.15 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=327)

  • Index Cond: (key = tc.commissiontype)
  • Filter: (commissiongroup = 'RB'::text)
  • Rows Removed by Filter: 1
63. 0.218 0.218 ↑ 1.0 1 109

Index Scan using tblnumber_idpolicy_indx on tblnumber n (cost=0.29..0.32 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=109)

  • Index Cond: (idpolicy = acc_entry.idpolicy)
64. 0.218 0.218 ↑ 1.0 1 109

Index Scan using tblpayment_pkey on tblpayment paym (cost=0.42..0.45 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=109)

  • Index Cond: (id = acc_entry.idpayment)
65. 0.327 0.327 ↓ 2.0 2 109

Index Scan using tblpolicyclient_idpolicyversion on tblpolicyclient pc (cost=0.42..0.58 rows=1 width=44) (actual time=0.003..0.003 rows=2 loops=109)

  • Index Cond: (idpolicyversion = pv.id)
66. 0.492 0.492 ↓ 0.0 0 246

Index Only Scan using tblpolicyclientrole_role_unique on tblpolicyclientrole pcr (cost=0.42..0.54 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=246)

  • Index Cond: ((idpolicyclient = pc.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 109
Planning time : 11.872 ms
Execution time : 94.943 ms