explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zSmQ : Optimization for: plan #8DIR

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,724.110 2,724.110 ↓ 10.0 10 1

CTE Scan on premiums (cost=25,422.15..25,422.17 rows=1 width=72) (actual time=2,724.049..2,724.110 rows=10 loops=1)

2.          

CTE datawindow

3. 0.011 2,723.943 ↓ 10.0 10 1

Limit (cost=25,400.57..25,400.57 rows=1 width=24) (actual time=2,723.934..2,723.943 rows=10 loops=1)

4. 1.073 2,723.932 ↓ 10.0 10 1

Sort (cost=25,400.57..25,400.57 rows=1 width=24) (actual time=2,723.932..2,723.932 rows=10 loops=1)

  • Sort Key: pv.createdtime DESC
  • Sort Method: top-N heapsort Memory: 26kB
5. 5.260 2,722.859 ↓ 12,159.0 12,159 1

WindowAgg (cost=23,921.26..25,400.56 rows=1 width=24) (actual time=2,721.509..2,722.859 rows=12,159 loops=1)

6. 70.275 2,717.599 ↓ 12,159.0 12,159 1

Nested Loop (cost=23,921.26..25,400.54 rows=1 width=16) (actual time=384.438..2,717.599 rows=12,159 loops=1)

7. 92.410 2,450.740 ↓ 954.3 196,584 1

Nested Loop (cost=23,920.98..25,335.72 rows=206 width=24) (actual time=384.325..2,450.740 rows=196,584 loops=1)

8. 116.249 1,768.572 ↓ 720.1 196,586 1

Nested Loop (cost=23,920.56..25,062.47 rows=273 width=24) (actual time=384.309..1,768.572 rows=196,586 loops=1)

  • Join Filter: (od.id = pv.id)
9. 0.000 1,040.977 ↓ 437.3 203,782 1

Nested Loop (cost=23,920.14..24,660.01 rows=466 width=16) (actual time=384.291..1,040.977 rows=203,782 loops=1)

10. 121.594 452.440 ↓ 147.1 205,531 1

HashAggregate (cost=23,919.71..23,933.68 rows=1,397 width=8) (actual time=384.265..452.440 rows=205,531 loops=1)

  • Group Key: od_1.id
11. 116.200 330.846 ↓ 147.1 205,531 1

Hash Join (cost=15,263.75..23,916.22 rows=1,397 width=8) (actual time=187.024..330.846 rows=205,531 loops=1)

  • Hash Cond: ((od_1.offernumber = tblofferdata.offernumber) AND ((od_1.offerversion)::bigint = (max((tblofferdata.offerversion)::bigint))))
12. 27.924 27.924 ↓ 1.0 279,388 1

Seq Scan on tblofferdata od_1 (cost=0.00..7,115.85 rows=279,385 width=21) (actual time=0.015..27.924 rows=279,388 loops=1)

13. 33.903 186.722 ↓ 1.3 205,512 1

Hash (cost=12,969.16..12,969.16 rows=152,973 width=19) (actual time=186.722..186.722 rows=205,512 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13287kB
14. 124.427 152.819 ↓ 1.3 205,512 1

HashAggregate (cost=9,909.70..11,439.43 rows=152,973 width=19) (actual time=107.237..152.819 rows=205,512 loops=1)

  • Group Key: tblofferdata.offernumber
15. 28.392 28.392 ↓ 1.0 279,388 1

Seq Scan on tblofferdata (cost=0.00..7,115.85 rows=279,385 width=13) (actual time=0.007..28.392 rows=279,388 loops=1)

16. 616.593 616.593 ↑ 1.0 1 205,531

Index Scan using tblofferdata_pkey on tblofferdata od (cost=0.42..0.52 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=205,531)

  • Index Cond: (id = od_1.id)
  • Filter: ((validstartdate + validdays) < now())
  • Rows Removed by Filter: 0
17. 611.346 611.346 ↑ 1.0 1 203,782

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv (cost=0.42..0.85 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=203,782)

  • Index Cond: (id = od_1.id)
  • Filter: ((canceldate IS NULL) AND (policyversionstatus = 'CURRENT'::text))
  • Rows Removed by Filter: 0
18. 589.758 589.758 ↑ 1.0 1 196,586

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..1.00 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=196,586)

  • Index Cond: (id = pv.idpolicy)
  • Filter: (policytype = 'OFFER'::text)
  • Rows Removed by Filter: 0
19. 196.584 196.584 ↓ 0.0 0 196,584

Index Scan using tblagencyunit_pkey on tblagencyunit agency (cost=0.28..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=196,584)

  • Index Cond: (id = p.idagency)
  • Filter: ((ltree2text(subltree(path, 0, 1)))::integer = 2375)
  • Rows Removed by Filter: 1
20.          

CTE premiums

21. 0.004 2,724.105 ↓ 10.0 10 1

Nested Loop (cost=12.83..21.58 rows=1 width=72) (actual time=2,724.047..2,724.105 rows=10 loops=1)

22. 0.009 2,724.071 ↓ 10.0 10 1

Nested Loop (cost=12.41..20.61 rows=1 width=144) (actual time=2,724.036..2,724.071 rows=10 loops=1)

23. 0.024 2,724.042 ↓ 10.0 10 1

GroupAggregate (cost=11.99..12.16 rows=1 width=136) (actual time=2,724.025..2,724.042 rows=10 loops=1)

  • Group Key: rp.idpolicyversion
24. 0.016 2,724.018 ↓ 3.3 10 1

WindowAgg (cost=11.99..12.05 rows=3 width=50) (actual time=2,724.011..2,724.018 rows=10 loops=1)

25. 0.012 2,724.002 ↓ 3.3 10 1

Sort (cost=11.99..11.99 rows=3 width=42) (actual time=2,724.001..2,724.002 rows=10 loops=1)

  • Sort Key: rp.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 25kB
26. 0.012 2,723.990 ↓ 3.3 10 1

Nested Loop (cost=0.42..11.96 rows=3 width=42) (actual time=2,723.953..2,723.990 rows=10 loops=1)

27. 2,723.948 2,723.948 ↓ 10.0 10 1

CTE Scan on datawindow w (cost=0.00..0.02 rows=1 width=8) (actual time=2,723.936..2,723.948 rows=10 loops=1)

28. 0.030 0.030 ↑ 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.003..0.003 rows=1 loops=10)

  • Index Cond: (idpolicyversion = w.idpolicyversion)
29. 0.020 0.020 ↑ 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.002..0.002 rows=1 loops=10)

  • Index Cond: (id = rp.idpolicyversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
30. 0.030 0.030 ↑ 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.003..0.003 rows=1 loops=10)

  • Index Cond: (id = pv_1.idpolicy)
Planning time : 4.377 ms
Execution time : 2,725.409 ms