explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yx51

Settings
# exclusive inclusive rows x rows loops node
1. 2.717 58.135 ↓ 9.5 1,201 1

Nested Loop Left Join (cost=152.66..9,907.88 rows=126 width=3,463) (actual time=3.584..58.135 rows=1,201 loops=1)

  • Join Filter: (reserves.idclaim = c.id)
  • Rows Removed by Join Filter: 4800
2.          

CTE reportingperiod

3. 0.019 0.019 ↑ 1.0 1 1

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

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

CTE payments

5. 0.890 2.052 ↑ 1.1 225 1

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

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

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

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

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

8. 0.051 0.475 ↓ 1.6 225 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
9. 0.253 0.424 ↓ 1.6 225 1

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

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

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

11. 0.168 0.168 ↓ 1.0 1,234 1

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

12.          

CTE payments_previous

13. 0.419 0.492 ↓ 2.6 207 1

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

  • Group Key: payments.idclaim, payments.currencytype
14. 0.054 0.073 ↓ 2.6 207 1

Nested Loop (cost=0.00..7.75 rows=79 width=264) (actual time=0.002..0.073 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.018 0.018 ↑ 1.1 225 1

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

17.          

CTE reserves

18. 0.019 0.120 ↑ 2.5 4 1

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

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

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

  • Merge Cond: (ca_1.idclaimpayment = cr.id)
20. 0.040 0.040 ↑ 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.017..0.040 rows=49 loops=1)

21. 0.012 0.045 ↑ 1.0 6 1

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

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

Nested Loop (cost=0.00..2.40 rows=6 width=24) (actual time=0.019..0.033 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.001 0.001 ↑ 1.0 1 1

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

24. 0.019 0.019 ↑ 1.0 55 1

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

25.          

CTE reserves_previous

26. 0.011 0.021 ↑ 1.0 3 1

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

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

Nested Loop (cost=0.00..0.34 rows=3 width=264) (actual time=0.003..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.006 0.006 ↑ 2.5 4 1

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

30. 0.365 55.418 ↓ 9.5 1,201 1

Hash Left Join (cost=18.81..9,735.19 rows=126 width=918) (actual time=3.570..55.418 rows=1,201 loops=1)

  • Hash Cond: ((payments_current.idclaim = payments_previous.idclaim) AND (payments_current.currencytype = payments_previous.currencytype))
31. 1.282 54.427 ↓ 9.5 1,201 1

Nested Loop Left Join (cost=16.05..9,731.47 rows=126 width=766) (actual time=2.926..54.427 rows=1,201 loops=1)

  • Join Filter: ((reserves_current.idclaim = c.id) AND (reserves_current.effectivedatemonth = reportingperiod.lastmonth))
  • Rows Removed by Join Filter: 4803
32. 0.530 53.145 ↓ 9.5 1,201 1

Hash Left Join (cost=15.94..9,709.00 rows=126 width=642) (actual time=2.754..53.145 rows=1,201 loops=1)

  • Hash Cond: (c.id = payments_current.idclaim)
  • Join Filter: (payments_current.effectivedatemonth = reportingperiod.lastmonth)
  • Rows Removed by Join Filter: 207
33. 0.576 50.323 ↓ 9.5 1,201 1

Nested Loop (cost=8.21..9,696.87 rows=126 width=442) (actual time=0.449..50.323 rows=1,201 loops=1)

34. 0.229 40.269 ↓ 9.0 1,354 1

Nested Loop (cost=7.78..9,616.60 rows=151 width=458) (actual time=0.421..40.269 rows=1,354 loops=1)

35. 2.122 29.904 ↓ 13.4 2,534 1

Nested Loop (cost=7.36..9,521.52 rows=189 width=405) (actual time=0.403..29.904 rows=2,534 loops=1)

36. 1.393 22.714 ↓ 3.2 2,534 1

Nested Loop (cost=7.07..9,256.52 rows=792 width=381) (actual time=0.389..22.714 rows=2,534 loops=1)

37. 0.251 12.914 ↓ 1.8 1,201 1

Nested Loop (cost=6.64..8,848.16 rows=662 width=325) (actual time=0.362..12.914 rows=1,201 loops=1)

38. 0.021 0.021 ↑ 1.0 1 1

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

  • Filter: (id = 'MF-PA'::text)
  • Rows Removed by Filter: 12
39. 0.452 12.642 ↓ 1.8 1,201 1

Hash Join (cost=6.64..8,840.38 rows=662 width=300) (actual time=0.341..12.642 rows=1,201 loops=1)

  • Hash Cond: (p.idbindingagreementyear = bay.id)
40. 0.210 12.155 ↓ 1.8 1,201 1

Nested Loop (cost=4.86..8,836.64 rows=662 width=140) (actual time=0.292..12.155 rows=1,201 loops=1)

41. 0.022 0.022 ↑ 1.0 1 1

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

42. 0.564 11.923 ↓ 1.8 1,201 1

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

43. 0.436 6.555 ↓ 1.8 1,201 1

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

  • Hash Cond: (p.idgeneralagreement = ga.id)
44. 0.736 6.084 ↑ 1.0 1,257 1

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

45. 0.320 0.320 ↑ 1.0 1,257 1

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

46. 5.028 5.028 ↑ 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.004..0.004 rows=1 loops=1,257)

  • Index Cond: (id = c.idpolicy)
47. 0.014 0.035 ↑ 1.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
48. 0.021 0.021 ↑ 1.0 28 1

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

  • Filter: (idbindingagreement = 'MF-PA'::text)
  • Rows Removed by Filter: 25
49. 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.004..0.004 rows=1 loops=1,201)

  • Index Cond: (idpolicy = p.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
50. 0.021 0.035 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
51. 0.014 0.014 ↑ 1.0 35 1

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

52. 8.407 8.407 ↓ 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.006..0.007 rows=2 loops=1,201)

  • Index Cond: (idpolicyversion = pv.id)
