explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RD9j

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 26.787 ↓ 0.0 0 1

Nested Loop (cost=142.16..9,918.04 rows=1 width=3,463) (actual time=26.787..26.787 rows=0 loops=1)

  • Join Filter: (reportingperiod.lastmonth = payments_current.effectivedatemonth)
  • Rows Removed by Join Filter: 3
2.          

CTE reportingperiod

3. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on tblbindingagreementyear (cost=0.00..1.47 rows=1 width=20) (actual time=0.011..0.012 rows=1 loops=1)

  • Filter: (id = 32)
  • Rows Removed by Filter: 34
4.          

CTE payments

5. 0.574 1.355 ↑ 1.1 225 1

HashAggregate (cost=105.80..112.94 rows=238 width=244) (actual time=1.205..1.355 rows=225 loops=1)

  • Group Key: cp.idclaim, ca.currencytype, date_trunc('month'::text, cp.effectivedate)
6. 0.289 0.781 ↓ 1.7 396 1

Hash Join (cost=44.51..99.85 rows=238 width=43) (actual time=0.307..0.781 rows=396 loops=1)

  • Hash Cond: (ca.idclaimpayment = cp.id)
7. 0.202 0.202 ↓ 1.0 2,143 1

Seq Scan on tblclaimamount ca (cost=0.00..44.40 rows=2,140 width=35) (actual time=0.004..0.202 rows=2,143 loops=1)

8. 0.034 0.290 ↓ 1.6 225 1

