|
14,667 min
49%
|
142 ms
|
6,182,198
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
|
|
3,137 min
10%
|
2 ms
|
83,589,359
db_user
|
select st.*
from season_teams as st
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
where st.team_id = $1
and (s.year = $2 or s.year = $3)
|
|
1,984 min
7%
|
1 ms
|
134,614,391
db_user
|
select st.id as id,
s.end_time as end_date,
s.start_time as start_date,
(select ins.start_time
from league_seasons as ils
left join leagues as il on il.external_id = ils.league_id
left join seasons as ins on ins.external_id = ils.season_id
where il.external_id = l.external_id
and ins.end_time < s.start_time
order by ins.end_time desc
limit $4) as previous_start_date,
(select ins.end_time
from league_seasons as ils
left join leagues as il on il.external_id = ils.league_id
left join seasons as ins on ins.external_id = ils.season_id
where il.external_id = l.external_id
and ins.end_time <= s.start_time
order by ins.end_time desc
limit $5) as previous_end_date
from season_teams as st
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
where st.team_id = $1
and l.type = $6
and s.end_time >= $2
and (
($3 is null or s.end_time <= $3) or
($3 is not null and
$3::timestamptz - $2::timestamptz < interval $7 and
s.end_time <= $3::timestamptz + interval $8)
)
order by s.end_time
|
|
699 min
2%
|
139 ms
|
302,022
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,237
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
|
|
352 min
1%
|
0 ms
|
1,396,417,780
db_user
|
UPDATE match_statistics SET updated_date = $1, match_id = $2, type = $3, home = $4, away = $5 WHERE match_statistics.id = $6
Covered by index on (id)
Rows: 1409980
Row progression: 1409980, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- match_id, type UNIQUE
|
|
350 min
1%
|
0 ms
|
1,347,405,554
db_user
|
UPDATE transfers SET updated_date = $1, external_updated_at = $2, player_id = $3, type = $4, transfer_time = $5, from_team_id = $6, from_team_name = $7, to_team_id = $8, to_team_name = $9, fee = $10, description = $11, deleted = $12 WHERE transfers.id = $13
Covered by index on (id)
Rows: 2165085
Row progression: 2165085, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- deleted, transfer_time
- from_team_id, deleted, transfer_time DESC
- player_id
- to_team_id, deleted, transfer_time DESC
- transfer_time
|
|
349 min
1%
|
0 ms
|
1,397,312,814
db_user
|
SELECT match_statistics.id, match_statistics.created_date, match_statistics.updated_date, match_statistics.match_id, match_statistics.type, match_statistics.home, match_statistics.away FROM match_statistics WHERE match_statistics.match_id = $1 AND (match_statistics.type = $2)
Covered by index on (match_id, type)
Rows: 1409980
Row progression: 1409980, 9
Row estimates
- match_id (=): 9
- type (=): 140998
Existing indexes
- id PRIMARY
- match_id, type UNIQUE
|
|
312 min
1%
|
0 ms
|
748,012,355
db_user
|
select m.*,
r.name as referee_name,
r.logo as referee_logo,
case
when s.mode = $2 then (select count(*) = $3
from matches as im
left join teams as iht on iht.external_id = im.home_team_id
left join teams as iat on iat.external_id = im.away_team_id
where im.stage_id = s.external_id
and im.home_team_id = m.away_team_id
and im.away_team_id = m.home_team_id
and im.match_time < m.match_time
limit $4)
else $5 end as knockout_stage
from matches as m
left join stages as s on s.external_id = m.stage_id
left join referees as r on r.external_id = m.referee_id
where m.external_id = $1
limit $6
|
|
259 min
0.9%
|
0 ms
|
1,237,507,528
db_user
|
UPDATE stages SET updated_date = $1, external_updated_at = $2, external_id = $3, season_id = $4, name = $5, searchable_name = $6, mode = $7, group_count = $8, round_count = $9, stage_order = $10 WHERE stages.id = $11
Covered by index on (id)
Rows: 41395
Row progression: 41395, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- season_id
- stage_order
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
251 min
0.8%
|
25 ms
|
592,283
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
|
|
228 min
0.8%
|
0 ms
|
309,717,685
db_user
|
UPDATE matches SET updated_date = $1, external_updated_at = $2, external_id = $3, is_overtime = $4, reversed = $5, status_id = $6, league_id = $7, season_id = $8, home_team_id = $9, away_team_id = $10, stage_id = $11, venue_id = $12, referee_id = $13, group_num = $14, round_num = $15, match_time = $16, ended_at = $17, neutral = $18, note = $19, home_score = $20, away_score = $21, home_half_time_score = $22, away_half_time_score = $23, home_over_time_score = $24, away_over_time_score = $25, home_penalty_score = $26, away_penalty_score = $27, home_agg_score = $28, away_agg_score = $29, home_red_cards = $30, away_red_cards = $31, home_yellow_cards = $32, away_yellow_cards = $33, home_corners = $34, away_corners = $35, home_position = $36, away_position = $37, related_id = $38, weather = $39, pressure = $40, temperature = $41, wind = $42, humidity = $43, tbd = $44, details = $45, kickoff_time = $46, last_goal_date = $47 WHERE matches.id = $48
Covered by index on (id)
Rows: 291411
Row progression: 291411, 1
Row estimates
- 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
|
|
196 min
0.7%
|
2 ms
|
7,660,740
db_user
|
select to_jsonb(l.*) as league,
to_jsonb(s.*) as season,
to_jsonb(t.*) as team,
to_jsonb(c.*) as coach,
tc.color as team_color,
tc.secondary_color as team_secondary_color,
stt.position as position,
to_jsonb(coun.*) as country
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
left join standings as sta on sta.season_id = ls.season_id
left join standings_stages as sts on sts.standings_id = sta.id
left join standings_teams as stt on stt.standings_stage_id = sts.external_id and stt.team_id = t.external_id
left join countries as coun on coun.external_id = t.country_id
where t.external_id = $1
and l.type = $2
order by s.end_time desc
limit $3
|
|
194 min
0.6%
|
6 ms
|
1,816,851
db_user
|
with lineup as (select ml.id as lineup_id,
m.home_team_id as home_team_id,
m.away_team_id as away_team_id,
m.is_overtime as is_overtime,
m.match_time as match_time,
m.league_id as league_id,
m.season_id as season_id,
(select jsonb_build_object(
$2, l.*,
$3, s.*,
$4, t.*,
$5, c.*,
$6, tc.color,
$7, tc.secondary_color
)
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.external_id = m.home_team_id
and l.id is not null
limit $8) as home_team_details,
(select jsonb_build_object(
$9, l.*,
$10, s.*,
$11, t.*,
$12, c.*,
$13, tc.color,
$14, tc.secondary_color
)
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.external_id = m.away_team_id
and l.id is not null
limit $15) as away_team_details
from matches as m
join match_lineups as ml on ml.match_id = m.external_id
where m.id = $1
limit $16),
goal_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description in ($17, $18)),
yellow_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description = $19),
red_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description in ($20, $21)),
extra_types as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description = any (array [$22,$23,$24])),
player_ids as (select array_agg(mp.player_id) as ids
from match_players as mp
where mp.match_lineup = (select lineup_id from lineup)),
player_stats as (select imi.player_id,
sum(case when imi.type = any ((select ids from goal_type)::bigint[]) then $25 else $26 end) as goals,
sum(case when imi.type = any ((select ids from yellow_card_type)::bigint[]) then $27 else $28 end) as yellow_cards,
sum(case when imi.type = any ((select ids from red_card_type)::bigint[]) then $29 else $30 end) as red_cards,
imi.assist1_id as assist_id
from match_incidents as imi
join match_lineups as iml on iml.id = imi.match_lineup
join matches as im on im.external_id = iml.match_id
where im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $31
and imi.player_id = any ((select ids from player_ids)::text[])
and (imi.type = any ((select ids from yellow_card_type)::bigint[])
or imi.type = any ((select ids from red_card_type)::bigint[])
or imi.type = any ((select ids from goal_type)::bigint[]))
group by imi.player_id, imi.assist1_id),
assist_stats as (select assist_id,
count(*) as assists
from player_stats
where assist_id is not null
group by assist_id),
aggregated_stats as (select player_id,
sum(goals) as total_goals,
sum(yellow_cards) as total_yellow_cards,
sum(red_cards) as total_red_cards
from player_stats
group by player_id)
select l.is_overtime as is_overtime,
l.match_time as match_time,
mi.time as time,
mi.addtime as addtime,
ts.description as description,
to_jsonb(mi.*) as incident,
case when p.id is not null then to_jsonb(p.*) else to_jsonb(c.*) end as player,
to_jsonb(sp.*) as secondary_player,
to_jsonb(t.*) as team,
to_jsonb(st.*) as secondary_team,
case
when mi.team_type = $32 then l.home_team_details
else l.away_team_details
end as team_details,
case
when mi.team_type = $33 then l.away_team_details
else l.home_team_details
end as secondary_team_details,
jsonb_build_object(
$34, coalesce(agg.total_goals, $35),
$36, coalesce(ast.assists, $37),
$38, coalesce(agg.total_yellow_cards, $39),
$40, coalesce(agg.total_red_cards, $41)
) as season_statistic
from match_incidents as mi
left join lineup as l on mi.match_lineup = l.lineup_id
left join technical_statistics as ts on ts.id = mi.type
left join teams as t on t.external_id = case
when mi.team_type = $42 then l.home_team_id
else l.away_team_id
end
left join teams as st on st.external_id = case
when mi.team_type = $43 then l.away_team_id
else l.home_team_id
end
left join coaches as c on c.external_id = mi.player_id
left join players as p on p.external_id = mi.player_id
left join players as sp on sp.external_id = coalesce(mi.assist1_id, mi.out_player_id)
left join aggregated_stats as agg on agg.player_id = mi.player_id
left join assist_stats as ast on ast.assist_id = mi.assist1_id
where mi.match_lineup = l.lineup_id
and (mi.player_id is not null or mi.type = any ((select ids from extra_types)::bigint[]))
and l.home_team_details is not null
and l.away_team_details is not null
and mi.deleted = $44
order by case
when ts.description = $45 and mi.time::bigint = $46 then $47
when ts.description = $48 and mi.time::bigint = $49 then $50
when ts.description = $51 and mi.time::bigint = $52 then $53
when ts.description = $54 then
|
|
189 min
0.6%
|
0 ms
|
1,250,030,367
db_user
|
select *
from stages
where external_id = $1
limit $2
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
|
|
172 min
0.6%
|
0 ms
|
365,504,291
db_user
|
UPDATE match_trends SET updated_date = $1, match_id = $2, half_number = $3, minute = $4, trend = $5 WHERE match_trends.id = $6
Covered by index on (id)
Rows: 11911272
Row progression: 11911272, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- match_id, half_number, minute UNIQUE
|
|
161 min
0.5%
|
0 ms
|
126,989,908
db_user
|
select *
from match_incidents as mi
left join match_lineups as ml on ml.id = mi.match_lineup
left join matches as m on m.external_id = ml.match_id
where m.id = $1
and mi.deleted = $2
order by mi.time::bigint desc, mi.addtime
|
|
158 min
0.5%
|
1 ms
|
10,020,730
db_user
|
select m.*
from matches as m
where m.id = any($1::bigint[])
Covered by index on (id)
Rows: 291411
Row progression: 291411, 1
Row estimates
- 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
|
|
146 min
0.5%
|
1 ms
|
7,170,061
db_user
|
select st.id as id,
s.end_time as end_date,
s.start_time as start_date,
(select ins.start_time
from league_seasons as ils
left join leagues as il on il.external_id = ils.league_id
left join seasons as ins on ins.external_id = ils.season_id
where il.external_id = l.external_id
and ins.end_time < s.start_time
order by ins.end_time desc
limit $4) as previous_start_date,
(select ins.end_time
from league_seasons as ils
left join leagues as il on il.external_id = ils.league_id
left join seasons as ins on ins.external_id = ils.season_id
where il.external_id = l.external_id
and ins.end_time <= s.start_time
order by ins.end_time desc
limit $5) as previous_end_date
from season_teams as st
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
where st.team_id = $1
and s.end_time >= $2
and (
($3 is null or s.end_time <= $3) or
($3 is not null and
$3::timestamptz - $2::timestamptz < interval $6 and
s.end_time <= $3::timestamptz + interval $7)
)
order by s.end_time
|
|
145 min
0.5%
|
4 ms
|
2,254,920
db_user
|
select tp.*
from team_players as tp
left join season_teams as st on st.id = tp.season_team_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
where tp.player_id = any ($1::text[])
order by tp.arrival_date
|
|
143 min
0.5%
|
0 ms
|
726,754,279
db_user
|
UPDATE league_seasons SET updated_date = $1, league_id = $2, season_id = $3, is_current = $4 WHERE league_seasons.id = $5
Covered by index on (id)
Rows: 11611
Row progression: 11611, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- league_id, season_id
- league_id, season_id UNIQUE
- league_id WHERE is_current = true
- season_id UNIQUE
|
|
140 min
0.5%
|
0 ms
|
441,795,086
db_user
|
select *
from match_trends
where match_id = $1
and half_number = $2
and minute = $3
limit $4
Details
CREATE INDEX CONCURRENTLY ON match_trends (match_id, minute)
Rows: 11911272
Row progression: 11911272, 98, 1
Row estimates
- match_id (=): 98
- minute (=): 154692
- half_number (=): 5955636
Existing indexes
- id PRIMARY
- match_id, half_number, minute UNIQUE
|
|
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
|
|
124 min
0.4%
|
0 ms
|
556,389,512
db_user
|
UPDATE seasons SET updated_date = $1, external_updated_at = $2, external_id = $3, year = $4, has_player_stats = $5, has_team_stats = $6, has_table = $7, is_current = $8, start_time = $9, end_time = $10 WHERE seasons.id = $11
Covered by index on (id)
Rows: 11612
Row progression: 11612, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE INDEX idx_seasons_external_id_current ON public.seasons USING btree (external_id) WHERE is_current
- external_id UNIQUE
- external_id, year
- uid
|
|
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
|
|
113 min
0.4%
|
0 ms
|
809,296,193
db_user
|
select *
from league_seasons
where league_id = $1
and season_id = $2
limit $3
Covered by index on (season_id)
Rows: 11611
Row progression: 11611, 1
Row estimates
- season_id (=): 1
- league_id (=): 9
Existing indexes
- id PRIMARY
- league_id, season_id
- league_id, season_id UNIQUE
- league_id WHERE is_current = true
- season_id UNIQUE
|
|
111 min
0.4%
|
0 ms
|
253,216,996
db_user
|
SELECT season_player_statistics.id, season_player_statistics.created_date, season_player_statistics.updated_date, season_player_statistics.external_updated_at, season_player_statistics.season_id, season_player_statistics.player_id, season_player_statistics.team_id, season_player_statistics.matches, season_player_statistics.court, season_player_statistics.first, season_player_statistics.goals, season_player_statistics.penalty, season_player_statistics.assists, season_player_statistics.minutes_played, season_player_statistics.red_cards, season_player_statistics.yellow_cards, season_player_statistics.shots, season_player_statistics.shots_on_target, season_player_statistics.dribble, season_player_statistics.dribble_succ, season_player_statistics.clearances, season_player_statistics.blocked_shots, season_player_statistics.interceptions, season_player_statistics.tackles, season_player_statistics.passes, season_player_statistics.passes_accuracy, season_player_statistics.key_passes, season_player_statistics.crosses, season_player_statistics.crosses_accuracy, season_player_statistics.long_balls, season_player_statistics.long_balls_accuracy, season_player_statistics.duels, season_player_statistics.duels_won, season_player_statistics.dispossessed, season_player_statistics.fouls, season_player_statistics.was_fouled, season_player_statistics.offsides, season_player_statistics.yellow2_red_cards, season_player_statistics.saves, season_player_statistics.punches, season_player_statistics.runs_out, season_player_statistics.runs_out_succ, season_player_statistics.good_high_claim, season_player_statistics.rating, season_player_statistics.freekicks, season_player_statistics.freekick_goals, season_player_statistics.hit_woodwork, season_player_statistics.fastbreaks, season_player_statistics.fastbreak_shots, season_player_statistics.fastbreak_goals, season_player_statistics.poss_losts, season_player_statistics.big_chance_created, season_player_statistics.big_chance_missed FROM season_player_statistics WHERE season_player_statistics.season_id = $1 AND (season_player_statistics.player_id = $2) AND (season_player_statistics.team_id = $3)
Covered by index on (player_id, season_id)
Rows: 700376
Row progression: 700376, 4
Row estimates
- player_id (=): 4
- team_id (=): 93
- season_id (=): 620
Existing indexes
- id PRIMARY
- player_id, season_id
- season_id, player_id, team_id UNIQUE
- season_id, rating
|
|
107 min
0.4%
|
21 ms
|
302,032
db_user
|
delete
from matches
where external_id = any ($1::text[])
Covered by index on (external_id)
Rows: 291411
Row progression: 291411, 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
|
|
105 min
0.3%
|
0 ms
|
466,229,725
db_user
|
select *
from player_abilities
where player_id = $1
and ability = $2
limit $3
Covered by index on (player_id, ability)
Rows: 215276
Row progression: 215276, 5
Row estimates
- player_id (=): 5
- ability (=): 23920
Existing indexes
- id PRIMARY
- player_id, ability UNIQUE
|
|
105 min
0.3%
|
0 ms
|
638,779,542
db_user
|
select *
from seasons
where external_id = $1
limit $2
Covered by index on (external_id)
Rows: 11612
Row progression: 11612, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- CREATE INDEX idx_seasons_external_id_current ON public.seasons USING btree (external_id) WHERE is_current
- external_id UNIQUE
- external_id, year
- uid
|
|
93 min
0.3%
|
5 ms
|
1,217,900
db_user
|
select st.*
from season_teams as st
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
where st.team_id = any($1::text[])
and ($2 is null or ls.is_current = $2)
and s.end_time < now()
|
|
92 min
0.3%
|
0 ms
|
240,880,610
db_user
|
UPDATE match_players SET updated_date = $1, match_lineup = $2, player_id = $3, team_type = $4, first = $5, captain = $6, name = $7, logo = $8, shirt_number = $9, position = $10, x = $11, y = $12, rating = $13 WHERE match_players.id = $14
Covered by index on (id)
Rows: 3573135
Row progression: 3573135, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- match_lineup, player_id UNIQUE
- match_lineup) INCLUDE (id, player_id, team_type
|
|
90 min
0.3%
|
0 ms
|
466,124,392
db_user
|
UPDATE player_abilities SET updated_date = $1, player_id = $2, ability = $3, rating = $4, average = $5 WHERE player_abilities.id = $6
Covered by index on (id)
Rows: 215276
Row progression: 215276, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- player_id, ability UNIQUE
|
|
83 min
0.3%
|
0 ms
|
253,103,355
db_user
|
UPDATE season_player_statistics SET updated_date = $1, external_updated_at = $2, season_id = $3, player_id = $4, team_id = $5, matches = $6, court = $7, first = $8, goals = $9, penalty = $10, assists = $11, minutes_played = $12, red_cards = $13, yellow_cards = $14, shots = $15, shots_on_target = $16, dribble = $17, dribble_succ = $18, clearances = $19, blocked_shots = $20, interceptions = $21, tackles = $22, passes = $23, passes_accuracy = $24, key_passes = $25, crosses = $26, crosses_accuracy = $27, long_balls = $28, long_balls_accuracy = $29, duels = $30, duels_won = $31, dispossessed = $32, fouls = $33, was_fouled = $34, offsides = $35, yellow2_red_cards = $36, saves = $37, punches = $38, runs_out = $39, runs_out_succ = $40, good_high_claim = $41, rating = $42, freekicks = $43, freekick_goals = $44, hit_woodwork = $45, fastbreaks = $46, fastbreak_shots = $47, fastbreak_goals = $48, poss_losts = $49, big_chance_created = $50, big_chance_missed = $51 WHERE season_player_statistics.id = $52
Covered by index on (id)
Rows: 700376
Row progression: 700376, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- player_id, season_id
- season_id, player_id, team_id UNIQUE
- season_id, rating
|
|
81 min
0.3%
|
4 ms
|
1,304,148
db_user
|
select t.*
from transfers as t
where player_id = any ($1::text[])
and t.deleted = $2
order by t.transfer_time
Covered by index on (player_id)
Rows: 2165085
Row progression: 2165085, 12
Row estimates
- player_id (=): 12
- 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
|
|
78 min
0.3%
|
0 ms
|
204,919,193
db_user
|
UPDATE players SET updated_date = $1, external_updated_at = $2, external_id = $3, cur_team_id = $4, cur_country_id = $5, status = $6, name = $7, short_name = $8, searchable_name = $9, logo = $10, national_logo = $11, nationality = $12, age = $13, birthday = $14, weight = $15, height = $16, market_value = $17, market_value_currency = $18, contract_until = $19, position = $20, main_position = $21, preferred_foot = $22, deathday = $23, uid = $24 WHERE players.id = $25
Covered by index on (id)
Rows: 1269086
Row progression: 1269086, 1
Row estimates
- id (=): 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
|
|
77 min
0.3%
|
0 ms
|
111,751,306
db_user
|
insert into match_status_tracker(match_id, old_status_id, new_status_id, changed_at)
values (new.id, old.status_id, new.status_id, now())
|
|
75 min
0.3%
|
0 ms
|
88,290,131
db_user
|
SELECT $2 FROM ONLY "public"."matches" x WHERE "external_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
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)
|
|
69 min
0.2%
|
0 ms
|
33,045,490
db_user
|
INSERT INTO team_players (updated_date, player_id, season_team_id, altered) VALUES ($1, $2, $3, $4) RETURNING id
|
|
68 min
0.2%
|
0 ms
|
163,139,611
db_user
|
SELECT season_player_statistics.id, season_player_statistics.created_date, season_player_statistics.updated_date, season_player_statistics.external_updated_at, season_player_statistics.season_id, season_player_statistics.player_id, season_player_statistics.team_id, season_player_statistics.matches, season_player_statistics.court, season_player_statistics.first, season_player_statistics.goals, season_player_statistics.penalty, season_player_statistics.assists, season_player_statistics.minutes_played, season_player_statistics.red_cards, season_player_statistics.yellow_cards, season_player_statistics.shots, season_player_statistics.shots_on_target, season_player_statistics.dribble, season_player_statistics.dribble_succ, season_player_statistics.clearances, season_player_statistics.blocked_shots, season_player_statistics.interceptions, season_player_statistics.tackles, season_player_statistics.passes, season_player_statistics.passes_accuracy, season_player_statistics.key_passes, season_player_statistics.crosses, season_player_statistics.crosses_accuracy, season_player_statistics.long_balls, season_player_statistics.long_balls_accuracy, season_player_statistics.duels, season_player_statistics.duels_won, season_player_statistics.dispossessed, season_player_statistics.fouls, season_player_statistics.was_fouled, season_player_statistics.offsides, season_player_statistics.yellow2_red_cards, season_player_statistics.saves, season_player_statistics.punches, season_player_statistics.runs_out, season_player_statistics.runs_out_succ, season_player_statistics.good_high_claim, season_player_statistics.rating, season_player_statistics.freekicks, season_player_statistics.freekick_goals, season_player_statistics.hit_woodwork, season_player_statistics.fastbreaks, season_player_statistics.fastbreak_shots, season_player_statistics.fastbreak_goals, season_player_statistics.poss_losts FROM season_player_statistics WHERE season_player_statistics.season_id = $1 AND (season_player_statistics.player_id = $2) AND (season_player_statistics.team_id = $3)
Covered by index on (player_id, season_id)
Rows: 700376
Row progression: 700376, 4
Row estimates
- player_id (=): 4
- team_id (=): 93
- season_id (=): 620
Existing indexes
- id PRIMARY
- player_id, season_id
- season_id, player_id, team_id UNIQUE
- season_id, rating
|
|
67 min
0.2%
|
0 ms
|
209,641,466
db_user
|
select *
from players
where external_id = $1
limit $2
Covered by index on (external_id)
Rows: 1269086
Row progression: 1269086, 1
Row estimates
- external_id (=): 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
|
|
57 min
0.2%
|
0 ms
|
240,882,776
db_user
|
SELECT match_players.id, match_players.created_date, match_players.updated_date, match_players.match_lineup, match_players.player_id, match_players.team_type, match_players.first, match_players.captain, match_players.name, match_players.logo, match_players.shirt_number, match_players.position, match_players.x, match_players.y, match_players.rating FROM match_players WHERE match_players.match_lineup = $1 AND (match_players.player_id = $2)
Details
CREATE INDEX CONCURRENTLY ON match_players (player_id)
Rows: 3573135
Row progression: 3573135, 31
Row estimates
- player_id (=): 31
- match_lineup (=): 42
Existing indexes
- id PRIMARY
- match_lineup, player_id UNIQUE
- match_lineup) INCLUDE (id, player_id, team_type
|
|
56 min
0.2%
|
0 ms
|
14,336,821
db_user
|
INSERT INTO team_players (updated_date, player_id, season_team_id, custom_arrival_date, altered) VALUES ($1, $2, $3, $4, $5) RETURNING id
|
|
55 min
0.2%
|
0 ms
|
285,329,359
db_user
|
select *
from player_characteristics
where player_id = $1
and type = $2
and characteristics_type = $3
limit $4
Covered by index on (player_id, type, characteristics_type)
Rows: 170455
Row progression: 170455, 5
Row estimates
- player_id (=): 5
- type (=): 4058
- characteristics_type (=): 85228
Existing indexes
- id PRIMARY
- player_id, type, characteristics_type UNIQUE
|
|
55 min
0.2%
|
82 ms
|
40,034
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%
|
0 ms
|
162,280,825
db_user
|
UPDATE season_player_statistics SET updated_date = $1, external_updated_at = $2, season_id = $3, player_id = $4, team_id = $5, matches = $6, court = $7, first = $8, goals = $9, penalty = $10, assists = $11, minutes_played = $12, red_cards = $13, yellow_cards = $14, shots = $15, shots_on_target = $16, dribble = $17, dribble_succ = $18, clearances = $19, blocked_shots = $20, interceptions = $21, tackles = $22, passes = $23, passes_accuracy = $24, key_passes = $25, crosses = $26, crosses_accuracy = $27, long_balls = $28, long_balls_accuracy = $29, duels = $30, duels_won = $31, dispossessed = $32, fouls = $33, was_fouled = $34, offsides = $35, yellow2_red_cards = $36, saves = $37, punches = $38, runs_out = $39, runs_out_succ = $40, good_high_claim = $41, rating = $42, freekicks = $43, freekick_goals = $44, hit_woodwork = $45, fastbreaks = $46, fastbreak_shots = $47, fastbreak_goals = $48, poss_losts = $49 WHERE season_player_statistics.id = $50
Covered by index on (id)
Rows: 700376
Row progression: 700376, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- player_id, season_id
- season_id, player_id, team_id UNIQUE
- season_id, rating
|
|
53 min
0.2%
|
0 ms
|
415,928,637
db_user
|
SELECT season_teams.id, season_teams.created_date, season_teams.updated_date, season_teams.league_season_id, season_teams.team_id FROM season_teams WHERE season_teams.league_season_id = $1 AND (season_teams.team_id = $2)
Details
CREATE INDEX CONCURRENTLY ON season_teams (team_id)
Rows: 73795
Row progression: 73795, 3
Row estimates
- team_id (=): 3
- league_season_id (=): 39
Existing indexes
- id PRIMARY
- league_season_id, team_id UNIQUE
|
|
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
|
|
52 min
0.2%
|
0 ms
|
285,171,800
db_user
|
UPDATE player_characteristics SET updated_date = $1, player_id = $2, type = $3, ranking = $4, characteristics_type = $5 WHERE player_characteristics.id = $6
Covered by index on (id)
Rows: 170455
Row progression: 170455, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- player_id, type, characteristics_type UNIQUE
|
|
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.2%
|
0 ms
|
75,135,923
db_user
|
INSERT INTO transfers (updated_date, external_updated_at, player_id, type, transfer_time, from_team_id, from_team_name, to_team_id, to_team_name, fee, description) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING id
|
|
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%
|
0 ms
|
12,922,196
db_user
|
select *
from teams
where external_id = any ($1::text[])
Covered by index on (external_id)
Rows: 96839
Row progression: 96839, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- name
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
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
|
|
37 min
0.1%
|
0 ms
|
246,067,283
db_user
|
SELECT $2 FROM ONLY "public"."players" x WHERE "external_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
35 min
0.1%
|
0 ms
|
162,729,989
db_user
|
UPDATE live_standings_teams SET updated_date = $1, external_updated_at = $2, standings_stage_id = $3, team_id = $4, promotion_id = $5, note = $6, points = $7, position = $8, deduct_points = $9, total = $10, won = $11, draw = $12, loss = $13, goals = $14, goals_against = $15, goal_diff = $16, home_points = $17, home_position = $18, home_total = $19, home_won = $20, home_draw = $21, home_loss = $22, home_goals = $23, home_goals_against = $24, home_goal_diff = $25, away_points = $26, away_position = $27, away_total = $28, away_won = $29, away_draw = $30, away_loss = $31, away_goals = $32, away_goals_against = $33, away_goal_diff = $34 WHERE live_standings_teams.id = $35
Covered by index on (id)
Rows: 13033
Row progression: 13033, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- team_id, standings_stage_id UNIQUE
|
|
35 min
0.1%
|
3 ms
|
610,397
db_user
|
with
list1_with_ord as (
select *, row_number() over () as ordinality
from unnest($1::bigint[]) as list1
),
list2_with_ord AS (
select *, row_number() over () as ordinality
from unnest($2::text[]) as list2
),
my_array as (
select array_agg((list1_with_ord.list1, list2_with_ord.list2)) as combined_list
from list1_with_ord
join list2_with_ord
on list1_with_ord.ordinality = list2_with_ord.ordinality
)
select tp.*
from team_players as tp
join lateral (
select elem.*
from unnest((select combined_list from my_array)) as elem (season_team_id bigint, player_id text)
) as a on tp.season_team_id = a.season_team_id and tp.player_id = a.player_id
|
|
34 min
0.1%
|
0 ms
|
99,692,921
db_user
|
UPDATE match_player_statistics SET updated_date = $1, match_player_id = $2, first = $3, goals = $4, penalty = $5, assists = $6, minutes_played = $7, red_cards = $8, yellow_cards = $9, shots = $10, shots_on_target = $11, dribble = $12, dribble_succ = $13, clearances = $14, blocked_shots = $15, interceptions = $16, tackles = $17, passes = $18, passes_accuracy = $19, key_passes = $20, crosses = $21, crosses_accuracy = $22, long_balls = $23, long_balls_accuracy = $24, duels = $25, duels_won = $26, dispossessed = $27, fouls = $28, was_fouled = $29, offsides = $30, yellow2red_cards = $31, saves = $32, punches = $33, runs_out = $34, runs_out_succ = $35, good_high_claim = $36, rating = $37, freekicks = $38, freekick_goals = $39, hit_woodwork = $40, fastbreaks = $41, fastbreak_shots = $42, fastbreak_goals = $43, poss_losts = $44 WHERE match_player_statistics.id = $45
Covered by index on (id)
Rows: 1601182
Row progression: 1601182, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- match_player_id UNIQUE
- match_player_id, rating WHERE rating > (0)::numeric
|
|
32 min
0.1%
|
0 ms
|
143,989,264
db_user
|
SELECT match_lineups.id, match_lineups.created_date, match_lineups.updated_date, match_lineups.match_id, match_lineups.home_coach_id, match_lineups.away_coach_id, match_lineups.confirmed, match_lineups.home_formation, match_lineups.away_formation FROM match_lineups WHERE match_lineups.match_id = $1
Covered by index on (match_id)
Rows: 168423
Row progression: 168423, 1
Row estimates
- match_id (=): 1
Existing indexes
- id PRIMARY
- match_id UNIQUE
|
|
30 min
0.1%
|
1 ms
|
1,909,263
db_user
|
with lineup as (select ml.id as lineup_id, m.*
from matches as m
join match_lineups as ml on ml.match_id = m.external_id
where m.external_id = $1
limit $4),
goal_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $5
or description = $6),
yellow_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $7),
red_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where description = $8
or description = $9)
select (select count(distinct imi.*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from goal_type)])
and im.match_time < (select match_time from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $10) as goals,
(select count(distinct imi.*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where (coalesce(imi.assist1_id, imi.assist2_id) = $3)
and imi.type = any (array [(select ids from goal_type)])
and im.match_time < (select match_time from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $11) as assists,
(select count(distinct imi.*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from yellow_card_type)])
and im.match_time < (select match_time from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $12) as yellow_cards,
(select count(distinct imi.*)
from match_incidents as imi
left join match_lineups as iml on iml.id = imi.match_lineup
left join matches as im on im.external_id = iml.match_id
where imi.player_id = $2
and imi.type = any (array [(select ids from red_card_type)])
and im.match_time < (select match_time from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $13) as red_cards
|
|
30 min
< 0.1%
|
0 ms
|
81,597,313
db_user
|
UPDATE teams SET updated_date = $1, external_updated_at = $2, external_id = $3, country_id = $4, venue_id = $5, coach_id = $6, name = $7, short_name = $8, searchable_name = $9, logo = $10, country_logo = $11, is_national_team = $12, foundation_date = $13, website = $14, market_value = $15, market_currency = $16, cur_total_players = $17, foreign_players = $18, national_players = $19, uid = $20 WHERE teams.id = $21
Covered by index on (id)
Rows: 96839
Row progression: 96839, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- name
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
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)
|
|
28 min
< 0.1%
|
0 ms
|
20,251,557
db_user
|
select *
from team_players as tp
left join season_teams as st on st.id = tp.season_team_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
where tp.player_id = $1
order by tp.arrival_date
|
|
25 min
< 0.1%
|
0 ms
|
167,419,640
db_user
|
SELECT live_standings_teams.id, live_standings_teams.created_date, live_standings_teams.updated_date, live_standings_teams.external_updated_at, live_standings_teams.standings_stage_id, live_standings_teams.team_id, live_standings_teams.promotion_id, live_standings_teams.note, live_standings_teams.points, live_standings_teams.position, live_standings_teams.deduct_points, live_standings_teams.total, live_standings_teams.won, live_standings_teams.draw, live_standings_teams.loss, live_standings_teams.goals, live_standings_teams.goals_against, live_standings_teams.goal_diff, live_standings_teams.home_points, live_standings_teams.home_position, live_standings_teams.home_total, live_standings_teams.home_won, live_standings_teams.home_draw, live_standings_teams.home_loss, live_standings_teams.home_goals, live_standings_teams.home_goals_against, live_standings_teams.home_goal_diff, live_standings_teams.away_points, live_standings_teams.away_position, live_standings_teams.away_total, live_standings_teams.away_won, live_standings_teams.away_draw, live_standings_teams.away_loss, live_standings_teams.away_goals, live_standings_teams.away_goals_against, live_standings_teams.away_goal_diff FROM live_standings_teams WHERE live_standings_teams.standings_stage_id = $1 AND (live_standings_teams.team_id = $2)
Covered by index on (team_id, standings_stage_id)
Rows: 13033
Row progression: 13033, 2
Row estimates
- team_id (=): 2
- standings_stage_id (=): 9
Existing indexes
- id PRIMARY
- team_id, standings_stage_id UNIQUE
|
|
24 min
< 0.1%
|
0 ms
|
100,148,634
db_user
|
SELECT match_player_statistics.id, match_player_statistics.created_date, match_player_statistics.updated_date, match_player_statistics.match_player_id, match_player_statistics.first, match_player_statistics.goals, match_player_statistics.penalty, match_player_statistics.assists, match_player_statistics.minutes_played, match_player_statistics.red_cards, match_player_statistics.yellow_cards, match_player_statistics.shots, match_player_statistics.shots_on_target, match_player_statistics.dribble, match_player_statistics.dribble_succ, match_player_statistics.clearances, match_player_statistics.blocked_shots, match_player_statistics.interceptions, match_player_statistics.tackles, match_player_statistics.passes, match_player_statistics.passes_accuracy, match_player_statistics.key_passes, match_player_statistics.crosses, match_player_statistics.crosses_accuracy, match_player_statistics.long_balls, match_player_statistics.long_balls_accuracy, match_player_statistics.duels, match_player_statistics.duels_won, match_player_statistics.dispossessed, match_player_statistics.fouls, match_player_statistics.was_fouled, match_player_statistics.offsides, match_player_statistics.yellow2red_cards, match_player_statistics.saves, match_player_statistics.punches, match_player_statistics.runs_out, match_player_statistics.runs_out_succ, match_player_statistics.good_high_claim, match_player_statistics.rating, match_player_statistics.freekicks, match_player_statistics.freekick_goals, match_player_statistics.hit_woodwork, match_player_statistics.fastbreaks, match_player_statistics.fastbreak_shots, match_player_statistics.fastbreak_goals, match_player_statistics.poss_losts FROM match_player_statistics WHERE match_player_statistics.match_player_id = $1
Covered by index on (match_player_id, rating WHERE rating > (0)::numeric)
Rows: 1601182
Row progression: 1601182, 1
Row estimates
- match_player_id (=): 1
Existing indexes
- id PRIMARY
- match_player_id UNIQUE
- match_player_id, rating WHERE rating > (0)::numeric
|
|
22 min
< 0.1%
|
0 ms
|
90,927,847
db_user
|
select *
from teams
where external_id = $1
limit $2
Covered by index on (external_id)
Rows: 96839
Row progression: 96839, 1
Row estimates
- external_id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- name
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
22 min
< 0.1%
|
0 ms
|
49,748,336
db_user
|
SELECT $2 FROM ONLY "public"."season_teams" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
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
|
|
21 min
< 0.1%
|
0 ms
|
270,304,106
db_user
|
SELECT $2 FROM ONLY "public"."teams" x WHERE "external_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
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%
|
0 ms
|
13,905,410
db_user
|
INSERT INTO league_hosts (updated_date, league_id, season_id, country) VALUES ($1, $2, $3, $4) RETURNING id
|
|
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)
|
|
18 min
< 0.1%
|
0 ms
|
32,729,292
db_user
|
INSERT INTO transfers (updated_date, external_updated_at, player_id, type, transfer_time, from_team_name, to_team_id, to_team_name, fee, description) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING id
|
|
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%
|
0 ms
|
32,038,685
db_user
|
INSERT INTO transfers (updated_date, external_updated_at, player_id, type, transfer_time, from_team_id, from_team_name, to_team_name, fee, description) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) 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%
|
0 ms
|
2,248,872,574
db_user
|
DELETE FROM team_players WHERE $1 = $2
|
|
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)
|
|
15 min
< 0.1%
|
0 ms
|
2,612,605
db_user
|
select t.*
from transfers as t
where player_id = any ($1::text[])
order by t.transfer_time
Covered by index on (player_id)
Rows: 2165085
Row progression: 2165085, 12
Row estimates
- player_id (=): 12
- 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
|
|
14 min
< 0.1%
|
0 ms
|
3,833,872
db_user
|
select mp.*
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 m.external_id = any($1::text[])
|
|
14 min
< 0.1%
|
0 ms
|
2,698,959
db_user
|
select *
from players
where ($1::text[] is null or external_id = any ($1::text[]))
|
|
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
|
|
14 min
< 0.1%
|
0 ms
|
4,954,947
db_user
|
SELECT match_players.id, match_players.created_date, match_players.updated_date, match_players.match_lineup, match_players.player_id, match_players.team_type, match_players.first, match_players.captain, match_players.name, match_players.logo, match_players.shirt_number, match_players.position, match_players.x, match_players.y, match_players.rating FROM match_players WHERE match_players.match_lineup = $1
Covered by index on (match_lineup, player_id)
Rows: 3573135
Row progression: 3573135, 42
Row estimates
- match_lineup (=): 42
Existing indexes
- id PRIMARY
- match_lineup, player_id UNIQUE
- match_lineup) INCLUDE (id, player_id, team_type
|
|
14 min
< 0.1%
|
0 ms
|
57,374,818
db_user
|
select *
from player_secondary_positions
where player_id = $1
and position = $2
limit $3
Covered by index on (player_id, position)
Rows: 128263
Row progression: 128263, 2
Row estimates
- player_id (=): 2
- position (=): 11660
Existing indexes
- id PRIMARY
- player_id, position UNIQUE
|
|
13 min
< 0.1%
|
0 ms
|
4,277,379
db_user
|
select *
from teams
where name = any ($1::text[])
Covered by index on (name)
Rows: 96839
Row progression: 96839, 1
Row estimates
- name (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- name
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
13 min
< 0.1%
|
9 ms
|
88,726
db_user
|
with lineup as (select ml.id as lineup_id,
m.home_team_id as home_team_id,
m.away_team_id as away_team_id,
m.is_overtime as is_overtime,
m.match_time as match_time,
m.league_id as league_id,
m.season_id as season_id,
(select jsonb_build_object(
$2, l.*,
$3, s.*,
$4, t.*,
$5, c.*,
$6, tc.color,
$7, tc.secondary_color
)
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.external_id = m.home_team_id
limit $8) as home_team_details,
(select jsonb_build_object(
$9, l.*,
$10, s.*,
$11, t.*,
$12, c.*,
$13, tc.color,
$14, tc.secondary_color
)
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
where t.external_id = m.away_team_id
limit $15) as away_team_details
from matches as m
join match_lineups as ml on ml.match_id = m.external_id
where m.id = $1
limit $16),
goal_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description in ($17, $18)),
yellow_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description = $19),
red_card_type as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description in ($20, $21)),
extra_types as (select array_agg(ts.id) as ids
from technical_statistics as ts
where ts.description = any (array [$22,$23,$24])),
player_ids as (select array_agg(mp.player_id) as ids
from match_players as mp
where mp.match_lineup = (select lineup_id from lineup)),
player_stats as (select imi.player_id,
sum(case when imi.type = any ((select ids from goal_type)::bigint[]) then $25 else $26 end) as goals,
sum(case when imi.type = any ((select ids from yellow_card_type)::bigint[]) then $27 else $28 end) as yellow_cards,
sum(case when imi.type = any ((select ids from red_card_type)::bigint[]) then $29 else $30 end) as red_cards,
imi.assist1_id as assist_id
from match_incidents as imi
join match_lineups as iml on iml.id = imi.match_lineup
join matches as im on im.external_id = iml.match_id
where im.match_time < (select match_time from lineup)
and im.league_id = (select league_id from lineup)
and im.season_id = (select season_id from lineup)
and imi.deleted = $31
and imi.player_id = any ((select ids from player_ids)::text[])
and (imi.type = any ((select ids from yellow_card_type)::bigint[])
or imi.type = any ((select ids from red_card_type)::bigint[])
or imi.type = any ((select ids from goal_type)::bigint[]))
group by imi.player_id, imi.assist1_id),
assist_stats as (select assist_id,
count(*) as assists
from player_stats
where assist_id is not null
group by assist_id),
aggregated_stats as (select player_id,
sum(goals) as total_goals,
sum(yellow_cards) as total_yellow_cards,
sum(red_cards) as total_red_cards
from player_stats
group by player_id)
select l.is_overtime as is_overtime,
l.match_time as match_time,
mi.time as time,
mi.addtime as addtime,
ts.description as description,
to_jsonb(mi.*) as incident,
case when p.id is not null then to_jsonb(p.*) else to_jsonb(c.*) end as player,
to_jsonb(sp.*) as secondary_player,
to_jsonb(t.*) as team,
to_jsonb(st.*) as secondary_team,
case
when mi.team_type = $32 then l.home_team_details
else l.away_team_details
end as team_details,
case
when mi.team_type = $33 then l.away_team_details
else l.home_team_details
end as secondary_team_details,
jsonb_build_object(
$34, coalesce(agg.total_goals, $35),
$36, coalesce(ast.assists, $37),
$38, coalesce(agg.total_yellow_cards, $39),
$40, coalesce(agg.total_red_cards, $41)
) as season_statistic
from match_incidents as mi
left join lineup as l on mi.match_lineup = l.lineup_id
left join technical_statistics as ts on ts.id = mi.type
left join teams as t on t.external_id = case
when mi.team_type = $42 then l.home_team_id
else l.away_team_id
end
left join teams as st on st.external_id = case
when mi.team_type = $43 then l.away_team_id
else l.home_team_id
end
left join coaches as c on c.external_id = mi.player_id
left join players as p on p.external_id = mi.player_id
left join players as sp on sp.external_id = coalesce(mi.assist1_id, mi.out_player_id)
left join aggregated_stats as agg on agg.player_id = mi.player_id
left join assist_stats as ast on ast.assist_id = mi.assist1_id
where mi.match_lineup = l.lineup_id
and (mi.player_id is not null or mi.type = any ((select ids from extra_types)::bigint[]))
and mi.deleted = $44
order by case
when ts.description = $45 and mi.time::bigint = $46 then $47
when ts.description = $48 and mi.time::bigint = $49 then $50
when ts.description = $51 and mi.time::bigint = $52 then $53
when ts.description = $54 then $55
when ts.description = $56 then $57
else mi.time::bigint
end - case
when ts.description = $58 then $59
|
|
12 min
< 0.1%
|
0 ms
|
224,219,426
db_user
|
SELECT $2 FROM ONLY "public"."match_statuses" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
12 min
< 0.1%
|
0 ms
|
32,923,370
db_user
|
UPDATE coaches SET updated_date = $1, external_updated_at = $2, external_id = $3, country_id = $4, nationality = $5, name = $6, short_name = $7, searchable_name = $8, logo = $9, age = $10, birthday = $11, deathday = $12, preferred_formation = $13 WHERE coaches.id = $14
Covered by index on (id)
Rows: 129679
Row progression: 129679, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- external_id UNIQUE
- to_tsvector('english'::regconfig, searchable_name) GIN
|
|
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%
|
2 ms
|
369,376
db_user
|
select to_jsonb(l.*) as league,
to_jsonb(s.*) as season,
to_jsonb(t.*) as team,
to_jsonb(c.*) as coach,
tc.color as team_color,
tc.secondary_color as team_secondary_color,
stt.position as position,
to_jsonb(coun.*) as country
from teams as t
left join season_teams as st on st.team_id = t.external_id
left join league_seasons as ls on ls.id = st.league_season_id
left join seasons as s on s.external_id = ls.season_id
left join leagues as l on l.external_id = ls.league_id
left join coaches as c on c.external_id = t.coach_id
left join team_colors as tc on tc.team_id = t.external_id
left join standings as sta on sta.season_id = ls.season_id
left join standings_stages as sts on sts.standings_id = sta.id
left join standings_teams as stt on stt.standings_stage_id = sts.external_id and stt.team_id = t.external_id
left join countries as coun on coun.external_id = t.country_id
where t.external_id = $1
order by case when l.type = $2 then $3 else $4 end desc, s.end_time desc
limit $5
|
|
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
|
|
11 min
< 0.1%
|
0 ms
|
6,137,449
db_user
|
INSERT INTO match_trends (updated_date, match_id, half_number, minute, trend) VALUES ($1, $2, $3, $4, $5) RETURNING id
|