53. 5.068 5.068 ↑ 1.0 1 2,534

Index Scan using tblnumber_idpolicy_indx on tblnumber n (cost=0.29..0.32 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=2,534)

  • Index Cond: (idpolicy = p.id)
54. 10.136 10.136 ↑ 1.0 1 2,534

Index Scan using tblpolicyaddress_idpolicyclient on tblpolicyaddress pa (cost=0.42..0.49 rows=1 width=53) (actual time=0.004..0.004 rows=1 loops=2,534)

  • Index Cond: (idpolicyclient = pc.id)
55. 9.478 9.478 ↑ 1.0 1 1,354

Index Only Scan using tblpolicyclientrole_role_unique on tblpolicyclientrole pcr (cost=0.42..0.53 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1,354)

  • Index Cond: ((idpolicyclient = pc.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 1201
56. 0.075 2.292 ↑ 1.1 225 1

Hash (cost=4.76..4.76 rows=238 width=208) (actual time=2.292..2.292 rows=225 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
57. 2.217 2.217 ↑ 1.1 225 1

CTE Scan on payments payments_current (cost=0.00..4.76 rows=238 width=208) (actual time=1.825..2.217 rows=225 loops=1)

58. 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)

59. 0.016 0.163 ↑ 2.5 4 1

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

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

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

61. 0.005 0.029 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.024 0.024 ↑ 1.0 3 1

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

63. 0.049 0.626 ↓ 2.6 207 1

Hash (cost=1.58..1.58 rows=79 width=200) (actual time=0.626..0.626 rows=207 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
64. 0.577 0.577 ↓ 2.6 207 1

CTE Scan on payments_previous (cost=0.00..1.58 rows=79 width=200) (actual time=0.363..0.577 rows=207 loops=1)

65. 0.000 0.000 ↑ 2.5 4 1,201

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

Planning time : 9.412 ms
Execution time : 59.101 ms