Hash (cost=42.80..42.80 rows=137 width=24) (actual time=0.290..0.290 rows=225 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
9. 0.144 0.256 ↓ 1.6 225 1

Nested Loop (cost=0.00..42.80 rows=137 width=24) (actual time=0.009..0.256 rows=225 loops=1)

  • Join Filter: ((cp.effectivedate >= rp.startdate) AND (cp.effectivedate <= rp.enddateexclusive))
  • Rows Removed by Join Filter: 1009
10. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on reportingperiod rp (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

11. 0.112 0.112 ↓ 1.0 1,234 1

Seq Scan on tblclaimpayment cp (cost=0.00..24.31 rows=1,231 width=24) (actual time=0.006..0.112 rows=1,234 loops=1)

12.          

CTE payments_previous

13. 0.446 0.511 ↓ 2.6 207 1

HashAggregate (cost=9.53..11.70 rows=79 width=264) (actual time=0.378..0.511 rows=207 loops=1)

  • Group Key: payments.idclaim, payments.currencytype
14. 0.043 0.065 ↓ 2.6 207 1

Nested Loop (cost=0.00..7.75 rows=79 width=264) (actual time=0.003..0.065 rows=207 loops=1)

  • Join Filter: (payments.effectivedatemonth < rp_1.lastmonth)
  • Rows Removed by Join Filter: 18
15. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on reportingperiod rp_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

16. 0.021 0.021 ↑ 1.1 225 1

CTE Scan on payments (cost=0.00..4.76 rows=238 width=272) (actual time=0.000..0.021 rows=225 loops=1)

17.          

CTE reserves

18. 0.018 0.090 ↑ 2.5 4 1

HashAggregate (cost=6.94..7.24 rows=10 width=244) (actual time=0.087..0.090 rows=4 loops=1)

  • Group Key: cr.idclaim, ca_1.currencytype, date_trunc('month'::text, cr.effectivedate)
19. 0.010 0.072 ↑ 1.4 7 1

Merge Join (cost=2.75..6.69 rows=10 width=43) (actual time=0.057..0.072 rows=7 loops=1)

  • Merge Cond: (ca_1.idclaimpayment = cr.id)
20. 0.023 0.023 ↑ 43.7 49 1

Index Scan using tblclaimammount_idclaimpayment_index on tblclaimamount ca_1 (cost=0.28..90.81 rows=2,140 width=35) (actual time=0.011..0.023 rows=49 loops=1)

21. 0.008 0.039 ↑ 1.0 6 1

Sort (cost=2.47..2.49 rows=6 width=24) (actual time=0.039..0.039 rows=6 loops=1)

  • Sort Key: cr.id
  • Sort Method: quicksort Memory: 25kB
22. 0.009 0.031 ↑ 1.5 4 1

Nested Loop (cost=0.00..2.40 rows=6 width=24) (actual time=0.021..0.031 rows=4 loops=1)

  • Join Filter: ((cr.effectivedate >= rp_2.startdate) AND (cr.effectivedate <= rp_2.enddateexclusive))
  • Rows Removed by Join Filter: 51
23. 0.014 0.014 ↑ 1.0 1 1

CTE Scan on reportingperiod rp_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)

24. 0.008 0.008 ↑ 1.0 55 1

Seq Scan on tblclaimreserve cr (cost=0.00..1.55 rows=55 width=24) (actual time=0.005..0.008 rows=55 loops=1)

25.          

CTE reserves_previous

26. 0.022 0.032 ↑ 1.0 3 1

HashAggregate (cost=0.41..0.49 rows=3 width=264) (actual time=0.030..0.032 rows=3 loops=1)

  • Group Key: reserves_1.idclaim, reserves_1.currencytype
27. 0.003 0.010 ↑ 1.0 3 1

Nested Loop (cost=0.00..0.34 rows=3 width=264) (actual time=0.002..0.010 rows=3 loops=1)

  • Join Filter: (reserves_1.effectivedatemonth < rp_3.lastmonth)
  • Rows Removed by Join Filter: 1
28. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on reportingperiod rp_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

29. 0.007 0.007 ↑ 2.5 4 1

CTE Scan on reserves reserves_1 (cost=0.00..0.20 rows=10 width=272) (actual time=0.000..0.007 rows=4 loops=1)

30. 0.006 26.785 ↓ 3.0 3 1

Nested Loop Left Join (cost=8.31..9,784.08 rows=1 width=918) (actual time=3.120..26.785 rows=3 loops=1)

  • Join Filter: (reserves.idclaim = c.id)
  • Rows Removed by Join Filter: 9
31. 0.038 26.779 ↓ 3.0 3 1

Nested Loop Left Join (cost=8.31..9,783.75 rows=1 width=926) (actual time=3.117..26.779 rows=3 loops=1)

  • Join Filter: ((payments_previous.idclaim = payments_current.idclaim) AND (payments_previous.currencytype = payments_current.currencytype))
  • Rows Removed by Join Filter: 618
32. 0.055 26.126 ↓ 3.0 3 1

Nested Loop (cost=8.31..9,780.99 rows=1 width=774) (actual time=2.682..26.126 rows=3 loops=1)

  • Join Filter: ((c.id = payments_current.idclaim) AND (reserves_current.effectivedatemonth = payments_current.effectivedatemonth))
  • Rows Removed by Join Filter: 897
33. 0.006 24.575 ↓ 4.0 4 1

Nested Loop (cost=8.31..9,772.66 rows=1 width=574) (actual time=0.698..24.575 rows=4 loops=1)

  • Join Filter: (pcr.idpolicyclient = pa.idpolicyclient)
34. 0.006 24.537 ↓ 4.0 4 1

Nested Loop (cost=7.89..9,772.15 rows=1 width=545) (actual time=0.684..24.537 rows=4 loops=1)

35. 0.802 24.503 ↑ 1.2 4 1

Nested Loop (cost=7.60..9,770.48 rows=5 width=521) (actual time=0.669..24.503 rows=4 loops=1)

  • Join Filter: (reserves_current.idclaim = c.id)
  • Rows Removed by Join Filter: 4800
36. 0.169 23.701 ↓ 1.8 1,201 1

Nested Loop (cost=7.49..9,670.91 rows=661 width=377) (actual time=0.296..23.701 rows=1,201 loops=1)

37. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on tblbindingagreement ba (cost=0.00..1.16 rows=1 width=64) (actual time=0.012..0.014 rows=1 loops=1)

  • Filter: (id = 'MF-PA'::text)
  • Rows Removed by Filter: 12
38. 0.367 23.518 ↓ 1.8 1,201 1

Hash Join (cost=7.49..9,663.14 rows=661 width=352) (actual time=0.282..23.518 rows=1,201 loops=1)

  • Hash Cond: (p.idbindingagreementyear = bay.id)
39. 0.000 23.124 ↓ 1.8 1,201 1

Nested Loop (cost=5.71..9,659.41 rows=661 width=192) (actual time=0.246..23.124 rows=1,201 loops=1)

40. 0.864 15.978 ↓ 3.2 2,534 1

Nested Loop (cost=5.28..9,238.36 rows=792 width=184) (actual time=0.229..15.978 rows=2,534 loops=1)

41. 0.000 10.310 ↓ 1.8 1,201 1

Nested Loop (cost=4.86..8,830.00 rows=662 width=128) (actual time=0.217..10.310 rows=1,201 loops=1)

42. 0.341 5.642 ↓ 1.8 1,201 1

Hash Join (cost=4.43..8,378.23 rows=664 width=104) (actual time=0.204..5.642 rows=1,201 loops=1)

  • Hash Cond: (p.idgeneralagreement = ga.id)
43. 1.228 5.278 ↑ 1.0 1,257 1

Nested Loop (cost=0.42..8,370.65 rows=1,257 width=107) (actual time=0.131..5.278 rows=1,257 loops=1)

44. 0.279 0.279 ↑ 1.0 1,257 1

Seq Scan on tblclaim c (cost=0.00..145.57 rows=1,257 width=81) (actual time=0.112..0.279 rows=1,257 loops=1)

45. 3.771 3.771 ↑ 1.0 1 1,257

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..6.54 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=1,257)

  • Index Cond: (id = c.idpolicy)
