explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Gjg : Optimization for: plan #MIAB

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.008 3,050.682 ↓ 10.0 10 1

Limit (cost=25,406.64..25,406.65 rows=1 width=24) (actual time=3,050.675..3,050.682 rows=10 loops=1)

2. 1.111 3,050.674 ↓ 10.0 10 1

Sort (cost=25,406.64..25,406.65 rows=1 width=24) (actual time=3,050.674..3,050.674 rows=10 loops=1)

  • Sort Key: pv.createdtime DESC
  • Sort Method: top-N heapsort Memory: 26kB
3. 5.133 3,049.563 ↓ 12,159.0 12,159 1

WindowAgg (cost=23,921.26..25,406.63 rows=1 width=24) (actual time=3,048.221..3,049.563 rows=12,159 loops=1)

4. 87.704 3,044.430 ↓ 12,159.0 12,159 1

Nested Loop (cost=23,921.26..25,406.62 rows=1 width=16) (actual time=401.610..3,044.430 rows=12,159 loops=1)

5. 0.000 2,758.942 ↓ 974.3 197,784 1

Nested Loop (cost=23,920.98..25,342.71 rows=203 width=24) (actual time=401.489..2,758.942 rows=197,784 loops=1)

6. 189.601 1,958.996 ↓ 705.1 203,782 1

Nested Loop (cost=23,920.56..25,062.47 rows=289 width=24) (actual time=401.472..1,958.996 rows=203,782 loops=1)

  • Join Filter: (od.id = pv.id)
7. 66.851 1,158.049 ↓ 437.3 203,782 1

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

8. 129.615 474.605 ↓ 147.1 205,531 1

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

  • Group Key: od_1.id
9. 123.977 344.990 ↓ 147.1 205,531 1

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

  • Hash Cond: ((od_1.offernumber = tblofferdata.offernumber) AND ((od_1.offerversion)::bigint = (max((tblofferdata.offerversion)::bigint))))
10. 28.298 28.298 ↓ 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.017..28.298 rows=279,388 loops=1)

11. 35.098 192.715 ↓ 1.3 205,512 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 13287kB
12. 128.482 157.617 ↓ 1.3 205,512 1

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

  • Group Key: tblofferdata.offernumber
13. 29.135 29.135 ↓ 1.0 279,388 1

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

14. 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
15. 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: (policyversionstatus = 'CURRENT'::text)
16. 815.128 815.128 ↑ 1.0 1 203,782

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..0.97 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=203,782)

  • Index Cond: (id = pv.idpolicy)
  • Filter: ((canceldate IS NULL) AND (policytype = 'OFFER'::text))
  • Rows Removed by Filter: 0
17. 197.784 197.784 ↓ 0.0 0 197,784

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=197,784)

  • Index Cond: (id = p.idagency)
  • Filter: ((ltree2text(subltree(path, 0, 1)))::integer = 2375)
  • Rows Removed by Filter: 1
Planning time : 2.559 ms
Execution time : 3,051.858 ms