explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WKtp

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.029 289,498.265 ↓ 1.5 21 1

Sort (cost=650,811.78..650,811.78 rows=14 width=33) (actual time=289,498.263..289,498.265 rows=21 loops=1)

  • Sort Key: v_s.disporder, s.sport_id
  • Sort Method: quicksort Memory: 26kB
2. 0.027 289,498.236 ↓ 1.5 21 1

HashAggregate (cost=650,811.68..650,811.72 rows=14 width=33) (actual time=289,498.233..289,498.236 rows=21 loops=1)

3. 0.003 289,498.209 ↓ 1.5 21 1

Append (cost=368,948.82..650,811.65 rows=14 width=33) (actual time=144,251.668..289,498.209 rows=21 loops=1)

4. 0.024 144,251.836 ↑ 2.0 6 1

Merge Join (cost=368,948.82..368,958.13 rows=12 width=34) (actual time=144,251.668..144,251.836 rows=6 loops=1)

  • Merge Cond: (c.ev_class_id = s.ob_id)
5. 0.002 144,251.566 ↑ 2.9 39 1

Nested Loop (cost=368,945.03..368,954.39 rows=115 width=13) (actual time=144,251.369..144,251.566 rows=39 loops=1)

6. 0.206 144,251.447 ↑ 2.9 39 1

Index Scan using cevclass_pk on tevclass c (cost=368,945.03..368,947.42 rows=115 width=15) (actual time=144,251.337..144,251.447 rows=39 loops=1)

  • Filter: (hashed SubPlan 2)
7.          

SubPlan (forIndex Scan)

8. 14,532.662 144,251.241 ↓ 1.4 39 1

Unique (cost=0.00..368,945.02 rows=27 width=4) (actual time=0.132..144,251.241 rows=39 loops=1)

9. 25,435.283 129,718.579 ↓ 19.0 210,651,023 1

Nested Loop (cost=0.00..363,397.78 rows=11,094,483 width=4) (actual time=0.132..129,718.579 rows=210,651,023 loops=1)

10. 41.540 925.734 ↓ 16.0 191,758 1

Nested Loop (cost=0.00..37,786.01 rows=11,948 width=12) (actual time=0.084..925.734 rows=191,758 loops=1)

11. 11.214 98.002 ↓ 2.4 11,912 1

Nested Loop (cost=0.00..4,432.46 rows=4,959 width=8) (actual time=0.057..98.002 rows=11,912 loops=1)

12. 62.514 62.514 ↓ 1.1 12,137 1

Index Scan using iev_x2 on tev e (cost=0.00..1,795.90 rows=10,916 width=12) (actual time=0.033..62.514 rows=12,137 loops=1)

  • Filter: ((status = 'A'::bpchar) AND (displayed = 'Y'::bpchar))
13. 24.274 24.274 ↑ 1.0 1 12,137

Index Scan using cevtype_pk on tevtype t (cost=0.00..0.24 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=12,137)

  • Index Cond: (t.ev_type_id = e.ev_type_id)
  • Filter: ((t.status = 'A'::bpchar) AND (t.displayed = 'Y'::bpchar))
14. 786.192 786.192 ↓ 1.6 16 11,912

Index Scan using ievmkt_ix3 on tevmkt m (cost=0.00..6.69 rows=10 width=4) (actual time=0.010..0.066 rows=16 loops=11,912)

  • Index Cond: (m.ev_id = e.ev_id)
  • Filter: ((m.status = 'A'::bpchar) AND (m.displayed = 'Y'::bpchar))
15. 103,357.562 103,357.562 ↓ 1.2 1,099 191,758

Index Scan using ievoc_ix2 on tevoc o (cost=0.00..23.91 rows=955 width=4) (actual time=0.007..0.539 rows=1,099 loops=191,758)

  • Index Cond: (o.ev_id = m.ev_id)
  • Filter: ((o.status = 'A'::bpchar) AND (o.displayed = 'Y'::bpchar))
16. 0.117 0.117 ↑ 1.0 1 39

Index Scan using ievcategory_ix1 on tevcategory y (cost=0.00..0.06 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=39)

  • Index Cond: (y.category = c.category)
17. 0.060 0.246 ↓ 2.4 56 1

Sort (cost=3.79..3.80 rows=23 width=25) (actual time=0.242..0.246 rows=56 loops=1)

  • Sort Key: s.ob_id
  • Sort Method: quicksort Memory: 29kB
18. 0.061 0.186 ↓ 2.5 58 1

Merge Join (cost=0.00..3.68 rows=23 width=25) (actual time=0.076..0.186 rows=58 loops=1)

  • Merge Cond: (s.sport_id = v_s.id)
19. 0.035 0.035 ↑ 1.0 58 1

Index Scan using csport_pk on tsport s (cost=0.00..0.71 rows=58 width=21) (actual time=0.016..0.035 rows=58 loops=1)

  • Filter: (ob_level = 'c'::bpchar)
20. 0.090 0.090 ↑ 1.2 81 1

Index Scan using iview_x2 on tview v_s (cost=0.00..5.69 rows=98 width=8) (actual time=0.050..0.090 rows=81 loops=1)

  • Index Cond: ((v_s.view = 'gb'::bpchar) AND (v_s.sort = 'SPT_PRE'::bpchar))
