explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KQOB

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.095 1,396.007 ↓ 109.0 109 1

Sort (cost=100,025.04..100,025.05 rows=1 width=323) (actual time=1,395.994..1,396.007 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 86.871 ↓ 54.5 109 1

Append (cost=19.77..1,589.57 rows=2 width=8) (actual time=65.880..86.871 rows=109 loops=1)

4. 0.001 42.019 ↓ 0.0 0 1

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

5. 0.000 42.018 ↓ 0.0 0 1

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

6. 5.573 42.018 ↓ 0.0 0 1

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

7. 1.365 16.756 ↓ 328.1 19,689 1

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

8. 0.010 0.817 ↓ 3.0 3 1

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

9. 0.026 0.026 ↑ 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.024..0.026 rows=1 loops=1)

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

Bitmap Heap Scan on tblagencyunit u2_1 (cost=4.45..57.95 rows=1 width=25) (actual time=0.428..0.781 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.081 0.081 ↓ 119.1 2,621 1

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

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

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

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

Bitmap Index Scan on tblpolicy_idagencyunit (cost=0.00..13.73 rows=174 width=0) (actual time=0.378..0.378 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=16) (never executed)

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

Nested Loop (cost=19.77..801.06 rows=1 width=8) (actual time=23.859..44.841 rows=109 loops=1)

18. 0.601 44.481 ↓ 109.0 109 1

Nested Loop (cost=19.34..800.15 rows=1 width=24) (actual time=23.845..44.481 rows=109 loops=1)

19. 3.712 38.555 ↓ 98.6 1,775 1

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

20. 1.422 15.154 ↓ 328.1 19,689 1

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

21. 0.005 0.754 ↓ 3.0 3 1

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

22. 0.011 0.011 ↑ 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.010..0.011 rows=1 loops=1)

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

Bitmap Heap Scan on tblagencyunit u2_2 (cost=4.45..57.95 rows=1 width=25) (actual time=0.396..0.738 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.075 0.075 ↓ 119.1 2,621 1

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

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

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

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

Bitmap Index Scan on tblpolicy_idagencyunit (cost=0.00..13.73 rows=174 width=0) (actual time=0.366..0.366 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=16) (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. 415.569 1,185.099 ↓ 2.0 349,901 1

GroupAggregate (cost=63,808.05..94,199.54 rows=177,247 width=136) (actual time=376.047..1,185.099 rows=349,901 loops=1)

  • Group Key: rp.idpolicyversion
32. 311.498 769.530 ↑ 1.0 536,931 1

WindowAgg (cost=63,808.05..75,888.99 rows=536,931 width=50) (actual time=376.031..769.530 rows=536,931 loops=1)

33. 366.543 458.032 ↑ 1.0 536,931 1

Sort (cost=63,808.05..65,150.37 rows=536,931 width=42) (actual time=376.015..458.032 rows=536,931 loops=1)

  • Sort Key: rp.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 76783kB
34. 91.489 91.489 ↑ 1.0 536,931 1

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

35. 0.521 1,395.912 ↓ 109.0 109 1

Nested Loop (cost=25.21..4,235.92 rows=1 width=323) (actual time=1,257.760..1,395.912 rows=109 loops=1)

36. 0.122 1,394.653 ↓ 246.0 246 1

Nested Loop (cost=24.79..4,235.31 rows=1 width=228) (actual time=1,257.711..1,394.653 rows=246 loops=1)

37. 0.043 1,393.986 ↓ 109.0 109 1

Nested Loop (cost=24.37..4,234.73 rows=1 width=216) (actual time=1,257.689..1,393.986 rows=109 loops=1)

38. 0.030 1,393.616 ↓ 109.0 109 1

Nested Loop (cost=23.95..4,234.28 rows=1 width=208) (actual time=1,257.665..1,393.616 rows=109 loops=1)

  • Join Filter: (p.id = n.idpolicy)
39. 0.069 1,393.259 ↓ 109.0 109 1

Nested Loop Left Join (cost=23.65..4,233.95 rows=1 width=220) (actual time=1,257.649..1,393.259 rows=109 loops=1)

40. 29.232 1,392.209 ↓ 109.0 109 1

Hash Join (cost=23.10..4,232.72 rows=1 width=215) (actual time=1,257.597..1,392.209 rows=109 loops=1)

  • Hash Cond: (premiums.idpolicyversion = pv.id)
41. 1,274.169 1,274.169 ↓ 2.0 349,901 1

CTE Scan on premiums (cost=0.00..3,544.94 rows=177,247 width=72) (actual time=376.049..1,274.169 rows=349,901 loops=1)

42. 0.052 88.808 ↓ 54.5 109 1

Hash (cost=23.07..23.07 rows=2 width=143) (actual time=88.808..88.808 rows=109 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
43. 0.060 88.756 ↓ 54.5 109 1

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

44. 0.023 88.587 ↓ 54.5 109 1

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

45. 0.034 88.455 ↓ 54.5 109 1

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

46. 0.083 88.312 ↓ 54.5 109 1

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

47. 0.090 88.011 ↓ 54.5 109 1

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

  • Join Filter: (p.id = pv_1.idpolicy)
48. 0.068 87.812 ↓ 54.5 109 1

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

49. 0.084 87.199 ↓ 54.5 109 1

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

50. 86.897 86.897 ↓ 54.5 109 1

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

51. 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)
52. 0.545 0.545 ↑ 1.0 1 109

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

  • Index Cond: (id = acc_entry.idpolicy)
53. 0.109 0.109 ↑ 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.001..0.001 rows=1 loops=109)

  • Index Cond: (idpolicy = acc_entry.idpolicy)
  • Filter: (policyversionstatus = 'CURRENT'::text)
54. 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)
55. 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)
56. 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
57. 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)
58. 0.109 0.981 ↑ 1.0 1 109

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

59. 0.545 0.545 ↑ 1.0 3 109

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

  • Index Cond: (pv_1.id = idpolicyversion)
60. 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
61. 0.327 0.327 ↑ 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.003..0.003 rows=1 loops=109)

  • Index Cond: (idpolicy = acc_entry.idpolicy)
62. 0.327 0.327 ↑ 1.0 1 109

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

  • Index Cond: (id = acc_entry.idpayment)
63. 0.545 0.545 ↓ 2.0 2 109

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

  • Index Cond: (idpolicyversion = pv.id)
64. 0.738 0.738 ↓ 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.003..0.003 rows=0 loops=246)

  • Index Cond: ((idpolicyclient = pc.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 109
Planning time : 10.876 ms
Execution time : 1,406.742 ms