explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HAXz : QA take 2

Settings
# exclusive inclusive rows x rows loops node
1. 44.349 18,604.150 ↓ 489.0 489 1

Nested Loop (cost=1,718.91..2,454.75 rows=1 width=1,582) (actual time=89.526..18,604.150 rows=489 loops=1)

  • Join Filter: (t.team_id = toh.team_id)
2. 5.783 18,146.630 ↓ 44,465.0 44,465 1

Nested Loop (cost=1,718.63..1,873.38 rows=1 width=64) (actual time=84.352..18,146.630 rows=44,465 loops=1)

3. 10,409.002 17,962.471 ↓ 44,594.0 44,594 1

Nested Loop (cost=1,718.35..1,873.03 rows=1 width=36) (actual time=84.304..17,962.471 rows=44,594 loops=1)

  • Join Filter: (o.team_id = t.team_id)
4. 1,091.462 3,682.297 ↓ 227,716.0 227,716 1

Nested Loop (cost=1,718.07..1,729.14 rows=1 width=25) (actual time=71.859..3,682.297 rows=227,716 loops=1)

  • Join Filter: ((((max(((os_1.season)::character varying)::text))::character varying)::ltree) = os.season)
  • Rows Removed by Join Filter: 7,231,057
5. 4.862 118.610 ↓ 2,354.5 4,709 1

Nested Loop (cost=1,717.79..1,728.22 rows=2 width=66) (actual time=69.790..118.610 rows=4,709 loops=1)

6. 25.174 100.224 ↓ 2,254.0 2,254 1

GroupAggregate (cost=1,717.50..1,717.54 rows=1 width=36) (actual time=69.764..100.224 rows=2,254 loops=1)

  • Group Key: o.team_id
7. 13.729 75.050 ↓ 32,245.0 32,245 1

Sort (cost=1,717.50..1,717.51 rows=1 width=17) (actual time=69.722..75.050 rows=32,245 loops=1)

  • Sort Key: o.team_id
  • Sort Method: quicksort Memory: 3,288kB
8. 4.860 61.321 ↓ 32,245.0 32,245 1

Nested Loop (cost=0.28..1,717.49 rows=1 width=17) (actual time=0.045..61.321 rows=32,245 loops=1)

9. 1.231 1.231 ↑ 1.0 3,682 1

Seq Scan on team_organization_history o (cost=0.00..154.82 rows=3,682 width=34) (actual time=0.013..1.231 rows=3,682 loops=1)

10. 55.230 55.230 ↓ 9.0 9 3,682

Index Only Scan using organization_season_pkey on organization_season os_1 (cost=0.28..0.41 rows=1 width=17) (actual time=0.004..0.015 rows=9 loops=3,682)

  • Index Cond: ((organization_id = o.organization_id) AND (season >= o.start_season) AND (season <= o.end_season))
  • Filter: (((season)::character varying)::numeric <= (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone))::numeric)
  • Rows Removed by Filter: 0
  • Heap Fetches: 0
11. 13.524 13.524 ↑ 1.0 2 2,254

Index Scan using team_history_pkey on team_history th (cost=0.28..10.66 rows=2 width=30) (actual time=0.005..0.006 rows=2 loops=2,254)

  • Index Cond: (team_id = o.team_id)
12. 2,472.225 2,472.225 ↓ 1,584.0 1,584 4,709

Index Scan using organization_season_season on organization_season os (cost=0.28..0.45 rows=1 width=17) (actual time=0.014..0.525 rows=1,584 loops=4,709)

  • Index Cond: ((season >= th.start_season) AND (season <= th.end_season))
13. 3,871.172 3,871.172 ↓ 0.0 0 227,716

