|
42,479 min
17%
|
11 ms
|
222,362,044
db_user
|
select to_jsonb(l.*) || jsonb_build_object($3, coalesce(tsl_l.custom_name, tsl_l.name, l.name)) as league,
to_jsonb(s.*) as season,
to_jsonb(t.*) || jsonb_build_object($4, coalesce(tsl_t.custom_name, tsl_t.name, t.name)) as team,
to_jsonb(c.*) as coach,
tc.color as team_color,
tc.secondary_color as team_secondary_color,
stt.position as position,
to_jsonb(coun.*) || jsonb_build_object($5, coalesce(tsl_coun.custom_name, tsl_coun.name, coun.name)) as country
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $6
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
left join standings as sta on sta.season_id = ls.season_id
left join standings_stages as sts on sts.standings_id = sta.id
left join standings_teams as stt on stt.standings_stage_id = sts.external_id and stt.team_id = t.external_id
left join countries as coun on coun.external_id = t.country_id
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $7 and tsl_l.language = $1::preferred_language
left join the_sports_languages as tsl_t on tsl_t.external_id = t.external_id and tsl_t.type = $8 and tsl_t.language = $1::preferred_language
left join the_sports_languages as tsl_coun on tsl_coun.external_id = coun.external_id and tsl_coun.type = $9 and tsl_coun.language = $1::preferred_language
where t.external_id = $2
order by case when l.type = $10 then $11 else $12 end desc, s.end_time desc
limit $13
|
|
32,827 min
13%
|
4,396 ms
|
448,015
db_user
|
select pairs.player_id as player_id,
pairs.team_id as team_id,
pairs.transfer_time as transfer_time,
sub.year as year,
sub.formatted_year as formatted_year
from unnest($1::text[], $2::text[], $3::timestamptz[]) as pairs(player_id, team_id, transfer_time)
left join lateral (select s.year as year,
case
when s.year ~ $4 then (s.year::int - $5)::text || $6 || s.year
when s.year ~ $7 then split_part(s.year, $8, $9)
else s.year
end as formatted_year
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
left join season_teams as st on st.league_season_id = ls.id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $10
where st.team_id = pairs.team_id
and s.start_time - interval $11 < pairs.transfer_time
group by s.year
order by s.year desc nulls last
limit $12) sub on $13
where sub.year is not null
|
|
25,425 min
10%
|
3 ms
|
589,015,564
db_user
|
select to_jsonb(l.*) as league,
to_jsonb(s.*) as season,
to_jsonb(t.*) as team,
to_jsonb(c.*) as coach,
tc.color as team_color,
tc.secondary_color as team_secondary_color,
stt.position as position,
to_jsonb(coun.*) as country
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
left join standings as sta on sta.season_id = ls.season_id
left join standings_stages as sts on sts.standings_id = sta.id
left join standings_teams as stt on stt.standings_stage_id = sts.external_id and stt.team_id = t.external_id
left join countries as coun on coun.external_id = t.country_id
where t.external_id = $1
order by case when l.type = $2 then $3 else $4 end desc, s.end_time desc
limit $5
|
|
24,605 min
10%
|
5 ms
|
286,258,227
db_user
|
select to_jsonb(l.*) || jsonb_build_object($3, coalesce(tsl_l.custom_name, tsl_l.name, l.name)) as league,
to_jsonb(s.*) as season,
to_jsonb(t.*) || jsonb_build_object($4, coalesce(tsl_t.custom_name, tsl_t.name, t.name), $5, coalesce(t.custom_logo, t.logo)) as team,
to_jsonb(c.*) as coach,
tc.color as team_color,
tc.secondary_color as team_secondary_color,
stt.position as position,
to_jsonb(coun.*) || jsonb_build_object($6, coalesce(tsl_coun.custom_name, tsl_coun.name, coun.name)) as country
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $7
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
left join standings as sta on sta.season_id = ls.season_id
left join standings_stages as sts on sts.standings_id = sta.id
left join standings_teams as stt on stt.standings_stage_id = sts.external_id and stt.team_id = t.external_id
left join countries as coun on coun.external_id = t.country_id
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $8 and tsl_l.language = $1::preferred_language
left join the_sports_languages as tsl_t on tsl_t.external_id = t.external_id and tsl_t.type = $9 and tsl_t.language = $1::preferred_language
left join the_sports_languages as tsl_coun on tsl_coun.external_id = coun.external_id and tsl_coun.type = $10 and tsl_coun.language = $1::preferred_language
where t.external_id = $2
order by case when l.type = $11 then $12 else $13 end desc, s.end_time desc
limit $14
|
|
23,180 min
9%
|
7 ms
|
187,116,245
db_user
|
select st.*
from season_teams as st
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join teams as t on t.external_id = st.team_id
where st.team_id = $1
and (s.year = $2 or s.year = $3
or (t.is_national_team and s.year in (
case when $2 ~ $4 then ($2::int - $5)::text || $6 || $2 end,
case when $3 ~ $7 then ($3::int - $8)::text || $9 || $3 end)))
|
|
19,125 min
8%
|
4 ms
|
323,814,998
db_user
|
select st.*
from season_teams as st
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
where st.team_id = $1
and (s.year = $2 or s.year = $3)
|
|
15,276 min
6%
|
143 ms
|
6,431,217
db_user
|
select *
from players
where ($1::text[] is null or external_id = any ($1::text[]))
and (
($2 = $6 or (uid is not null)) or
($3 = $7 or
((height is null or height = $8) and
(weight is null or weight = $9) and
(market_value is null or market_value = $10)))
)
order by searchable_name
limit $4 offset $5 * $4
|
|
6,544 min
3%
|
1 ms
|
407,187,924
db_user
|
with lineup as (select ml.id as lineup_id, m.*
from matches as m
join match_lineups as ml on ml.match_id = m.external_id
where m.external_id = $1
limit $4),
goal_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $5
or description = $6),
yellow_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $7),
red_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $8
or description = $9)
select (select count(distinct imi.*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from goal_type)])
and im.match_time < (select match_time from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $10) as goals,
(select count(distinct imi.*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where (coalesce(imi.assist1_id, imi.assist2_id) = $3)
and imi.type = any (array [(select ids from goal_type)])
and im.match_time < (select match_time from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $11) as assists,
(select count(distinct imi.*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from yellow_card_type)])
and im.match_time < (select match_time from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $12) as yellow_cards,
(select count(distinct imi.*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from red_card_type)])
and im.match_time < (select match_time from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $13) as red_cards
|
|
4,096 min
2%
|
294 ms
|
835,915
db_user
|
select pairs.player_id as player_id,
pairs.transfer_time as transfer_time,
pairs.team_id as team_id,
pairs.year as year,
pairs.formatted_year as formatted_year,
coalesce(sub.seasons, $6::jsonb) as seasons
from unnest($1::text[], $2::timestamptz[], $3::text[], $4::text[], $5::text[]) as pairs(player_id, transfer_time, team_id, year, formatted_year)
left join lateral (select jsonb_agg(jsonb_build_object($7, st.id, $8, s.year, $9, l.name)) as seasons
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $10
left join season_teams as st on st.league_season_id = ls.id
where st.team_id = pairs.team_id
and (s.year = pairs.year or s.year = pairs.formatted_year)
and s.end_time >= pairs.transfer_time
group by st.team_id) sub on $11
|
|
3,404 min
1%
|
67 ms
|
3,063,399
db_user
|
with incident as (select mi.*,
case when ts.description in ($3, $4) then mi.home_score when coalesce(m.home_over_time_score, $5) != $6 then m.home_over_time_score else m.home_score end as home_score,
case when ts.description in ($7, $8) then mi.away_score when coalesce(m.away_over_time_score, $9) != $10 then m.away_over_time_score else m.away_score end as away_score,
ml.match_id,
mi.team_type,
coalesce(mi.assist2_id, mi.assist1_id, mi.out_player_id) as secondary_player_id,
case when mi.team_type != $11 then m.home_team_id else m.away_team_id end as team_id,
case when mi.team_type != $12 then m.away_team_id else m.home_team_id end as secondary_team_id,
ts.description,
m.match_time,
m.is_overtime,
m.round_num,
m.season_id,
case
when ts.description = $13 and mi.time::bigint = $14 then $15
when ts.description = $16 and mi.time::bigint = $17 then $18
when ts.description = $19 and mi.time::bigint = $20 then $21
else mi.time::bigint
end - mi.addtime::bigint as adjusted_time
from match_incidents as mi
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id
join technical_statistics as ts on ts.id = mi.type
where m.id = $1
and mi.deleted = $22),
related_incident as (select mi.*,
r.incident_id as parent_incident_id,
ml.match_id,
coalesce(mi.assist2_id, mi.assist1_id, mi.out_player_id) as secondary_player_id,
case when mi.team_type != $23 then m.home_team_id else m.away_team_id end as team_id,
case when mi.team_type != $24 then m.away_team_id else m.home_team_id end as secondary_team_id,
ts.description,
m.match_time,
m.is_overtime
from related_incidents as r
join match_incidents as mi on mi.id = r.related_incident_id
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id
join technical_statistics as ts on ts.id = mi.type
where r.incident_id in (select id from incident)),
goal_types as (select array_agg(id) as ids
from technical_statistics
where description in ($25, $26)),
red_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($27, $28)),
yellow_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($29)),
player_statistics as (select mi.player_id,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from goal_types)])) as total_goals,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from red_card_types)])) as total_red_cards,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from yellow_card_types)])) as total_yellow_cards
from incident as p_i
join match_incidents as mi on mi.player_id = p_i.player_id and mi.deleted = $30
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
group by mi.player_id),
player_assists as (select coalesce(mi.assist1_id, mi.assist2_id) as player_id,
count(distinct mi.*) as assists
from incident as p_i
join match_incidents as mi on coalesce(mi.assist1_id, mi.assist2_id) = p_i.player_id and mi.deleted = $31
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
where mi.type = any (array [(select ids from goal_types)])
group by coalesce(mi.assist1_id, mi.assist2_id))
select i.is_overtime as is_overtime,
i.match_time as match_time,
i.time as time,
i.addtime as addtime,
i.description as description,
to_jsonb(i.*) as incident,
case when p.id is not null then to_jsonb(p.*) || jsonb_build_object($32, coalesce((select tsl_p.custom_name from the_sports_languages as tsl_p where tsl_p.external_id = p.external_id and tsl_p.type = $33 and tsl_p.language = $2::preferred_language), p.name)) else to_jsonb(c.*) end as player,
to_jsonb(sp.*) || jsonb_build_object($34, coalesce((select tsl_sp.custom_name from the_sports_languages as tsl_sp where tsl_sp.external_id = sp.external_id and tsl_sp.type = $35 and tsl_sp.language = $2::preferred_language), sp.name)) as secondary_player,
to_jsonb(t.*) || jsonb_build_object($36, coalesce(tsl_t.custom_name, tsl_t.name, t.name), $37, coalesce(t.custom_logo, t.logo)) as team,
to_jsonb(st.*) || jsonb_build_object($38, coalesce(tsl_st.custom_name, tsl_st.name, st.name), $39, coalesce(st.custom_logo, st.logo)) as secondary_team,
(select jsonb_build_object(
$40, coalesce(l2, ml2),
$41, coalesce(s2, ms2),
$42, to_jsonb(t2.*) || jsonb_build_object($43, coalesce(tsl_t2.custom_name, tsl_t2.name, t2.name), $44, coalesce(t2.custom_logo, t2.logo)),
$45, c2.*,
$46, tc.color,
$47, tc.secondary_color
)
from teams as t2
left join season_teams as st2 on st2.team_id = t2.external_id
left join league_seasons as ls2 on ls2.id = st2.league_season_id
left join seasons as s2 on s2.external_id = ls2.season_id
left join seasons as ms2 on ms2.external_id = m.season_id
left join leagues as l2 on l2.external_id = ls2.league_id and l2.deleted = $48
left join leagues as ml2 on ml2.external_id = m.league_id and ml2.deleted = $49
left join coaches as c2 on c2.external_id = t2.coach_id
left join team_colors as tc on tc.team_id = t2.external_id
left join the_sports_languages as tsl_t2 on tsl_t2.external_id = t2.external_id and tsl_t2.type = $50 and tsl_t2.language = $2::preferred_language
where t2.external_id = i.team_id
limit $51) as team_details,
(select jsonb_build_object(
$52, coalesce(l3, ml3),
$53, coalesce(s3, ms3),
$54, to_jsonb(t3.*) || jsonb_build_object($55, coalesce(tsl_t3.custom_name, tsl_t3.name, t3.name), $56, coalesce(t3.custom_logo, t3.logo)),
$57, c3.*,
$58, tc2.color,
$59, tc2.secondary_color
)
from teams as t3
left join season_teams as st3 on st3.team_id = t3.external_id
left join league_seasons as ls3 on ls3.id = st3.league_season_id
left join seasons as s3 on s3.external_id = ls3.season_id
left join seasons as ms3 on ms3.external_id = m.season_id
left join leagues as l3 on l3.external_id = ls3.league_id
|
|
3,234 min
1%
|
2 ms
|
91,068,682
db_user
|
select to_jsonb(l.*) || jsonb_build_object($3, coalesce(tsl_l.name, l.name)) as league,
to_jsonb(s.*) as season,
to_jsonb(t.*) || jsonb_build_object($4, coalesce(tsl_t.name, t.name)) as team,
to_jsonb(c.*) as coach,
tc.color as team_color,
tc.secondary_color as team_secondary_color,
stt.position as position,
to_jsonb(coun.*) || jsonb_build_object($5, coalesce(tsl_coun.name, coun.name)) as country
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
left join standings as sta on sta.season_id = ls.season_id
left join standings_stages as sts on sts.standings_id = sta.id
left join standings_teams as stt on stt.standings_stage_id = sts.external_id and stt.team_id = t.external_id
left join countries as coun on coun.external_id = t.country_id
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $6 and tsl_l.language = $1::preferred_language
left join the_sports_languages as tsl_t on tsl_t.external_id = t.external_id and tsl_t.type = $7 and tsl_t.language = $1::preferred_language
left join the_sports_languages as tsl_coun on tsl_coun.external_id = coun.external_id and tsl_coun.type = $8 and tsl_coun.language = $1::preferred_language
where t.external_id = $2
order by case when l.type = $9 then $10 else $11 end desc, s.end_time desc
limit $12
|
|
2,960 min
1%
|
375 ms
|
473,806
db_user
|
delete
from matches
where external_id = any ($1::text[])
Covered by index on (external_id)
Rows: 2066978
Row progression: 2066978, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- away_team_id, home_team_id
- details
- external_id UNIQUE
- home_team_id, match_time
- league_id
- match_time
- match_time WHERE (status_id > 1) AND (status_id < 8)
- referee_id, status_id, season_id WHERE referee_id IS NOT NULL
- round_num, match_time, external_id
- season_id, match_time
- season_id, match_time DESC
- season_id) INCLUDE (external_id, match_time, home_team_id, away_team_id
- stage_id
- status_id
|
|
2,098 min
0.9%
|
131 ms
|
957,498
db_user
|
select pairs.player_id as player_id,
pairs.team_id as team_id,
pairs.transfer_time as transfer_time,
sub.year as year,
sub.formatted_year as formatted_year
from unnest($1::text[], $2::text[], $3::timestamptz[]) as pairs(player_id, team_id, transfer_time)
left join lateral (select s.year as year,
case
when s.year ~ $4 then (s.year::int - $5)::text || $6 || s.year
when s.year ~ $7 then split_part(s.year, $8, $9)
else s.year
end as formatted_year
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
left join season_teams as st on st.league_season_id = ls.id
left join leagues as l on l.external_id = ls.league_id
where st.team_id = pairs.team_id
and s.start_time - interval $10 < pairs.transfer_time
group by s.year
order by s.year desc nulls last
limit $11) sub on $12
where sub.year is not null
|
|
1,984 min
0.8%
|
1 ms
|
134,614,391
db_user
|
select st.id as id,
s.end_time as end_date,
s.start_time as start_date,
(select ins.start_time
from league_seasons as ils
left join leagues as il on il.external_id = ils.league_id
left join seasons as ins on ins.external_id = ils.season_id
where il.external_id = l.external_id
and ins.end_time < s.start_time
order by ins.end_time desc
limit $4) as previous_start_date,
(select ins.end_time
from league_seasons as ils
left join leagues as il on il.external_id = ils.league_id
left join seasons as ins on ins.external_id = ils.season_id
where il.external_id = l.external_id
and ins.end_time <= s.start_time
order by ins.end_time desc
limit $5) as previous_end_date
from season_teams as st
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
where st.team_id = $1
and l.type = $6
and s.end_time >= $2
and (
($3 is null or s.end_time <= $3) or
($3 is not null and
$3::timestamptz - $2::timestamptz < interval $7 and
s.end_time <= $3::timestamptz + interval $8)
)
order by s.end_time
|
|
1,925 min
0.8%
|
1,265 ms
|
91,302
db_user
|
select pairs.player_id as player_id,
pairs.transfer_time as transfer_time,
pairs.team_id as team_id,
pairs.year as year,
pairs.formatted_year as formatted_year,
coalesce(sub.seasons, $6::jsonb) as seasons
from unnest($1::text[], $2::timestamptz[], $3::text[], $4::text[], $5::text[]) as pairs(player_id, transfer_time, team_id, year, formatted_year)
left join lateral (select jsonb_agg(jsonb_build_object($7, st.id, $8, s.year, $9, l.name)) as seasons
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $10
left join season_teams as st on st.league_season_id = ls.id
where st.team_id = pairs.team_id
and s.end_time >= pairs.transfer_time
and s.year != pairs.year
and s.year != pairs.formatted_year
group by st.team_id) sub on $11
|
|
1,791 min
0.7%
|
2 ms
|
49,403,070
db_user
|
select to_jsonb(l.*) || jsonb_build_object($3, coalesce(tsl_l.name, l.name)) as league,
to_jsonb(s.*) as season,
to_jsonb(t.*) || jsonb_build_object($4, coalesce(tsl_t.name, t.name)) as team,
to_jsonb(c.*) as coach,
tc.color as team_color,
tc.secondary_color as team_secondary_color,
stt.position as position,
to_jsonb(coun.*) || jsonb_build_object($5, coalesce(tsl_coun.name, coun.name)) as country
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $6
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
left join standings as sta on sta.season_id = ls.season_id
left join standings_stages as sts on sts.standings_id = sta.id
left join standings_teams as stt on stt.standings_stage_id = sts.external_id and stt.team_id = t.external_id
left join countries as coun on coun.external_id = t.country_id
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $7 and tsl_l.language = $1::preferred_language
left join the_sports_languages as tsl_t on tsl_t.external_id = t.external_id and tsl_t.type = $8 and tsl_t.language = $1::preferred_language
left join the_sports_languages as tsl_coun on tsl_coun.external_id = coun.external_id and tsl_coun.type = $9 and tsl_coun.language = $1::preferred_language
where t.external_id = $2
order by case when l.type = $10 then $11 else $12 end desc, s.end_time desc
limit $13
|
|
1,243 min
0.5%
|
1,540 ms
|
48,451
db_user
|
DELETE FROM ONLY "public"."match_status_tracker" WHERE $1 OPERATOR(pg_catalog.=) "match_id"
|
|
1,004 min
0.4%
|
127 ms
|
472,831
db_user
|
delete
from stages
where external_id = any ($1::text[])
Covered by index on (external_id)
Rows: 42702
Row progression: 42702, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- season_id
- stage_order
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
995 min
0.4%
|
0 ms
|
247,900,241
db_user
|
SELECT $2 FROM ONLY "public"."matches" x WHERE "external_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
904 min
0.4%
|
0 ms
|
489,625,029
db_user
|
INSERT INTO team_players (updated_date, player_id, season_team_id, arrival_date, altered) VALUES ($1, $2, $3, $4, $5) RETURNING id
|
|
872 min
0.4%
|
2,733 ms
|
19,142
db_user
|
with distinct_lfps_country_ids as (select array_agg(distinct lfp.user_country_id) as distinct_country_ids
from league_fixture_priorities as lfp),
is_country_in_priorities as (select case
when $1::bigint is null then $7
when $1::bigint = any (array [(select distinct_country_ids from distinct_lfps_country_ids)])
then $8
else $9
end as is_in),
distinct_lfsp_country_ids as (select array_agg(distinct lfsp.user_country_id) as distinct_country_ids
from league_fixture_secondary_priorities as lfsp),
is_country_in_secondary_priorities as (select case
when $1::bigint is null then $10
when $1::bigint = any (array [(select distinct_country_ids from distinct_lfsp_country_ids)])
then $11
else $12
end as is_in),
current_statusses as (select array_agg(ms.id) as ids from match_statuses as ms where ms.id > $13 and ms.id < $14)
select lfp.priorty as league_fixture_priority,
lfsp.priorty as league_fixture_secondary_priority,
coalesce(tsl_c.name, c.name) as country_name,
c.logo as country_logo,
coalesce(tsl_cat.name, cat.name) as category_name,
l.id, l.created_date, l.updated_date, l.external_updated_at, l.external_id, l.category_id,
l.country_id, l.cur_round, l.round_count, coalesce(tsl_l.name, l.name) as name, l.short_name,
l.searchable_name, l.type, l.logo, l.primary_color, l.secondary_color,
jsonb_agg(
jsonb_build_object($15, to_jsonb(m.*) ||
jsonb_build_object($16, r.name,
$17, r.logo,
$18, case
when s.mode != $19 then $20
when (select count(*)
from matches as im
where im.stage_id = s.external_id
and least(im.home_team_id, im.away_team_id) = least(m.home_team_id, m.away_team_id)
and greatest(im.home_team_id, im.away_team_id) = greatest(m.home_team_id, m.away_team_id)) = $21 then $22
else exists(select $23
from matches as im
where im.stage_id = s.external_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time)
end
),
$24, to_jsonb(s.*),
$25, to_jsonb(l.*) || jsonb_build_object($26, coalesce(tsl_l.name, l.name)),
$27, to_jsonb(ht.*) || jsonb_build_object($28, coalesce(tsl_ht.name, ht.name)),
$29, to_jsonb(at.*) || jsonb_build_object($30, coalesce(tsl_at.name, at.name)),
$31, jsonb_build_object($32, to_jsonb(l.*) || jsonb_build_object($33, coalesce(tsl_l.name, l.name)),
$34, se.*,
$35, to_jsonb(ht.*) || jsonb_build_object($36, coalesce(tsl_ht.name, ht.name)),
$37, hc.*,
$38, htc.color,
$39, htc.secondary_color),
$40, jsonb_build_object($41, to_jsonb(l.*) || jsonb_build_object($42, coalesce(tsl_l.name, l.name)),
$43, se.*,
$44, to_jsonb(at.*) || jsonb_build_object($45, coalesce(tsl_at.name, at.name)),
$46, ac.*,
$47, atc.color,
$48, atc.secondary_color)
) order by m.match_time
) as matches
from matches as m
left join seasons as se on se.external_id = m.season_id
left join referees as r on r.external_id = m.referee_id
left join stages as s on s.external_id = m.stage_id
left join leagues as l on l.external_id = m.league_id and l.deleted = $49
left join league_fixture_priorities as lfp on lfp.league_id = l.external_id and
case
when (select is_in from is_country_in_priorities) then lfp.user_country_id = $1::bigint
else lfp.user_country_id is null end
left join league_fixture_secondary_priorities as lfsp on lfsp.league_id = l.external_id and
case
when (select is_in from is_country_in_secondary_priorities) then lfsp.user_country_id = $1::bigint
else lfsp.user_country_id is null end
left join countries as c on c.external_id = l.country_id
left join categories as cat on cat.external_id = l.category_id
left join teams as ht on ht.external_id = m.home_team_id
left join teams as at on at.external_id = m.away_team_id
left join team_colors as htc on htc.team_id = ht.external_id
left join team_colors as atc on atc.team_id = at.external_id
left join match_lineups as ml on ml.match_id = m.external_id
left join coaches as hc on hc.external_id = ml.home_coach_id
left join coaches as ac on ac.external_id = ml.away_coach_id
left join the_sports_languages as tsl_c on tsl_c.external_id = c.external_id and tsl_c.type = $50 and tsl_c.language = $2::preferred_language
left join the_sports_languages as tsl_cat on tsl_cat.external_id = cat.external_id and tsl_cat.type = $51 and tsl_cat.language = $2::preferred_language
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $52 and tsl_l.language = $2::preferred_language
left join the_sports_languages as tsl_ht on tsl_ht.external_id = ht.external_id and tsl_ht.type = $53 and tsl_ht.language = $2::preferred_language
left join the_sports_languages as tsl_at on tsl_at.external_id = at.external_id and tsl_at.type = $54 and tsl_at.language = $2::preferred_language
where (m.status_id = any (array [(select ids from current_statusses)]) and $3 - m.match_time <= interval $55 and $3 > m.match_time)
group by l.id, lfp.priorty, lfsp.priorty, c.id, c.name, cat.name, l.name, tsl_c.name, tsl_cat.name, tsl_l.name
order by case when l.id = any (array [$4::bigint[]]) then $56 else $57 end, lfp.priorty nulls last, coalesce(c.name, cat.name, l.name), lfsp.priorty nulls last
limit case when $5::bigint is null then $58 else $5::bigint end offset case when $5::bigint is null or $6::bigint is null then $59 else $6::bigint * $5::bigint end
|
|
787 min
0.3%
|
93 ms
|
506,930
db_user
|
with recently_ended as (select distinct on (mst.match_id) mst.match_id,
mst.changed_at
from match_status_tracker as mst
where mst.new_status_id = $4
and mst.changed_at <= now() - $1::interval
and mst.changed_at > now() - $2::interval
order by mst.match_id, mst.changed_at desc),
mvps as (select distinct on (m.id) mps.id as mps_id,
mp.id as mp_id,
m.id as match_id,
re.changed_at as changed_at
from recently_ended as re
join matches as m on m.id = re.match_id and m.status_id = $5
join match_lineups as ml on ml.match_id = m.external_id
join match_players as mp on mp.match_lineup = ml.id
join match_player_statistics as mps on mps.match_player_id = mp.id and mps.rating > $6
left join notified_mvps as nm on nm.match_id = m.external_id
where nm is null
order by m.id, mps.rating desc)
select v.match_id as id,
extract($7 from (now() - v.changed_at))::bigint as time_diff,
jsonb_build_object(
$8, mp.id,
$9, to_jsonb(mps.*),
$10, to_jsonb(p.*) || jsonb_build_object($11, p.name),
$12, to_jsonb(ht.*) || jsonb_build_object($13, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name)),
$14, to_jsonb(at.*) || jsonb_build_object($15, coalesce(tsl_at.custom_name, tsl_at.name, at.name)),
$16, m.home_score,
$17, m.away_score,
$18, coalesce(htc.color, $19),
$20, coalesce(htc.secondary_color, $21),
$22, coalesce(atc.color, $23),
$24, coalesce(atc.secondary_color, $25),
$26, m.match_time,
$27, mp.team_type,
$28, m.round_num,
$29, m.id,
$30, m.external_id,
$31, l.type,
$32, coalesce(st.custom_names ->> $3::text, st.name),
$33, st.mode,
$34, exists(select $35
from matches as im
where im.stage_id = m.stage_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time)
) as rating
from mvps as v
join match_player_statistics as mps on mps.id = v.mps_id
join match_players as mp on mp.id = v.mp_id
join match_lineups as ml on ml.id = mp.match_lineup
join matches as m on m.external_id = ml.match_id
left join leagues as l on l.external_id = m.league_id and l.deleted = $36
left join players as p on p.external_id = mp.player_id
left join teams as ht on ht.external_id = m.home_team_id
left join team_colors as htc on htc.team_id = ht.external_id
left join teams as at on at.external_id = m.away_team_id
left join team_colors as atc on atc.team_id = at.external_id
left join stages as st on st.external_id = m.stage_id
left join the_sports_languages as tsl_ht on tsl_ht.external_id = ht.external_id and tsl_ht.type = $37 and tsl_ht.language = $3::preferred_language
left join the_sports_languages as tsl_at on tsl_at.external_id = at.external_id and tsl_at.type = $38 and tsl_at.language = $3::preferred_language
order by v.changed_at desc, v.match_id
|
|
772 min
0.3%
|
0 ms
|
396,664,481
db_user
|
INSERT INTO team_players (updated_date, player_id, season_team_id, arrival_date, leaving_date, altered) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id
|
|
740 min
0.3%
|
0 ms
|
2,194,093,087
db_user
|
UPDATE match_statistics SET updated_date = $1, match_id = $2, type = $3, home = $4, away = $5 WHERE match_statistics.id = $6
Covered by index on (id)
Rows: 7497042
Row progression: 7497042, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- match_id, type UNIQUE
|
|
719 min
0.3%
|
27 ms
|
1,584,394
db_user
|
with incident as (select mi.*,
case when coalesce(m.home_over_time_score, $2) != $3 then m.home_over_time_score else m.home_score end as home_score,
case when coalesce(m.away_over_time_score, $4) != $5 then m.away_over_time_score else m.away_score end as away_score,
ml.match_id,
mi.team_type,
coalesce(mi.assist2_id, mi.assist1_id, mi.out_player_id) as secondary_player_id,
case when mi.team_type != $6 then m.home_team_id else m.away_team_id end as team_id,
case when mi.team_type != $7 then m.away_team_id else m.home_team_id end as secondary_team_id,
ts.description,
m.match_time,
m.is_overtime,
m.round_num,
m.season_id,
case
when ts.description = $8 and mi.time::bigint = $9 then $10
when ts.description = $11 and mi.time::bigint = $12 then $13
when ts.description = $14 and mi.time::bigint = $15 then $16
else mi.time::bigint
end - mi.addtime::bigint as adjusted_time
from match_incidents as mi
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id
join technical_statistics as ts on ts.id = mi.type
where m.id = $1
and mi.deleted = $17),
goal_types as (select array_agg(id) as ids
from technical_statistics
where description in ($18, $19)),
red_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($20, $21)),
yellow_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($22)),
player_statistics as (select mi.player_id,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from goal_types)])) as total_goals,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from red_card_types)])) as total_red_cards,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from yellow_card_types)])) as total_yellow_cards
from incident as p_i
join match_incidents as mi on mi.player_id = p_i.player_id and mi.deleted = $23
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
group by mi.player_id),
player_assists as (select coalesce(mi.assist1_id, mi.assist2_id) as player_id,
count(distinct mi.*) as assists
from incident as p_i
join match_incidents as mi on coalesce(mi.assist1_id, mi.assist2_id) = p_i.player_id and mi.deleted = $24
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
where mi.type = any (array [(select ids from goal_types)])
group by coalesce(mi.assist1_id, mi.assist2_id))
select i.is_overtime as is_overtime,
i.match_time as match_time,
i.time as time,
i.addtime as addtime,
i.description as description,
to_jsonb(i.*) as incident,
case when p.id is not null then to_jsonb(p.*) else to_jsonb(c.*) end as player,
to_jsonb(sp.*) as secondary_player,
to_jsonb(t.*) as team,
to_jsonb(st.*) as secondary_team,
(select jsonb_build_object(
$25, coalesce(l2, ml2),
$26, coalesce(s2, ms2),
$27, t2.*,
$28, c2.*,
$29, tc.color,
$30, tc.secondary_color
)
from teams as t2
left join season_teams as st2 on st2.team_id = t2.external_id
left join league_seasons as ls2 on ls2.id = st2.league_season_id
left join seasons as s2 on s2.external_id = ls2.season_id
left join seasons as ms2 on ms2.external_id = m.season_id
left join leagues as l2 on l2.external_id = ls2.league_id
left join leagues as ml2 on ml2.external_id = m.league_id
left join coaches as c2 on c2.external_id = t2.coach_id
left join team_colors as tc on tc.team_id = t2.external_id
where t2.external_id = i.team_id
limit $31) as team_details,
(select jsonb_build_object(
$32, coalesce(l3, ml3),
$33, coalesce(s3, ms3),
$34, t3.*,
$35, c3.*,
$36, tc2.color,
$37, tc2.secondary_color
)
from teams as t3
left join season_teams as st3 on st3.team_id = t3.external_id
left join league_seasons as ls3 on ls3.id = st3.league_season_id
left join seasons as s3 on s3.external_id = ls3.season_id
left join seasons as ms3 on ms3.external_id = m.season_id
left join leagues as l3 on l3.external_id = ls3.league_id
left join leagues as ml3 on ml3.external_id = m.league_id
left join coaches as c3 on c3.external_id = t3.coach_id
left join team_colors as tc2 on tc2.team_id = t3.external_id
where t3.external_id = i.secondary_team_id
limit $38) as secondary_team_details,
jsonb_build_object(
$39, coalesce(ps.total_goals, $40),
$41, coalesce(pa.assists, $42),
$43, coalesce(ps.total_yellow_cards, $44),
$45, coalesce(ps.total_red_cards, $46)
) as season_statistic
from incident as i
left join player_statistics as ps on ps.player_id = i.player_id
left join player_assists as pa on pa.player_id = i.player_id
left join players as p on p.external_id = i.player_id
left join players as sp on sp.external_id = i.secondary_player_id
left join teams as t on t.external_id = i.team_id
left join teams as st on st.external_id = i.secondary_team_id
left join coaches as c on c.external_id = i.player_id
left join match_lineups as ml on ml.id = i.match_lineup
left join matches as m on m.external_id = ml.match_id
order by case
when i.description = $47 and i.time::bigint = $48 then $49
when i.description = $50 and i.time::bigint = $51 then $52
when i.description = $53 and i.time::bigint = $54 then $55
when i.description = $56 then $57
when i.description = $58 then $59
else i.time::bigint
end - case
when i.description = $60 then $61
when i.description = $62 then $63
when i.description = $64 then $65
else i.addtime::bigint
end,
case
when i.description = $66 then $67
when i.description = $68 then $69
when i.description = $70 then $71
else i.addtime::bigint
end
|
|
683 min
0.3%
|
0 ms
|
2,201,285,493
db_user
|
SELECT match_statistics.id, match_statistics.created_date, match_statistics.updated_date, match_statistics.match_id, match_statistics.type, match_statistics.home, match_statistics.away FROM match_statistics WHERE match_statistics.match_id = $1 AND (match_statistics.type = $2)
Covered by index on (match_id, type)
Rows: 7497042
Row progression: 7497042, 10
Row estimates
- match_id (=): 10
- type (=): 749704
Existing indexes
- id PRIMARY
- match_id, type UNIQUE
|
|
664 min
0.3%
|
50 ms
|
791,333
db_user
|
with lineup as (select ml.id as lineup_id, m.*
from matches as m
join match_lineups as ml on ml.match_id = m.external_id
where m.external_id = $1
limit $4),
goal_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $5
or description = $6),
yellow_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $7),
red_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $8
or description = $9)
select (select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from goal_type)])
and imi.deleted = $10
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and im.season_id = (select season_id from lineup)) as goals,
(select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where (coalesce(imi.assist2_id, imi.assist1_id) = $3)
and imi.type = any (array [(select ids from goal_type)])
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and imi.deleted = $11
and im.season_id = (select season_id from lineup)) as assists,
(select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from yellow_card_type)])
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and imi.deleted = $12
and im.season_id = (select season_id from lineup)) as yellow_cards,
(select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.deleted = $13
and imi.type = any (array [(select ids from red_card_type)])
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and im.season_id = (select season_id from lineup)) as red_cards
|
|
627 min
0.3%
|
47 ms
|
792,947
db_user
|
select pairs.player_id as player_id,
pairs.team_id as team_id,
pairs.transfer_time as transfer_time,
sub.year as year,
sub.formatted_year as formatted_year
from unnest($1::text[], $2::text[], $3::timestamptz[]) as pairs(player_id, team_id, transfer_time)
left join lateral (select s.year as year,
case
when s.year ~ $4 then (s.year::int - $5)::text || $6 || s.year
when s.year ~ $7 then split_part(s.year, $8, $9)
else s.year
end as formatted_year
from season_teams as st
join league_seasons as ls on ls.id = st.league_season_id
join seasons as s on s.external_id = ls.season_id
where st.team_id = pairs.team_id
and s.start_time - interval $10 < pairs.transfer_time
group by s.year
order by s.year desc nulls last
limit $11) sub on $12
where sub.year is not null
|
|
588 min
0.2%
|
0 ms
|
378,086,032
db_user
|
UPDATE matches SET updated_date = $1, external_updated_at = $2, external_id = $3, is_overtime = $4, reversed = $5, status_id = $6, league_id = $7, season_id = $8, home_team_id = $9, away_team_id = $10, stage_id = $11, venue_id = $12, referee_id = $13, group_num = $14, round_num = $15, match_time = $16, ended_at = $17, neutral = $18, note = $19, home_score = $20, away_score = $21, home_half_time_score = $22, away_half_time_score = $23, home_over_time_score = $24, away_over_time_score = $25, home_penalty_score = $26, away_penalty_score = $27, home_agg_score = $28, away_agg_score = $29, home_red_cards = $30, away_red_cards = $31, home_yellow_cards = $32, away_yellow_cards = $33, home_corners = $34, away_corners = $35, home_position = $36, away_position = $37, related_id = $38, weather = $39, pressure = $40, temperature = $41, wind = $42, humidity = $43, tbd = $44, details = $45, kickoff_time = $46, last_goal_date = $47 WHERE matches.id = $48
Covered by index on (id)
Rows: 2066978
Row progression: 2066978, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- away_team_id, home_team_id
- details
- external_id UNIQUE
- home_team_id, match_time
- league_id
- match_time
- match_time WHERE (status_id > 1) AND (status_id < 8)
- referee_id, status_id, season_id WHERE referee_id IS NOT NULL
- round_num, match_time, external_id
- season_id, match_time
- season_id, match_time DESC
- season_id) INCLUDE (external_id, match_time, home_team_id, away_team_id
- stage_id
- status_id
|
|
564 min
0.2%
|
0 ms
|
92,152,969
db_user
|
UPDATE matches SET updated_date = $1, external_updated_at = $2, external_id = $3, last_socket_update = $4, is_overtime = $5, reversed = $6, status_id = $7, league_id = $8, season_id = $9, home_team_id = $10, away_team_id = $11, stage_id = $12, venue_id = $13, referee_id = $14, group_num = $15, round_num = $16, match_time = $17, ended_at = $18, neutral = $19, note = $20, home_score = $21, away_score = $22, home_half_time_score = $23, away_half_time_score = $24, home_over_time_score = $25, away_over_time_score = $26, home_penalty_score = $27, away_penalty_score = $28, home_agg_score = $29, away_agg_score = $30, home_red_cards = $31, away_red_cards = $32, home_yellow_cards = $33, away_yellow_cards = $34, home_corners = $35, away_corners = $36, home_position = $37, away_position = $38, related_id = $39, weather = $40, pressure = $41, temperature = $42, wind = $43, humidity = $44, tbd = $45, details = $46, kickoff_time = $47, first_half_kickoff_time = $48, second_half_kickoff_time = $49, overtime_kickoff_time = $50, last_goal_date = $51 WHERE matches.id = $52
Covered by index on (id)
Rows: 2066978
Row progression: 2066978, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- away_team_id, home_team_id
- details
- external_id UNIQUE
- home_team_id, match_time
- league_id
- match_time
- match_time WHERE (status_id > 1) AND (status_id < 8)
- referee_id, status_id, season_id WHERE referee_id IS NOT NULL
- round_num, match_time, external_id
- season_id, match_time
- season_id, match_time DESC
- season_id) INCLUDE (external_id, match_time, home_team_id, away_team_id
- stage_id
- status_id
|
|
558 min
0.2%
|
5,505 ms
|
6,086
db_user
|
select m.*,
(select case
when s.mode != $3 then $4
when (select count(*)
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))) = $5
then $6
else
exists((select $7
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.match_time < m.match_time))) end) as knockout_stage
from matches as m
left join stages as s on s.external_id = m.stage_id
order by m.match_time desc, m.id desc
limit $1 offset $1 * $2
|
|
535 min
0.2%
|
0 ms
|
650,894,210
db_user
|
UPDATE match_players SET updated_date = $1, match_lineup = $2, player_id = $3, team_type = $4, first = $5, captain = $6, name = $7, logo = $8, shirt_number = $9, position = $10, x = $11, y = $12, rating = $13 WHERE match_players.id = $14
Covered by index on (id)
Rows: 20276096
Row progression: 20276096, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- match_lineup, player_id UNIQUE
- match_lineup) INCLUDE (id, player_id, team_type
- player_id
|
|
513 min
0.2%
|
206 ms
|
149,629
db_user
|
with valid_ratings as (select mp.player_id,
m.season_id,
count(*) filter (where mps.minutes_played > $9 and mps.rating > $10) as valid_matches,
round(coalesce(sum(mps.rating) filter (where mps.minutes_played > $11 and mps.rating > $12), $13) * $14) as valid_rating_sum
from match_player_statistics mps
join match_players mp on mp.id = mps.match_player_id
join match_lineups ml on ml.id = mp.match_lineup
join matches m on m.external_id = ml.match_id
where ($1::text is null or m.season_id = $1)
group by mp.player_id, m.season_id),
season_league as (select ls.id as league_season_id
from league_seasons as ls
where ls.season_id = $1
limit $15),
teams_data as (select td.player_id,
jsonb_agg(td.team order by td.arrival_date desc) as teams,
jsonb_agg(td.team_colors order by td.arrival_date desc) as team_colors
from (select distinct on (tp.player_id, t.external_id) tp.player_id,
to_jsonb(t.*) || jsonb_build_object($16, coalesce(tsl_t.custom_name, tsl_t.name, t.name), $17, coalesce(t.custom_logo, t.logo)) as team,
to_jsonb(tc.*) as team_colors,
tp.arrival_date
from team_players as tp
left join season_teams as st
on st.id = tp.season_team_id and
st.league_season_id = (select league_season_id from season_league)
join teams as t on t.external_id = st.team_id
left join team_colors as tc on tc.team_id = t.external_id
left join the_sports_languages as tsl_t on tsl_t.external_id = t.external_id and tsl_t.type = $18 and tsl_t.language = $2::preferred_language
order by tp.player_id, t.external_id, tp.arrival_date desc) as td
group by td.player_id),
player_agg as (select sps.player_id as player_id,
sps.season_id as season_id,
coalesce(sum(sps.court), $19) as court,
coalesce(sum(sps.minutes_played), $20) as minutes_played,
coalesce(sum(sps.first), $21) as first,
coalesce(sum(sps.yellow2_red_cards), $22) as yellow2_red_cards,
coalesce(sum(sps.goals), $23) as goals,
coalesce(sum(sps.penalty), $24) as penalty,
coalesce(sum(sps.assists), $25) as assists,
coalesce(sum(sps.freekick_goals), $26) as freekick_goals,
coalesce(sum(sps.shots), $27) as shots,
coalesce(sum(sps.shots_on_target), $28) as shots_on_target,
coalesce(sum(sps.passes), $29) as passes,
coalesce(sum(sps.passes_accuracy), $30) as passes_accuracy,
coalesce(sum(sps.key_passes), $31) as key_passes,
coalesce(sum(sps.crosses), $32) as crosses,
coalesce(sum(sps.crosses_accuracy), $33) as crosses_accuracy,
coalesce(sum(sps.long_balls), $34) as long_balls,
coalesce(sum(sps.long_balls_accuracy), $35) as long_balls_accuracy,
coalesce(sum(sps.dribble), $36) as dribble,
coalesce(sum(sps.dribble_succ), $37) as dribble_succ,
coalesce(sum(sps.tackles), $38) as tackles,
coalesce(sum(sps.interceptions), $39) as interceptions,
coalesce(sum(sps.clearances), $40) as clearances,
coalesce(sum(sps.duels), $41) as duels,
coalesce(sum(sps.duels_won), $42) as duels_won,
coalesce(sum(sps.fouls), $43) as fouls,
coalesce(sum(sps.was_fouled), $44) as was_fouled,
coalesce(sum(sps.yellow_cards), $45) as yellow_cards,
coalesce(sum(sps.red_cards), $46) as red_cards,
coalesce(sum(sps.poss_losts), $47) as poss_losts,
coalesce(sum(sps.hit_woodwork), $48) as hit_woodwork,
coalesce(sum(sps.saves), $49) as saves,
coalesce(sum(sps.punches), $50) as punches,
coalesce(sum(sps.runs_out), $51) as runs_out,
coalesce(sum(sps.runs_out_succ), $52) as runs_out_succ,
coalesce(sum(sps.good_high_claim), $53) as good_high_claim,
coalesce(sum(sps.big_chance_created), $54) as big_chance_created,
coalesce(sum(sps.big_chance_missed), $55) as big_chance_missed,
coalesce(sum(sps.rating), $56) as rating
from season_player_statistics as sps
where sps.minutes_played > $57
and sps.court > $58
and ($1::text is null or sps.season_id = $1)
group by sps.player_id, sps.season_id),
multiplier as (select $59 as mul),
divider as (select case when coalesce($3::boolean, $60) = $61 then $62 end as div),
player_stats as (select pa.court as court,
pa.minutes_played as minutes_played,
pa.player_id as player_id,
coalesce(pa.first, $63)::float as first,
coalesce(pa.yellow2_red_cards, $64)::float as yellow2_red_cards,
(pa.goals::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as goals,
(coalesce(pa.penalty, $65)::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as penalty,
(pa.assists::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as assists,
((pa.goals::float + pa.assists::float) / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as goals_assists,
pa.minutes_played::float / greatest(pa.goals::float, $66) as goal_frequency,
(coalesce(pa.freekick_goals, $67)::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as freekick_goals,
(pa.shots::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as shots,
(pa.shots_on_target::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as shots_on_target,
case when pa.shots::float > $68 then (pa.goals::float / pa.shots::float) * $69
else $70 end as goal_conversion,
(pa.passes::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as pas
|
|
508 min
0.2%
|
409 ms
|
74,474
db_user
|
with matched as (select jsonb_build_object(
$3, to_jsonb(m.*) ||
jsonb_build_object(
$4, case when coalesce(m.home_over_time_score, $5) != $6 then m.home_over_time_score else m.home_score end,
$7, case when coalesce(m.away_over_time_score, $8) != $9 then m.away_over_time_score else m.away_score end,
$10, r.name,
$11, r.logo,
$12, (select case
when s.mode != $13 then $14
when (select count(*)
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))) = $15
then $16
else
exists((select $17
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.match_time < m.match_time))) end)),
$18, to_jsonb(s.*) || jsonb_build_object($19, coalesce(s.custom_names ->> $1::text, s.name)),
$20, to_jsonb(l.*) || jsonb_build_object($21, coalesce(tsl_l.custom_name, tsl_l.name, l.name)),
$22, to_jsonb(ht.*) || jsonb_build_object($23, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name), $24, coalesce(ht.custom_logo, ht.logo)),
$25, to_jsonb(at.*) || jsonb_build_object($26, coalesce(tsl_at.custom_name, tsl_at.name, at.name), $27, coalesce(at.custom_logo, at.logo)),
$28, jsonb_build_object(
$29, to_jsonb(l.*) || jsonb_build_object($30, coalesce(tsl_l.custom_name, tsl_l.name, l.name)),
$31, season.*,
$32, to_jsonb(ht.*) || jsonb_build_object($33, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name), $34, coalesce(ht.custom_logo, ht.logo)),
$35, htc.*,
$36, htcolor.color,
$37, htcolor.secondary_color),
$38, jsonb_build_object(
$39, to_jsonb(l.*) || jsonb_build_object($40, coalesce(tsl_l.custom_name, tsl_l.name, l.name)),
$41, season.*,
$42, to_jsonb(at.*) || jsonb_build_object($43, coalesce(tsl_at.custom_name, tsl_at.name, at.name), $44, coalesce(at.custom_logo, at.logo)),
$45, atc.*,
$46, atcolor.color,
$47, atcolor.secondary_color)
) as match,
least(m.home_team_id, m.away_team_id) as t1,
greatest(m.home_team_id, m.away_team_id) as t2
from matches m
left join stages s on s.external_id = m.stage_id
left join leagues as l on l.external_id = m.league_id and l.deleted = $48
left join seasons as season on season.external_id = m.season_id
left join referees as r on r.external_id = m.referee_id
left join teams as ht on ht.external_id = m.home_team_id
left join coaches as htc on htc.external_id = ht.coach_id
left join team_colors as htcolor on htcolor.team_id = ht.external_id
left join teams as at on at.external_id = m.away_team_id
left join coaches as atc on atc.external_id = at.coach_id
left join team_colors as atcolor on atcolor.team_id = at.external_id
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $49 and tsl_l.language = $1::preferred_language
left join the_sports_languages as tsl_ht on tsl_ht.external_id = ht.external_id and tsl_ht.type = $50 and tsl_ht.language = $1::preferred_language
left join the_sports_languages as tsl_at on tsl_at.external_id = at.external_id and tsl_at.type = $51 and tsl_at.language = $1::preferred_language
where m.stage_id = $2),
ordered as (select t1,
t2,
row_number() over (partition by t1, t2 order by (match -> $52 ->> $53)::timestamptz) as rn,
to_jsonb(matched.match) as match
from matched)
select (select match from ordered as o2 where o2.t1 = o1.t1 and o2.t2 = o1.t2 and rn = $54) as first_match,
(select match from ordered as o2 where o2.t1 = o1.t1 and o2.t2 = o1.t2 and rn = $55) as second_match
from ordered as o1
group by t1, t2
|
|
501 min
0.2%
|
14 ms
|
2,198,683
db_user
|
with paged_players as (select distinct t.player_id
from transfers as t
where ($1::text[] is null or t.player_id = any (array [$1::text[]]))
order by t.player_id desc
limit $2 offset $2 * $3)
select pp.player_id,
jsonb_agg(jsonb_build_object($4, t.player_id,
$5, t.to_team_id,
$6, t.transfer_time)
order by t.transfer_time) as transfers
from paged_players as pp
left join transfers as t on t.player_id = pp.player_id
group by pp.player_id
|
|
443 min
0.2%
|
3 ms
|
9,088,746
db_user
|
INSERT INTO match_trends (updated_date, match_id, half_number, minute, trend) VALUES ($1, $2, $3, $4, $5) RETURNING id
|
|
425 min
0.2%
|
24 ms
|
1,073,425
db_user
|
with incident as (select mi.*,
ml.match_id,
mi.team_type,
coalesce(mi.assist2_id, mi.assist1_id, mi.out_player_id) as secondary_player_id,
case when mi.team_type != $2 then m.home_team_id else m.away_team_id end as team_id,
case when mi.team_type != $3 then m.away_team_id else m.home_team_id end as secondary_team_id,
ts.description,
m.match_time,
m.is_overtime,
m.round_num,
m.season_id,
case
when ts.description = $4 and mi.time::bigint = $5 then $6
when ts.description = $7 and mi.time::bigint = $8 then $9
when ts.description = $10 and mi.time::bigint = $11 then $12
else mi.time::bigint
end - mi.addtime::bigint as adjusted_time
from match_incidents as mi
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id
join technical_statistics as ts on ts.id = mi.type
where m.id = $1
and mi.deleted = $13),
goal_types as (select array_agg(id) as ids
from technical_statistics
where description in ($14, $15)),
red_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($16, $17)),
yellow_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($18)),
player_statistics as (select mi.player_id,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from goal_types)])) as total_goals,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from red_card_types)])) as total_red_cards,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from yellow_card_types)])) as total_yellow_cards
from incident as p_i
join match_incidents as mi on mi.player_id = p_i.player_id and mi.deleted = $19
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
group by mi.player_id),
player_assists as (select coalesce(mi.assist1_id, mi.assist2_id) as player_id,
count(distinct mi.*) as assists
from incident as p_i
join match_incidents as mi on coalesce(mi.assist1_id, mi.assist2_id) = p_i.player_id and mi.deleted = $20
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
where mi.type = any (array [(select ids from goal_types)])
group by coalesce(mi.assist1_id, mi.assist2_id))
select i.is_overtime as is_overtime,
i.match_time as match_time,
i.time as time,
i.addtime as addtime,
i.description as description,
to_jsonb(i.*) as incident,
case when p.id is not null then to_jsonb(p.*) else to_jsonb(c.*) end as player,
to_jsonb(sp.*) as secondary_player,
to_jsonb(t.*) as team,
to_jsonb(st.*) as secondary_team,
(select jsonb_build_object(
$21, l2.*,
$22, s2.*,
$23, t2.*,
$24, c2.*,
$25, tc.color,
$26, tc.secondary_color
)
from teams as t2
left join season_teams as st2 on st2.team_id = t2.external_id
left join league_seasons as ls2 on ls2.id = st2.league_season_id
left join seasons as s2 on s2.external_id = ls2.season_id
left join leagues as l2 on l2.external_id = ls2.league_id
left join coaches as c2 on c2.external_id = t2.coach_id
left join team_colors as tc on tc.team_id = t2.external_id
where t2.external_id = i.team_id
limit $27) as team_details,
(select jsonb_build_object(
$28, l3.*,
$29, s3.*,
$30, t3.*,
$31, c3.*,
$32, tc2.color,
$33, tc2.secondary_color
)
from teams as t3
left join season_teams as st3 on st3.team_id = t3.external_id
left join league_seasons as ls3 on ls3.id = st3.league_season_id
left join seasons as s3 on s3.external_id = ls3.season_id
left join leagues as l3 on l3.external_id = ls3.league_id
left join coaches as c3 on c3.external_id = t3.coach_id
left join team_colors as tc2 on tc2.team_id = t3.external_id
where t3.external_id = i.secondary_team_id
limit $34) as secondary_team_details,
jsonb_build_object(
$35, coalesce(ps.total_goals, $36),
$37, coalesce(pa.assists, $38),
$39, coalesce(ps.total_yellow_cards, $40),
$41, coalesce(ps.total_red_cards, $42)
) as season_statistic
from incident as i
left join player_statistics as ps on ps.player_id = i.player_id
left join player_assists as pa on pa.player_id = i.player_id
left join players as p on p.external_id = i.player_id
left join players as sp on sp.external_id = i.secondary_player_id
left join teams as t on t.external_id = i.team_id
left join teams as st on st.external_id = i.secondary_team_id
left join coaches as c on c.external_id = i.player_id
order by case
when i.description = $43 and i.time::bigint = $44 then $45
when i.description = $46 and i.time::bigint = $47 then $48
when i.description = $49 and i.time::bigint = $50 then $51
when i.description = $52 then $53
when i.description = $54 then $55
else i.time::bigint
end - case
when i.description = $56 then $57
when i.description = $58 then $59
when i.description = $60 then $61
else i.addtime::bigint
end,
case
when i.description = $62 then $63
when i.description = $64 then $65
when i.description = $66 then $67
else i.addtime::bigint
end
|
|
419 min
0.2%
|
0 ms
|
633,047,173
db_user
|
select m.*,
r.name as referee_name,
r.logo as referee_logo,
(select case
when s.mode != $2 then $3
when (select count(*)
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))) = $4
then $5
else
exists((select $6
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.match_time < m.match_time))) end) as knockout_stage
from matches as m
left join stages as s on s.external_id = m.stage_id
left join referees as r on r.external_id = m.referee_id
where m.external_id = $1
limit $7
|
|
405 min
0.2%
|
366 ms
|
66,324
db_user
|
with filtered_seasons as (select distinct to_jsonb(s.*) as season,
to_jsonb(l.*) as league,
s.start_time,
l.type
from seasons as s
join league_seasons as ls on ls.season_id = s.external_id
join season_teams as st on st.league_season_id = ls.id
join team_players as tp on tp.season_team_id = st.id
join leagues as l on l.external_id = ls.league_id and l.deleted = $5
where ($1::text is null or ls.league_id = $1::text)
and ($2::text is null or st.team_id = $2::text)
and ($3::text is null or tp.player_id = $3::text)
and s.start_time <= $4),
first_season as (select to_jsonb(s.*) as season,
to_jsonb(l.*) as league,
s.start_time,
l.type
from seasons as s
join league_seasons as ls on ls.season_id = s.external_id
join season_teams as st on st.league_season_id = ls.id
join team_players as tp on tp.season_team_id = st.id
join leagues as l on l.external_id = ls.league_id and l.deleted = $6
where ($1::text is null or ls.league_id = $1::text)
and ($2::text is null or st.team_id = $2::text)
and ($3::text is null or tp.player_id = $3::text)
order by case when l.type = $7 then $8 else $9 end desc, s.start_time
limit $10),
selected_season as (select *
from filtered_seasons as fs
order by case when fs.type = $11 then $12 else $13 end desc, fs.start_time desc
limit $14),
custom_season as (select to_jsonb(s.*) as season,
to_jsonb(l.*) as league,
s.start_time,
l.type
from custom_league_season as cls
join seasons as s on s.external_id = cls.season_id
join leagues as l on l.external_id = cls.league_id and l.deleted = $15
where $1::text is not null
and cls.league_id = $1::text
limit $16)
select *
from custom_season
union all
select *
from selected_season
where not exists (select $17 from custom_season)
union all
select *
from first_season
where not exists (select $18 from custom_season)
and not exists (select $19 from selected_season)
|
|
387 min
0.2%
|
0 ms
|
91,391,318
db_user
|
INSERT INTO team_players (updated_date, player_id, season_team_id, custom_arrival_date, altered) VALUES ($1, $2, $3, $4, $5) RETURNING id
|
|
382 min
0.2%
|
25 ms
|
924,272
db_user
|
select pairs.player_id as player_id,
pairs.transfer_time as transfer_time,
pairs.team_id as team_id,
pairs.year as year,
pairs.formatted_year as formatted_year,
coalesce(sub.seasons, $6::jsonb) as seasons
from unnest($1::text[], $2::timestamptz[], $3::text[], $4::text[], $5::text[]) as pairs(player_id, transfer_time, team_id, year, formatted_year)
left join lateral (select jsonb_agg(jsonb_build_object($7, st.id, $8, s.year, $9, l.name)) as seasons
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
left join leagues as l on l.external_id = ls.league_id
left join season_teams as st on st.league_season_id = ls.id
where st.team_id = pairs.team_id
and (s.year = pairs.year or s.year = pairs.formatted_year)
and s.end_time >= pairs.transfer_time
group by st.team_id) sub on $10
|
|
355 min
0.1%
|
0 ms
|
1,355,560,039
db_user
|
UPDATE transfers SET updated_date = $1, external_updated_at = $2, player_id = $3, type = $4, transfer_time = $5, from_team_id = $6, from_team_name = $7, to_team_id = $8, to_team_name = $9, fee = $10, description = $11, deleted = $12 WHERE transfers.id = $13
Covered by index on (id)
Rows: 2583248
Row progression: 2583248, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- deleted, transfer_time
- from_team_id, deleted, transfer_time DESC
- player_id
- to_team_id, deleted, transfer_time DESC
- transfer_time
|
|
343 min
0.1%
|
0 ms
|
612,652,463
db_user
|
UPDATE match_incidents SET updated_date = $1, deleted = $2, hash = $3, match_lineup = $4, team_type = $5, type = $6, reason_type = $7, time = $8, addtime = $9, belong = $10, var_reason = $11, var_result = $12, home_score = $13, away_score = $14, player_id = $15, player_name = $16, assist1_id = $17, assist1_name = $18, assist2_id = $19, assist2_name = $20, in_player_id = $21, in_player_name = $22, out_player_id = $23, out_player_name = $24 WHERE match_incidents.id = $25
Covered by index on (id)
Rows: 10830382
Row progression: 10830382, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- assist1_id, match_lineup WHERE deleted = false
- assist2_id, match_lineup WHERE deleted = false
- deleted
- match_lineup
- player_id, match_lineup WHERE deleted = false
|
|
334 min
0.1%
|
0 ms
|
769,589,458
db_user
|
UPDATE league_seasons SET updated_date = $1, league_id = $2, season_id = $3, is_current = $4 WHERE league_seasons.id = $5
Covered by index on (id)
Rows: 11170
Row progression: 11170, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- league_id, season_id UNIQUE
- league_id WHERE is_current = true
- season_id UNIQUE
|
|
320 min
0.1%
|
0 ms
|
684,136,286
db_user
|
UPDATE match_trends SET updated_date = $1, match_id = $2, half_number = $3, minute = $4, trend = $5 WHERE match_trends.id = $6
Covered by index on (id)
Rows: 14684365
Row progression: 14684365, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- match_id, half_number, minute UNIQUE
|
|
320 min
0.1%
|
0 ms
|
767,310,602
db_user
|
select m.*,
r.name as referee_name,
r.logo as referee_logo,
case
when s.mode = $2 then (select count(*) = $3
from matches as im
left join teams as iht on iht.external_id = im.home_team_id
left join teams as iat on iat.external_id = im.away_team_id
where im.stage_id = s.external_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time
limit $4)
else $5 end as knockout_stage
from matches as m
left join stages as s on s.external_id = m.stage_id
left join referees as r on r.external_id = m.referee_id
where m.external_id = $1
limit $6
|
|
315 min
0.1%
|
2 ms
|
7,669,414
db_user
|
delete from standings_teams
where standings_stage_id = $1
and team_id not in ($2, $3, $4, $5)
Details
CREATE INDEX CONCURRENTLY ON standings_teams (standings_stage_id)
Rows: 174313
Row progression: 174313, 9
Row estimates
- standings_stage_id (=): 9
- team_id (<>): 174299
Existing indexes
- id PRIMARY
- team_id, standings_stage_id UNIQUE
|
|
302 min
0.1%
|
83 ms
|
219,196
db_user
|
with current_statusses as (select array_agg(ms.id) as ids from match_statuses as ms where ms.id > $10 and ms.id < $11)
select to_jsonb(m.*) || jsonb_build_object($12, r.name,
$13, r.logo,
$14, (select case
when s.mode != $15 then $16
when (select count(*)
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))) = $17
then $18
else
exists((select $19
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.match_time < m.match_time))) end)) as match,
to_jsonb(s.*) || jsonb_build_object($20, coalesce(s.custom_names ->> $1::text, s.name)) as stage,
to_jsonb(l.*) || jsonb_build_object($21, coalesce(tsl_l.custom_name, tsl_l.name, l.name)) as league,
to_jsonb(ht.*) || jsonb_build_object($22, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name)) as home_team,
to_jsonb(at.*) || jsonb_build_object($23, coalesce(tsl_at.custom_name, tsl_at.name, at.name)) as away_team,
(select jsonb_build_object(
$24, to_jsonb(l.*) || jsonb_build_object($25, coalesce(tsl_hl.custom_name, tsl_hl.name, l.name)),
$26, s.*,
$27, to_jsonb(t.*) || jsonb_build_object($28, coalesce(tsl_htd.custom_name, tsl_htd.name, t.name)),
$29, c.*,
$30, tc.color,
$31, tc.secondary_color
)
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
join leagues as l on l.external_id = ls.league_id and l.deleted = $32
left join coaches as c on c.external_id = coalesce(ml.home_coach_id, t.coach_id)
left join team_colors as tc on tc.team_id = t.external_id
left join the_sports_languages as tsl_hl on tsl_hl.external_id = l.external_id and tsl_hl.type = $33 and tsl_hl.language = $1::preferred_language
left join the_sports_languages as tsl_htd on tsl_htd.external_id = t.external_id and tsl_htd.type = $34 and tsl_htd.language = $1::preferred_language
where t.id = ht.id
limit $35) as home_team_details,
(select jsonb_build_object(
$36, to_jsonb(l.*) || jsonb_build_object($37, coalesce(tsl_al.custom_name, tsl_al.name, l.name)),
$38, s.*,
$39, to_jsonb(t.*) || jsonb_build_object($40, coalesce(tsl_atd.custom_name, tsl_atd.name, t.name)),
$41, c.*,
$42, tc.color,
$43, tc.secondary_color
)
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
join leagues as l on l.external_id = ls.league_id and l.deleted = $44
left join coaches as c on c.external_id = coalesce(ml.away_coach_id, t.coach_id)
left join team_colors as tc on tc.team_id = t.external_id
left join the_sports_languages as tsl_al on tsl_al.external_id = l.external_id and tsl_al.type = $45 and tsl_al.language = $1::preferred_language
left join the_sports_languages as tsl_atd on tsl_atd.external_id = t.external_id and tsl_atd.type = $46 and tsl_atd.language = $1::preferred_language
where t.id = at.id
limit $47) as away_team_details
from matches as m
left join match_lineups as ml on ml.match_id = m.external_id
join leagues as l on l.external_id = m.league_id and l.deleted = $48
left join stages as s on s.external_id = m.stage_id
left join referees as r on r.external_id = m.referee_id
left join teams as ht on ht.external_id = m.home_team_id
left join teams as at on at.external_id = m.away_team_id
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $49 and tsl_l.language = $1::preferred_language
left join the_sports_languages as tsl_ht on tsl_ht.external_id = ht.external_id and tsl_ht.type = $50 and tsl_ht.language = $1::preferred_language
left join the_sports_languages as tsl_at on tsl_at.external_id = at.external_id and tsl_at.type = $51 and tsl_at.language = $1::preferred_language
where ($2::bigint is null or l.id = $2::bigint)
and ($3::text is null or ht.external_id = $3 or at.external_id = $3)
and (
($4::timestamptz is null or (m.match_time >= $4::timestamptz and m.match_time < $4::timestamptz + interval $52)) or
($5 = $53 and m.status_id = any (array [(select ids from current_statusses)]))
)
and ($6::timestamptz is null or m.match_time >= $6::timestamptz)
and ($7::timestamptz is null or m.match_time < $7::timestamptz)
order by m.match_time
limit coalesce($8::bigint, $54) offset coalesce($8::bigint, $55) * coalesce($9::bigint, $56)
|
|
263 min
0.1%
|
41 ms
|
384,374
db_user
|
with season_ids as (select array_agg(s.external_id order by s.end_time desc) as ids
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
where ls.league_id = any (array [$1::text[]])
and s.year = any (array [$2::text[]]))
select to_jsonb(m.*) || jsonb_build_object(
$7, r.name,
$8, r.logo,
$9, (select case
when s.mode != $10 then $11
when (select count(*)
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))) = $12
then $13
else
exists((select $14
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.match_time < m.match_time))) end)) as match,
to_jsonb(s.*) || jsonb_build_object($15, coalesce(s.custom_names ->> $3::text, s.name)) as stage,
to_jsonb(l.*) || jsonb_build_object($16, coalesce(tsl_l.custom_name, tsl_l.name, l.name)) as league,
to_jsonb(ht.*) || jsonb_build_object($17, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name), $18, coalesce(ht.custom_logo, ht.logo)) as home_team,
to_jsonb(at.*) || jsonb_build_object($19, coalesce(tsl_at.custom_name, tsl_at.name, at.name), $20, coalesce(at.custom_logo, at.logo)) as away_team,
jsonb_build_object(
$21, to_jsonb(l.*) || jsonb_build_object($22, coalesce(tsl_l.custom_name, tsl_l.name, l.name)),
$23, season.*,
$24, to_jsonb(ht.*) || jsonb_build_object($25, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name), $26, coalesce(ht.custom_logo, ht.logo)),
$27, htc.*,
$28, htcolor.color,
$29, htcolor.secondary_color) as home_team_details,
jsonb_build_object(
$30, to_jsonb(l.*) || jsonb_build_object($31, coalesce(tsl_l.custom_name, tsl_l.name, l.name)),
$32, season.*,
$33, to_jsonb(at.*) || jsonb_build_object($34, coalesce(tsl_at.custom_name, tsl_at.name, at.name), $35, coalesce(at.custom_logo, at.logo)),
$36, atc.*,
$37, atcolor.color,
$38, atcolor.secondary_color) as away_team_details
from matches as m
left join match_lineups as ml on ml.match_id = m.external_id
left join leagues as l on l.external_id = m.league_id and l.deleted = $39
left join stages as s on s.external_id = m.stage_id
left join seasons as season on season.external_id = m.season_id
left join referees as r on r.external_id = m.referee_id
left join teams as ht on ht.external_id = m.home_team_id
left join coaches as htc on htc.external_id = coalesce(ml.home_coach_id, ht.coach_id)
left join team_colors as htcolor on htcolor.team_id = ht.external_id
left join teams as at on at.external_id = m.away_team_id
left join coaches as atc on atc.external_id = coalesce(ml.away_coach_id, at.coach_id)
left join team_colors as atcolor on atcolor.team_id = at.external_id
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $40 and tsl_l.language = $3::preferred_language
left join the_sports_languages as tsl_ht on tsl_ht.external_id = ht.external_id and tsl_ht.type = $41 and tsl_ht.language = $3::preferred_language
left join the_sports_languages as tsl_at on tsl_at.external_id = at.external_id and tsl_at.type = $42 and tsl_at.language = $3::preferred_language
where m.season_id = any (array [(select ids from season_ids)::text[]])
and ($4::text is null or ht.external_id = $4::text or at.external_id = $4::text)
order by case when array_length($1::text[], $43) > $44 then m.match_time end,
s.stage_order,
m.round_num,
m.match_time,
m.external_id
limit case when $5::bigint is not null then $5::bigint end offset case when $5::bigint is not null and $6::bigint is not null then $5::bigint * $6::bigint end
|
|
259 min
0.1%
|
1 ms
|
12,041,222
db_user
|
select m.*
from matches as m
where m.id = any($1::bigint[])
Covered by index on (id)
Rows: 2066978
Row progression: 2066978, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- away_team_id, home_team_id
- details
- external_id UNIQUE
- home_team_id, match_time
- league_id
- match_time
- match_time WHERE (status_id > 1) AND (status_id < 8)
- referee_id, status_id, season_id WHERE referee_id IS NOT NULL
- round_num, match_time, external_id
- season_id, match_time
- season_id, match_time DESC
- season_id) INCLUDE (external_id, match_time, home_team_id, away_team_id
- stage_id
- status_id
|
|
259 min
0.1%
|
0 ms
|
1,237,905,429
db_user
|
UPDATE stages SET updated_date = $1, external_updated_at = $2, external_id = $3, season_id = $4, name = $5, searchable_name = $6, mode = $7, group_count = $8, round_count = $9, stage_order = $10 WHERE stages.id = $11
Covered by index on (id)
Rows: 42702
Row progression: 42702, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- season_id
- stage_order
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
247 min
0.1%
|
37 ms
|
404,957
db_user
|
select pairs.player_id as player_id,
pairs.transfer_time as transfer_time,
pairs.team_id as team_id,
pairs.year as year,
pairs.formatted_year as formatted_year,
coalesce(sub.seasons, $7::jsonb) as seasons
from unnest($1::text[], $2::timestamptz[], $3::text[], $4::text[], $5::text[]) as pairs(player_id, transfer_time, team_id, year, formatted_year)
left join lateral (select jsonb_agg(jsonb_build_object($8, st.id, $9, s.year, $10, coalesce((select coalesce(tsl.custom_name, tsl.name) from the_sports_languages as tsl where tsl.external_id = l.external_id and tsl.type = $11 and tsl.language = $6::preferred_language), l.name))) as seasons
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $12
left join season_teams as st on st.league_season_id = ls.id
where st.team_id = pairs.team_id
and (s.year = pairs.year or s.year = pairs.formatted_year)
and s.end_time >= pairs.transfer_time
group by st.team_id) sub on $13
|
|
246 min
< 0.1%
|
0 ms
|
760,432,289
db_user
|
select *
from match_trends
where match_id = $1
and half_number = $2
and minute = $3
limit $4
Details
CREATE INDEX CONCURRENTLY ON match_trends (match_id, minute)
Rows: 14684365
Row progression: 14684365, 99, 1
Row estimates
- match_id (=): 99
- minute (=): 195792
- half_number (=): 7342183
Existing indexes
- id PRIMARY
- match_id, half_number, minute UNIQUE
|
|
238 min
< 0.1%
|
187 ms
|
76,173
db_user
|
with knockout_matches as (select m.external_id
from matches as m
where m.season_id = $1
and exists(select $9
from matches as im
where im.stage_id = m.stage_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time)),
base as (select mp.id as match_player_id,
case when mps.id is null then $10 else to_jsonb(mps.*) end as stats,
m.home_score as home_score,
m.away_score as away_score,
mps.rating as rating,
m.id as match_id,
m.external_id as match_external_id,
l.type as league_type,
mp.team_type as team_type,
m.round_num,
coalesce(s.custom_names ->> $2::text, s.name) as stage_name,
mp.player_id as player_id,
s.mode as stage_mode,
s.stage_order,
m.match_time,
m.home_team_id,
m.away_team_id,
m.stage_id,
(m.external_id in (select external_id from knockout_matches)) as is_knockout
from match_players as mp
left join match_player_statistics as mps on mps.match_player_id = mp.id
left join match_lineups as ml on ml.id = mp.match_lineup
left join matches as m on m.external_id = ml.match_id
left join leagues as l on l.external_id = m.league_id and l.deleted = $11
left join stages as s on s.external_id = m.stage_id
where m.season_id = $1
and mp.team_type is not null
and m.status_id in (select ms.id
from match_statuses as ms
where ms.description not in
($12, $13, $14, $15, $16))
and ($3 is null or mp.player_id = $3)
and ($4 is null or (select case when mp.team_type = $17 then m.home_team_id else m.away_team_id end) = $4)
order by mps.rating desc nulls last, m.match_time desc),
grouped as (select jsonb_build_object(
$18, stats
) as data,
base.*,
case
when league_type <> $19 and round_num is not null and round_num <> $20
then case
when stage_order > $21
then stage_name || $22 || round_num
else $23 || round_num end
when league_type <> $24
and (round_num is null or round_num = $25)
and stage_name is not null
and stage_mode = $26
then stage_name
when league_type = $27
and (round_num is null or round_num = $28)
and stage_name is not null
and stage_mode = $29
then case
when exists(select $30
from matches as im
where im.stage_id = base.stage_id
and im.home_team_id = base.away_team_id
and im.away_team_id = base.home_team_id)
then stage_name || $31 ||
case when is_knockout then $32 else $33 end
else stage_name
end
when league_type = $34
and (round_num is null or round_num = $35)
and stage_name is not null
and stage_mode = $36
then stage_name
when league_type = $37
and round_num is not null
and round_num <> $38
and stage_name is not null
and stage_mode = $39
then stage_name || $40 || round_num
else $41
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by rating desc nulls last, match_time desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.data || jsonb_build_object(
$42, ranked.match_player_id,
$43, p.*,
$44, ht.*,
$45, at.*,
$46, home_score,
$47, away_score,
$48, htc.color,
$49, atc.color,
$50, htc.secondary_color,
$51, atc.secondary_color,
$52, match_time,
$53, team_type,
$54, round_num,
$55, match_id,
$56, match_external_id,
$57, league_type,
$58, stage_name,
$59, stage_mode,
$60, is_knockout) order by ranked.rating desc nulls last, ranked.match_time desc) filter (where rn <= $5) as data,
count(ranked.*) filter (where rn <= $5) as inner_total,
count(ranked.*) as total
from ranked
left join players as p on p.external_id = player_id
left join teams as ht on ht.external_id = home_team_id
left join teams as at on at.external_id = away_team_id
left join team_colors as htc on htc.team_id = ht.external_id
left join team_colors as atc on atc.team_id = at.external_id
left join the_sports_languages as tsl_ht on tsl_ht.external_id = ht.external_id and tsl_ht.type = $61 and tsl_ht.language = $2::preferred_language
left join the_sports_languages as tsl_at on tsl_at.external_id = at.external_id and tsl_at.type = $62 and tsl_at.language = $2::preferred_language
group by ranked.group_name, ranked.stage_order, ranked.is_knockout, ranked.round_num
order by ranked.stage_order desc, case when ranked.is_knockout then $63 else $64 end desc, ranked.round_num desc),
target_group as (select stage_order,
case when is_knockout then $65 else $66 end as ko_order,
round_num
from grouped
where match_time <= $6::timestamptz + interval $67
order by stage_order desc,
case when is_knockout then 1 else 0 end desc,
round_num desc
limit $68)
select *
from data
where inner_total > $69
and ($6::timestamptz is null or
(stage_order, case when is_knockout then $70 else $71 end, round_num) <= (select t.stage_order, t.ko_order, t.round_num from target_group t))
order by stage_order desc, case when is_knockout then $72 else $73 end desc, round_num desc
limit
|
|
233 min
< 0.1%
|
2 ms
|
7,062,761
db_user
|
INSERT INTO match_statistics (updated_date, match_id, type, home, away) VALUES ($1, $2, $3, $4, $5) RETURNING id
|
|
228 min
< 0.1%
|
0 ms
|
269,368,779
db_user
|
insert into match_status_tracker(match_id, old_status_id, new_status_id, changed_at)
values (new.id, old.status_id, new.status_id, now())
|
|
219 min
< 0.1%
|
0 ms
|
1,231,389,719
db_user
|
SELECT $2 FROM ONLY "public"."players" x WHERE "external_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
215 min
< 0.1%
|
62 ms
|
208,089
db_user
|
with recently_ended as (select distinct on (mst.match_id) mst.match_id,
mst.changed_at
from match_status_tracker as mst
where mst.new_status_id = $4
and mst.changed_at <= now() - $1::interval
and mst.changed_at > now() - $2::interval
order by mst.match_id, mst.changed_at desc),
mvps as (select distinct on (m.id) mps.id as mps_id,
mp.id as mp_id,
m.id as match_id,
re.changed_at as changed_at
from recently_ended as re
join matches as m on m.id = re.match_id and m.status_id = $5
join match_lineups as ml on ml.match_id = m.external_id
join match_players as mp on mp.match_lineup = ml.id
join match_player_statistics as mps on mps.match_player_id = mp.id and mps.rating > $6
left join notified_mvps as nm on nm.match_id = m.external_id
where nm is null
order by m.id, mps.rating desc)
select v.match_id as id,
extract($7 from (now() - v.changed_at))::bigint as time_diff,
jsonb_build_object(
$8, mp.id,
$9, to_jsonb(mps.*),
$10, to_jsonb(p.*) || jsonb_build_object($11, p.name),
$12, to_jsonb(ht.*) || jsonb_build_object($13, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name)),
$14, to_jsonb(at.*) || jsonb_build_object($15, coalesce(tsl_at.custom_name, tsl_at.name, at.name)),
$16, m.home_score,
$17, m.away_score,
$18, coalesce(htc.color, $19),
$20, coalesce(htc.secondary_color, $21),
$22, coalesce(atc.color, $23),
$24, coalesce(atc.secondary_color, $25),
$26, m.match_time,
$27, mp.team_type,
$28, m.round_num,
$29, m.id,
$30, m.external_id,
$31, l.type,
$32, coalesce(st.custom_names ->> $3::text, st.name),
$33, st.mode,
$34, exists(select $35
from matches as im
where im.stage_id = m.stage_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time)
) as rating
from mvps as v
join match_player_statistics as mps on mps.id = v.mps_id
join match_players as mp on mp.id = v.mp_id
join match_lineups as ml on ml.id = mp.match_lineup
join matches as m on m.external_id = ml.match_id
join stages as st on st.external_id = m.stage_id
left join leagues as l on l.external_id = m.league_id and l.deleted = $36
left join players as p on p.external_id = mp.player_id
left join teams as ht on ht.external_id = m.home_team_id
left join team_colors as htc on htc.team_id = ht.external_id
left join teams as at on at.external_id = m.away_team_id
left join team_colors as atc on atc.team_id = at.external_id
left join the_sports_languages as tsl_ht on tsl_ht.external_id = ht.external_id and tsl_ht.type = $37 and tsl_ht.language = $3::preferred_language
left join the_sports_languages as tsl_at on tsl_at.external_id = at.external_id and tsl_at.type = $38 and tsl_at.language = $3::preferred_language
order by v.changed_at desc, v.match_id
|
|
214 min
< 0.1%
|
0 ms
|
717,413,428
db_user
|
select *
from teams
where external_id = $1
limit $2
Covered by index on (external_id)
Rows: 95309
Row progression: 95309, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- coach_id
- external_id UNIQUE
- name
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
213 min
< 0.1%
|
10,201 ms
|
1,253
db_user
|
SELECT DISTINCT ON (p.external_id)
p.external_id, p.name, p.market_value, m.id AS match_pk, m.match_time, mps.rating,
mp.team_type, ht.name AS home_name, at.name AS away_name
FROM matches m
JOIN match_lineups ml ON ml.match_id = m.external_id
JOIN match_players mp ON mp.match_lineup = ml.id
JOIN match_player_statistics mps ON mps.match_player_id = mp.id
JOIN players p ON p.external_id = mp.player_id
LEFT JOIN teams ht ON ht.external_id = m.home_team_id
LEFT JOIN teams at ON at.external_id = m.away_team_id
WHERE m.match_time > NOW() - ($1 || $4)::interval
AND mps.rating >= $5 AND m.match_time IS NOT NULL
AND COALESCE(p.market_value, $6) >= $2
ORDER BY p.external_id, m.match_time DESC
LIMIT $3
|
|
209 min
< 0.1%
|
0 ms
|
545,196,550
db_user
|
select *
from players
where external_id = $1
limit $2
Covered by index on (external_id)
Rows: 1270387
Row progression: 1270387, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- market_value DESC WHERE (market_value IS NOT NULL) AND (market_value > 0)
- market_value DESC WHERE uid IS NULL
- searchable_name
- searchable_name gin_trgm_ops) WITH (fastupdate='on' GIN
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
207 min
< 0.1%
|
0 ms
|
1,279,384,286
db_user
|
select *
from league_seasons
where league_id = $1
and season_id = $2
limit $3
Covered by index on (season_id)
Rows: 11170
Row progression: 11170, 1
Row estimates
- season_id (=): 1
- league_id (=): 9
Existing indexes
- id PRIMARY
- league_id, season_id UNIQUE
- league_id WHERE is_current = true
- season_id UNIQUE
|
|
206 min
< 0.1%
|
0 ms
|
1,328,391,532
db_user
|
select *
from stages
where external_id = $1
limit $2
Covered by index on (external_id)
Rows: 42702
Row progression: 42702, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- season_id
- stage_order
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
205 min
< 0.1%
|
91 ms
|
135,562
db_user
|
with filtered_seasons as (select distinct to_jsonb(s.*) as season,
to_jsonb(l.*) || jsonb_build_object($6, coalesce((select coalesce(tsl.custom_name, tsl.name) from the_sports_languages as tsl where tsl.external_id = l.external_id and tsl.type = $7 and tsl.language = $1::preferred_language), l.name)) as league,
s.start_time,
l.type
from seasons as s
join league_seasons as ls on ls.season_id = s.external_id
join leagues as l on l.external_id = ls.league_id and l.deleted = $8
where ($2::text is null or ls.league_id = $2::text)
and s.start_time <= $3
and exists (select $9
from season_teams as st
where st.league_season_id = ls.id
and ($4::text is null or st.team_id = $4::text)
and exists (select $10
from team_players as tp
where tp.season_team_id = st.id
and ($5::text is null or tp.player_id = $5::text)))),
first_season as (select to_jsonb(s.*) as season,
to_jsonb(l.*) || jsonb_build_object($11, coalesce((select coalesce(tsl.custom_name, tsl.name) from the_sports_languages as tsl where tsl.external_id = l.external_id and tsl.type = $12 and tsl.language = $1::preferred_language), l.name)) as league,
s.start_time,
l.type
from seasons as s
join league_seasons as ls on ls.season_id = s.external_id
join leagues as l on l.external_id = ls.league_id and l.deleted = $13
where ($2::text is null or ls.league_id = $2::text)
and exists (select $14
from season_teams as st
where st.league_season_id = ls.id
and ($4::text is null or st.team_id = $4::text)
and exists (select $15
from team_players as tp
where tp.season_team_id = st.id
and ($5::text is null or tp.player_id = $5::text)))
order by case when l.type = $16 then $17 else $18 end desc, s.start_time
limit $19),
selected_season as (select *
from filtered_seasons as fs
order by case when fs.type = $20 then $21 else $22 end desc, fs.start_time desc
limit $23),
custom_season as (select to_jsonb(s.*) as season,
to_jsonb(l.*) || jsonb_build_object($24, coalesce((select coalesce(tsl.custom_name, tsl.name) from the_sports_languages as tsl where tsl.external_id = l.external_id and tsl.type = $25 and tsl.language = $1::preferred_language), l.name)) as league,
s.start_time,
l.type
from custom_league_season as cls
join seasons as s on s.external_id = cls.season_id
join leagues as l on l.external_id = cls.league_id and l.deleted = $26
where $2::text is not null
and cls.league_id = $2::text
limit $27),
bigger_season as (select to_jsonb(s.*) as season,
to_jsonb(l.*) || jsonb_build_object($28, coalesce((select coalesce(tsl.custom_name, tsl.name) from the_sports_languages as tsl where tsl.external_id = l.external_id and tsl.type = $29 and tsl.language = $1::preferred_language), l.name)) as league,
s.start_time,
l.type
from selected_season as sel,
seasons as s
join league_seasons as ls on ls.season_id = s.external_id
join leagues as l on l.external_id = ls.league_id and l.deleted = $30
join season_teams as st on st.league_season_id = ls.id
where s.start_time > sel.start_time
and ($2::text is null or ls.league_id = $2::text)
and ($4::text is null or st.team_id = $4::text)
and exists (select $31
from transfers as tr
where tr.deleted is not true
and (tr.to_team_id = st.team_id or tr.from_team_id = st.team_id)
and tr.transfer_time > (sel.season ->> $32)::timestamptz)
order by case when l.type = $33 then $34 else $35 end desc, s.start_time desc
limit $36)
select *
from custom_season
union all
select *
from bigger_season
where not exists (select $37 from custom_season)
union all
select *
from selected_season
where not exists (select $38 from custom_season)
and not exists (select $39 from bigger_season)
union all
select *
from first_season
where not exists (select $40 from custom_season)
and not exists (select $41 from bigger_season)
and not exists (select $42 from selected_season)
|
|
201 min
< 0.1%
|
0 ms
|
443,517,881
db_user
|
SELECT season_player_statistics.id, season_player_statistics.created_date, season_player_statistics.updated_date, season_player_statistics.external_updated_at, season_player_statistics.season_id, season_player_statistics.player_id, season_player_statistics.team_id, season_player_statistics.matches, season_player_statistics.court, season_player_statistics.first, season_player_statistics.goals, season_player_statistics.penalty, season_player_statistics.assists, season_player_statistics.minutes_played, season_player_statistics.red_cards, season_player_statistics.yellow_cards, season_player_statistics.shots, season_player_statistics.shots_on_target, season_player_statistics.dribble, season_player_statistics.dribble_succ, season_player_statistics.clearances, season_player_statistics.blocked_shots, season_player_statistics.interceptions, season_player_statistics.tackles, season_player_statistics.passes, season_player_statistics.passes_accuracy, season_player_statistics.key_passes, season_player_statistics.crosses, season_player_statistics.crosses_accuracy, season_player_statistics.long_balls, season_player_statistics.long_balls_accuracy, season_player_statistics.duels, season_player_statistics.duels_won, season_player_statistics.dispossessed, season_player_statistics.fouls, season_player_statistics.was_fouled, season_player_statistics.offsides, season_player_statistics.yellow2_red_cards, season_player_statistics.saves, season_player_statistics.punches, season_player_statistics.runs_out, season_player_statistics.runs_out_succ, season_player_statistics.good_high_claim, season_player_statistics.rating, season_player_statistics.freekicks, season_player_statistics.freekick_goals, season_player_statistics.hit_woodwork, season_player_statistics.fastbreaks, season_player_statistics.fastbreak_shots, season_player_statistics.fastbreak_goals, season_player_statistics.poss_losts, season_player_statistics.big_chance_created, season_player_statistics.big_chance_missed FROM season_player_statistics WHERE season_player_statistics.season_id = $1 AND (season_player_statistics.player_id = $2) AND (season_player_statistics.team_id = $3)
Covered by index on (player_id, season_id)
Rows: 3760127
Row progression: 3760127, 21
Row estimates
- player_id (=): 21
- team_id (=): 490
- season_id (=): 654
Existing indexes
- id PRIMARY
- player_id, season_id
- season_id, player_id, team_id UNIQUE
- season_id, rating
|
|
200 min
< 0.1%
|
0 ms
|
1,107,984,258
db_user
|
select *
from seasons
where external_id = $1
limit $2
Covered by index on (external_id, year)
Rows: 11992
Row progression: 11992, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- CREATE INDEX idx_seasons_external_id_current ON public.seasons USING btree (external_id) WHERE is_current
- external_id UNIQUE
- external_id, year
- uid
|
|
196 min
< 0.1%
|
0 ms
|
31,752,809
db_user
|
select to_jsonb(m.*) || jsonb_build_object(
$3, r.name,
$4, r.logo,
$5, (select case
when s.mode != $6 then $7
when (select count(*)
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))) = $8
then $9
else
exists((select $10
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.match_time < m.match_time))) end)) as match,
to_jsonb(s.*) || jsonb_build_object($11, coalesce(s.custom_names ->> $1::text, s.name)) as stage,
to_jsonb(l.*) || jsonb_build_object($12, coalesce(tsl_l.custom_name, tsl_l.name, l.name)) as league,
to_jsonb(ht.*) || jsonb_build_object($13, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name), $14, coalesce(ht.custom_logo, ht.logo)) as home_team,
to_jsonb(at.*) || jsonb_build_object($15, coalesce(tsl_at.custom_name, tsl_at.name, at.name), $16, coalesce(at.custom_logo, at.logo)) as away_team,
jsonb_build_object(
$17, to_jsonb(l.*) || jsonb_build_object($18, coalesce(tsl_l.custom_name, tsl_l.name, l.name)),
$19, season.*,
$20, to_jsonb(ht.*) || jsonb_build_object($21, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name), $22, coalesce(ht.custom_logo, ht.logo)),
$23, htc.*,
$24, htcolor.color,
$25, htcolor.secondary_color) as home_team_details,
jsonb_build_object(
$26, to_jsonb(l.*) || jsonb_build_object($27, coalesce(tsl_l.custom_name, tsl_l.name, l.name)),
$28, season.*,
$29, to_jsonb(at.*) || jsonb_build_object($30, coalesce(tsl_at.custom_name, tsl_at.name, at.name), $31, coalesce(at.custom_logo, at.logo)),
$32, atc.*,
$33, atcolor.color,
$34, atcolor.secondary_color) as away_team_details
from matches as m
left join match_lineups as ml on ml.match_id = m.external_id
join leagues as l on l.external_id = m.league_id and l.deleted = $35
left join stages as s on s.external_id = m.stage_id
left join seasons as season on season.external_id = m.season_id
left join referees as r on r.external_id = m.referee_id
left join teams as ht on ht.external_id = m.home_team_id
left join coaches as htc on htc.external_id = coalesce(ml.home_coach_id, ht.coach_id)
left join team_colors as htcolor on htcolor.team_id = ht.external_id
left join teams as at on at.external_id = m.away_team_id
left join coaches as atc on atc.external_id = coalesce(ml.away_coach_id, at.coach_id)
left join team_colors as atcolor on atcolor.team_id = at.external_id
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $36 and tsl_l.language = $1::preferred_language
left join the_sports_languages as tsl_ht on tsl_ht.external_id = ht.external_id and tsl_ht.type = $37 and tsl_ht.language = $1::preferred_language
left join the_sports_languages as tsl_at on tsl_at.external_id = at.external_id and tsl_at.type = $38 and tsl_at.language = $1::preferred_language
where m.id = $2
order by m.id desc
limit $39
|
|
196 min
< 0.1%
|
2 ms
|
7,660,740
db_user
|
select to_jsonb(l.*) as league,
to_jsonb(s.*) as season,
to_jsonb(t.*) as team,
to_jsonb(c.*) as coach,
tc.color as team_color,
tc.secondary_color as team_secondary_color,
stt.position as position,
to_jsonb(coun.*) as country
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
left join standings as sta on sta.season_id = ls.season_id
left join standings_stages as sts on sts.standings_id = sta.id
left join standings_teams as stt on stt.standings_stage_id = sts.external_id and stt.team_id = t.external_id
left join countries as coun on coun.external_id = t.country_id
where t.external_id = $1
and l.type = $2
order by s.end_time desc
limit $3
|
|
195 min
< 0.1%
|
99 ms
|
117,904
db_user
|
with knockout_matches as (select m.external_id
from matches as m
where m.season_id = $1
and exists(select $9
from matches as im
where im.stage_id = m.stage_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time)),
base as (select mp.id as match_player_id,
case when mps.id is null then $10 else to_jsonb(mps.*) end as stats,
case when coalesce(m.home_over_time_score, $11) != $12 then m.home_over_time_score else m.home_score end as home_score,
case when coalesce(m.away_over_time_score, $13) != $14 then m.away_over_time_score else m.away_score end as away_score,
mps.rating as rating,
m.id as match_id,
m.external_id as match_external_id,
l.type as league_type,
mp.team_type as team_type,
m.round_num,
coalesce(s.custom_names ->> $2::text, s.name) as stage_name,
mp.player_id as player_id,
s.mode as stage_mode,
s.stage_order,
m.match_time,
m.home_team_id,
m.away_team_id,
m.stage_id,
(m.external_id in (select external_id from knockout_matches)) as is_knockout
from match_players as mp
left join match_player_statistics as mps on mps.match_player_id = mp.id
left join match_lineups as ml on ml.id = mp.match_lineup
left join matches as m on m.external_id = ml.match_id
left join leagues as l on l.external_id = m.league_id and l.deleted = $15
left join stages as s on s.external_id = m.stage_id
where m.season_id = $1
and mp.team_type is not null
and m.status_id in (select ms.id
from match_statuses as ms
where ms.description not in
($16, $17, $18, $19, $20))
and ($3 is null or mp.player_id = $3)
and ($4 is null or (select case when mp.team_type = $21 then m.home_team_id else m.away_team_id end) = $4)
order by mps.rating desc nulls last, m.match_time desc),
grouped as (select jsonb_build_object(
$22, stats
) as data,
base.*,
case
when league_type <> $23 and round_num is not null and round_num <> $24
then case
when stage_order > $25
then stage_name || $26 || round_num
else $27 || round_num end
when league_type <> $28
and (round_num is null or round_num = $29)
and stage_name is not null
and stage_mode = $30
then stage_name
when league_type = $31
and (round_num is null or round_num = $32)
and stage_name is not null
and stage_mode = $33
then case
when exists(select $34
from matches as im
where im.stage_id = base.stage_id
and im.home_team_id = base.away_team_id
and im.away_team_id = base.home_team_id)
then stage_name || $35 ||
case when is_knockout then $36 else $37 end
else stage_name
end
when league_type = $38
and (round_num is null or round_num = $39)
and stage_name is not null
and stage_mode = $40
then stage_name
when league_type = $41
and round_num is not null
and round_num <> $42
and stage_name is not null
and stage_mode = $43
then stage_name || $44 || round_num
else $45
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by rating desc nulls last, match_time desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.data || jsonb_build_object(
$46, ranked.match_player_id,
$47, to_jsonb(p.*) || jsonb_build_object($48, coalesce((select tsl_p.custom_name from the_sports_languages as tsl_p where tsl_p.external_id = p.external_id and tsl_p.type = $49 and tsl_p.language = $2::preferred_language), p.name)),
$50, to_jsonb(ht.*) || jsonb_build_object($51, coalesce(tsl_ht.custom_name, tsl_ht.name, ht.name), $52, coalesce(ht.custom_logo, ht.logo)),
$53, to_jsonb(at.*) || jsonb_build_object($54, coalesce(tsl_at.custom_name, tsl_at.name, at.name), $55, coalesce(at.custom_logo, at.logo)),
$56, home_score,
$57, away_score,
$58, htc.color,
$59, atc.color,
$60, htc.secondary_color,
$61, atc.secondary_color,
$62, match_time,
$63, team_type,
$64, round_num,
$65, match_id,
$66, match_external_id,
$67, league_type,
$68, stage_name,
$69, stage_mode,
$70, is_knockout) order by ranked.rating desc nulls last, ranked.match_time desc) filter (where rn <= $5) as data,
count(ranked.*) filter (where rn <= $5) as inner_total,
count(ranked.*) as total
from ranked
left join players as p on p.external_id = player_id
left join teams as ht on ht.external_id = home_team_id
left join teams as at on at.external_id = away_team_id
left join team_colors as htc on htc.team_id = ht.external_id
left join team_colors as atc on atc.team_id = at.external_id
left join the_sports_languages as tsl_ht on tsl_ht.external_id = ht.external_id and tsl_ht.type = $71 and tsl_ht.language = $2::preferred_language
left join the_sports_languages as tsl_at on tsl_at.external_id = at.external_id and tsl_at.type = $72 and tsl_at.language = $2::preferred_language
group by ranked.group_name, ranked.stage_order, ranked.is_knockout, ranked.round_num
order by ranked.stage_order desc, case when ranked.is_knockout then $73 else $74 end desc, ranked.round_num desc),
target_group as (select stage_order,
case when is_knockout then $75 else $76 end as ko_order,
round_num
from grouped
wh
|
|
194 min
< 0.1%
|
6 ms
|
1,816,851
db_user
|
with lineup as (select ml.id as lineup_id,
m.home_team_id as home_team_id,
m.away_team_id as away_team_id,
m.is_overtime as is_overtime,
m.match_time as match_time,
m.league_id as league_id,
m.season_id as season_id,
(select jsonb_build_object(
$2, l.*,
$3, s.*,
$4, t.*,
$5, c.*,
$6, tc.color,
$7, tc.secondary_color
)
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.external_id = m.home_team_id
and l.id is not null
limit $8) as home_team_details,
(select jsonb_build_object(
$9, l.*,
$10, s.*,
$11, t.*,
$12, c.*,
$13, tc.color,
$14, tc.secondary_color
)
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.external_id = m.away_team_id
and l.id is not null
limit $15) as away_team_details
from matches as m
join match_lineups as ml on ml.match_id = m.external_id
where m.id = $1
limit $16),
goal_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description in ($17, $18)),
yellow_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description = $19),
red_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description in ($20, $21)),
extra_types as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description = any (array [$22,$23,$24])),
player_ids as (select array_agg(mp.player_id) as ids
from match_players as mp
where mp.match_lineup = (select lineup_id from lineup)),
player_stats as (select imi.player_id,
sum(case when imi.type = any ((select ids from goal_type)::bigint[]) then $25 else $26 end) as goals,
sum(case when imi.type = any ((select ids from yellow_card_type)::bigint[]) then $27 else $28 end) as yellow_cards,
sum(case when imi.type = any ((select ids from red_card_type)::bigint[]) then $29 else $30 end) as red_cards,
imi.assist1_id as assist_id
from match_incidents as imi
join match_lineups as iml on iml.id = imi.match_lineup
join matches as im on im.external_id = iml.match_id
where im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $31
and imi.player_id = any ((select ids from player_ids)::text[])
and (imi.type = any ((select ids from yellow_card_type)::bigint[])
or imi.type = any ((select ids from red_card_type)::bigint[])
or imi.type = any ((select ids from goal_type)::bigint[]))
group by imi.player_id, imi.assist1_id),
assist_stats as (select assist_id,
count(*) as assists
from player_stats
where assist_id is not null
group by assist_id),
aggregated_stats as (select player_id,
sum(goals) as total_goals,
sum(yellow_cards) as total_yellow_cards,
sum(red_cards) as total_red_cards
from player_stats
group by player_id)
select l.is_overtime as is_overtime,
l.match_time as match_time,
mi.time as time,
mi.addtime as addtime,
ts.description as description,
to_jsonb(mi.*) as incident,
case when p.id is not null then to_jsonb(p.*) else to_jsonb(c.*) end as player,
to_jsonb(sp.*) as secondary_player,
to_jsonb(t.*) as team,
to_jsonb(st.*) as secondary_team,
case
when mi.team_type = $32 then l.home_team_details
else l.away_team_details
end as team_details,
case
when mi.team_type = $33 then l.away_team_details
else l.home_team_details
end as secondary_team_details,
jsonb_build_object(
$34, coalesce(agg.total_goals, $35),
$36, coalesce(ast.assists, $37),
$38, coalesce(agg.total_yellow_cards, $39),
$40, coalesce(agg.total_red_cards, $41)
) as season_statistic
from match_incidents as mi
left join lineup as l on mi.match_lineup = l.lineup_id
left join technical_statistics as ts on ts.id = mi.type
left join teams as t on t.external_id = case
when mi.team_type = $42 then l.home_team_id
else l.away_team_id
end
left join teams as st on st.external_id = case
when mi.team_type = $43 then l.away_team_id
else l.home_team_id
end
left join coaches as c on c.external_id = mi.player_id
left join players as p on p.external_id = mi.player_id
left join players as sp on sp.external_id = coalesce(mi.assist1_id, mi.out_player_id)
left join aggregated_stats as agg on agg.player_id = mi.player_id
left join assist_stats as ast on ast.assist_id = mi.assist1_id
where mi.match_lineup = l.lineup_id
and (mi.player_id is not null or mi.type = any ((select ids from extra_types)::bigint[]))
and l.home_team_details is not null
and l.away_team_details is not null
and mi.deleted = $44
order by case
when ts.description = $45 and mi.time::bigint = $46 then $47
when ts.description = $48 and mi.time::bigint = $49 then $50
when ts.description = $51 and mi.time::bigint = $52 then $53
when ts.description = $54 then
|
|
194 min
< 0.1%
|
0 ms
|
39,349,709
db_user
|
select to_jsonb(m.*) || jsonb_build_object(
$2, r.name,
$3, r.logo,
$4, (select case
when s.mode != $5 then $6
when (select count(*)
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))) = $7
then $8
else
exists((select $9
from matches as m2
where m2.stage_id = m.stage_id
and (m2.home_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.away_team_id = any (array [m.home_team_id, m.away_team_id]))
and (m2.match_time < m.match_time))) end)) as match,
to_jsonb(s.*) as stage,
to_jsonb(l.*) as league,
to_jsonb(ht.*) as home_team,
to_jsonb(at.*) as away_team,
jsonb_build_object(
$10, l.*,
$11, season.*,
$12, ht.*,
$13, htc.*,
$14, htcolor.color,
$15, htcolor.secondary_color) as home_team_details,
jsonb_build_object(
$16, l.*,
$17, season.*,
$18, at.*,
$19, atc.*,
$20, atcolor.color,
$21, atcolor.secondary_color) as away_team_details
from matches as m
left join match_lineups as ml on ml.match_id = m.external_id
left join leagues as l on l.external_id = m.league_id
left join stages as s on s.external_id = m.stage_id
left join seasons as season on season.external_id = m.season_id
left join referees as r on r.external_id = m.referee_id
left join teams as ht on ht.external_id = m.home_team_id
left join coaches as htc on htc.external_id = coalesce(ml.home_coach_id, ht.coach_id)
left join team_colors as htcolor on htcolor.team_id = ht.external_id
left join teams as at on at.external_id = m.away_team_id
left join coaches as atc on atc.external_id = coalesce(ml.away_coach_id, at.coach_id)
left join team_colors as atcolor on atcolor.team_id = at.external_id
where m.id = $1
order by m.id desc
limit $22
|
|
191 min
< 0.1%
|
0 ms
|
35,697,536
db_user
|
delete from team_players
where player_id = $1
and arrival_date is not null
and custom_arrival_date is null
and altered = $2
Details
CREATE INDEX CONCURRENTLY ON team_players (player_id, arrival_date)
Rows: 85991456
Row progression: 85991456, 786, 59
Row estimates
- player_id (=): 786
- arrival_date (not_null): 6486619
- custom_arrival_date (null): 6486622
- altered (=): 85991456
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX team_players_player_season_team_arrival_leaving_date_unique_idx ON public.team_players USING btree (player_id, season_team_id, arrival_date, leaving_date, custom_arrival_date) NULLS NOT DISTINCT UNIQUE
- player_id, season_team_id
- season_team_id, player_id, arrival_date DESC
|
|
188 min
< 0.1%
|
1,664 ms
|
6,770
db_user
|
with external_id as (select external_id as ex_id from players as p where p.id = $1),
latest_transfer as (select tr.type as transfer_type
from transfers as tr
where tr.player_id = (select ex_id from external_id)
order by tr.transfer_time desc nulls last
limit $3),
jersey as (select mp.shirt_number as jersey_number
from match_players as mp
left join match_lineups as ml on ml.id = mp.match_lineup
left join matches as m on m.external_id = ml.match_id
where mp.player_id = (select ex_id from external_id)
order by m.match_time desc
limit $4),
player_team as (select tp.*
from team_players as tp
left join season_teams as st on st.id = tp.season_team_id
left join league_seasons as ls on ls.id = st.league_season_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $5
where tp.player_id = (select ex_id from external_id)
and l.type = $6
and coalesce(tp.arrival_date, tp.custom_arrival_date) < now()
order by coalesce(tp.arrival_date, tp.custom_arrival_date) desc nulls last
limit $7),
player_positions as (select jsonb_agg(psp.*) as positions
from player_secondary_positions as psp
where psp.player_id = (select ex_id from external_id)),
player_national_team as (select t.*
from teams as t
join matches as m on m.home_team_id = t.external_id or m.away_team_id = t.external_id
join match_lineups as ml on ml.match_id = m.external_id
join match_players as mp on mp.player_id = (select ex_id from external_id) and
mp.match_lineup = ml.id and
mp.team_type = case when m.home_team_id = t.external_id then $8::team_position else $9::team_position end
where t.is_national_team = $10
limit $11)
select to_jsonb(p.*) || jsonb_build_object($12, p.name, $13, case when lt.transfer_type = $14 then $15 when lt.transfer_type = $16 then $17 else p.status::text end) as player,
to_jsonb(pcoun) || jsonb_build_object($18, coalesce(tsl_pcoun.custom_name, tsl_pcoun.name, pcoun.name)) as country,
psp.positions as positions,
j.jersey_number as jersey_number,
jsonb_build_object(
$19, to_jsonb(t.*) || jsonb_build_object($20, coalesce(tsl_t.custom_name, tsl_t.name, t.name)),
$21, to_jsonb(l.*) || jsonb_build_object($22, coalesce(tsl_l.custom_name, tsl_l.name, l.name)),
$23, to_jsonb(s.*),
$24, to_jsonb(c.*),
$25, tc.color,
$26, tc.secondary_color,
$27, stt.position,
$28, coun
) as team,
case
when pnt is not null and nl is not null and ns is not null then jsonb_build_object(
$29, to_jsonb(pnt.*) || jsonb_build_object($30, coalesce(tsl_pnt.custom_name, tsl_pnt.name, pnt.name)),
$31, to_jsonb(nl.*) || jsonb_build_object($32, coalesce(tsl_nl.custom_name, tsl_nl.name, nl.name)),
$33, to_jsonb(ns.*),
$34, to_jsonb(nc.*),
$35, ntc.color,
$36, ntc.secondary_color,
$37, nstt.position,
$38, ncoun) end as national_team
from players as p
left join countries as pcoun on pcoun.external_id = p.cur_country_id
left join player_team as pt on $39
left join player_national_team as pnt on $40
left join season_teams as st on st.id = pt.season_team_id
left join teams as t on t.external_id = st.team_id
left join league_seasons as ls on ls.id = st.league_season_id
left join coaches as c on c.external_id = t.coach_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $41
left join team_colors as tc on tc.team_id = t.external_id
left join countries as coun on coun.external_id = t.country_id
left join standings as sta on sta.season_id = s.external_id
left join standings_stages as sts on sts.standings_id = sta.id
left join standings_teams as stt on stt.standings_stage_id = sts.external_id and stt.team_id = t.external_id
left join season_teams as nst on nst.team_id = pnt.external_id
left join league_seasons as nls on nls.id = nst.league_season_id
left join coaches as nc on nc.external_id = pnt.coach_id
left join leagues as nl on nl.external_id = nls.league_id and nl.deleted = $42
left join seasons as ns on ns.external_id = nls.season_id
left join team_colors as ntc on ntc.team_id = pnt.external_id
left join countries as ncoun on ncoun.external_id = pnt.country_id
left join standings as nsta on nsta.season_id = ns.external_id
left join standings_stages as nsts on nsts.standings_id = nsta.id
left join standings_teams as nstt on nstt.standings_stage_id = nsts.external_id and nstt.team_id = pnt.external_id
left join player_positions as psp on $43
left join jersey as j on $44
left join latest_transfer as lt on $45
left join the_sports_languages as tsl_pcoun on tsl_pcoun.external_id = pcoun.external_id and tsl_pcoun.type = $46 and tsl_pcoun.language = $2::preferred_language
left join the_sports_languages as tsl_t on tsl_t.external_id = t.external_id and tsl_t.type = $47 and tsl_t.language = $2::preferred_language
left join the_sports_languages as tsl_l on tsl_l.external_id = l.external_id and tsl_l.type = $48 and tsl_l.language = $2::preferred_language
left join the_sports_languages as tsl_pnt on tsl_pnt.external_id = pnt.external_id and tsl_pnt.type = $49 and tsl_pnt.language = $2::preferred_language
left join the_sports_languages as tsl_nl on tsl_nl.external_id = nl.external_id and tsl_nl.type = $50 and tsl_nl.language = $2::preferred_language
where p.id = $1
and l.type = $51
order by s.end_time desc
limit $52
|
|
182 min
< 0.1%
|
5,712 ms
|
1,915
db_user
|
with valid_ratings as (select mp.player_id,
m.season_id,
case when mp.team_type = $5 then m.home_team_id else m.away_team_id end as team_id,
count(*) filter (where mps.minutes_played > $6 and mps.rating > $7) as valid_matches,
round(coalesce(sum(mps.rating) filter (where mps.minutes_played > $8 and mps.rating > $9), $10) * $11) as valid_rating_sum
from match_player_statistics mps
join match_players mp on mp.id = mps.match_player_id
join match_lineups ml on ml.id = mp.match_lineup
join matches m on m.external_id = ml.match_id
join seasons se on se.external_id = m.season_id
where (case when mp.team_type = $12 then m.home_team_id else m.away_team_id end) = $1
group by mp.player_id, m.season_id,
case when mp.team_type = 'Home' then m.home_team_id else m.away_team_id end)
select to_jsonb(p.*) || jsonb_build_object($13, coalesce((select coalesce(tsl_p.custom_name, tsl_p.name) from the_sports_languages as tsl_p where tsl_p.external_id = p.external_id and tsl_p.type = $14 and tsl_p.language = $2::preferred_language), p.name)) as player,
coalesce(sum(sps.goals), $15)::int as goals,
coalesce(sum(sps.assists), $16)::int as assists,
coalesce(sum(sps.yellow_cards), $17)::int as yellow_cards,
coalesce(sum(sps.red_cards), $18)::int as red_cards,
coalesce(sum(sps.court), $19)::int as appearances,
coalesce(sum(sps.minutes_played), $20)::int as minutes_played,
coalesce(sum(sps.saves), $21)::int as saves,
coalesce(sum(sps.tackles), $22)::int as tackles,
coalesce(sum(sps.interceptions), $23)::int as interceptions,
coalesce(sum(sps.duels_won), $24)::int as duels_won,
coalesce(sum(sps.key_passes), $25)::int as key_passes,
coalesce(sum(sps.shots_on_target), $26)::int as shots_on_target,
coalesce(sum(sps.dribble_succ), $27)::int as dribble_succ,
coalesce(sum(sps.crosses), $28)::int as crosses,
coalesce(sum(sps.clearances), $29)::int as clearances,
coalesce(sum(sps.blocked_shots), $30)::int as blocked_shots,
coalesce(sum(sps.big_chance_created), $31)::int as big_chance_created,
coalesce(case
when coalesce(sum(vr.valid_matches), $32) > $33
then sum(vr.valid_rating_sum)::float / sum(vr.valid_matches)::float / $34
else sum(sps.rating)::float / nullif(sum(sps.court), $35)::float / $36
end, $37)::float as rating
from season_player_statistics as sps
join players as p on p.external_id = sps.player_id
join seasons as s on s.external_id = sps.season_id
join teams as t on t.external_id = sps.team_id
cross join lateral (select case when $3 ~ $38 then $3 || $39 || ($3::int + $40)::text
else split_part($3, $41, $42) end as formatted_year) as fy
left join valid_ratings as vr
on vr.season_id = sps.season_id and vr.player_id = sps.player_id and vr.team_id = sps.team_id
where sps.team_id = $1
and (s.year = $3 or s.year = fy.formatted_year
or (t.is_national_team and s.year in (
case when $3 ~ $43 then ($3::int - $44)::text || $45 || $3 end,
case when fy.formatted_year ~ $46 then (fy.formatted_year::int - $47)::text || $48 || fy.formatted_year end)))
and ($4::text is null or sps.season_id in (select ls.season_id from league_seasons as ls where ls.league_id = $4))
group by p.id
|
|
182 min
< 0.1%
|
0 ms
|
650,902,479
db_user
|
SELECT match_players.id, match_players.created_date, match_players.updated_date, match_players.match_lineup, match_players.player_id, match_players.team_type, match_players.first, match_players.captain, match_players.name, match_players.logo, match_players.shirt_number, match_players.position, match_players.x, match_players.y, match_players.rating FROM match_players WHERE match_players.match_lineup = $1 AND (match_players.player_id = $2)
Covered by index on (match_lineup, player_id)
Rows: 20276096
Row progression: 20276096, 43
Row estimates
- match_lineup (=): 43
- player_id (=): 147
Existing indexes
- id PRIMARY
- match_lineup, player_id UNIQUE
- match_lineup) INCLUDE (id, player_id, team_type
- player_id
|
|
178 min
< 0.1%
|
762,247 ms
|
14
db_user
|
select c.*
from coaches as c
left join lateral (select t.external_id
from teams as t
where t.coach_id = c.external_id
order by t.is_national_team, t.id
limit $3) as ct on $4
left join team_market_values as tmv on tmv.team_id = ct.external_id
order by tmv.total_value desc nulls last, c.id
limit $1 offset $2 * $1
|
|
165 min
< 0.1%
|
0 ms
|
69,664,455
db_user
|
select *
from match_incidents as mi
left join match_lineups as ml on ml.id = mi.match_lineup
left join matches as m on m.external_id = ml.match_id
where m.id = $1
and mi.deleted = $2
order by mi.time::bigint desc, mi.addtime, mi.created_date
|
|
161 min
< 0.1%
|
0 ms
|
126,989,908
db_user
|
select *
from match_incidents as mi
left join match_lineups as ml on ml.id = mi.match_lineup
left join matches as m on m.external_id = ml.match_id
where m.id = $1
and mi.deleted = $2
order by mi.time::bigint desc, mi.addtime
|
|
156 min
< 0.1%
|
0 ms
|
440,335,121
db_user
|
UPDATE season_player_statistics SET updated_date = $1, external_updated_at = $2, season_id = $3, player_id = $4, team_id = $5, matches = $6, court = $7, first = $8, goals = $9, penalty = $10, assists = $11, minutes_played = $12, red_cards = $13, yellow_cards = $14, shots = $15, shots_on_target = $16, dribble = $17, dribble_succ = $18, clearances = $19, blocked_shots = $20, interceptions = $21, tackles = $22, passes = $23, passes_accuracy = $24, key_passes = $25, crosses = $26, crosses_accuracy = $27, long_balls = $28, long_balls_accuracy = $29, duels = $30, duels_won = $31, dispossessed = $32, fouls = $33, was_fouled = $34, offsides = $35, yellow2_red_cards = $36, saves = $37, punches = $38, runs_out = $39, runs_out_succ = $40, good_high_claim = $41, rating = $42, freekicks = $43, freekick_goals = $44, hit_woodwork = $45, fastbreaks = $46, fastbreak_shots = $47, fastbreak_goals = $48, poss_losts = $49, big_chance_created = $50, big_chance_missed = $51 WHERE season_player_statistics.id = $52
Covered by index on (id)
Rows: 3760127
Row progression: 3760127, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- player_id, season_id
- season_id, player_id, team_id UNIQUE
- season_id, rating
|
|
154 min
< 0.1%
|
1 ms
|
7,597,287
db_user
|
select st.id as id,
s.end_time as end_date,
s.start_time as start_date,
(select ins.start_time
from league_seasons as ils
left join leagues as il on il.external_id = ils.league_id
left join seasons as ins on ins.external_id = ils.season_id
where il.external_id = l.external_id
and ins.end_time < s.start_time
order by ins.end_time desc
limit $4) as previous_start_date,
(select ins.end_time
from league_seasons as ils
left join leagues as il on il.external_id = ils.league_id
left join seasons as ins on ins.external_id = ils.season_id
where il.external_id = l.external_id
and ins.end_time <= s.start_time
order by ins.end_time desc
limit $5) as previous_end_date
from season_teams as st
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
where st.team_id = $1
and s.end_time >= $2
and (
($3 is null or s.end_time <= $3) or
($3 is not null and
$3::timestamptz - $2::timestamptz < interval $6 and
s.end_time <= $3::timestamptz + interval $7)
)
order by s.end_time
|
|
153 min
< 0.1%
|
4 ms
|
2,340,940
db_user
|
select tp.*
from team_players as tp
left join season_teams as st on st.id = tp.season_team_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
where tp.player_id = any ($1::text[])
order by tp.arrival_date
|
|
146 min
< 0.1%
|
641 ms
|
13,630
db_user
|
select p.id, p.created_date, p.updated_date, p.external_updated_at, p.external_id, p.cur_team_id,
p.cur_country_id, p.status, coalesce((select tsl_p.custom_name from the_sports_languages as tsl_p where tsl_p.external_id = p.external_id and tsl_p.type = $3 and tsl_p.language = $1::preferred_language), p.name) as name, p.short_name, p.searchable_name,
p.logo, p.national_logo, p.nationality, p.age, p.birthday, p.weight, p.height, p.market_value,
p.market_value_currency, p.contract_until, p.position, p.main_position, p.preferred_foot,
p.deathday, p.uid
from players as p
left join team_players as tp on tp.player_id = p.external_id
left join season_teams as st on st.id = tp.season_team_id
left join league_seasons as ls on ls.id = st.league_season_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $4
left join teams as t on t.external_id = st.team_id
left join season_player_statistics as sps on sps.team_id = t.external_id and
sps.season_id = ls.season_id and
sps.player_id = p.external_id
where (tp.leaving_date is null or
tp.leaving_date > now())
and (coalesce(tp.arrival_date, tp.custom_arrival_date) is null or coalesce(tp.arrival_date, tp.custom_arrival_date) <= now())
and p.uid is null
and t.id = $2::bigint
and (p.cur_team_id = t.external_id or tp.altered)
group by p.id, sps.id
order by coalesce(sps.rating, $5) desc, p.name
|
|
145 min
< 0.1%
|
0 ms
|
927,610,986
db_user
|
SELECT season_teams.id, season_teams.created_date, season_teams.updated_date, season_teams.league_season_id, season_teams.team_id FROM season_teams WHERE season_teams.league_season_id = $1 AND (season_teams.team_id = $2)
Covered by index on (team_id)
Rows: 410216
Row progression: 410216, 22
Row estimates
- team_id (=): 22
- league_season_id (=): 45
Existing indexes
- id PRIMARY
- league_season_id, team_id UNIQUE
- team_id
|
|
144 min
< 0.1%
|
126 ms
|
68,385
db_user
|
select pairs.player_id as player_id,
pairs.transfer_time as transfer_time,
pairs.team_id as team_id,
pairs.year as year,
pairs.formatted_year as formatted_year,
coalesce(sub.seasons, $6::jsonb) as seasons
from unnest($1::text[], $2::timestamptz[], $3::text[], $4::text[], $5::text[]) as pairs(player_id, transfer_time, team_id, year, formatted_year)
left join lateral (select jsonb_agg(jsonb_build_object($7, st.id, $8, s.year, $9, l.name)) as seasons
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
left join leagues as l on l.external_id = ls.league_id
left join season_teams as st on st.league_season_id = ls.id
where st.team_id = pairs.team_id
and s.end_time >= pairs.transfer_time
and s.year != pairs.year
and s.year != pairs.formatted_year
group by st.team_id) sub on $10
|
|
138 min
< 0.1%
|
41 ms
|
200,085
db_user
|
with lineup as (select ml.id as lineup_id, m.*
from matches as m
join match_lineups as ml on ml.match_id = m.external_id
where m.external_id = $1
limit $4),
goal_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $5
or description = $6),
yellow_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $7),
red_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $8
or description = $9)
select (select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from goal_type)])
and imi.deleted = $10
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and im.season_id = (select season_id from lineup)) as goals,
(select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where (imi.assist1_id = $3)
and imi.type = any (array [(select ids from goal_type)])
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and imi.deleted = $11
and im.season_id = (select season_id from lineup)) as assists,
(select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from yellow_card_type)])
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and imi.deleted = $12
and im.season_id = (select season_id from lineup)) as yellow_cards,
(select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.deleted = $13
and imi.type = any (array [(select ids from red_card_type)])
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and im.season_id = (select season_id from lineup)) as red_cards
|
|
136 min
< 0.1%
|
0 ms
|
598,802,350
db_user
|
UPDATE seasons SET updated_date = $1, external_updated_at = $2, external_id = $3, year = $4, has_player_stats = $5, has_team_stats = $6, has_table = $7, is_current = $8, start_time = $9, end_time = $10 WHERE seasons.id = $11
Covered by index on (id)
Rows: 11992
Row progression: 11992, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE INDEX idx_seasons_external_id_current ON public.seasons USING btree (external_id) WHERE is_current
- external_id UNIQUE
- external_id, year
- uid
|
|
135 min
< 0.1%
|
202 ms
|
40,172
db_user
|
with incident as (select mi.*,
case when ts.description in ($3, $4) then mi.home_score when coalesce(m.home_over_time_score, $5) != $6 then m.home_over_time_score else m.home_score end as home_score,
case when ts.description in ($7, $8) then mi.away_score when coalesce(m.away_over_time_score, $9) != $10 then m.away_over_time_score else m.away_score end as away_score,
ml.match_id,
mi.team_type,
coalesce(mi.assist2_id, mi.assist1_id, mi.out_player_id) as secondary_player_id,
case when mi.team_type != $11 then m.home_team_id else m.away_team_id end as team_id,
case when mi.team_type != $12 then m.away_team_id else m.home_team_id end as secondary_team_id,
ts.description,
m.match_time,
m.is_overtime,
m.round_num,
m.season_id,
case
when ts.description = $13 and mi.time::bigint = $14 then $15
when ts.description = $16 and mi.time::bigint = $17 then $18
when ts.description = $19 and mi.time::bigint = $20 then $21
else mi.time::bigint
end - mi.addtime::bigint as adjusted_time
from match_incidents as mi
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id
join technical_statistics as ts on ts.id = mi.type
where m.id = $1
and mi.deleted = $22),
related_incident as (select mi.*,
r.incident_id as parent_incident_id,
ml.match_id,
coalesce(mi.assist2_id, mi.assist1_id, mi.out_player_id) as secondary_player_id,
case when mi.team_type != $23 then m.home_team_id else m.away_team_id end as team_id,
case when mi.team_type != $24 then m.away_team_id else m.home_team_id end as secondary_team_id,
ts.description,
m.match_time,
m.is_overtime
from related_incidents as r
join match_incidents as mi on mi.id = r.related_incident_id
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id
join technical_statistics as ts on ts.id = mi.type
where r.incident_id in (select id from incident)),
goal_types as (select array_agg(id) as ids
from technical_statistics
where description in ($25, $26)),
red_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($27, $28)),
yellow_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($29)),
player_statistics as (select mi.player_id,
count(distinct mi.id) filter (where mi.type = any (array [(select ids from goal_types)])) as total_goals,
count(distinct mi.id) filter (where mi.type = any (array [(select ids from red_card_types)])) as total_red_cards,
count(distinct mi.id) filter (where mi.type = any (array [(select ids from yellow_card_types)])) as total_yellow_cards
from incident as p_i
join match_incidents as mi on mi.player_id = p_i.player_id and mi.deleted = $30
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
group by mi.player_id),
player_assists as (select coalesce(mi.assist1_id, mi.assist2_id) as player_id,
count(distinct mi.id) as assists
from incident as p_i
join match_incidents as mi on coalesce(mi.assist1_id, mi.assist2_id) = p_i.player_id and mi.deleted = $31
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
where mi.type = any (array [(select ids from goal_types)])
group by coalesce(mi.assist1_id, mi.assist2_id))
select i.is_overtime as is_overtime,
i.match_time as match_time,
i.time as time,
i.addtime as addtime,
i.description as description,
to_jsonb(i.*) as incident,
case when p.id is not null then to_jsonb(p.*) || jsonb_build_object($32, coalesce((select tsl_p.custom_name from the_sports_languages as tsl_p where tsl_p.external_id = p.external_id and tsl_p.type = $33 and tsl_p.language = $2::preferred_language), p.name)) else to_jsonb(c.*) end as player,
to_jsonb(sp.*) || jsonb_build_object($34, coalesce((select tsl_sp.custom_name from the_sports_languages as tsl_sp where tsl_sp.external_id = sp.external_id and tsl_sp.type = $35 and tsl_sp.language = $2::preferred_language), sp.name)) as secondary_player,
to_jsonb(t.*) || jsonb_build_object($36, coalesce(tsl_t.custom_name, tsl_t.name, t.name), $37, coalesce(t.custom_logo, t.logo)) as team,
to_jsonb(st.*) || jsonb_build_object($38, coalesce(tsl_st.custom_name, tsl_st.name, st.name), $39, coalesce(st.custom_logo, st.logo)) as secondary_team,
(select jsonb_build_object(
$40, coalesce(l2, ml2),
$41, coalesce(s2, ms2),
$42, to_jsonb(t2.*) || jsonb_build_object($43, coalesce(tsl_t2.custom_name, tsl_t2.name, t2.name), $44, coalesce(t2.custom_logo, t2.logo)),
$45, c2.*,
$46, tc.color,
$47, tc.secondary_color
)
from teams as t2
left join season_teams as st2 on st2.team_id = t2.external_id
left join league_seasons as ls2 on ls2.id = st2.league_season_id
left join seasons as s2 on s2.external_id = ls2.season_id
left join seasons as ms2 on ms2.external_id = m.season_id
left join leagues as l2 on l2.external_id = ls2.league_id and l2.deleted = $48
left join leagues as ml2 on ml2.external_id = m.league_id and ml2.deleted = $49
left join coaches as c2 on c2.external_id = t2.coach_id
left join team_colors as tc on tc.team_id = t2.external_id
left join the_sports_languages as tsl_t2 on tsl_t2.external_id = t2.external_id and tsl_t2.type = $50 and tsl_t2.language = $2::preferred_language
where t2.external_id = i.team_id
limit $51) as team_details,
(select jsonb_build_object(
$52, coalesce(l3, ml3),
$53, coalesce(s3, ms3),
$54, to_jsonb(t3.*) || jsonb_build_object($55, coalesce(tsl_t3.custom_name, tsl_t3.name, t3.name), $56, coalesce(t3.custom_logo, t3.logo)),
$57, c3.*,
$58, tc2.color,
$59, tc2.secondary_color
)
from teams as t3
left join season_teams as st3 on st3.team_id = t3.external_id
left join league_seasons as ls3 on ls3.id = st3.league_season_id
left join seasons as s3 on s3.external_id = ls3.season_id
left join seasons as ms3 on ms3.external_id = m.season_id
left join leagues as l3 on l3.external_id = ls3.leagu
|
|
132 min
< 0.1%
|
0 ms
|
1,013,565,987
db_user
|
SELECT $2 FROM ONLY "public"."season_teams" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
131 min
< 0.1%
|
8 ms
|
992,777
db_user
|
with recently_ended as (select distinct on (mst.match_id) mst.match_id,
mst.changed_at
from match_status_tracker as mst
where mst.new_status_id = $3
and mst.changed_at <= now() - $1::interval
and mst.changed_at > now() - $2::interval
order by mst.match_id, mst.changed_at desc),
mvps as (select distinct on (m.id) mps.id as mps_id,
mp.id as mp_id,
m.id as match_id,
re.changed_at as changed_at
from recently_ended as re
join matches as m on m.id = re.match_id and m.status_id = $4
join match_lineups as ml on ml.match_id = m.external_id
join match_players as mp on mp.match_lineup = ml.id
join match_player_statistics as mps on mps.match_player_id = mp.id and mps.rating > $5
left join notified_mvps as nm on nm.match_id = m.external_id
where nm is null
order by m.id, mps.rating desc)
select v.match_id as id,
extract($6 from (now() - v.changed_at))::bigint as time_diff,
jsonb_build_object(
$7, to_jsonb(mps.*),
$8, to_jsonb(p.*),
$9, to_jsonb(ht.*),
$10, to_jsonb(at.*),
$11, m.home_score,
$12, m.away_score,
$13, coalesce(htc.color, $14),
$15, coalesce(htc.secondary_color, $16),
$17, coalesce(atc.color, $18),
$19, coalesce(atc.secondary_color, $20),
$21, m.match_time,
$22, mp.team_type,
$23, m.round_num,
$24, m.id,
$25, m.external_id,
$26, l.type,
$27, st.name,
$28, st.mode,
$29, exists(select $30
from matches as im
where im.stage_id = m.stage_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time)
) as rating
from mvps as v
join match_player_statistics as mps on mps.id = v.mps_id
join match_players as mp on mp.id = v.mp_id
join match_lineups as ml on ml.id = mp.match_lineup
join matches as m on m.external_id = ml.match_id
left join leagues as l on l.external_id = m.league_id
left join players as p on p.external_id = mp.player_id
left join teams as ht on ht.external_id = m.home_team_id
left join team_colors as htc on htc.team_id = ht.external_id
left join teams as at on at.external_id = m.away_team_id
left join team_colors as atc on atc.team_id = at.external_id
left join stages as st on st.external_id = m.stage_id
order by v.changed_at desc, v.match_id
|
|
129 min
< 0.1%
|
708 ms
|
10,967
db_user
|
select p.id, p.created_date, p.updated_date, p.external_updated_at, p.external_id, p.cur_team_id,
p.cur_country_id, p.status, coalesce((select coalesce(tsl_p.custom_name, tsl_p.name) from the_sports_languages as tsl_p where tsl_p.external_id = p.external_id and tsl_p.type = $3 and tsl_p.language = $1::preferred_language), p.name) as name, p.short_name, p.searchable_name,
p.logo, p.national_logo, p.nationality, p.age, p.birthday, p.weight, p.height, p.market_value,
p.market_value_currency, p.contract_until, p.position, p.main_position, p.preferred_foot,
p.deathday, p.uid
from players as p
left join team_players as tp on tp.player_id = p.external_id
left join season_teams as st on st.id = tp.season_team_id
left join league_seasons as ls on ls.id = st.league_season_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $4
left join teams as t on t.external_id = st.team_id
left join season_player_statistics as sps on sps.team_id = t.external_id and
sps.season_id = ls.season_id and
sps.player_id = p.external_id
where (tp.leaving_date is null or
tp.leaving_date > now())
and (coalesce(tp.arrival_date, tp.custom_arrival_date) is null or coalesce(tp.arrival_date, tp.custom_arrival_date) <= now())
and p.uid is null
and t.id = $2::bigint
and (p.cur_team_id = t.external_id or tp.altered)
group by p.id, sps.id
order by coalesce(sps.rating, $5) desc, p.name
|
|
129 min
< 0.1%
|
51 ms
|
151,415
db_user
|
with lineup as (select ml.id as lineup_id, m.*
from matches as m
join match_lineups as ml on ml.match_id = m.external_id
where m.external_id = $1
limit $4),
goal_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $5
or description = $6),
yellow_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $7),
red_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $8
or description = $9)
select (select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from goal_type)])
and imi.deleted = $10
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and im.season_id = (select season_id from lineup)) as goals,
(select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where (coalesce(imi.assist1_id) = $3)
and imi.type = any (array [(select ids from goal_type)])
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and imi.deleted = $11
and im.season_id = (select season_id from lineup)) as assists,
(select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from yellow_card_type)])
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and imi.deleted = $12
and im.season_id = (select season_id from lineup)) as yellow_cards,
(select count(*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.deleted = $13
and imi.type = any (array [(select ids from red_card_type)])
and im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and im.season_id = (select season_id from lineup)) as red_cards
|
|
129 min
< 0.1%
|
175 ms
|
43,964
db_user
|
with valid_ratings as (select mp.player_id,
m.season_id,
count(*) filter (where mps.minutes_played > $9 and mps.rating > $10) as valid_matches,
round(coalesce(sum(mps.rating) filter (where mps.minutes_played > $11 and mps.rating > $12), $13) * $14) as valid_rating_sum
from match_player_statistics mps
join match_players mp on mp.id = mps.match_player_id
join match_lineups ml on ml.id = mp.match_lineup
join matches m on m.external_id = ml.match_id
where ($1::text is null or m.season_id = $1)
group by mp.player_id, m.season_id),
season_league as (select ls.id as league_season_id
from league_seasons as ls
where ls.season_id = $1
limit $15),
teams_data as (select td.player_id,
jsonb_agg(td.team order by td.arrival_date desc) as teams,
jsonb_agg(td.team_colors order by td.arrival_date desc) as team_colors
from (select distinct on (tp.player_id, t.external_id) tp.player_id,
to_jsonb(t.*) || jsonb_build_object($16, coalesce(tsl_t.custom_name, tsl_t.name, t.name)) as team,
to_jsonb(tc.*) as team_colors,
tp.arrival_date
from team_players as tp
left join season_teams as st
on st.id = tp.season_team_id and
st.league_season_id = (select league_season_id from season_league)
join teams as t on t.external_id = st.team_id
left join team_colors as tc on tc.team_id = t.external_id
left join the_sports_languages as tsl_t on tsl_t.external_id = t.external_id and tsl_t.type = $17 and tsl_t.language = $2::preferred_language
order by tp.player_id, t.external_id, tp.arrival_date desc) as td
group by td.player_id),
player_agg as (select sps.player_id as player_id,
sps.season_id as season_id,
coalesce(sum(sps.court), $18) as court,
coalesce(sum(sps.minutes_played), $19) as minutes_played,
coalesce(sum(sps.first), $20) as first,
coalesce(sum(sps.yellow2_red_cards), $21) as yellow2_red_cards,
coalesce(sum(sps.goals), $22) as goals,
coalesce(sum(sps.penalty), $23) as penalty,
coalesce(sum(sps.assists), $24) as assists,
coalesce(sum(sps.freekick_goals), $25) as freekick_goals,
coalesce(sum(sps.shots), $26) as shots,
coalesce(sum(sps.shots_on_target), $27) as shots_on_target,
coalesce(sum(sps.passes), $28) as passes,
coalesce(sum(sps.passes_accuracy), $29) as passes_accuracy,
coalesce(sum(sps.key_passes), $30) as key_passes,
coalesce(sum(sps.crosses), $31) as crosses,
coalesce(sum(sps.crosses_accuracy), $32) as crosses_accuracy,
coalesce(sum(sps.long_balls), $33) as long_balls,
coalesce(sum(sps.long_balls_accuracy), $34) as long_balls_accuracy,
coalesce(sum(sps.dribble), $35) as dribble,
coalesce(sum(sps.dribble_succ), $36) as dribble_succ,
coalesce(sum(sps.tackles), $37) as tackles,
coalesce(sum(sps.interceptions), $38) as interceptions,
coalesce(sum(sps.clearances), $39) as clearances,
coalesce(sum(sps.duels), $40) as duels,
coalesce(sum(sps.duels_won), $41) as duels_won,
coalesce(sum(sps.fouls), $42) as fouls,
coalesce(sum(sps.was_fouled), $43) as was_fouled,
coalesce(sum(sps.yellow_cards), $44) as yellow_cards,
coalesce(sum(sps.red_cards), $45) as red_cards,
coalesce(sum(sps.poss_losts), $46) as poss_losts,
coalesce(sum(sps.hit_woodwork), $47) as hit_woodwork,
coalesce(sum(sps.saves), $48) as saves,
coalesce(sum(sps.punches), $49) as punches,
coalesce(sum(sps.runs_out), $50) as runs_out,
coalesce(sum(sps.runs_out_succ), $51) as runs_out_succ,
coalesce(sum(sps.good_high_claim), $52) as good_high_claim,
coalesce(sum(sps.big_chance_created), $53) as big_chance_created,
coalesce(sum(sps.big_chance_missed), $54) as big_chance_missed,
coalesce(sum(sps.rating), $55) as rating
from season_player_statistics as sps
where sps.minutes_played > $56
and sps.court > $57
and ($1::text is null or sps.season_id = $1)
group by sps.player_id, sps.season_id),
multiplier as (select $58 as mul),
divider as (select case when coalesce($3::boolean, $59) = $60 then $61 end as div),
player_stats as (select pa.court as court,
pa.minutes_played as minutes_played,
pa.player_id as player_id,
coalesce(pa.first, $62)::float as first,
coalesce(pa.yellow2_red_cards, $63)::float as yellow2_red_cards,
(pa.goals::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as goals,
(coalesce(pa.penalty, $64)::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as penalty,
(pa.assists::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as assists,
((pa.goals::float + pa.assists::float) / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as goals_assists,
pa.minutes_played::float / greatest(pa.goals::float, $65) as goal_frequency,
(coalesce(pa.freekick_goals, $66)::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as freekick_goals,
(pa.shots::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as shots,
(pa.shots_on_target::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as shots_on_target,
case when pa.shots::float > $67 then (pa.goals::float / pa.shots::float) * $68
else $69 end as goal_conversion,
(pa.passes::float / coalesce(divider.div::float, pa.court::float)) *
multiplier.mul::float as passes,
|
|
125 min
< 0.1%
|
712 ms
|
10,536
db_user
|
select coalesce((select tsl_p.custom_name from the_sports_languages as tsl_p where tsl_p.external_id = p.external_id and tsl_p.type = $3 and tsl_p.language = $1::preferred_language), p.name) as name,
p.*
from players as p
left join team_players as tp on tp.player_id = p.external_id
left join season_teams as st on st.id = tp.season_team_id
left join league_seasons as ls on ls.id = st.league_season_id
left join leagues as l on l.external_id = ls.league_id and l.deleted = $4
left join teams as t on t.external_id = st.team_id
left join season_player_statistics as sps on sps.team_id = t.external_id and
sps.season_id = ls.season_id and
sps.player_id = p.external_id
where (tp.leaving_date is null or
tp.leaving_date > now())
and (coalesce(tp.arrival_date, tp.custom_arrival_date) is null or coalesce(tp.arrival_date, tp.custom_arrival_date) <= now())
and p.uid is null
and t.id = $2::bigint
and (p.cur_team_id = t.external_id or tp.altered)
group by p.id, sps.id
order by coalesce(sps.rating, $5) desc, p.name
|
|
122 min
< 0.1%
|
26 ms
|
287,823
db_user
|
with incident as (select mi.*,
ml.match_id,
mi.team_type,
coalesce(mi.assist2_id, mi.assist1_id, mi.out_player_id) as secondary_player_id,
case when mi.team_type != $2 then m.home_team_id else m.away_team_id end as team_id,
case when mi.team_type != $3 then m.away_team_id else m.home_team_id end as secondary_team_id,
ts.description,
m.match_time,
m.is_overtime,
m.round_num,
m.season_id,
case
when ts.description = $4 and mi.time::bigint = $5 then $6
when ts.description = $7 and mi.time::bigint = $8 then $9
when ts.description = $10 and mi.time::bigint = $11 then $12
else mi.time::bigint
end - mi.addtime::bigint as adjusted_time
from match_incidents as mi
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id
join technical_statistics as ts on ts.id = mi.type
where m.id = $1
and mi.deleted = $13),
goal_types as (select array_agg(id) as ids
from technical_statistics
where description in ($14, $15)),
red_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($16, $17)),
yellow_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($18)),
player_statistics as (select mi.player_id,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from goal_types)])) as total_goals,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from red_card_types)])) as total_red_cards,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from yellow_card_types)])) as total_yellow_cards
from incident as p_i
join match_incidents as mi on mi.player_id = p_i.player_id and mi.deleted = $19
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
group by mi.player_id),
player_assists as (select coalesce(mi.assist1_id, mi.assist2_id) as player_id,
count(distinct mi.*) as assists
from incident as p_i
join match_incidents as mi on coalesce(mi.assist1_id, mi.assist2_id) = p_i.player_id and mi.deleted = $20
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
where mi.type = any (array [(select ids from goal_types)])
group by coalesce(mi.assist1_id, mi.assist2_id))
select i.is_overtime as is_overtime,
i.match_time as match_time,
i.time as time,
i.addtime as addtime,
i.description as description,
to_jsonb(i.*) as incident,
case when p.id is not null then to_jsonb(p.*) else to_jsonb(c.*) end as player,
to_jsonb(sp.*) as secondary_player,
to_jsonb(t.*) as team,
to_jsonb(st.*) as secondary_team,
(select jsonb_build_object(
$21, coalesce(l2, ml2),
$22, s2.*,
$23, t2.*,
$24, c2.*,
$25, tc.color,
$26, tc.secondary_color
)
from teams as t2
left join season_teams as st2 on st2.team_id = t2.external_id
left join league_seasons as ls2 on ls2.id = st2.league_season_id
left join seasons as s2 on s2.external_id = ls2.season_id
left join leagues as l2 on l2.external_id = ls2.league_id
left join leagues as ml2 on ml2.external_id = m.league_id
left join coaches as c2 on c2.external_id = t2.coach_id
left join team_colors as tc on tc.team_id = t2.external_id
where t2.external_id = i.team_id
limit $27) as team_details,
(select jsonb_build_object(
$28, coalesce(l3, ml3),
$29, s3.*,
$30, t3.*,
$31, c3.*,
$32, tc2.color,
$33, tc2.secondary_color
)
from teams as t3
left join season_teams as st3 on st3.team_id = t3.external_id
left join league_seasons as ls3 on ls3.id = st3.league_season_id
left join seasons as s3 on s3.external_id = ls3.season_id
left join leagues as l3 on l3.external_id = ls3.league_id
left join leagues as ml3 on ml3.external_id = m.league_id
left join coaches as c3 on c3.external_id = t3.coach_id
left join team_colors as tc2 on tc2.team_id = t3.external_id
where t3.external_id = i.secondary_team_id
limit $34) as secondary_team_details,
jsonb_build_object(
$35, coalesce(ps.total_goals, $36),
$37, coalesce(pa.assists, $38),
$39, coalesce(ps.total_yellow_cards, $40),
$41, coalesce(ps.total_red_cards, $42)
) as season_statistic
from incident as i
left join player_statistics as ps on ps.player_id = i.player_id
left join player_assists as pa on pa.player_id = i.player_id
left join players as p on p.external_id = i.player_id
left join players as sp on sp.external_id = i.secondary_player_id
left join teams as t on t.external_id = i.team_id
left join teams as st on st.external_id = i.secondary_team_id
left join coaches as c on c.external_id = i.player_id
left join match_lineups as ml on ml.id = i.match_lineup
left join matches as m on m.external_id = ml.match_id
order by case
when i.description = $43 and i.time::bigint = $44 then $45
when i.description = $46 and i.time::bigint = $47 then $48
when i.description = $49 and i.time::bigint = $50 then $51
when i.description = $52 then $53
when i.description = $54 then $55
else i.time::bigint
end - case
when i.description = $56 then $57
when i.description = $58 then $59
when i.description = $60 then $61
else i.addtime::bigint
end,
case
when i.description = $62 then $63
when i.description = $64 then $65
when i.description = $66 then $67
else i.addtime::bigint
end
|
|
122 min
< 0.1%
|
568 ms
|
12,900
db_user
|
select *
from players
order by external_id desc, id desc
limit $1 offset $2 * $1
Covered by index on (external_id)
Rows: 1270387
Row progression: 1270387, 1
Row estimates
- external_id (sort): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- market_value DESC WHERE (market_value IS NOT NULL) AND (market_value > 0)
- market_value DESC WHERE uid IS NULL
- searchable_name
- searchable_name gin_trgm_ops) WITH (fastupdate='on' GIN
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
122 min
< 0.1%
|
25 ms
|
292,630
db_user
|
with incident as (select mi.*,
ml.match_id,
mi.team_type,
coalesce(mi.assist2_id, mi.assist1_id, mi.out_player_id) as secondary_player_id,
case when mi.team_type != $2 then m.home_team_id else m.away_team_id end as team_id,
case when mi.team_type != $3 then m.away_team_id else m.home_team_id end as secondary_team_id,
ts.description,
m.match_time,
m.is_overtime,
m.round_num,
m.season_id,
case
when ts.description = $4 and mi.time::bigint = $5 then $6
when ts.description = $7 and mi.time::bigint = $8 then $9
when ts.description = $10 and mi.time::bigint = $11 then $12
else mi.time::bigint
end - mi.addtime::bigint as adjusted_time
from match_incidents as mi
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id
join technical_statistics as ts on ts.id = mi.type
where m.id = $1
and mi.deleted = $13),
goal_types as (select array_agg(id) as ids
from technical_statistics
where description in ($14, $15)),
red_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($16, $17)),
yellow_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($18)),
player_statistics as (select mi.player_id,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from goal_types)])) as total_goals,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from red_card_types)])) as total_red_cards,
count(distinct mi.*) filter (where mi.type = any (array [(select ids from yellow_card_types)])) as total_yellow_cards
from incident as p_i
join match_incidents as mi on mi.player_id = p_i.player_id and mi.deleted = $19
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
group by mi.player_id),
player_assists as (select coalesce(mi.assist1_id, mi.assist2_id) as player_id,
count(distinct mi.*) as assists
from incident as p_i
join match_incidents as mi on coalesce(mi.assist1_id, mi.assist2_id) = p_i.player_id and mi.deleted = $20
join match_lineups as ml on ml.id = mi.match_lineup
join matches as m on m.external_id = ml.match_id and m.season_id = p_i.season_id and m.match_time < p_i.match_time
where mi.type = any (array [(select ids from goal_types)])
group by coalesce(mi.assist1_id, mi.assist2_id))
select i.is_overtime as is_overtime,
i.match_time as match_time,
i.time as time,
i.addtime as addtime,
i.description as description,
to_jsonb(i.*) as incident,
case when p.id is not null then to_jsonb(p.*) else to_jsonb(c.*) end as player,
to_jsonb(sp.*) as secondary_player,
to_jsonb(t.*) as team,
to_jsonb(st.*) as secondary_team,
(select jsonb_build_object(
$21, coalesce(l2, ml2),
$22, coalesce(s2, ms2),
$23, t2.*,
$24, c2.*,
$25, tc.color,
$26, tc.secondary_color
)
from teams as t2
left join season_teams as st2 on st2.team_id = t2.external_id
left join league_seasons as ls2 on ls2.id = st2.league_season_id
left join seasons as s2 on s2.external_id = ls2.season_id
left join seasons as ms2 on ms2.external_id = m.season_id
left join leagues as l2 on l2.external_id = ls2.league_id
left join leagues as ml2 on ml2.external_id = m.league_id
left join coaches as c2 on c2.external_id = t2.coach_id
left join team_colors as tc on tc.team_id = t2.external_id
where t2.external_id = i.team_id
limit $27) as team_details,
(select jsonb_build_object(
$28, coalesce(l3, ml3),
$29, coalesce(s3, ms3),
$30, t3.*,
$31, c3.*,
$32, tc2.color,
$33, tc2.secondary_color
)
from teams as t3
left join season_teams as st3 on st3.team_id = t3.external_id
left join league_seasons as ls3 on ls3.id = st3.league_season_id
left join seasons as s3 on s3.external_id = ls3.season_id
left join seasons as ms3 on ms3.external_id = m.season_id
left join leagues as l3 on l3.external_id = ls3.league_id
left join leagues as ml3 on ml3.external_id = m.league_id
left join coaches as c3 on c3.external_id = t3.coach_id
left join team_colors as tc2 on tc2.team_id = t3.external_id
where t3.external_id = i.secondary_team_id
limit $34) as secondary_team_details,
jsonb_build_object(
$35, coalesce(ps.total_goals, $36),
$37, coalesce(pa.assists, $38),
$39, coalesce(ps.total_yellow_cards, $40),
$41, coalesce(ps.total_red_cards, $42)
) as season_statistic
from incident as i
left join player_statistics as ps on ps.player_id = i.player_id
left join player_assists as pa on pa.player_id = i.player_id
left join players as p on p.external_id = i.player_id
left join players as sp on sp.external_id = i.secondary_player_id
left join teams as t on t.external_id = i.team_id
left join teams as st on st.external_id = i.secondary_team_id
left join coaches as c on c.external_id = i.player_id
left join match_lineups as ml on ml.id = i.match_lineup
left join matches as m on m.external_id = ml.match_id
order by case
when i.description = $43 and i.time::bigint = $44 then $45
when i.description = $46 and i.time::bigint = $47 then $48
when i.description = $49 and i.time::bigint = $50 then $51
when i.description = $52 then $53
when i.description = $54 then $55
else i.time::bigint
end - case
when i.description = $56 then $57
when i.description = $58 then $59
when i.description = $60 then $61
else i.addtime::bigint
end,
case
when i.description = $62 then $63
when i.description = $64 then $65
when i.description = $66 then $67
else i.addtime::bigint
end
|
|
119 min
< 0.1%
|
0 ms
|
53,318,100
db_user
|
UPDATE matches SET updated_date = $1, external_updated_at = $2, external_id = $3, last_socket_update = $4, is_overtime = $5, reversed = $6, status_id = $7, league_id = $8, season_id = $9, home_team_id = $10, away_team_id = $11, stage_id = $12, venue_id = $13, referee_id = $14, group_num = $15, round_num = $16, match_time = $17, ended_at = $18, neutral = $19, note = $20, home_score = $21, away_score = $22, home_half_time_score = $23, away_half_time_score = $24, home_over_time_score = $25, away_over_time_score = $26, home_penalty_score = $27, away_penalty_score = $28, home_agg_score = $29, away_agg_score = $30, home_red_cards = $31, away_red_cards = $32, home_yellow_cards = $33, away_yellow_cards = $34, home_corners = $35, away_corners = $36, home_position = $37, away_position = $38, related_id = $39, weather = $40, pressure = $41, temperature = $42, wind = $43, humidity = $44, tbd = $45, details = $46, kickoff_time = $47, last_goal_date = $48 WHERE matches.id = $49
Covered by index on (id)
Rows: 2066978
Row progression: 2066978, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- away_team_id, home_team_id
- details
- external_id UNIQUE
- home_team_id, match_time
- league_id
- match_time
- match_time WHERE (status_id > 1) AND (status_id < 8)
- referee_id, status_id, season_id WHERE referee_id IS NOT NULL
- round_num, match_time, external_id
- season_id, match_time
- season_id, match_time DESC
- season_id) INCLUDE (external_id, match_time, home_team_id, away_team_id
- stage_id
- status_id
|