explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ir8M

Settings
# exclusive inclusive rows x rows loops node
1. 0.888 18.125 ↓ 1.0 1,257 1

Hash Left Join (cost=122.99..9,404.26 rows=1,253 width=1,747) (actual time=3.743..18.125 rows=1,257 loops=1)

  • Hash Cond: (c.id = payments_current.idclaim)
  • Join Filter: (payments_current.effectivedatemonth = rp.lastmonth)
  • Rows Removed by Join Filter: 207
2.          

CTE reportingperiod

3. 0.037 0.037 ↑ 1.0 1 1

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

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

CTE payments

5. 1.197 3.158 ↑ 1.1 225 1

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

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

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

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

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

8. 0.073 0.749 ↓ 1.6 225 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
9. 0.435 0.676 ↓ 1.6 225 1

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

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

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

11. 0.237 0.237 ↓ 1.0 1,234 1

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

12. 0.659 13.757 ↓ 1.0 1,257 1

Nested Loop (cost=0.84..9,238.43 rows=1,253 width=1,475) (actual time=0.246..13.757 rows=1,257 loops=1)

13. 0.038 0.038 ↑ 1.0 1 1

CTE Scan on reportingperiod rp (cost=0.00..0.02 rows=1 width=20) (actual time=0.037..0.038 rows=1 loops=1)

14. 1.516 13.060 ↓ 1.0 1,257 1

Nested Loop (cost=0.84..9,225.88 rows=1,253 width=1,455) (actual time=0.197..13.060 rows=1,257 loops=1)

15. 1.164 6.516 ↑ 1.0 1,257 1

Nested Loop (cost=0.42..8,370.65 rows=1,257 width=1,105) (actual time=0.174..6.516 rows=1,257 loops=1)

16. 0.324 0.324 ↑ 1.0 1,257 1

Seq Scan on tblclaim c (cost=0.00..145.57 rows=1,257 width=567) (actual time=0.145..0.324 rows=1,257 loops=1)

17. 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=538) (actual time=0.004..0.004 rows=1 loops=1,257)

  • Index Cond: (id = c.idpolicy)
18. 5.028 5.028 ↑ 1.0 1 1,257

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

  • Index Cond: (idpolicy = p.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
19. 0.083 3.480 ↑ 1.1 225 1

Hash (cost=4.76..4.76 rows=238 width=272) (actual time=3.480..3.480 rows=225 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
20. 3.397 3.397 ↑ 1.1 225 1

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

Planning time : 2.682 ms
Execution time : 18.504 ms