|
14,654 min
49%
|
142 ms
|
6,177,564
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
|
|
699 min
2%
|
139 ms
|
301,829
db_user
|
delete
from stages
where external_id = any ($1::text[])
Covered by index on (external_id)
Rows: 41395
Row progression: 41395, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- season_id
- stage_order
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
664 min
2%
|
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
|
|
472 min
2%
|
1,744 ms
|
16,236
db_user
|
DELETE FROM ONLY "public"."match_status_tracker" WHERE $1 OPERATOR(pg_catalog.=) "match_id"
|
|
425 min
1%
|
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
|
|
251 min
0.8%
|
25 ms
|
592,259
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
|
|
138 min
0.5%
|
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
|
|
129 min
0.4%
|
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
|
|
122 min
0.4%
|
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.4%
|
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: 1269086
Row progression: 1269086, 1
Row estimates
- external_id (sort): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- searchable_name
- searchable_name gin_trgm_ops) WITH (fastupdate='on' GIN
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
122 min
0.4%
|
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
|
|
107 min
0.4%
|
21 ms
|
301,839
db_user
|
delete
from matches
where external_id = any ($1::text[])
Covered by index on (external_id)
Rows: 291407
Row progression: 291407, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- away_team_id, home_team_id
- details
- external_id UNIQUE
- league_id
- match_time
- round_num, match_time, external_id
- season_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
|
|
72 min
0.2%
|
1,109 ms
|
3,868
db_user
|
select distinct t.id,
t.created_date,
t.updated_date,
t.external_updated_at,
t.player_id,
p.external_id as player_external_id,
p.name as player_name,
t.type,
t.transfer_time,
t.from_team_id as from_team_id,
t.from_team_id as from_team_external_id,
coalesce(ft.name, t.from_team_name) as from_team_name,
t.to_team_id as to_team_id,
t.to_team_id as to_team_external_id,
coalesce(tt.name, t.to_team_name) as to_team_name,
t.fee,
t.description,
ftc.color as from_team_color,
ftc.secondary_color as from_team_secondary_color,
ttc.color as to_team_color,
ttc.secondary_color as to_team_secondary_color
from transfers as t
join players as p on p.external_id = t.player_id
left join teams as ft on ft.external_id = t.from_team_id
left join teams as tt on tt.external_id = t.to_team_id
left join team_colors as ftc on ftc.team_id = ft.external_id
left join team_colors as ttc on ttc.team_id = tt.external_id
left join season_teams as st on st.team_id = tt.external_id or st.team_id = ft.external_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
where ls.league_id = $1
and l.country_id is not null
and ($2::timestamptz is null or t.transfer_time >= $2)
and ($3::timestamptz is null or t.transfer_time <= $3)
and deleted = $6
order by t.transfer_time desc
limit coalesce($4::bigint, $7) offset coalesce($5::bigint, $8) * coalesce($4::bigint, $9)
|
|
55 min
0.2%
|
82 ms
|
40,016
db_user
|
with current_statusses as (select array_agg(ms.id) as ids from match_statuses as ms where ms.id > $8 and ms.id < $9),
paginated_matches as (select m.*
from matches as m
join leagues as l on l.external_id = m.league_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
where ($1::timestamptz is not null or
(m.status_id = any (array [(select ids from current_statusses)]) and now() - m.match_time <= interval $10 and now() > m.match_time))
and (
($2::bigint[] is null and
$3::bigint[] is null and
$4::bigint[] is null) or
(($2::bigint[] is not null and l.id = any ($2::bigint[])) or
($3::bigint[] is not null and m.id = any ($3::bigint[])) or
($4::bigint[] is not null and ht.id = any ($4::bigint[])) or
($4::bigint[] is not null and at.id = any ($4::bigint[])))
)
and ($1::timestamptz is null or (m.match_time >= $1::timestamptz and m.match_time < $1::timestamptz + interval $11))
and ($5::text is null or m.season_id = $5::text)
order by l.name
limit coalesce($6::bigint, $12) offset coalesce($6::bigint, $13) * coalesce($7::bigint, $14))
select l.*,
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 exists(select $20
from matches as im
left join teams as iht on iht.external_id = im.home_team_id
left join teams as iat on iat.external_id = im.away_team_id
where im.stage_id = s.external_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time)
else $21 end),
$22, s.*,
$23, l.*,
$24, ht.*,
$25, at.*,
$26, (select jsonb_build_object(
$27, l.*,
$28, s.*,
$29, t.*,
$30, c.*,
$31, tc.color,
$32, 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
join seasons as s on s.external_id = ls.season_id
join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.id = ht.id
limit $33),
$34, (select jsonb_build_object(
$35, l.*,
$36, s.*,
$37, t.*,
$38, c.*,
$39, tc.color,
$40, 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
join seasons as s on s.external_id = ls.season_id
join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.id = at.id
limit $41)
) order by l.name, m.match_time
) as matches
from paginated_matches as m
join leagues as l on l.external_id = m.league_id
left join stages as s on s.external_id = m.stage_id
left join 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
group by l.id, l.name
order by l.name
|
|
53 min
0.2%
|
21,893 ms
|
145
db_user
|
INSERT INTO matches (updated_date, external_updated_at, external_id, is_overtime, reversed, status_id, league_id, season_id, home_team_id, away_team_id, stage_id, group_num, round_num, match_time, ended_at, neutral, home_score, away_score, home_half_time_score, away_half_time_score, home_over_time_score, away_over_time_score, home_penalty_score, away_penalty_score, home_agg_score, away_agg_score, home_red_cards, away_red_cards, home_yellow_cards, away_yellow_cards, home_corners, away_corners, home_position, away_position, related_id, weather, pressure, temperature, wind, humidity, tbd, details) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42) RETURNING id
|
|
49 min
0.2%
|
710 ms
|
4,126
db_user
|
select *
from transfers
order by transfer_time desc, id desc
limit $1 offset $1 * $2
Covered by index on (transfer_time)
Rows: 2165085
Row progression: 2165085, 291
Row estimates
- transfer_time (sort): 291
Existing indexes
- id PRIMARY
- deleted, transfer_time
- from_team_id, deleted, transfer_time DESC
- player_id
- to_team_id, deleted, transfer_time DESC
- transfer_time
|
|
45 min
0.1%
|
620 ms
|
4,326
db_user
|
INSERT INTO matches (updated_date, external_updated_at, external_id, is_overtime, reversed, status_id, league_id, season_id, home_team_id, away_team_id, stage_id, group_num, round_num, match_time, ended_at, neutral, home_score, away_score, home_half_time_score, away_half_time_score, home_over_time_score, away_over_time_score, home_penalty_score, away_penalty_score, home_red_cards, away_red_cards, home_yellow_cards, away_yellow_cards, home_corners, away_corners, home_position, away_position, tbd, details) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34) RETURNING id
|
|
44 min
0.1%
|
3,571 ms
|
740
db_user
|
INSERT INTO matches (updated_date, external_updated_at, external_id, is_overtime, reversed, status_id, league_id, season_id, home_team_id, away_team_id, stage_id, group_num, round_num, match_time, ended_at, neutral, home_score, away_score, home_half_time_score, away_half_time_score, home_over_time_score, away_over_time_score, home_penalty_score, away_penalty_score, home_red_cards, away_red_cards, home_yellow_cards, away_yellow_cards, home_corners, away_corners, tbd, details) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32) RETURNING id
|
|
39 min
0.1%
|
190 ms
|
12,270
db_user
|
select p.*
from players as p
order by p.id
limit coalesce($1::bigint, $3) offset coalesce($2::bigint, $4) * coalesce($1::bigint, $5)
Covered by index on (id)
Rows: 1269086
Row progression: 1269086, 1
Row estimates
- id (sort): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- searchable_name
- searchable_name gin_trgm_ops) WITH (fastupdate='on' GIN
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
38 min
0.1%
|
714 ms
|
3,173
db_user
|
select *
from transfers
where deleted = $3
order by transfer_time desc, id desc
limit $1 offset $1 * $2
Covered by index on (deleted, transfer_time)
Rows: 2165085
Row progression: 2165085, 1082543, 145
Row estimates
- deleted (=): 1082543
- transfer_time (sort): 291
Existing indexes
- id PRIMARY
- deleted, transfer_time
- from_team_id, deleted, transfer_time DESC
- player_id
- to_team_id, deleted, transfer_time DESC
- transfer_time
|
|
29 min
< 0.1%
|
332 ms
|
5,311
db_user
|
with not_started_status as (select ms.id as nsId
from match_statuses as ms
where ms.description = $5
limit $6),
end_statuses as (select array_agg(id) as ids
from match_statuses as ms
where (ms.description = any (array [
$7,
$8,
$9,
$10,
$11])) = $12),
max_closest_season as (select s.start_time as start_time
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
where ls.league_id = $1
and s.start_time <= $2
order by s.start_time desc
limit $13),
max_date as (select max(m.match_time) as md
from matches as m
where m.match_time <= $2
and m.match_time >= (select start_time from max_closest_season)
and m.league_id = $1
and (
m.status_id = any ((select ids from end_statuses)::bigint[]) or
($2 > now() and m.status_id = (select nsId from not_started_status))
))
select to_jsonb(mps.*) as stats,
to_jsonb(p.*) as player,
to_jsonb(ht.*) as home_team,
to_jsonb(at.*) as away_team,
m.home_score as home_score,
m.away_score as away_Score,
coalesce(htc.color, $14) as home_color,
coalesce(htc.secondary_color, $15) as home_secondary_color,
coalesce(atc.color, $16) as away_color,
coalesce(atc.secondary_color, $17) as away_secondary_color,
m.match_time as match_time,
mp.team_type as team,
m.round_num as round,
m.id as match_id,
m.external_id as match_external_id
from match_player_statistics as mps
left join match_players as mp on mp.id = mps.match_player_id
left join players as p on p.external_id = mp.player_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 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 seasons as s on s.external_id = m.season_id
where m.league_id = $1
and m.match_time <= (select md from max_date)
and mps.rating != $18
order by s.start_time desc, m.round_num desc, mps.rating desc
limit coalesce($3, $19) offset coalesce($4, $20) * coalesce($3, $21)
|
|
22 min
< 0.1%
|
145 ms
|
9,074
db_user
|
select m.*,
r.name as referee_name,
r.logo as referee_logo,
case
when s.mode = $3 then (select count(*) = $4
from matches as im
left join teams as iht on iht.external_id = im.home_team_id
left join teams as iat on iat.external_id = im.away_team_id
where im.stage_id = s.external_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time
limit $5)
else $6 end as knockout_stage
from matches as m
left join match_statuses as ms on ms.id = m.status_id
left join stages as s on s.external_id = m.stage_id
left join referees as r on r.external_id = m.referee_id
where (ms.description = $7 and match_time - interval $8 < now() and match_time > now())
or (ms.description = any (array [$9, $10, $11, $12, $13, $14]) and match_time < now() and match_time + interval $15 > now())
or (ms.description = $16 and ended_at is not null and ended_at + interval $17 > now())
or (ms.description = $18 and ended_at is null and match_time + interval $19 > now())
or (ms.description = any (array [$20, $21, $22, $23, $24]) and match_time > now() and match_time - interval $25 < now())
or (ms.description = any (array [$26, $27, $28, $29, $30]) and match_time < now() and match_time + interval $31 > now())
order by match_time
limit $1 offset $2 * $1
|
|
20 min
< 0.1%
|
105 ms
|
11,641
db_user
|
with current_statusses as (select array_agg(ms.id) as ids from match_statuses as ms where ms.id > $7 and ms.id < $8),
paginated_matches as (select m.*
from matches as m
join leagues as l on l.external_id = m.league_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
where ($1::timestamptz is not null or
(m.status_id = any (array [(select ids from current_statusses)]) and now() - m.match_time <= interval $9 and now() > m.match_time))
and (
($2::bigint[] is null and
$3::bigint[] is null and
$4::bigint[] is null) or
(($2::bigint[] is not null and l.id = any ($2::bigint[])) or
($3::bigint[] is not null and m.id = any ($3::bigint[])) or
($4::bigint[] is not null and ht.id = any ($4::bigint[])) or
($4::bigint[] is not null and at.id = any ($4::bigint[])))
)
and ($1::timestamptz is null or (m.match_time >= $1::timestamptz and m.match_time < $1::timestamptz + interval $10))
order by l.name
limit coalesce($5::bigint, $11) offset coalesce($5::bigint, $12) * coalesce($6::bigint, $13))
select l.*,
jsonb_agg(jsonb_build_object($14, to_jsonb(m.*) || jsonb_build_object($15, r.name,
$16, r.logo,
$17, case
when s.mode = $18 then exists(select $19
from matches as im
left join teams as iht on iht.external_id = im.home_team_id
left join teams as iat on iat.external_id = im.away_team_id
where im.stage_id = s.external_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time)
else $20 end),
$21, s.*,
$22, l.*,
$23, ht.*,
$24, at.*,
$25, (select jsonb_build_object(
$26, l.*,
$27, s.*,
$28, t.*,
$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
join seasons as s on s.external_id = ls.season_id
join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.id = ht.id
limit $32),
$33, (select jsonb_build_object(
$34, l.*,
$35, s.*,
$36, t.*,
$37, c.*,
$38, tc.color,
$39, 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
join seasons as s on s.external_id = ls.season_id
join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.id = at.id
limit $40)
) order by l.name, m.match_time
) as matches
from paginated_matches as m
join leagues as l on l.external_id = m.league_id
left join stages as s on s.external_id = m.stage_id
left join 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
group by l.id
order by l.name
|
|
18 min
< 0.1%
|
688 ms
|
1,565
db_user
|
select distinct t.id,
t.created_date,
t.updated_date,
t.external_updated_at,
t.player_id,
p.external_id as player_external_id,
p.name as player_name,
t.type,
t.transfer_time,
t.from_team_id as from_team_id,
t.from_team_id as from_team_external_id,
COALESCE(ft.name, t.from_team_name) as from_team_name,
t.to_team_id as to_team_id,
t.to_team_id as to_team_external_id,
COALESCE(tt.name, t.to_team_name) as to_team_name,
t.fee,
t.description
from transfers t
join players p on p.external_id = t.player_id
left join teams ft on ft.external_id = t.from_team_id
left join teams tt on tt.external_id = t.to_team_id
left join season_teams st on st.team_id = tt.external_id or st.team_id = ft.external_id
left join league_seasons ls on ls.id = st.league_season_id
where ls.league_id = $1
and ($2::timestamptz is null or t.transfer_time >= $2)
and ($3::timestamptz is null or t.transfer_time <= $3)
and deleted = $6
order by t.transfer_time desc
limit coalesce($4::bigint, $7) offset coalesce($5::bigint, $8) * coalesce($4::bigint, $9)
|
|
17 min
< 0.1%
|
315 ms
|
3,302
db_user
|
INSERT INTO matches (updated_date, external_updated_at, external_id, is_overtime, reversed, status_id, league_id, season_id, home_team_id, away_team_id, stage_id, group_num, round_num, match_time, neutral, home_score, away_score, home_half_time_score, away_half_time_score, home_over_time_score, away_over_time_score, home_penalty_score, away_penalty_score, home_red_cards, away_red_cards, home_yellow_cards, away_yellow_cards, home_corners, away_corners, home_position, away_position, tbd, details) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33) RETURNING id
|
|
16 min
< 0.1%
|
278 ms
|
3,402
db_user
|
select p.*
from players as p
where ($1::boolean is null or $1::boolean = $4 or p.uid is null)
order by p.id
limit coalesce($2::bigint, $5) offset coalesce($3::bigint, $6) * coalesce($2::bigint, $7)
|
|
15 min
< 0.1%
|
239 ms
|
3,769
db_user
|
with not_started_status as (select ms.id as nsId
from match_statuses as ms
where ms.description = $5
limit $6),
end_statuses as (select array_agg(id) as ids
from match_statuses as ms
where (ms.description = any (array [
$7,
$8,
$9,
$10,
$11])) = $12),
max_closest_season as (select s.start_time as start_time
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
where ls.league_id = $1
and s.start_time <= $2
order by s.start_time desc
limit $13),
max_round as (select max(round_num) as round
from matches as m
where m.match_time <= $2
and m.match_time >= (select start_time from max_closest_season)
and m.league_id = $1),
max_date as (select max(m.match_time) as md
from matches as m
where m.round_num = (select round from max_round)
and m.league_id = $1
and (
m.status_id = any ((select ids from end_statuses)::bigint[]) or
($2 > now() and m.status_id = (select nsId from not_started_status))
))
select to_jsonb(mps.*) as stats,
to_jsonb(p.*) as player,
to_jsonb(ht.*) as home_team,
to_jsonb(at.*) as away_team,
m.home_score as home_score,
m.away_score as away_Score,
coalesce(htc.color, $14) as home_color,
coalesce(htc.secondary_color, $15) as home_secondary_color,
coalesce(atc.color, $16) as away_color,
coalesce(atc.secondary_color, $17) as away_secondary_color,
m.match_time as match_time,
mp.team_type as team,
m.round_num as round,
m.id as match_id,
m.external_id as match_external_id
from match_player_statistics as mps
left join match_players as mp on mp.id = mps.match_player_id
left join players as p on p.external_id = mp.player_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 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 seasons as s on s.external_id = m.season_id
where m.league_id = $1
and m.match_time <= (select md from max_date)
and mps.rating != $18
order by s.start_time desc, m.round_num desc, mps.rating desc
limit coalesce($3, $19) offset coalesce($4, $20) * coalesce($3, $21)
|
|
14 min
< 0.1%
|
242 ms
|
3,431
db_user
|
with goal_types as (select array_agg(id) as ids
from technical_statistics
where description in ($5, $6)),
red_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($7, $8)),
yellow_card_types as (select array_agg(id) as ids
from technical_statistics
where description in ($9)),
max_closest_season as (select s.start_time as start_time,
s.external_id as season_id
from seasons as s
left join league_seasons as ls on ls.season_id = s.external_id
where ls.league_id = $1
and s.start_time <= $2
order by s.start_time desc
limit $10),
closes_match_date as (select m.match_time as mt
from matches as m
where m.season_id = (select season_id from max_closest_season)
and m.match_time <= $2
order by m.match_time desc
limit $11),
relevant_matches as (select m.external_id, m.match_time, m.home_team_id, m.away_team_id, m.is_overtime, m.round_num, m.season_id
from matches m
where m.match_time <= $2
and m.league_id = $1
and m.season_id = (select season_id from max_closest_season)),
player_incidents 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 != $12 then m.home_team_id else m.away_team_id end as team_id,
case when mi.team_type != $13 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 = $14 and mi.time::bigint = $15 then $16
when ts.description = $17 and mi.time::bigint = $18 then $19
when ts.description = $20 and mi.time::bigint = $21 then $22
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 relevant_matches as m on m.external_id = ml.match_id
join technical_statistics as ts on ts.id = mi.type
where mi.player_id is not null
and mi.deleted = $23),
player_statistics as (select mi.player_id,
count(distinct mi.*) filter ( where type = any (array [(select ids from goal_types)]) ) as cumulative_goals,
count(distinct mi.*) filter ( where type = any (array [(select ids from red_card_types)]) ) as cumulative_red_cards,
count(distinct mi.*) filter ( where type = any (array [(select ids from yellow_card_types)]) ) as cumulative_yellow_cards
from match_incidents as mi
left join match_lineups as ml on ml.id = mi.match_lineup
left join matches as m on m.external_id = ml.match_id
left join seasons as s on s.external_id = m.season_id
where mi.deleted = $24
and m.match_time < (select mt from closes_match_date)
and s.external_id = (select season_id from max_closest_season)
and mi.player_id = any (select mi.player_id from player_incidents as mi)
group by mi.player_id),
player_assists as (select coalesce(mi.assist1_id, mi.assist2_id) as player_id,
count(distinct mi.*) as cumulative_assists
from match_incidents as mi
left join match_lineups as ml on ml.id = mi.match_lineup
left join matches as m on m.external_id = ml.match_id
left join seasons as s on s.external_id = m.season_id
where mi.deleted = $25
and m.match_time < (select mt from closes_match_date)
and s.external_id = (select season_id from max_closest_season)
and coalesce(mi.assist1_id, mi.assist2_id) = any (select mi.player_id from player_incidents as mi)
and mi.type = any (array [(select ids from goal_types)])
group by coalesce(mi.assist1_id, mi.assist2_id))
select jsonb_build_object(
$26, jsonb_build_object($27, p_i.match_time,
$28, p_i.*,
$29, coalesce(to_jsonb(p.*), to_jsonb(c.*)),
$30, sp.*,
$31, t.*,
$32, st.*,
$33, (select jsonb_build_object($34, coalesce(ps.cumulative_goals, $35),
$36, coalesce(pa.cumulative_assists, $37),
$38, coalesce(ps.cumulative_yellow_cards, $39),
$40, coalesce(ps.cumulative_red_cards, $41))),
$42, (select jsonb_build_object(
$43, l.*,
$44, s.*,
$45, t.*,
$46, c.*,
$47, tc.color,
$48, tc.secondary_color)
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.external_id = p_i.team_id
limit $49),
$50, (select jsonb_build_object(
$51, l.*,
$52, s.*,
$53, t.*,
$54, c.*,
$55, tc.color,
$56, tc.secondary_color)
from teams as t
left join season_teams as st on st.team_id = t.external_id
|
|
11 min
< 0.1%
|
109 ms
|
6,226
db_user
|
with current_statusses as (select array_agg(ms.id) as ids from match_statuses as ms where ms.id > $7 and ms.id < $8),
paginated_matches as (select m.*
from matches as m
join leagues as l on l.external_id = m.league_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
where ($1::timestamptz is not null or
(m.status_id = any (array [(select ids from current_statusses)]) and now() - m.match_time <= interval $9 and now() > m.match_time))
and (
($2::bigint[] is null and
$3::bigint[] is null and
$4::bigint[] is null) or
(($2::bigint[] is not null and l.id = any ($2::bigint[])) or
($3::bigint[] is not null and m.id = any ($3::bigint[])) or
($4::bigint[] is not null and ht.id = any ($4::bigint[])) or
($4::bigint[] is not null and at.id = any ($4::bigint[])))
)
and ($1::timestamptz is null or (m.match_time >= $1::timestamptz and m.match_time < $1::timestamptz + interval $10))
order by l.name
limit coalesce($5::bigint, $11) offset coalesce($5::bigint, $12) * coalesce($6::bigint, $13))
select l.*,
jsonb_agg(jsonb_build_object($14, to_jsonb(m.*) || jsonb_build_object($15, r.name,
$16, r.logo,
$17, case
when s.mode = $18 then exists(select $19
from matches as im
left join teams as iht on iht.external_id = im.home_team_id
left join teams as iat on iat.external_id = im.away_team_id
where im.stage_id = s.external_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time)
else $20 end),
$21, s.*,
$22, l.*,
$23, ht.*,
$24, at.*,
$25, (select jsonb_build_object(
$26, l.*,
$27, s.*,
$28, t.*,
$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
join seasons as s on s.external_id = ls.season_id
join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.id = ht.id
limit $32),
$33, (select jsonb_build_object(
$34, l.*,
$35, s.*,
$36, t.*,
$37, c.*,
$38, tc.color,
$39, 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
join seasons as s on s.external_id = ls.season_id
join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.id = at.id
limit $40)
) order by l.name, m.match_time
) as matches
from paginated_matches as m
join leagues as l on l.external_id = m.league_id
left join stages as s on s.external_id = m.stage_id
left join 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
group by l.id, l.name
order by l.name
|
|
11 min
< 0.1%
|
988 ms
|
665
db_user
|
INSERT INTO matches (updated_date, external_updated_at, external_id, is_overtime, reversed, status_id, league_id, season_id, home_team_id, away_team_id, stage_id, group_num, round_num, match_time, ended_at, neutral, home_score, away_score, home_half_time_score, away_half_time_score, home_over_time_score, away_over_time_score, home_penalty_score, away_penalty_score, home_red_cards, away_red_cards, home_yellow_cards, away_yellow_cards, home_corners, away_corners, home_position, away_position, weather, pressure, temperature, wind, humidity, tbd, details) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39) RETURNING id
|