|
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
|
|
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
|
|
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
|
|
2,960 min
1%
|
375 ms
|
473,795
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,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,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,820
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
|
|
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
|
|
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
|
|
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,809
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
|
|
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
|
|
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%
|
410 ms
|
74,401
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
|
|
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
|
|
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)
|
|
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
|
|
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
|
383,348
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
|
|
247 min
0.1%
|
37 ms
|
404,819
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
|
|
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
|
|
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
|
|
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
|
|
204 min
< 0.1%
|
90 ms
|
135,387
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)
|
|
195 min
< 0.1%
|
99 ms
|
117,840
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
|
|
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
|
|
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
|
|
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
|
|
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
|
|
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
|