explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6rVc : Optimization for: Optimization for: plan #8DIR; plan #zSmQ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,870.501 1,870.501 ↓ 1.2 10 1

CTE Scan on premiums (cost=57,173.43..57,173.59 rows=8 width=72) (actual time=1,870.388..1,870.501 rows=10 loops=1)

2.          

CTE datawindow

3. 0.008 1,870.178 ↑ 1.0 10 1

Limit (cost=56,959.07..56,959.10 rows=10 width=24) (actual time=1,870.171..1,870.178 rows=10 loops=1)

4. 1.143 1,870.170 ↑ 14.5 10 1

Sort (cost=56,959.07..56,959.43 rows=145 width=24) (actual time=1,870.169..1,870.170 rows=10 loops=1)

  • Sort Key: pv.createdtime DESC
  • Sort Method: top-N heapsort Memory: 25kB
5. 3.261 1,869.027 ↓ 83.9 12,159 1

WindowAgg (cost=52,687.51..56,955.94 rows=145 width=24) (actual time=1,867.748..1,869.027 rows=12,159 loops=1)

6. 19.279 1,865.766 ↓ 83.9 12,159 1

Hash Join (cost=52,687.51..56,954.13 rows=145 width=16) (actual time=1,759.902..1,865.766 rows=12,159 loops=1)

  • Hash Cond: (od.id = pv.id)
7. 27.095 1,525.020 ↓ 4.0 203,763 1

Subquery Scan on od (cost=32,388.87..36,462.82 rows=50,991 width=8) (actual time=1,433.108..1,525.020 rows=203,763 loops=1)

  • Filter: ((od.validstartdate + od.validdays) < now())
  • Rows Removed by Filter: 1749
8. 37.551 1,497.925 ↓ 1.3 205,512 1

Unique (cost=32,388.87..33,785.80 rows=152,973 width=173) (actual time=1,433.102..1,497.925 rows=205,512 loops=1)

9. 1,415.953 1,460.374 ↓ 1.0 279,388 1

Sort (cost=32,388.87..33,087.33 rows=279,385 width=173) (actual time=1,433.099..1,460.374 rows=279,388 loops=1)

  • Sort Key: tblofferdata.offernumber, tblofferdata.offerversion DESC
  • Sort Method: quicksort Memory: 34116kB
10. 44.421 44.421 ↓ 1.0 279,388 1

Seq Scan on tblofferdata (cost=0.00..7,115.85 rows=279,385 width=173) (actual time=0.012..44.421 rows=279,388 loops=1)

11. 3.402 321.467 ↓ 12.4 12,263 1

Hash (cost=20,286.24..20,286.24 rows=992 width=16) (actual time=321.466..321.467 rows=12,263 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 703kB
12. 10.573 318.065 ↓ 12.4 12,263 1

Nested Loop (cost=18.52..20,286.24 rows=992 width=16) (actual time=5.176..318.065 rows=12,263 loops=1)

13. 1.673 37.112 ↓ 11.7 12,290 1

Nested Loop (cost=18.09..19,204.02 rows=1,054 width=8) (actual time=5.142..37.112 rows=12,290 loops=1)

14. 0.975 0.975 ↑ 5.5 4 1

Seq Scan on tblagencyunit agency (cost=0.00..194.77 rows=22 width=8) (actual time=0.054..0.975 rows=4 loops=1)

  • Filter: ((ltree2text(subltree(path, 0, 1)))::integer = 2375)
  • Rows Removed by Filter: 4301
15. 30.628 34.464 ↓ 16.9 3,072 4

Bitmap Heap Scan on tblpolicy p (cost=18.09..862.24 rows=182 width=16) (actual time=1.280..8.616 rows=3,072 loops=4)

  • Recheck Cond: (idagency = agency.id)
  • Filter: (policytype = 'OFFER'::text)
  • Rows Removed by Filter: 1850
  • Heap Blocks: exact=5465
16. 3.836 3.836 ↓ 20.4 4,922 4

Bitmap Index Scan on tblpolicy_idagency (cost=0.00..18.05 rows=241 width=0) (actual time=0.959..0.959 rows=4,922 loops=4)

  • Index Cond: (idagency = agency.id)
17. 270.380 270.380 ↑ 1.0 1 12,290

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv (cost=0.42..1.02 rows=1 width=24) (actual time=0.022..0.022 rows=1 loops=12,290)

  • Index Cond: (idpolicy = p.id)
  • Filter: ((canceldate IS NULL) AND (policyversionstatus = 'CURRENT'::text))
  • Rows Removed by Filter: 0
18.          

CTE premiums

19. 0.008 1,870.496 ↓ 1.2 10 1

Nested Loop (cost=121.22..214.33 rows=8 width=72) (actual time=1,870.386..1,870.496 rows=10 loops=1)

20. 0.005 1,870.418 ↓ 1.2 10 1

Nested Loop (cost=120.79..206.60 rows=8 width=144) (actual time=1,870.368..1,870.418 rows=10 loops=1)

21. 0.036 1,870.373 ↑ 1.0 10 1

GroupAggregate (cost=120.37..122.07 rows=10 width=136) (actual time=1,870.351..1,870.373 rows=10 loops=1)

  • Group Key: rp.idpolicyversion
22. 0.017 1,870.337 ↑ 3.0 10 1

WindowAgg (cost=120.37..121.05 rows=30 width=50) (actual time=1,870.328..1,870.337 rows=10 loops=1)

23. 0.014 1,870.320 ↑ 3.0 10 1

Sort (cost=120.37..120.45 rows=30 width=42) (actual time=1,870.319..1,870.320 rows=10 loops=1)

  • Sort Key: rp.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 25kB
24. 0.004 1,870.306 ↑ 3.0 10 1

Nested Loop (cost=0.42..119.64 rows=30 width=42) (actual time=1,870.227..1,870.306 rows=10 loops=1)

25. 1,870.182 1,870.182 ↑ 1.0 10 1

CTE Scan on datawindow w (cost=0.00..0.20 rows=10 width=8) (actual time=1,870.173..1,870.182 rows=10 loops=1)

26. 0.120 0.120 ↑ 3.0 1 10

Index Scan using tblriskpremium_idpolicyversion_index on tblriskpremium rp (cost=0.42..11.91 rows=3 width=42) (actual time=0.011..0.012 rows=1 loops=10)

  • Index Cond: (idpolicyversion = w.idpolicyversion)
27. 0.040 0.040 ↑ 1.0 1 10

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv_1 (cost=0.42..8.44 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: (id = rp.idpolicyversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
28. 0.070 0.070 ↑ 1.0 1 10

Index Scan using pk_tblinsurance on tblpolicy p_1 (cost=0.42..0.97 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: (id = pv_1.idpolicy)
Planning time : 3.285 ms
Execution time : 1,870.759 ms