explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lgYI

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,440.546 1,440.546 ↑ 1.0 100 1

CTE Scan on premiums (cost=7,343,937.51..7,343,939.51 rows=100 width=72) (actual time=1,440.486..1,440.546 rows=100 loops=1)

2.          

CTE datawindow

3. 0.010 0.252 ↑ 1.0 100 1

Limit (cost=2.24..58.40 rows=100 width=1,195) (actual time=0.046..0.252 rows=100 loops=1)

4. 0.089 0.242 ↑ 4,428.1 100 1

Merge Join (cost=2.24..248,675.94 rows=442,814 width=1,195) (actual time=0.044..0.242 rows=100 loops=1)

  • Merge Cond: (p.id = pv.idpolicy)
5. 0.093 0.093 ↑ 4,415.4 100 1

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..154,818.70 rows=441,539 width=582) (actual time=0.018..0.093 rows=100 loops=1)

6. 0.060 0.060 ↑ 4,428.1 100 1

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv (cost=0.42..87,230.93 rows=442,814 width=621) (actual time=0.017..0.060 rows=100 loops=1)

  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 1
7.          

CTE premiums

8. 0.086 1,440.522 ↑ 1.0 100 1

HashAggregate (cost=7,343,877.61..7,343,879.11 rows=100 width=72) (actual time=1,440.482..1,440.522 rows=100 loops=1)

  • Group Key: w.idpolicyversion
9. 58.963 1,440.436 ↑ 3,589,821.8 100 1

Hash Join (cost=153,711.57..4,651,511.28 rows=358,982,178 width=45) (actual time=1,169.429..1,440.436 rows=100 loops=1)

  • Hash Cond: (tblriskpremium.idpolicyversion = w.idpolicyversion)
10. 127.160 650.540 ↓ 2.0 742,875 1

Unique (cost=103,589.74..109,223.93 rows=378,937 width=39) (actual time=438.266..650.540 rows=742,875 loops=1)

11. 432.507 523.380 ↑ 1.0 751,226 1

Sort (cost=103,589.74..105,467.80 rows=751,226 width=39) (actual time=438.265..523.380 rows=751,226 loops=1)

  • Sort Key: tblriskpremium.idpolicyversion, tblriskpremium.riskname, tblriskpremium.validfrom DESC
  • Sort Method: quicksort Memory: 89099kB
12. 90.873 90.873 ↑ 1.0 751,226 1

Seq Scan on tblriskpremium (cost=0.00..30,274.26 rows=751,226 width=39) (actual time=0.012..90.873 rows=751,226 loops=1)

13. 0.050 730.933 ↑ 1,894.7 100 1

Hash (cost=47,753.48..47,753.48 rows=189,468 width=48) (actual time=730.933..730.933 rows=100 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 2054kB
14. 40.219 730.883 ↑ 1,894.7 100 1

Hash Join (cost=35,911.71..47,753.48 rows=189,468 width=48) (actual time=378.913..730.883 rows=100 loops=1)

  • Hash Cond: (tblriskpremium_1.idpolicyversion = w.idpolicyversion)
15. 588.163 690.230 ↓ 2.0 742,875 1

HashAggregate (cost=35,908.46..40,645.17 rows=378,937 width=62) (actual time=378.342..690.230 rows=742,875 loops=1)

  • Group Key: tblriskpremium_1.idpolicyversion, tblriskpremium_1.riskname
16. 102.067 102.067 ↑ 1.0 751,226 1

Seq Scan on tblriskpremium tblriskpremium_1 (cost=0.00..30,274.26 rows=751,226 width=35) (actual time=0.009..102.067 rows=751,226 loops=1)

17. 0.015 0.434 ↑ 1.0 100 1

Hash (cost=2.00..2.00 rows=100 width=8) (actual time=0.434..0.434 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
18. 0.419 0.419 ↑ 1.0 100 1

CTE Scan on datawindow w (cost=0.00..2.00 rows=100 width=8) (actual time=0.052..0.419 rows=100 loops=1)

Planning time : 0.796 ms
Execution time : 1,441.994 ms