46. 0.008 0.023 ↑ 1.0 28 1

Hash (cost=3.66..3.66 rows=28 width=18) (actual time=0.023..0.023 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
47. 0.015 0.015 ↑ 1.0 28 1

Seq Scan on tblgeneralagreement ga (cost=0.00..3.66 rows=28 width=18) (actual time=0.007..0.015 rows=28 loops=1)

  • Filter: (idbindingagreement = 'MF-PA'::text)
  • Rows Removed by Filter: 25
48. 4.804 4.804 ↑ 1.0 1 1,201

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv (cost=0.42..0.67 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=1,201)

  • Index Cond: (idpolicy = p.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
49. 4.804 4.804 ↓ 2.0 2 1,201

Index Scan using tblpolicyclient_idpolicyversion on tblpolicyclient pc (cost=0.42..0.61 rows=1 width=72) (actual time=0.003..0.004 rows=2 loops=1,201)

  • Index Cond: (idpolicyversion = pv.id)
50. 7.602 7.602 ↓ 0.0 0 2,534

Index Only Scan using tblpolicyclientrole_role_unique on tblpolicyclientrole pcr (cost=0.42..0.53 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2,534)

  • Index Cond: ((idpolicyclient = pc.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 1201
51. 0.014 0.027 ↑ 1.0 35 1

Hash (cost=1.35..1.35 rows=35 width=176) (actual time=0.026..0.027 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
52. 0.013 0.013 ↑ 1.0 35 1

Seq Scan on tblbindingagreementyear bay (cost=0.00..1.35 rows=35 width=176) (actual time=0.008..0.013 rows=35 loops=1)

53. 0.000 0.000 ↑ 2.5 4 1,201

Materialize (cost=0.10..0.44 rows=10 width=144) (actual time=0.000..0.000 rows=4 loops=1,201)

54. 0.012 0.141 ↑ 2.5 4 1

Hash Left Join (cost=0.10..0.39 rows=10 width=144) (actual time=0.139..0.141 rows=4 loops=1)

  • Hash Cond: ((reserves_current.idclaim = reserves_previous.idclaim) AND (reserves_current.currencytype = reserves_previous.currencytype))
55. 0.089 0.089 ↑ 2.5 4 1

CTE Scan on reserves reserves_current (cost=0.00..0.20 rows=10 width=112) (actual time=0.088..0.089 rows=4 loops=1)

56. 0.006 0.040 ↑ 1.0 3 1

Hash (cost=0.06..0.06 rows=3 width=104) (actual time=0.040..0.040 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.034 0.034 ↑ 1.0 3 1

CTE Scan on reserves_previous (cost=0.00..0.06 rows=3 width=104) (actual time=0.031..0.034 rows=3 loops=1)

58. 0.028 0.028 ↑ 1.0 1 4

Index Scan using tblnumber_idpolicy_indx on tblnumber n (cost=0.29..0.32 rows=1 width=56) (actual time=0.007..0.007 rows=1 loops=4)

  • Index Cond: (idpolicy = p.id)
59. 0.032 0.032 ↑ 1.0 1 4

Index Scan using tblpolicyaddress_idpolicyclient on tblpolicyaddress pa (cost=0.42..0.49 rows=1 width=53) (actual time=0.007..0.008 rows=1 loops=4)

  • Index Cond: (idpolicyclient = pc.id)
60. 1.496 1.496 ↑ 1.1 225 4

CTE Scan on payments payments_current (cost=0.00..4.76 rows=238 width=208) (actual time=0.302..0.374 rows=225 loops=4)

61. 0.615 0.615 ↓ 2.6 207 3

CTE Scan on payments_previous (cost=0.00..1.58 rows=79 width=200) (actual time=0.126..0.205 rows=207 loops=3)

62. 0.000 0.000 ↑ 2.5 4 3

CTE Scan on reserves (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.000 rows=4 loops=3)

63. 0.000 0.000 ↑ 1.0 1 3

CTE Scan on reportingperiod (cost=0.00..0.02 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=3)

Planning time : 11.270 ms
Execution time : 27.384 ms