Index Scan using team_pkey on team t (cost=0.28..143.88 rows=1 width=11) (actual time=0.017..0.017 rows=0 loops=227,716)

  • Index Cond: ((team_id = th.team_id) AND (team_id = ANY ('{1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,22,23,24,25,26,27,28,29,30,34,2950,2951,2952,2953,2954,2955,2956,2957,2958,2959,2960,2961,2962,2963,2964,2965,2966,2967,2968,2969,2970,2971,2972,2973,2974,2975,2976,2977,2978,2979,2980,2981,2982,2983,2984,2985,2986,12,1174,1210,1270,1271,1410,1411,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1310,1311,1330,1331,1370,1990,1991,1992,1993,1994,1995,1550,1551,1552,1553,2987,3272,3273,3274,3275,3276,3277,3278,3279,3280,3281,3282,3283,3284,3285,3286,3287,3288,3289,3290,3291,3292,3293,3294,3295,3296,3297,716,717,1522,1523,1524,1525,1526,1527,1528,1529,1530,1531,1532,1533,1534,1535,1536,1537,1538,1539,1540,1541,1542,1543,1544,1545,1546,1547,1590,1591,1592,2190,2191,2192,2193,2250,2330,2370,2412,2413,2670,2671,2672,1294,1295,1296,1297,1996,1997,1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2290,2291,2292,2293,2350,2351,2352,2390,2510,2511,2512,2513,2585,2586,2587,2588,2590,2591,2592,2593,2594,2595,2630,2631,35,36,37,38,3298,3350,3351,3755,3756,3757,3758,3759,3760,3761,3762,3763,3764,3765,3766,3767,3768,3769,3770,3771,3772,3773,3774,3775,3776,3777,3778,3779,3780,3781,3782,3783,3784,3785,3786,3787,3788,3789,3790,3791,3792,3793,3794,3795,3796,3797,3798,1390,2616,2617,2618,2619,2620,2621,2673,2674,2770,2988,2989,2990,2991,2992,2993,2994,2995,2996,2997,2998,2999,3000,3001,3002,3003,3004,3005,3006,3007,3008,3009,3010,3011,3051,3070,3151,3190,3191,3192,3193,3194,3195,3196,3197,3198,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,2690,2755,2756,2757,2758,2759,2796,2797,2798,2799,2800,2801,2802,2803,2804,2805,2873,2874,2875,2876,2877,2878,2879,2880,2881,2882,2883,2884,2885,2886,2887,2888,599,600,601,602,603,604,605,606,607,608,609,610,611,612,615,616,617,618,619,620,621,622,623,624,625,626,627,628}'::integer[])))
14. 178.376 178.376 ↑ 1.0 1 44,594

