explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KgP4 : Optimization for: plan #lgYI

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,808.908 1,808.908 ↓ 497.6 99,514 1

CTE Scan on premiums (cost=4,522,729.98..4,522,733.98 rows=200 width=72) (actual time=1,505.955..1,808.908 rows=99,514 loops=1)

2.          

CTE datawindow

3. 4.990 507.842 ↑ 1.0 100,000 1

Limit (cost=30,097.17..40,519.23 rows=100,000 width=1,195) (actual time=390.621..507.842 rows=100,000 loops=1)

4. 65.648 502.852 ↑ 4.4 100,000 1

Hash Join (cost=30,097.17..76,247.48 rows=442,814 width=1,195) (actual time=390.619..502.852 rows=100,000 loops=1)

  • Hash Cond: (p.id = pv.idpolicy)
5. 47.143 47.143 ↑ 4.4 99,995 1

Seq Scan on tblpolicy p (cost=0.00..40,066.39 rows=441,539 width=582) (actual time=0.010..47.143 rows=99,995 loops=1)

6. 165.189 390.061 ↑ 1.0 441,552 1

Hash (cost=24,562.00..24,562.00 rows=442,814 width=621) (actual time=390.060..390.061 rows=441,552 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 67571kB
7. 224.872 224.872 ↑ 1.0 441,552 1

Seq Scan on tblpolicyversion pv (cost=0.00..24,562.00 rows=442,814 width=621) (actual time=0.011..224.872 rows=441,552 loops=1)

  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 22210
8.          

CTE premiums

9. 84.755 1,789.052 ↓ 497.6 99,514 1

GroupAggregate (cost=118,302.91..4,482,210.75 rows=200 width=72) (actual time=1,505.953..1,789.052 rows=99,514 loops=1)

  • Group Key: w.idpolicyversion
10. 45.033 1,704.297 ↑ 722.3 268,020 1

Merge Join (cost=118,302.91..3,030,207.75 rows=193,600,000 width=45) (actual time=1,505.940..1,704.297 rows=268,020 loops=1)

  • Merge Cond: (w.idpolicyversion = w_1.idpolicyversion)
11. 27.023 673.299 ↑ 1.0 100,000 1

Sort (cost=10,304.82..10,554.82 rows=100,000 width=8) (actual time=664.930..673.299 rows=100,000 loops=1)

  • Sort Key: w.idpolicyversion
  • Sort Method: quicksort Memory: 7760kB
12. 646.276 646.276 ↑ 1.0 100,000 1

CTE Scan on datawindow w (cost=0.00..2,000.00 rows=100,000 width=8) (actual time=390.627..646.276 rows=100,000 loops=1)

13. 35.395 985.965 ↑ 1.4 268,020 1

Materialize (cost=107,998.09..116,370.93 rows=387,200 width=53) (actual time=841.006..985.965 rows=268,020 loops=1)

14. 54.011 950.570 ↑ 1.4 268,020 1

Merge Join (cost=107,998.09..115,402.93 rows=387,200 width=53) (actual time=841.003..950.570 rows=268,020 loops=1)

  • Merge Cond: (w_1.idpolicyversion = riskpremiums.idpolicyversion)
15. 21.327 409.294 ↓ 17.4 152,845 1

Unique (cost=61,589.65..63,076.50 rows=8,800 width=39) (actual time=376.892..409.294 rows=152,845 loops=1)

16. 63.907 387.967 ↑ 1.3 153,537 1

Sort (cost=61,589.65..62,085.27 rows=198,246 width=39) (actual time=376.891..387.967 rows=153,537 loops=1)

  • Sort Key: w_1.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 18616kB
17. 69.842 324.060 ↑ 1.3 153,537 1

Hash Join (cost=39,664.59..44,147.05 rows=198,246 width=39) (actual time=241.638..324.060 rows=153,537 loops=1)

  • Hash Cond: (w_1.id = rp.idpolicyversion)
18. 13.354 13.354 ↑ 1.0 100,000 1

CTE Scan on datawindow w_1 (cost=0.00..2,000.00 rows=100,000 width=16) (actual time=0.001..13.354 rows=100,000 loops=1)

19. 136.755 240.864 ↑ 1.0 751,226 1

Hash (cost=30,274.26..30,274.26 rows=751,226 width=39) (actual time=240.863..240.864 rows=751,226 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 64249kB
20. 104.109 104.109 ↑ 1.0 751,226 1

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

21. 56.975 487.265 ↓ 30.5 268,751 1

Sort (cost=46,408.43..46,430.43 rows=8,800 width=40) (actual time=464.104..487.265 rows=268,751 loops=1)

  • Sort Key: riskpremiums.idpolicyversion
  • Sort Method: quicksort Memory: 13350kB
22. 9.210 430.290 ↓ 17.5 153,643 1

Subquery Scan on riskpremiums (cost=45,633.89..45,831.89 rows=8,800 width=40) (actual time=360.020..430.290 rows=153,643 loops=1)

23. 113.184 421.080 ↓ 17.5 153,643 1

HashAggregate (cost=45,633.89..45,743.89 rows=8,800 width=62) (actual time=360.018..421.080 rows=153,643 loops=1)

  • Group Key: w_2.idpolicyversion, rp_1.riskname
24. 71.065 307.896 ↑ 1.3 153,643 1

Hash Join (cost=39,664.59..44,147.05 rows=198,246 width=35) (actual time=222.401..307.896 rows=153,643 loops=1)

  • Hash Cond: (w_2.idpolicyversion = rp_1.idpolicyversion)
25. 15.158 15.158 ↑ 1.0 100,000 1

CTE Scan on datawindow w_2 (cost=0.00..2,000.00 rows=100,000 width=8) (actual time=0.001..15.158 rows=100,000 loops=1)

26. 126.800 221.673 ↑ 1.0 751,226 1

Hash (cost=30,274.26..30,274.26 rows=751,226 width=35) (actual time=221.673..221.673 rows=751,226 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 59710kB
27. 94.873 94.873 ↑ 1.0 751,226 1

Seq Scan on tblriskpremium rp_1 (cost=0.00..30,274.26 rows=751,226 width=35) (actual time=0.013..94.873 rows=751,226 loops=1)

Planning time : 0.867 ms
Execution time : 1,817.341 ms