explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tqae

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on counts (cost=243,587,621.48..258,343,745.72 rows=218,609,248 width=650) (actual rows= loops=)

2.          

CTE counts

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=220,633,650.44..243,587,621.48 rows=218,609,248 width=312) (actual rows= loops=)

  • Group Key: v.visit_id, z.zone_name, vf.id
4.          

CTE orderdatacte

5. 0.000 0.000 ↓ 0.0

Hash Join (cost=24,054.35..67,593.48 rows=652,147 width=68) (actual rows= loops=)

  • Hash Cond: (od.order_type = odt.id)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=24,053.31..58,625.42 rows=652,147 width=24) (actual rows= loops=)

  • Hash Cond: (od.order_id = opq.order_id)
7. 0.000 0.000 ↓ 0.0

Seq Scan on order_data od (cost=0.00..14,114.92 rows=653,792 width=19) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash (cost=12,079.47..12,079.47 rows=652,147 width=17) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on order_product_quantity opq (cost=0.00..12,079.47 rows=652,147 width=17) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash (cost=1.02..1.02 rows=2 width=50) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on outlet_order_type odt (cost=0.00..1.02 rows=2 width=50) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Sort (cost=220,566,056.96..221,112,580.08 rows=218,609,248 width=260) (actual rows= loops=)

  • Sort Key: v.visit_id, z.zone_name, vf.id
13. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=75,761,182.05..82,687,797.28 rows=218,609,248 width=260) (actual rows= loops=)

  • Merge Cond: (vf.visit_id = v.visit_id)
14. 0.000 0.000 ↓ 0.0

Sort (cost=111,874.94..112,827.47 rows=381,012 width=161) (actual rows= loops=)

  • Sort Key: vf.visit_id
15. 0.000 0.000 ↓ 0.0

Seq Scan on visit_fl_hierarchy_data vf (cost=0.00..14,046.12 rows=381,012 width=161) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Materialize (cost=75,649,306.92..79,848,105.52 rows=218,609,248 width=103) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=75,649,306.92..79,301,582.40 rows=218,609,248 width=103) (actual rows= loops=)

  • Merge Cond: ((v.visit_id = od_1.visit_id) AND (pm.id = od_1.product_id))
18. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=75,555,450.92..78,101,637.22 rows=218,609,248 width=71) (actual rows= loops=)

  • Merge Cond: ((v.visit_id = vn.visit_id) AND (pm.id = vn.product_id))
19. 0.000 0.000 ↓ 0.0

Sort (cost=65,950,659.75..66,497,182.87 rows=218,609,248 width=63) (actual rows= loops=)

  • Sort Key: v.visit_id, pm.id
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,629.45..2,792,360.07 rows=218,609,248 width=63) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,629.45..59,723.32 rows=382,184 width=59) (actual rows= loops=)

  • Hash Cond: (v.outlet_id = o.outlet_id)
22. 0.000 0.000 ↓ 0.0

Seq Scan on visit v (cost=0.00..52,838.84 rows=382,184 width=58) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=1,323.54..1,323.54 rows=24,473 width=9) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.31..1,323.54 rows=24,473 width=9) (actual rows= loops=)

  • Hash Cond: (o.zone_id = z.zone_id)
25. 0.000 0.000 ↓ 0.0

Seq Scan on outlet o (cost=0.00..984.73 rows=24,473 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=1.58..1.58 rows=58 width=9) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on zone z (cost=0.00..1.58 rows=58 width=9) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..22.58 rows=572 width=4) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on product_master pm (cost=0.00..19.72 rows=572 width=4) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Materialize (cost=9,604,757.87..9,831,420.43 rows=45,332,512 width=20) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Sort (cost=9,604,757.87..9,718,089.15 rows=45,332,512 width=20) (actual rows= loops=)

  • Sort Key: vn.visit_id, vn.product_id
32. 0.000 0.000 ↓ 0.0

Seq Scan on visit_norms_data vn (cost=0.00..1,050,792.12 rows=45,332,512 width=20) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Materialize (cost=93,856.01..97,116.74 rows=652,147 width=40) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Sort (cost=93,856.01..95,486.37 rows=652,147 width=40) (actual rows= loops=)

  • Sort Key: od_1.visit_id, od_1.product_id
35. 0.000 0.000 ↓ 0.0

CTE Scan on orderdatacte od_1 (cost=0.00..13,042.94 rows=652,147 width=40) (actual rows= loops=)