Index Scan using organization_history_pkey on organization_history oh (cost=0.28..0.34 rows=1 width=54) (actual time=0.003..0.004 rows=1 loops=44,594)

  • Index Cond: ((organization_id = os.organization_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 6
15. 44.465 44.465 ↓ 0.0 0 44,465

Index Scan using team_organization_history_pkey on team_organization_history toh (cost=0.28..0.45 rows=1 width=34) (actual time=0.001..0.001 rows=0 loops=44,465)

  • Index Cond: ((team_id = th.team_id) AND (organization_id = os.organization_id) AND (os.season >= start_season))
  • Filter: (((organization_id <> ALL ('{114,115}'::integer[])) OR (organization_id IS NULL)) AND (os.season <= end_season))
  • Rows Removed by Filter: 0
16.          

SubPlan (for Nested Loop)

17. 1.956 1.956 ↑ 1.0 1 489

Index Scan using team_history_pkey on team_history th_1 (cost=0.28..8.30 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 1
18. 1.467 9.780 ↑ 1.0 1 489

Nested Loop (cost=0.70..17.07 rows=1 width=12) (actual time=0.020..0.020 rows=1 loops=489)

19. 5.379 5.379 ↑ 1.0 1 489

Index Scan using team_name_language_history_pkey on team_name_language_history h (cost=0.41..8.76 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND ((name_role_code)::text = 'NS'::text) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 0
20. 2.934 2.934 ↑ 1.0 1 489

Index Scan using team_name_pkey on team_name n (cost=0.28..8.30 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=489)

  • Index Cond: (team_name_id = h.team_name_id)
21. 0.978 4.890 ↑ 1.0 1 489

Nested Loop (cost=0.70..17.07 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=489)

22. 2.445 2.445 ↑ 1.0 1 489

Index Scan using team_name_language_history_pkey on team_name_language_history h_1 (cost=0.41..8.76 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND ((name_role_code)::text = 'N'::text) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 0
23. 1.467 1.467 ↑ 1.0 1 489

Index Scan using team_name_pkey on team_name n_1 (cost=0.28..8.30 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=489)

  • Index Cond: (team_name_id = h_1.team_name_id)
24. 1.467 5.379 ↑ 1.0 1 489

Nested Loop (cost=0.70..17.07 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=489)

25. 1.956 1.956 ↑ 1.0 1 489

Index Scan using team_name_language_history_pkey on team_name_language_history h_2 (cost=0.41..8.76 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND ((name_role_code)::text = 'NA'::text) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 0
26. 1.956 1.956 ↑ 1.0 1 489

Index Scan using team_name_pkey on team_name n_2 (cost=0.28..8.30 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=489)

  • Index Cond: (team_name_id = h_2.team_name_id)
27. 1.467 4.890 ↑ 1.0 1 489

Nested Loop (cost=0.70..17.07 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=489)

28. 2.934 2.934 ↑ 1.0 1 489

Index Scan using team_name_language_history_pkey on team_name_language_history h_3 (cost=0.41..8.76 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND ((name_role_code)::text = 'NDB'::text) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 4
29. 0.489 0.489 ↑ 1.0 1 489

Index Scan using team_name_pkey on team_name n_3 (cost=0.28..8.30 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=489)

  • Index Cond: (team_name_id = h_3.team_name_id)
30. 1.467 4.401 ↑ 1.0 1 489

Nested Loop (cost=0.70..17.07 rows=1 width=12) (actual time=0.008..0.009 rows=1 loops=489)

31. 2.445 2.445 ↑ 1.0 1 489

Index Scan using team_name_language_history_pkey on team_name_language_history h_4 (cost=0.41..8.76 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND ((name_role_code)::text = 'NDS'::text) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 0
32. 0.489 0.489 ↑ 1.0 1 489

Index Scan using team_name_pkey on team_name n_4 (cost=0.28..8.30 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=489)

  • Index Cond: (team_name_id = h_4.team_name_id)
33. 1.467 4.890 ↑ 1.0 1 489

Nested Loop (cost=0.70..17.07 rows=1 width=12) (actual time=0.009..0.010 rows=1 loops=489)

34. 2.445 2.445 ↑ 1.0 1 489

Index Scan using team_name_language_history_pkey on team_name_language_history h_5 (cost=0.41..8.76 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND ((name_role_code)::text = 'NDF'::text) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 1
35. 0.978 0.978 ↑ 1.0 1 489

Index Scan using team_name_pkey on team_name n_5 (cost=0.28..8.30 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=489)

  • Index Cond: (team_name_id = h_5.team_name_id)
36. 1.467 3.912 ↑ 1.0 1 489

Nested Loop (cost=0.70..17.07 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=489)

37. 1.956 1.956 ↑ 1.0 1 489

Index Scan using team_name_language_history_pkey on team_name_language_history h_6 (cost=0.41..8.76 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND ((name_role_code)::text = 'NDL'::text) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 0
38. 0.489 0.489 ↑ 1.0 1 489

Index Scan using team_name_pkey on team_name n_6 (cost=0.28..8.30 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=489)

  • Index Cond: (team_name_id = h_6.team_name_id)
39. 1.467 1.467 ↑ 1.0 1 489

Index Scan using team_history_pkey on team_history th_1_1 (cost=0.28..8.30 rows=1 width=2) (actual time=0.002..0.003 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 1
40. 0.978 0.978 ↑ 1.0 1 489

Index Scan using team_history_pkey on team_history th_1_2 (cost=0.28..8.30 rows=1 width=2) (actual time=0.002..0.002 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 1
41. 0.978 0.978 ↑ 1.0 1 489

Index Scan using team_history_pkey on team_history th_1_3 (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 1
42. 4.149 129.096 ↓ 0.0 0 489

Nested Loop (cost=0.27..92.15 rows=1 width=4) (actual time=0.252..0.264 rows=0 loops=489)

43. 121.761 121.761 ↓ 2.0 2 489

Seq Scan on organization_history dh (cost=0.00..83.69 rows=1 width=4) (actual time=0.084..0.249 rows=2 loops=489)

  • Filter: ((organization_path @> oh.organization_path) AND (os.season >= start_season) AND (os.season <= end_season))
  • Rows Removed by Filter: 1,466
44. 3.186 3.186 ↓ 0.0 0 1,062

Index Scan using organization_pkey on organization d (cost=0.27..8.29 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1,062)

  • Index Cond: (organization_id = dh.organization_id)
  • Filter: ((organization_type_code)::bpchar = 'D'::bpchar)
  • Rows Removed by Filter: 1
45. 1.299 111.003 ↑ 1.0 1 489

Nested Loop (cost=0.27..92.11 rows=1 width=4) (actual time=0.170..0.227 rows=1 loops=489)

46. 107.580 107.580 ↓ 2.0 2 489

Seq Scan on organization_history dh_1 (cost=0.00..83.69 rows=1 width=4) (actual time=0.068..0.220 rows=2 loops=489)

  • Filter: ((organization_path @> oh.organization_path) AND (os.season >= start_season) AND (os.season <= end_season))
  • Rows Removed by Filter: 1,466
47. 2.124 2.124 ↓ 0.0 0 1,062

Index Scan using organization_pkey on organization d_1 (cost=0.27..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,062)

  • Index Cond: (organization_id = dh_1.organization_id)
  • Filter: ((organization_type_code)::bpchar = 'L'::bpchar)
  • Rows Removed by Filter: 1
48. 0.489 26.406 ↓ 0.0 0 489

Nested Loop (cost=0.28..72.44 rows=1 width=4) (actual time=0.054..0.054 rows=0 loops=489)

49. 14.181 14.181 ↑ 1.0 8 489

Seq Scan on organization d_2 (cost=0.00..10.40 rows=8 width=4) (actual time=0.019..0.029 rows=8 loops=489)

  • Filter: ((organization_type_code)::bpchar = 'C'::bpchar)
  • Rows Removed by Filter: 264
50. 11.736 11.736 ↓ 0.0 0 3,912

Index Scan using idx_organization_history_organization_id on organization_history dh_2 (cost=0.28..7.75 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=3,912)

  • Index Cond: (organization_id = d_2.organization_id)
  • Filter: ((organization_path @> oh.organization_path) AND (os.season >= start_season) AND (os.season <= end_season))
  • Rows Removed by Filter: 5
51. 0.978 45.477 ↑ 1.0 1 489

Limit (cost=0.27..92.39 rows=1 width=4) (actual time=0.092..0.093 rows=1 loops=489)

52. 1.187 44.499 ↑ 1.0 1 489

Nested Loop (cost=0.27..92.39 rows=1 width=4) (actual time=0.091..0.091 rows=1 loops=489)

53. 42.054 42.054 ↑ 1.0 1 489

Seq Scan on organization_history dh_3 (cost=0.00..83.69 rows=1 width=4) (actual time=0.068..0.086 rows=1 loops=489)

  • Filter: ((organization_path @> oh.organization_path) AND (os.season >= start_season) AND (os.season <= end_season))
  • Rows Removed by Filter: 530
54. 1.258 1.258 ↑ 1.0 1 629

Index Scan using organization_pkey on organization d_3 (cost=0.27..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=629)

  • Index Cond: (organization_id = dh_3.organization_id)
  • Filter: ((organization_id <> 5150) AND ((organization_type_code)::bpchar = 'S'::bpchar))
  • Rows Removed by Filter: 0
55. 1.467 1.467 ↑ 1.0 1 489

Index Scan using team_history_pkey on team_history th_1_4 (cost=0.28..8.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 1
56. 2.934 3.912 ↑ 1.0 1 489

Aggregate (cost=4.34..4.35 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=489)

57. 0.978 0.978 ↑ 1.0 2 489

Index Only Scan using team_history_pkey on team_history x (cost=0.28..4.32 rows=2 width=13) (actual time=0.002..0.002 rows=2 loops=489)

  • Index Cond: (team_id = t.team_id)
  • Heap Fetches: 0
58. 2.445 3.423 ↑ 1.0 1 489

Index Scan using venue_history_dwo_pkey on venue_history_dwo g (cost=8.59..16.61 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=489)

  • Index Cond: (($46 = venue_id) AND (os.season = season))
59.          

Initplan (for Index Scan)

60. 0.978 0.978 ↑ 1.0 1 489

Index Scan using team_history_pkey on team_history th_1_5 (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 1
61. 0.000 0.978 ↑ 1.0 1 489

Index Scan using venue_history_dwo_pkey on venue_history_dwo g_1 (cost=8.59..16.61 rows=1 width=2) (actual time=0.002..0.002 rows=1 loops=489)

  • Index Cond: (($49 = venue_id) AND (os.season = season))
62.          

Initplan (for Index Scan)

63. 0.978 0.978 ↑ 1.0 1 489

Index Scan using team_history_pkey on team_history th_1_6 (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 1
64. 0.000 0.978 ↑ 1.0 1 489

Index Scan using venue_history_dwo_pkey on venue_history_dwo g_2 (cost=8.59..16.61 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=489)

  • Index Cond: (($52 = venue_id) AND (os.season = season))
65.          

Initplan (for Index Scan)

66. 0.978 0.978 ↑ 1.0 1 489

Index Scan using team_history_pkey on team_history th_1_7 (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=489)

  • Index Cond: ((team_id = t.team_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
  • Rows Removed by Filter: 1
67. 0.489 2.445 ↓ 0.0 0 489

Nested Loop (cost=0.56..16.62 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=489)

68. 1.956 1.956 ↓ 0.0 0 489

Index Scan using team_organization_history_pkey on team_organization_history toh_1 (cost=0.28..8.31 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=489)

  • Index Cond: ((team_id = t.team_id) AND (os.season >= start_season))
  • Filter: ((organization_id = ANY ('{114,115}'::integer[])) AND (os.season <= end_season))
  • Rows Removed by Filter: 2
69. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_history_pkey on organization_history oh_1 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((organization_id = toh_1.organization_id) AND (os.season >= start_season))
  • Filter: (os.season <= end_season)
Planning time : 15.604 ms
Execution time : 18,605.671 ms