explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zHqn : Optimization for: plan #AxJS

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 0.082 ↑ 1.0 1 1

Aggregate (cost=3,023.59..3,023.60 rows=1 width=8) (actual time=0.082..0.082 rows=1 loops=1)

2.          

CTE deleted

3. 0.001 0.078 ↓ 0.0 0 1

Delete on vipineligibledeals (cost=3,015.63..3,023.56 rows=1 width=104) (actual time=0.078..0.078 rows=0 loops=1)

4. 0.001 0.077 ↓ 0.0 0 1

Nested Loop (cost=3,015.63..3,023.56 rows=1 width=104) (actual time=0.077..0.077 rows=0 loops=1)

5. 0.001 0.076 ↓ 0.0 0 1

HashAggregate (cost=3,015.21..3,015.22 rows=1 width=102) (actual time=0.076..0.076 rows=0 loops=1)

  • Group Key: vdf.id
6. 0.008 0.075 ↓ 0.0 0 1

Hash Semi Join (cost=1,513.82..3,015.21 rows=1 width=102) (actual time=0.075..0.075 rows=0 loops=1)

  • Hash Cond: ((upper((cvs.track)::text) = upper((cvs_1.track)::text)) AND (upper((cvs.subtrack)::text) = upper((cvs_1.subtrack)::text)))
7. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=1,509.80..3,011.19 rows=1 width=165) (never executed)

  • Hash Cond: (((vdf.productid)::text = ("ANY_subquery".productid)::text) AND (vdf.allocatedbooknet = "ANY_subquery".allocatedbooknet) AND ((vdf.directresellerpo)::text = ("ANY_subquery".directresellerpo)::text) AND (vdf.soposnumber = "ANY_subquery".soposnumber) AND ((vdf.track)::text = ("ANY_subquery".track)::text) AND (cvs.precedence_order = "ANY_subquery".max))
8. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..1,501.79 rows=1 width=125) (never executed)

  • Join Filter: ((upper((vdf.track)::text) = upper((cvs.track)::text)) AND (upper((vdf.subtrack)::text) = upper((cvs.subtrack)::text)))
9. 0.000 0.000 ↓ 0.0 0

Seq Scan on ciscovipsubtractprecedence cvs (cost=0.00..4.00 rows=1 width=44) (never executed)

  • Filter: (vip_period_id = 2)
10. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_vipineligibledeals_1 on vipineligibledeals vdf (cost=0.42..1,495.92 rows=75 width=81) (never executed)

  • Index Cond: (customer_id = 129)
  • Filter: (vip_period_id = 2)
11. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,507.51..1,507.51 rows=75 width=136) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ANY_subquery (cost=1,504.32..1,507.51 rows=75 width=136) (never executed)

13. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=1,504.32..1,506.76 rows=75 width=60) (never executed)

  • Group Key: vdf_1.productid, vdf_1.allocatedbooknet, vdf_1.directresellerpo, vdf_1.soposnumber, vdf_1.track, vdf_1.transactiondate
  • Filter: (count(vdf_1.id) > 1)
14. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,504.32..1,504.51 rows=75 width=64) (never executed)

  • Sort Key: vdf_1.productid, vdf_1.allocatedbooknet, vdf_1.directresellerpo, vdf_1.soposnumber, vdf_1.track, vdf_1.transactiondate
15. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.42..1,501.98 rows=75 width=64) (never executed)

  • Join Filter: ((upper((cvs_2.track)::text) = upper((vdf_1.track)::text)) AND (upper((cvs_2.subtrack)::text) = upper((vdf_1.subtrack)::text)))
16. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_vipineligibledeals_1 on vipineligibledeals vdf_1 (cost=0.42..1,495.92 rows=75 width=79) (never executed)

  • Index Cond: (customer_id = 129)
  • Filter: (vip_period_id = 2)
17. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..4.00 rows=1 width=38) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Seq Scan on ciscovipsubtractprecedence cvs_2 (cost=0.00..4.00 rows=1 width=38) (never executed)

  • Filter: (vip_period_id = 2)
19. 0.001 0.067 ↓ 0.0 0 1

Hash (cost=4.00..4.00 rows=1 width=40) (actual time=0.067..0.067 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
20. 0.066 0.066 ↓ 0.0 0 1

Seq Scan on ciscovipsubtractprecedence cvs_1 (cost=0.00..4.00 rows=1 width=40) (actual time=0.066..0.066 rows=0 loops=1)

  • Filter: (vip_period_id = 2)
  • Rows Removed by Filter: 160
21. 0.000 0.000 ↓ 0.0 0

Index Scan using vipineligibledeals_pkey on vipineligibledeals (cost=0.42..8.33 rows=1 width=10) (never executed)

  • Index Cond: (id = vdf.id)
22. 0.079 0.079 ↓ 0.0 0 1

CTE Scan on deleted (cost=0.00..0.02 rows=1 width=0) (actual time=0.079..0.079 rows=0 loops=1)