21. 0.005 145,246.370 ↓ 7.5 15 1

Nested Loop (cost=281,851.71..281,853.48 rows=2 width=30) (actual time=145,246.203..145,246.370 rows=15 loops=1)

22. 0.028 145,246.185 ↓ 2.5 15 1

Hash Join (cost=281,851.71..281,852.13 rows=6 width=26) (actual time=145,246.164..145,246.185 rows=15 loops=1)

  • Hash Cond: (s.ob_id = y.ev_category_id)
23. 0.017 0.017 ↑ 1.0 23 1

Seq Scan on tsport s (cost=0.00..0.38 rows=23 width=21) (actual time=0.004..0.017 rows=23 loops=1)

  • Filter: (ob_level = 'y'::bpchar)
24. 0.008 145,246.140 ↑ 1.2 16 1

Hash (cost=281,851.64..281,851.64 rows=20 width=13) (actual time=145,246.140..145,246.140 rows=16 loops=1)

25. 0.088 145,246.132 ↑ 1.2 16 1

Index Scan using cevcategory_pk on tevcategory y (cost=281,851.10..281,851.64 rows=20 width=13) (actual time=145,246.120..145,246.132 rows=16 loops=1)

  • Filter: (hashed SubPlan 1)
26.          

SubPlan (forIndex Scan)

27. 16,382.642 145,246.044 ↑ 1.8 16 1

Unique (cost=452.68..281,851.09 rows=29 width=11) (actual time=32.894..145,246.044 rows=16 loops=1)

28. 25,165.665 128,863.402 ↓ 25.1 210,651,023 1

Nested Loop (cost=452.68..277,654.48 rows=8,393,217 width=11) (actual time=32.893..128,863.402 rows=210,651,023 loops=1)

29. 42.039 1,107.207 ↓ 21.2 191,758 1

Nested Loop (cost=452.68..31,318.67 rows=9,039 width=19) (actual time=32.869..1,107.207 rows=191,758 loops=1)

30. 187.723 338.536 ↓ 3.2 11,912 1

Nested Loop (cost=452.68..6,083.23 rows=3,752 width=15) (actual time=32.833..338.536 rows=11,912 loops=1)

  • Join Filter: (t.ev_class_id = c.ev_class_id)
31. 0.465 0.465 ↓ 1.2 201 1

Index Scan using ievclass_ix3 on tevclass c (cost=0.00..21.93 rows=174 width=15) (actual time=0.014..0.465 rows=201 loops=1)

  • Filter: ((status = 'A'::bpchar) AND (displayed = 'Y'::bpchar))
32. 129.491 150.348 ↓ 2.4 11,912 201

Materialize (cost=452.68..467.55 rows=4,959 width=8) (actual time=0.058..0.748 rows=11,912 loops=201)

33. 3.519 20.857 ↓ 2.4 11,912 1

Hash Join (cost=262.56..451.19 rows=4,959 width=8) (actual time=11.629..20.857 rows=11,912 loops=1)

  • Hash Cond: (e.ev_type_id = t.ev_type_id)
34. 5.734 5.734 ↓ 1.1 12,137 1

Seq Scan on tev e (cost=0.00..149.19 rows=10,916 width=8) (actual time=0.007..5.734 rows=12,137 loops=1)

  • Filter: ((status = 'A'::bpchar) AND (displayed = 'Y'::bpchar))
35. 3.358 11.604 ↓ 1.4 19,772 1

Hash (cost=214.50..214.50 rows=13,732 width=8) (actual time=11.604..11.604 rows=19,772 loops=1)

36. 8.246 8.246 ↓ 1.4 19,772 1

Seq Scan on tevtype t (cost=0.00..214.50 rows=13,732 width=8) (actual time=0.005..8.246 rows=19,772 loops=1)

  • Filter: ((status = 'A'::bpchar) AND (displayed = 'Y'::bpchar))
37. 726.632 726.632 ↓ 1.6 16 11,912

Index Scan using ievmkt_ix3 on tevmkt m (cost=0.00..6.69 rows=10 width=4) (actual time=0.009..0.061 rows=16 loops=11,912)

  • Index Cond: (m.ev_id = e.ev_id)
  • Filter: ((m.status = 'A'::bpchar) AND (m.displayed = 'Y'::bpchar))
38. 102,590.530 102,590.530 ↓ 1.2 1,099 191,758

Index Scan using ievoc_ix2 on tevoc o (cost=0.00..23.91 rows=955 width=4) (actual time=0.007..0.535 rows=1,099 loops=191,758)

  • Index Cond: (o.ev_id = m.ev_id)
  • Filter: ((o.status = 'A'::bpchar) AND (o.displayed = 'Y'::bpchar))
39. 0.180 0.180 ↑ 1.0 1 15

Index Scan using iview_x2 on tview v_s (cost=0.00..0.22 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=15)

  • Index Cond: ((v_s.view = 'gb'::bpchar) AND (v_s.sort = 'SPT_PRE'::bpchar) AND (v_s.id = s.sport_id))