explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tr0W

Settings
# exclusive inclusive rows x rows loops node
1. 2,597.132 2,597.132 ↓ 500.0 100,000 1

CTE Scan on premiums (cost=8,080,207,172.02..8,080,207,176.02 rows=200 width=72) (actual time=1,959.057..2,597.132 rows=100,000 loops=1)

2.          

CTE datawindow

3. 5.050 445.986 ↑ 1.0 100,000 1

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

4. 63.077 440.936 ↑ 4.4 100,000 1

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

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

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

6. 141.590 332.205 ↑ 1.0 441,552 1

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

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

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

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

CTE premiums

9. 101.466 2,578.815 ↓ 500.0 100,000 1

GroupAggregate (cost=193,440.64..8,080,166,652.79 rows=200 width=72) (actual time=1,959.054..2,578.815 rows=100,000 loops=1)

  • Group Key: w.idpolicyversion
10. 91.686 2,477.349 ↑ 990,254.6 362,516 1

Merge Join (cost=193,440.64..5,387,793,212.88 rows=358,983,124,922 width=45) (actual time=1,959.036..2,477.349 rows=362,516 loops=1)

  • Merge Cond: (tblriskpremium.idpolicyversion = w.idpolicyversion)
11. 128.311 681.598 ↓ 2.0 741,681 1

Unique (cost=103,589.74..109,223.93 rows=378,937 width=39) (actual time=465.869..681.598 rows=741,681 loops=1)

12. 451.672 553.287 ↑ 1.0 750,010 1

Sort (cost=103,589.74..105,467.80 rows=751,226 width=39) (actual time=465.867..553.287 rows=750,010 loops=1)

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

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

14. 26.435 1,704.065 ↑ 522.7 362,515 1

Materialize (cost=89,850.90..3,406,049.65 rows=189,468,500 width=48) (actual time=1,493.161..1,704.065 rows=362,515 loops=1)

15. 88.280 1,677.630 ↑ 1,159.3 163,430 1

Merge Join (cost=89,850.90..2,932,378.40 rows=189,468,500 width=48) (actual time=1,493.156..1,677.630 rows=163,430 loops=1)

  • Merge Cond: (w.idpolicyversion = riskpremiums.idpolicyversion)
16. 29.743 610.506 ↑ 1.0 100,000 1

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

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

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

18. 265.140 978.844 ↓ 2.0 741,681 1

Sort (cost=79,546.08..80,493.42 rows=378,937 width=40) (actual time=893.067..978.844 rows=741,681 loops=1)

  • Sort Key: riskpremiums.idpolicyversion
  • Sort Method: quicksort Memory: 59446kB
19. 44.022 713.704 ↓ 2.0 742,875 1

Subquery Scan on riskpremiums (cost=35,908.46..44,434.54 rows=378,937 width=40) (actual time=359.836..713.704 rows=742,875 loops=1)

20. 572.237 669.682 ↓ 2.0 742,875 1

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

  • Group Key: tblriskpremium_1.idpolicyversion, tblriskpremium_1.riskname
21. 97.445 97.445 ↑ 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.008..97.445 rows=751,226 loops=1)

Planning time : 1.129 ms
Execution time : 2,606.812 ms