|
232 min
6%
|
223 ms
|
62,433
db_user
|
with base as (select (to_jsonb(co) - $9) || jsonb_build_object($10, coalesce(cs.comment_count, $11)) as jsonb,
co.league_type as league_type,
co.round as round_num,
coalesce(co.custom_names ->> $1::text, co.stage_name) as stage_name,
co.stage_mode as stage_mode,
co.is_knockout as is_knockout,
coalesce(cs.comment_count, $12) as comment_count,
co.match_time as match_time,
co.stage_order as stage_order,
co.reference_id as reference_id
from commentable_objects as co
left join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = $13 and
cs.comment_to_sub_type = $14 and
cs.cumulative = $15
where co.season_id = $2
and co.type = $16
and co.event_type is not null
and co.deleted = $17
and ($3 is null or co.player_id = $3)
and ($4 is null
or co.team = $4
or (co.event_type::text = $18 and (co.home_team = $4 or co.away_team = $4)))
order by coalesce(cs.comment_count, 0) desc, co.match_time desc, co.reference_id desc),
grouped as (select base.*,
case
when league_type <> $19 and round_num is not null and round_num <> $20
then case
when stage_order > $21
then stage_name || $22 || round_num
else $23 || round_num end
when league_type <> $24
and (round_num is null or round_num = $25)
and stage_name is not null
and stage_mode = $26
then stage_name
when league_type = $27
and (round_num is null or round_num = $28)
and stage_name is not null
and stage_mode = $29
then stage_name ||
case when is_knockout then $30 when is_knockout is null then $31 else $32 end
when league_type = $33
and (round_num is null or round_num = $34)
and stage_name is not null
and stage_mode = $35
then stage_name
when league_type = $36
and round_num is not null
and round_num <> $37
and stage_name is not null
and stage_mode = $38
then stage_name || $39 || round_num
else $40
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by comment_count desc, match_time desc, reference_id desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($41, match_time,
$42, round_num,
$43, league_type,
$44, stage_name,
$45, stage_mode,
$46, is_knockout)
order by ranked.comment_count desc, ranked.match_time desc) filter (where rn <= $5) as data,
count(ranked.*) filter (where rn <= $5) as inner_total,
count(ranked.*) as total
from ranked
group by group_name, stage_order, round_num, is_knockout
order by stage_order desc, case when is_knockout then $47 else $48 end desc, round_num desc),
target_group as (select stage_order,
case when is_knockout then $49 else $50 end as ko_order,
round_num
from grouped
where match_time <= $6::timestamptz + interval $51
order by stage_order desc,
case when is_knockout then 1 else 0 end desc,
round_num desc
limit $52)
select *
from data
where inner_total > $53
and ($6::timestamptz is null or
(stage_order, case when is_knockout then $54 else $55 end, round_num) <= (select t.stage_order, t.ko_order, t.round_num from target_group t))
order by stage_order desc, case when is_knockout then $56 else $57 end desc, round_num desc
limit case when $7 is not null then $7::bigint end offset case when $7::bigint is not null and $8::bigint is not null then $8::bigint * $7::bigint end
|
|
168 min
5%
|
162,848 ms
|
62
db_user
|
WITH stats AS (
SELECT
EXTRACT($2 FROM AVG(ua.time_spent))::float AS avg_screen_secs,
AVG(s.sess_count)::float AS avg_sessions_per_user,
AVG(s.total_secs)::float AS avg_total_secs
FROM user_activities ua
CROSS JOIN (
SELECT device_id, COUNT(DISTINCT session)::float AS sess_count,
EXTRACT($3 FROM COALESCE(SUM(time_spent), INTERVAL $4))::float AS total_secs
FROM user_activities
GROUP BY device_id
) s
),
me AS (
SELECT
EXTRACT($5 FROM AVG(time_spent))::float AS me_avg_screen_secs,
COUNT(DISTINCT session)::float AS me_sessions,
EXTRACT($6 FROM COALESCE(SUM(time_spent), INTERVAL $7))::float AS me_total_secs
FROM user_activities WHERE device_id = $1
),
top_screens_me AS (
SELECT screen_name, COUNT(*)::int AS visits
FROM user_activities WHERE device_id = $1 AND screen_name NOT IN ($8,$9)
GROUP BY screen_name ORDER BY visits DESC LIMIT $10
),
top_hours_me AS (
SELECT EXTRACT($11 FROM created_date)::int AS h, COUNT(*)::int AS c
FROM user_activities WHERE device_id = $1
GROUP BY h ORDER BY c DESC LIMIT $12
)
SELECT
(SELECT avg_screen_secs FROM stats) AS avg_screen_secs,
(SELECT avg_sessions_per_user FROM stats) AS avg_sessions,
(SELECT avg_total_secs FROM stats) AS avg_total_secs,
(SELECT me_avg_screen_secs FROM me) AS me_avg_screen_secs,
(SELECT me_sessions FROM me) AS me_sessions,
(SELECT me_total_secs FROM me) AS me_total_secs,
(SELECT array_agg(screen_name) FROM top_screens_me) AS top_screens,
(SELECT array_agg(h) FROM top_hours_me) AS top_hours
|
|
142 min
4%
|
662 ms
|
12,842
db_user
|
with daily as (
select created_date::date as d,
count(distinct device_id) as dau,
count(distinct session) as sessions,
count(*) filter (where screen_name = $1) as opens,
extract($2 from sum(time_spent)) / $3 as total_minutes
from user_activities
group by created_date::date
)
select
(select dau from daily order by dau desc nulls last limit $4)::int as best_dau_value,
(select d::text from daily order by dau desc nulls last limit $5) as best_dau_date,
(select sessions from daily order by sessions desc nulls last limit $6)::int as best_sessions_value,
(select d::text from daily order by sessions desc nulls last limit $7) as best_sessions_date,
(select opens from daily order by opens desc nulls last limit $8)::int as best_opens_value,
(select d::text from daily order by opens desc nulls last limit $9) as best_opens_date,
(select total_minutes from daily order by total_minutes desc nulls last limit $10)::float8 as best_minutes_value,
(select d::text from daily order by total_minutes desc nulls last limit $11) as best_minutes_date
|
|
139 min
4%
|
483 ms
|
17,267
db_user
|
SELECT
EXTRACT($1 FROM created_date AT TIME ZONE $2)::int AS dow,
EXTRACT($3 FROM created_date AT TIME ZONE $4)::int AS hour,
COUNT(DISTINCT sesssion)::int AS sessions
FROM user_activities
WHERE created_date >= NOW() - INTERVAL $5
GROUP BY dow, hour
Covered by index on (created_date)
Rows: 1501895
Row progression: 1501895, 150190
Row estimates
- created_date (>=): 150190
Existing indexes
- id PRIMARY
- created_date
|
|
136 min
4%
|
479,952 ms
|
17
db_user
|
-- Backfill existing feed rows whose comment targets an event now reviewed by a VAR.
update feeds as f
set display_reference_id = v.reference_id
from commentable_objects as co
join comments as c on c.id = co.reference_id
join commentable_objects as v on v.related_incident_ids @> array [c.reference_id]
where f.commentable = co.id
and co.type = $1
and c.comment_to = $2
|
|
132 min
4%
|
4 ms
|
1,911,063
db_user
|
INSERT INTO commentable_objects (reference_id, type) VALUES ($1, $2) RETURNING id
|
|
119 min
3%
|
558 ms
|
12,844
db_user
|
with device_owners as (
select distinct on (ua.device_id)
ua.device_id,
ua.user_id as attributed_user_id,
ua.updated_date as last_login_at
from user_activities ua
where ua.user_id is not null
order by ua.device_id, ua.updated_date desc
),
daily as (
select device_id, created_date::date as d
from user_activities group by device_id, created_date::date
),
grouped as (
select device_id, d,
d - (row_number() over (partition by device_id order by d))::int * interval $1 as streak_group
from daily
),
streaks as (
select device_id, count(*)::int as streak_days, min(d) as start_date, max(d) as end_date
from grouped group by device_id, streak_group
),
best as (
select device_id, max(streak_days) as best_streak
from streaks group by device_id
),
current as (
select s.device_id, s.streak_days as current_streak
from streaks s
where s.end_date >= current_date - interval $2
)
select b.device_id,
device_owners.attributed_user_id as user_id,
u.username,
u.country as country_id,
coalesce(c.current_streak, $3)::int as current_streak,
b.best_streak::int as best_streak
from best b
left join current c on c.device_id = b.device_id
left join device_owners on device_owners.device_id = b.device_id
left join users u on u.id = device_owners.attributed_user_id
where b.best_streak is not null
order by current_streak desc, best_streak desc
limit $4
|
|
114 min
3%
|
265 ms
|
25,706
db_user
|
select
(select count(distinct device_id) from user_activities
where created_date >= $1 and created_date <= $2)::int as users,
(select count(distinct session) from user_activities
where created_date >= $1 and created_date <= $2)::int as sessions,
(select count(*) filter (where screen_name = $3) from user_activities
where created_date >= $1 and created_date <= $2)::int as opens,
(select extract($4 from coalesce(sum(time_spent), interval $5)) / $6 from user_activities
where created_date >= $1 and created_date <= $2)::float8 as minutes,
(select count(*) from (
select device_id, min(created_date) as first_seen
from user_activities group by device_id
) f where f.first_seen >= $1 and f.first_seen <= $2)::int as signups
|
|
110 min
3%
|
513 ms
|
12,853
db_user
|
with days as (
select generate_series(date_trunc($3, $1::timestamptz), date_trunc($4, $2::timestamptz), $5)::date as day
)
select d.day::text as day,
coalesce(count(distinct ua.device_id), $6)::int as dau,
coalesce(count(distinct ua.session), $7)::int as sessions,
coalesce(sum(extract($8 from ua.time_spent)) / $9, $10)::float8 as minutes,
coalesce(count(*) filter (where ua.screen_name = $11), $12)::int as app_opens
from days d
left join user_activities ua on date_trunc($13, ua.created_date)::date = d.day
group by d.day
order by d.day
|
|
103 min
3%
|
480 ms
|
12,853
db_user
|
select
(select count(distinct device_id) from user_activities
where updated_date >= now() - interval $1 and screen_name not in ($2,$3))::int as active_now,
(select count(distinct device_id) from user_activities
where created_date >= date_trunc($4, now()))::int as dau_today,
(select count(distinct device_id) from user_activities
where created_date >= now() - interval $5)::int as wau_7d,
(select count(distinct device_id) from user_activities
where created_date >= now() - interval $6)::int as mau_30d,
(select count(distinct session) from user_activities
where created_date >= date_trunc($7, now()))::int as sessions_today,
(select count(*) from user_activities
where screen_name = $8 and created_date >= date_trunc($9, now()))::int as app_opens_today,
coalesce((select extract($10 from avg(s.total_dur)) from (
select session, coalesce(sum(time_spent), interval $11) as total_dur
from user_activities
where created_date >= date_trunc($12, now())
group by session having sum(time_spent) > interval $13
) s), $14)::float8 as avg_session_seconds,
coalesce((select extract($15 from sum(s.total_dur)) / $16 from (
select session, coalesce(sum(time_spent), interval $17) as total_dur
from user_activities
where created_date >= date_trunc($18, now())
group by session having sum(time_spent) > interval $19
) s), $20)::float8 as total_minutes_today,
(select count(*) from (
select device_id, min(created_date) as first_seen
from user_activities group by device_id
) firsts where first_seen >= date_trunc($21, now()))::int as new_users_today
|
|
99 min
3%
|
367 ms
|
16,158
db_user
|
WITH cohorts AS (
SELECT device_id, MIN(created_date)::date AS signup_date
FROM user_activities
GROUP BY device_id
),
activity AS (
SELECT device_id, created_date::date AS activity_date
FROM user_activities
GROUP BY device_id, created_date::date
)
SELECT
COUNT(DISTINCT c.device_id) FILTER (WHERE c.signup_date <= CURRENT_DATE - INTERVAL $1)::int AS cohort_d1,
COUNT(DISTINCT a.device_id) FILTER (WHERE c.signup_date <= CURRENT_DATE - INTERVAL $2
AND a.activity_date = c.signup_date + INTERVAL $3)::int AS returned_d1,
COUNT(DISTINCT c.device_id) FILTER (WHERE c.signup_date <= CURRENT_DATE - INTERVAL $4)::int AS cohort_d7,
COUNT(DISTINCT a.device_id) FILTER (WHERE c.signup_date <= CURRENT_DATE - INTERVAL $5
AND a.activity_date BETWEEN c.signup_date + INTERVAL $6 AND c.signup_date + INTERVAL $7)::int AS returned_d7,
COUNT(DISTINCT c.device_id) FILTER (WHERE c.signup_date <= CURRENT_DATE - INTERVAL $8)::int AS cohort_d30,
COUNT(DISTINCT a.device_id) FILTER (WHERE c.signup_date <= CURRENT_DATE - INTERVAL $9
AND a.activity_date BETWEEN c.signup_date + INTERVAL $10 AND c.signup_date + INTERVAL $11)::int AS returned_d30
FROM cohorts c
LEFT JOIN activity a ON a.device_id = c.device_id
|
|
95 min
3%
|
445 ms
|
12,852
db_user
|
with device_owners as (
select distinct on (ua.device_id)
ua.device_id,
ua.user_id as attributed_user_id,
ua.updated_date as last_login_at
from user_activities ua
where ua.user_id is not null
order by ua.device_id, ua.updated_date desc
)
select u.country as country_id,
count(distinct ua.device_id)::int as active_count,
max(ua.updated_date) as last_seen
from user_activities ua
left join device_owners on device_owners.device_id = ua.device_id
left join users u on u.id = device_owners.attributed_user_id
where ua.updated_date >= $1 and ua.updated_date <= $2
group by u.country
order by active_count desc
|
|
89 min
2%
|
415 ms
|
12,847
db_user
|
with device_owners as (
select distinct on (ua.device_id)
ua.device_id,
ua.user_id as attributed_user_id,
ua.updated_date as last_login_at
from user_activities ua
where ua.user_id is not null
order by ua.device_id, ua.updated_date desc
)
select ua.device_id,
device_owners.attributed_user_id as user_id,
u.username,
u.country as country_id,
coalesce(extract($3 from sum(ua.time_spent)) / $4, $5)::float8 as total_minutes,
count(distinct ua.session)::int as sessions,
count(*)::int as screens_visited,
max(ua.updated_date) as last_seen
from user_activities ua
left join device_owners on device_owners.device_id = ua.device_id
left join users u on u.id = device_owners.attributed_user_id
where ua.created_date >= $1 and ua.created_date <= $2
group by ua.device_id, device_owners.attributed_user_id, u.username, u.country
order by total_minutes desc nulls last
limit $6
|
|
86 min
2%
|
321 ms
|
16,148
db_user
|
WITH
device_owners AS (
SELECT DISTINCT ON (ua.device_id)
ua.device_id,
ua.user_id AS attributed_user_id,
ua.updated_date AS last_login_at
FROM user_activities ua
WHERE ua.user_id IS NOT NULL
ORDER BY ua.device_id, ua.updated_date DESC
)
SELECT ua.id, ua.device_id,
device_owners.attributed_user_id AS user_id,
u.username, u.country, ua.screen_name, ua.updated_date AS ts,
EXTRACT($1 FROM ua.time_spent)::float AS duration_seconds
FROM user_activities ua
LEFT JOIN device_owners ON device_owners.device_id = ua.device_id
LEFT JOIN users u ON u.id = device_owners.attributed_user_id
WHERE ua.updated_date >= NOW() - INTERVAL $2
ORDER BY ua.updated_date DESC
LIMIT $3
|
|
85 min
2%
|
290 ms
|
17,665
db_user
|
WITH
device_owners AS (
SELECT DISTINCT ON (ua.device_id)
ua.device_id,
ua.user_id AS attributed_user_id,
ua.updated_date AS last_login_at
FROM user_activities ua
WHERE ua.user_id IS NOT NULL
ORDER BY ua.device_id, ua.updated_date DESC
)
SELECT ua.id, ua.device_id, device_owners.attributed_user_id AS user_id,
u.country, u.username, ua.screen_name, ua.session, ua.updated_date AS ts
FROM user_activities ua
LEFT JOIN device_owners ON device_owners.device_id = ua.device_id
LEFT JOIN users u ON u.id = device_owners.attributed_user_id
WHERE ua.updated_date >= NOW() - INTERVAL $1
ORDER BY ua.updated_date DESC
LIMIT $2
|
|
82 min
2%
|
384 ms
|
12,845
db_user
|
with device_owners as (
select distinct on (ua.device_id)
ua.device_id,
ua.user_id as attributed_user_id,
ua.updated_date as last_login_at
from user_activities ua
where ua.user_id is not null
order by ua.device_id, ua.updated_date desc
),
sess as (
select ua.session, ua.device_id, u.premium,
extract($3 from coalesce(sum(ua.time_spent), interval $4)) as total_secs,
count(*) as screen_views
from user_activities ua
left join device_owners on device_owners.device_id = ua.device_id
left join users u on u.id = device_owners.attributed_user_id
where ua.created_date >= $1 and ua.created_date <= $2
group by ua.session, ua.device_id, u.premium
)
select premium,
count(distinct device_id)::int as users,
count(*)::int as sessions,
coalesce(avg(total_secs), $5)::float8 as avg_session_secs,
coalesce(avg(screen_views), $6)::float8 as avg_screens
from sess
group by premium
|
|
74 min
2%
|
0 ms
|
515,681,288
db_user
|
select *
from feeds
where commentable = $1
limit $2
Covered by index on (commentable)
Rows: 32859196
Row progression: 32859196, 1
Row estimates
- commentable (=): 1
Existing indexes
- id PRIMARY
- ((((base_score)::numeric + extra_score) + follower_score)) DESC WHERE commentable_type = 'Comment'::comment_type
- commentable UNIQUE
|
|
71 min
2%
|
2,947 ms
|
1,448
db_user
|
select co.*,
ln(count(c.*) + $1) - (select recency from trending_match_params limit $2) * avg((extract($3 from now()) - extract($4 from (c.created_date at time zone $5))) / $6) as hotness
from commentable_objects as co
left join comments as c on c.reference_id = co.reference_id and
c.comment_to = $7
where co.type = $8
group by co.id
order by hotness desc nulls last
limit (select match_limit from trending_match_params limit $9)
|
|
70 min
2%
|
505 ms
|
8,351
db_user
|
with device_owners as (
select distinct on (ua.device_id)
ua.device_id,
ua.user_id as attributed_user_id,
ua.updated_date as last_login_at
from user_activities ua
where ua.user_id is not null
order by ua.device_id, ua.updated_date desc
)
select ua.device_id::bigint as device_id,
device_owners.attributed_user_id::bigint as user_id,
host(u.ip_address) as ip,
u.country::bigint as country_id,
count(distinct ua.session)::int as active_count,
max(ua.updated_date) as last_seen
from user_activities ua
left join device_owners on device_owners.device_id = ua.device_id
left join users u on u.id = device_owners.attributed_user_id
where ua.updated_date >= now() - make_interval(mins => $1)
and u.ip_address is not null
group by ua.device_id, device_owners.attributed_user_id, u.ip_address, u.country
|
|
65 min
2%
|
241 ms
|
16,160
db_user
|
SELECT
(SELECT COUNT(DISTINCT device_id) FROM user_activities WHERE created_date >= DATE_TRUNC($1, NOW()))::int AS dau_today,
(SELECT COUNT(DISTINCT device_id) FROM user_activities WHERE created_date >= DATE_TRUNC($2, NOW()) - INTERVAL $3 AND created_date < DATE_TRUNC($4, NOW()))::int AS dau_yesterday,
(SELECT COUNT(DISTINCT session) FROM user_activities WHERE created_date >= DATE_TRUNC($5, NOW()))::int AS sessions_today,
(SELECT COUNT(DISTINCT session) FROM user_activities WHERE created_date >= DATE_TRUNC($6, NOW()) - INTERVAL $7 AND created_date < DATE_TRUNC($8, NOW()))::int AS sessions_yesterday,
(SELECT COUNT(DISTINCT device_id) FROM user_activities WHERE created_date >= NOW() - INTERVAL $9)::int AS wau_now,
(SELECT COUNT(DISTINCT device_id) FROM user_activities WHERE created_date >= NOW() - INTERVAL $10 AND created_date < NOW() - INTERVAL $11)::int AS wau_prev
|
|
60 min
2%
|
837 ms
|
4,287
db_user
|
with input_ids as (select *
from unnest($1::bigint[]) with ordinality t(id, ord)),
favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $2)
select to_jsonb(s.*) as "user",
(s.id = any (array [(select ids from favorite_users)])) as following,
count(distinct followers.id) as followers,
count(distinct followings.id) as following_count,
count(distinct c.id) as posts_count
from input_ids i
join users s on s.id = i.id
left join user_favorite_users followings on followings.user_id = s.id
left join user_favorite_users followers on followers.favorite_user_id = s.id
left join comments c on c.user_id = s.id
group by s.id, i.ord
order by i.ord
|
|
57 min
2%
|
0 ms
|
61,632,733
db_user
|
UPDATE commentable_objects SET reference_id = $1, type = $2, event_type = $3, home_team = $4, away_team = $5, league_id = $6, player_id = $7, country_id = $8, match_id = $9, league_type = $10, match_time = $11, round = $12, stage_name = $13, stage_mode = $14, is_knockout = $15, season_id = $16, stage_order = $17, team = $18, related_incident_ids = $19, custom_names = $20 WHERE commentable_objects.id = $21
Covered by index on (id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
55 min
2%
|
0 ms
|
90,388,832
db_user
|
UPDATE commentable_objects SET reference_id = $1, type = $2, event_type = $3, home_team = $4, away_team = $5, home_coach = $6, away_coach = $7, league_id = $8, player_id = $9, secondary_player_id = $10, country_id = $11, match_id = $12, league_type = $13, match_time = $14, round = $15, stage_name = $16, stage_mode = $17, is_knockout = $18, season_id = $19, stage_order = $20, team = $21, related_incident_ids = $22, custom_names = $23 WHERE commentable_objects.id = $24
Covered by index on (id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
55 min
2%
|
255 ms
|
12,844
db_user
|
with ordered as (
select session,
screen_name,
created_date,
row_number() over (partition by session order by created_date) as seq
from user_activities
where created_date >= $1 and created_date <= $2 and screen_name not in ($3,$4)
),
triplets as (
select o1.session,
o1.screen_name as s1,
o2.screen_name as s2,
o3.screen_name as s3
from ordered o1
join ordered o2 on o2.session = o1.session and o2.seq = o1.seq + $5
join ordered o3 on o3.session = o1.session and o3.seq = o1.seq + $6
)
select s1, s2, s3, count(*)::int as occurrences
from triplets
group by s1, s2, s3
order by occurrences desc
limit $7
|
|
54 min
1%
|
202 ms
|
16,156
db_user
|
SELECT
(SELECT COUNT(DISTINCT device_id) FROM user_activities WHERE created_date >= NOW() - INTERVAL $1)::int AS dau,
(SELECT COUNT(DISTINCT device_id) FROM user_activities WHERE created_date >= NOW() - INTERVAL $2)::int AS mau
|
|
54 min
1%
|
0 ms
|
174,201,673
db_user
|
UPDATE commentable_objects SET reference_id = $1, type = $2, event_type = $3, home_team = $4, away_team = $5, league_id = $6, player_id = $7, country_id = $8, match_id = $9, league_type = $10, match_time = $11, round = $12, stage_name = $13, stage_mode = $14, is_knockout = $15, season_id = $16, stage_order = $17, team = $18 WHERE commentable_objects.id = $19
Covered by index on (id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
54 min
1%
|
1,876 ms
|
1,729
db_user
|
with favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $1),
favorite_leagues as (select array_agg(league_id) as ids
from user_favorite_leagues
where user_id = $1),
favorite_teams as (select array_agg(team_id) as ids
from user_favorite_teams
where user_id = $1),
favorite_matches as (select array_agg(match_id) as ids
from user_favorite_matches
where user_id = $1),
favorite_players as (select array_agg(player_id) as ids
from user_favorite_players
where user_id = $1),
top_feeds as (select distinct f.id as feed_id,
c.id as comment_id,
co.id as commentable_object_id,
case
when $2::text = $14 or $3::boolean = $15 then co.created_epoch
else (f.base_score + f.extra_score + f.follower_score) end as score
from feeds as f
left join commentable_objects as co on co.id = f.commentable
left join comments as c on c.id = co.reference_id and co.type = $16
cross join favorite_leagues as fl
cross join favorite_teams as ft
cross join favorite_matches as fm
cross join favorite_players as fp
cross join favorite_users as fu
where c.comment_to != $17
and (co.type != $18 or $4::bigint[] is null or c.language = any (array [$4::bigint[]]))
and ((co.type = $19 and c.id is not null) or co.type != $20)
and (co.type = $21 or $5::bigint[] is null or co.league_id = any (array [$5::bigint[]]))
and (co.type = $22 or $6::bigint[] is null or co.home_team = any (array [$6::bigint[]]) or co.away_team = any (array [$6::bigint[]]))
and (co.type = $23 or $7::bigint[] is null or co.player_id = any (array [$7::bigint[]]))
and (co.type = $24 or $8::event_type[] is null or co.event_type = any (array [$8::event_type[]]))
and (co.type = $25 or $9::comment_type[] is null or co.type = any (array [$9::comment_type[]]))
and (co.type = $26 or $10 is null or exists (select $27
from generate_subscripts($10::bigint[][], $28) s
where array [
($10::bigint[][])[s][$29],
($10::bigint[][])[s][$30]
] = array [co.home_team, co.away_team]))
and ($11::timestamptz is null or co.created_date <= $11::timestamptz)
and ($3::boolean is null or $3::boolean = $31 or
co.league_id = any (fl.ids) or
co.home_team = any (ft.ids) or
co.away_team = any (ft.ids) or
co.match_id = any (fm.ids) or
co.player_id = any (fp.ids) or
c.user_id = any (fu.ids))
and ($1 is null or c.user_id not in
(select ub.blocked_user_id from user_blocks as ub where ub.blocker_user_id = $1))
order by score desc
limit $12 offset $13 * $12)
select to_jsonb(fe.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
to_jsonb(c.*) || jsonb_build_object(
$32, coalesce(cs.likes, $33),
$34, coalesce(csu.liked, $35),
$36, coalesce(cs.comment_count, $37),
$38, coalesce(cs.view_count, $39),
$40, jsonb_build_object(
$41, (u.id = any (array [(select ids from favorite_users)])),
$42, count(distinct follower_count.*),
$43, count(distinct following_count.*),
$44, count(distinct post_count.*),
$45, (to_jsonb(u.*) || jsonb_build_object($46, max(ust.team_id))))) as comment_response
from top_feeds as f
left join feeds as fe on fe.id = f.feed_id
left join commentable_objects as co on co.id = f.commentable_object_id
left join comments as c on c.id = f.comment_id
left join users as u on u.id = c.user_id
left join user_support_teams as ust on ust.user_id = u.id
left join comment_statistics as cs on cs.comment_to = $47 and
cs.reference_id = c.id and
cs.comment_to_sub_type = $48 and
cs.cumulative = $49
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $1 and
$1 is not null
left join user_favorite_users as follower_count on follower_count.favorite_user_id = c.user_id and
follower_count.user_id != c.user_id
left join user_favorite_users as following_count on following_count.user_id = c.user_id and
following_count.favorite_user_id != c.user_id
left join comments as post_count on post_count.user_id = c.user_id and
post_count.comment_to != $50
group by fe.id, co.id, c.id, cs.id, csu.id, u.id, f.score
order by f.score desc
|
|
42 min
1%
|
0 ms
|
175,397,403
db_user
|
SELECT commentable_objects.id, commentable_objects.created_date, commentable_objects.reference_id, commentable_objects.type, commentable_objects.event_type, commentable_objects.home_team, commentable_objects.away_team, commentable_objects.league_id, commentable_objects.player_id, commentable_objects.country_id, commentable_objects.match_id, commentable_objects.league_type, commentable_objects.match_time, commentable_objects.round, commentable_objects.stage_name, commentable_objects.stage_mode, commentable_objects.is_knockout, commentable_objects.season_id, commentable_objects.stage_order, commentable_objects.team FROM commentable_objects WHERE commentable_objects.reference_id = $1 AND (commentable_objects.type = $2)
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (reference_id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- reference_id (=): 1
- type (=): 5859622
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
39 min
1%
|
0 ms
|
99,826,963
db_user
|
select *
from commentable_objects
where reference_id = $1
and type = $2
and event_type::text is not distinct from $3
limit $4
|
|
34 min
0.9%
|
159 ms
|
12,845
db_user
|
with first_seen as (
select device_id, min(created_date) as first_at
from user_activities group by device_id
),
active as (
select distinct ua.device_id
from user_activities ua
where ua.created_date >= $1 and ua.created_date <= $2
)
select
count(*) filter (where fs.first_at >= $1 and fs.first_at <= $2)::int as new_users,
count(*) filter (where not (fs.first_at >= $1 and fs.first_at <= $2))::int as returning_users,
count(*)::int as total
from active a
join first_seen fs on fs.device_id = a.device_id
|
|
33 min
0.9%
|
14 ms
|
144,520
db_user
|
with base as (select (to_jsonb(co) - $10) || jsonb_build_object($11, coalesce(cs.comment_count, $12)) as jsonb,
co.league_type as league_type,
co.round as round_num,
coalesce(co.custom_names ->> $1::text, co.stage_name) as stage_name,
co.stage_mode as stage_mode,
co.is_knockout as is_knockout,
coalesce(cs.comment_count, $13) as comment_count,
co.match_time as match_time,
co.stage_order as stage_order,
co.reference_id as reference_id
from commentable_objects as co
left join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = $14 and
cs.comment_to_sub_type = (case when co.event_type::text = $15 then $16 else $17 end)::comment_sub_type and
cs.cumulative = $18
where co.season_id = $2
and co.type = $19
and co.event_type is not null
and co.deleted = $20
and ($3 is null
or co.player_id = $3
or (co.secondary_player_id = $3 and co.event_type::text not in ($21, $22)))
and ($4 is null
or co.team = $4
or (co.event_type::text = $23 and (co.home_team = $4 or co.away_team = $4)))
and ($5 is null
or ((co.home_coach = $5 or co.away_coach = $5)
and co.player_id = $5
and co.event_type::text in ($24, $25, $26)))
order by coalesce(cs.comment_count, 0) desc, co.match_time desc, co.reference_id desc),
grouped as (select base.*,
case
when league_type <> $27 and round_num is not null and round_num <> $28
then case
when stage_order > $29
then stage_name || $30 || round_num
else $31 || round_num end
when league_type <> $32
and (round_num is null or round_num = $33)
and stage_name is not null
and stage_mode = $34
then stage_name
when league_type = $35
and (round_num is null or round_num = $36)
and stage_name is not null
and stage_mode = $37
then stage_name ||
case when is_knockout then $38 when is_knockout is null then $39 else $40 end
when league_type = $41
and (round_num is null or round_num = $42)
and stage_name is not null
and stage_mode = $43
then stage_name
when league_type = $44
and round_num is not null
and round_num <> $45
and stage_name is not null
and stage_mode = $46
then stage_name || $47 || round_num
else $48
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by comment_count desc, match_time desc, reference_id desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($49, match_time,
$50, round_num,
$51, league_type,
$52, stage_name,
$53, stage_mode,
$54, is_knockout)
order by ranked.comment_count desc, ranked.match_time desc) filter (where rn <= $6) as data,
count(ranked.*) filter (where rn <= $6) as inner_total,
count(ranked.*) as total
from ranked
group by group_name, stage_order, round_num, is_knockout
order by stage_order desc, case when is_knockout then $55 else $56 end desc, round_num desc),
target_group as (select stage_order,
case when is_knockout then $57 else $58 end as ko_order,
round_num
from grouped
where match_time <= $7::timestamptz + interval $59
order by stage_order desc,
case when is_knockout then 1 else 0 end desc,
round_num desc
limit $60)
select *
from data
where inner_total > $61
and ($7::timestamptz is null or
(stage_order, case when is_knockout then $62 else $63 end, round_num) <= (select t.stage_order, t.ko_order, t.round_num from target_group t))
order by stage_order desc, case when is_knockout then $64 else $65 end desc, round_num desc
limit case when $8 is not null then $8::bigint end offset case when $8::bigint is not null and $9::bigint is not null then $9::bigint * $8::bigint end
|
|
29 min
0.8%
|
137 ms
|
12,847
db_user
|
with lasts as (
select distinct on (session) session, screen_name
from user_activities
where created_date >= $1 and created_date <= $2 and screen_name not in ($3,$4)
order by session, created_date desc
)
select screen_name, count(*)::int as sessions
from lasts
group by screen_name
order by sessions desc
limit $5
|
|
29 min
0.8%
|
136 ms
|
12,847
db_user
|
with firsts as (
select distinct on (session) session, screen_name
from user_activities
where created_date >= $1 and created_date <= $2 and screen_name not in ($3,$4)
order by session, created_date
)
select screen_name, count(*)::int as sessions
from firsts
group by screen_name
order by sessions desc
limit $5
|
|
28 min
0.8%
|
130 ms
|
12,852
db_user
|
select screen_name,
count(*)::int as visits,
count(distinct device_id)::int as unique_users,
coalesce(avg(extract($3 from time_spent)), $4)::float8 as avg_seconds
from user_activities
where created_date >= $1 and created_date <= $2 and screen_name not in ($5,$6)
group by screen_name
order by visits desc
limit $7
|
|
26 min
0.7%
|
509 ms
|
3,046
db_user
|
with favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $1)
select c.*,
coalesce(cs.likes, $8) as likes,
coalesce(csu.liked, $9) as liked,
coalesce(cs.comment_count, $10) as comment_count,
coalesce(cs.view_count, $11) as view_count,
jsonb_build_object(
$12, (u.id = any (array [(select ids from favorite_users)])),
$13, count(distinct follower_count.*),
$14, count(distinct following_count.*),
$15, count(distinct post_count.*),
$16, (to_jsonb(u.*) || jsonb_build_object($17, max(ust.team_id)))) as "user"
from comments as c
left join comment_statistics as cs on cs.reference_id = c.id and
cs.comment_to = $18 and
cs.comment_to_sub_type = $19 and
cs.cumulative = $20
left join users as u on u.id = c.user_id
left join user_support_teams as ust on ust.user_id = u.id
left join comment_statistic_users as csu on $1 is not null and csu.user_id = $1 and csu.comment_statistic_id = cs.id
left join user_favorite_users as follower_count on follower_count.user_id != c.user_id and follower_count.favorite_user_id = c.user_id
left join user_favorite_users as following_count on following_count.user_id = c.user_id and following_count.favorite_user_id != c.user_id
left join comments as post_count on post_count.user_id = c.user_id and post_count.comment_to != $21
where c.user_id = $2
and (case when $3::boolean = $22 then c.comment_to = $23 else c.comment_to != $24 end)
and ($4 is null or c.created_date <= $4)
and ($5 is null or c.created_date > $5)
group by c.id, c.created_date, cs.id, u.id, csu.id
order by c.created_date desc
limit case when $6::bigint is not null then $6::bigint end offset case when $7::bigint is not null and $6::bigint is not null then $7::bigint * $6::bigint end
|
|
25 min
0.7%
|
0 ms
|
65,971,296
db_user
|
SELECT commentable_objects.id, commentable_objects.created_date, commentable_objects.reference_id, commentable_objects.type, commentable_objects.event_type, commentable_objects.home_team, commentable_objects.away_team, commentable_objects.league_id, commentable_objects.player_id, commentable_objects.country_id, commentable_objects.match_id, commentable_objects.league_type, commentable_objects.match_time, commentable_objects.round, commentable_objects.stage_name, commentable_objects.stage_mode, commentable_objects.is_knockout, commentable_objects.season_id, commentable_objects.stage_order, commentable_objects.team, commentable_objects.related_incident_ids, commentable_objects.custom_names FROM commentable_objects WHERE commentable_objects.reference_id = $1 AND (commentable_objects.type = $2)
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (reference_id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- reference_id (=): 1
- type (=): 5859622
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
25 min
0.7%
|
118 ms
|
12,846
db_user
|
select extract($3 from created_date at time zone $4)::int as hour,
count(distinct session)::int as sessions
from user_activities
where created_date >= $1 and created_date <= $2
group by hour
order by hour
|
|
25 min
0.7%
|
9,537 ms
|
158
db_user
|
select d.id as device_id,
d.device as device,
u.id as user_id,
u.username as username,
count(pt.id)::int as push_tokens,
max(pt.created_date) as last_token_at
from devices as d
join push_tokens as pt on pt.device_id = d.id
left join lateral (
select coalesce(
(select ptu.user_id
from push_tokens as ptu
where ptu.device_id = d.id and ptu.user_id is not null
order by ptu.created_date desc
limit $4),
(select ud.user_id
from users_devices as ud
where ud.device_id = d.id
order by ud.id desc
limit $5)
) as user_id
) as link on $6
left join users as u on u.id = link.user_id
where lower(d.device) like $1
or lower(u.username) like $1
or lower(u.email) like $1
or d.id = $2
or u.id = $2
group by d.id, d.device, u.id, u.username
order by max(pt.created_date) desc nulls last
limit $3
|
|
25 min
0.7%
|
116 ms
|
12,853
db_user
|
with days as (
select generate_series(date_trunc($3, $1::timestamptz), date_trunc($4, $2::timestamptz), $5)::date as day
),
first_seen_devices as (
select device_id, min(created_date)::date as first_day
from user_activities group by device_id
),
signups as (
select first_day as d, count(*)::int as signups
from first_seen_devices group by first_day
)
select d.day::text as day,
coalesce(s.signups, $6)::int as signups,
(sum(coalesce(s.signups, $7)) over (order by d.day))::int as cumulative
from days d
left join signups s on s.d = d.day
order by d.day
|
|
23 min
0.6%
|
202 ms
|
6,692
db_user
|
with base as (select to_jsonb(co) || jsonb_build_object($8, coalesce(cs.comment_count, $9)) as jsonb,
co.league_type as league_type,
co.round as round_num,
co.stage_name as stage_name,
co.stage_mode as stage_mode,
co.is_knockout as is_knockout,
coalesce(cs.comment_count, $10) as comment_count,
co.match_time as match_time,
co.stage_order as stage_order,
co.reference_id as reference_id
from commentable_objects as co
left join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = $11 and
cs.comment_to_sub_type = $12 and
cs.cumulative = $13
where co.season_id = $1
and co.type = $14
and co.event_type is not null
and co.deleted = $15
and ($2 is null or co.player_id = $2)
and ($3 is null
or co.team = $3
or (co.event_type::text = $16 and (co.home_team = $3 or co.away_team = $3)))
order by coalesce(cs.comment_count, 0) desc, co.match_time desc, co.reference_id desc),
grouped as (select base.*,
case
when league_type <> $17 and round_num is not null and round_num <> $18
then case
when stage_order > $19
then stage_name || $20 || round_num
else $21 || round_num end
when league_type <> $22
and (round_num is null or round_num = $23)
and stage_name is not null
and stage_mode = $24
then stage_name
when league_type = $25
and (round_num is null or round_num = $26)
and stage_name is not null
and stage_mode = $27
then stage_name ||
case when is_knockout then $28 when is_knockout is null then $29 else $30 end
when league_type = $31
and (round_num is null or round_num = $32)
and stage_name is not null
and stage_mode = $33
then stage_name
when league_type = $34
and round_num is not null
and round_num <> $35
and stage_name is not null
and stage_mode = $36
then stage_name || $37 || round_num
else $38
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by comment_count desc, match_time desc, reference_id desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($39, match_time,
$40, round_num,
$41, league_type,
$42, stage_name,
$43, stage_mode,
$44, is_knockout)
order by ranked.comment_count desc, ranked.match_time desc) filter (where rn <= $4) as data,
count(ranked.*) filter (where rn <= $4) as inner_total,
count(ranked.*) as total
from ranked
group by group_name, stage_order, round_num, is_knockout
order by stage_order desc, case when is_knockout then $45 else $46 end desc, round_num desc),
target_group as (select stage_order,
case when is_knockout then $47 else $48 end as ko_order,
round_num
from grouped
where match_time <= $5::timestamptz + interval $49
order by stage_order desc,
case when is_knockout then 1 else 0 end desc,
round_num desc
limit $50)
select *
from data
where inner_total > $51
and ($5::timestamptz is null or
(stage_order, case when is_knockout then $52 else $53 end, round_num) <= (select t.stage_order, t.ko_order, t.round_num from target_group t))
order by stage_order desc, case when is_knockout then $54 else $55 end desc, round_num desc
limit case when $6 is not null then $6::bigint end offset case when $6::bigint is not null and $7::bigint is not null then $7::bigint * $6::bigint end
|
|
22 min
0.6%
|
0 ms
|
542,634,102
db_user
|
BEGIN READ WRITE
|
|
21 min
0.6%
|
72 ms
|
17,190
db_user
|
WITH latest AS (
SELECT DISTINCT ON (session) session, screen_name, user_id, updated_date
FROM user_activities
WHERE updated_date >= NOW() - INTERVAL $1
ORDER BY session, updated_date DESC
)
SELECT screen_name, COUNT(*)::int AS sessions
FROM latest
WHERE screen_name NOT IN ($2)
GROUP BY screen_name
ORDER BY sessions DESC
|
|
19 min
0.5%
|
477 ms
|
2,375
db_user
|
with daily as (
select date_trunc($1, created_date)::date as day,
count(distinct device_id)::int as dau,
count(distinct session)::int as sessions
from user_activities
where created_date >= now() - interval $2
group by day
)
select day::text as day,
dau,
sessions,
(avg(dau) over (order by day rows between $3 preceding and $4 preceding))::float8 as dau_baseline,
(stddev(dau) over (order by day rows between 7 preceding and 1 preceding))::float8 as dau_stddev,
(avg(sessions) over (order by day rows between 7 preceding and 1 preceding))::float8 as s_baseline
from daily
order by day desc
limit $5
|
|
18 min
0.5%
|
0 ms
|
127,002,216
db_user
|
select * from jwt_secrets
order by created_date
|
|
18 min
0.5%
|
4,300 ms
|
254
db_user
|
select u.id::bigint as id,
u.username::text as username,
u.email::text as email,
u.country::bigint as country,
u.premium::boolean as premium,
u.status::text as status,
u.created_date as created_date,
(select max(updated_date) from user_activities where user_id = u.id) as last_activity,
(select array_agg(distinct device_id) from user_activities where user_id = u.id)::bigint[] as device_ids
from users u
where lower(u.username) like $1
or lower(u.email) like $1
or u.id::text = $2
or exists (select $3 from user_activities ua where ua.user_id = u.id and ua.device_id::text = $2)
order by last_activity desc nulls last
limit $4
|
|
17 min
0.5%
|
0 ms
|
37,409,929
db_user
|
SELECT COUNT(user_favorite_teams.id) FROM user_favorite_teams WHERE user_favorite_teams.team_id = $1
Covered by index on (team_id)
Rows: 10477
Row progression: 10477, 28
Row estimates
- team_id (=): 28
Existing indexes
- id PRIMARY
- team_id
- user_id, team_id UNIQUE
|
|
17 min
0.5%
|
60 ms
|
16,844
db_user
|
with updated as (update comment_statistics
set comment_count = greatest(coalesce(comment_count, $3) + $1, $4)
where id = $2
returning *),
related_refs as (select u.reference_id as ref
from updated as u
union
select unnest(co.related_incident_ids)
from commentable_objects as co,
updated as u
where co.reference_id = u.reference_id
and co.type = u.comment_to),
others as (select coalesce(sum(s.comment_count), $5) as comment_count,
coalesce(sum(s.likes), $6) as likes,
coalesce(sum(s.view_count), $7) as view_count
from comment_statistics as s,
updated as u
where s.reference_id in (select ref from related_refs)
and s.comment_to = u.comment_to
and s.comment_to_sub_type = u.comment_to_sub_type
and s.cumulative = u.cumulative
and s.id != u.id)
select u.id,
u.comment_to,
u.comment_to_sub_type,
u.reference_id,
u.cumulative,
(u.likes + (select likes from others)) as likes,
(u.comment_count + (select comment_count from others)) as comment_count,
(u.view_count + (select view_count from others)) as view_count
from updated as u
|
|
17 min
0.5%
|
0 ms
|
33,216,598
db_user
|
INSERT INTO feeds (follower_score, commentable, commentable_type) VALUES ($1, $2, $3) RETURNING id
|
|
16 min
0.4%
|
118 ms
|
7,994
db_user
|
with favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $1)
select c.*,
coalesce(cs.likes, $9) as likes,
coalesce(csu.liked, $10) as liked,
coalesce(cs.comment_count, $11) as comment_count,
coalesce(cs.view_count, $12) as view_count,
jsonb_build_object(
$13, (u.id = any (array[(select ids from favorite_users)])),
$14, count(distinct follower_count.*),
$15, count(distinct following_count.*),
$16, count(distinct post_count.*),
$17, (to_jsonb(u.*) || jsonb_build_object($18, max(ust.team_id)))) as "user"
from comments as c
left join comment_statistics as cs on cs.reference_id = c.id and
cs.comment_to = $19 and
cs.comment_to_sub_type = $20 and
cs.cumulative = $21
left join users as u on u.id = c.user_id
left join user_support_teams as ust on ust.user_id = u.id
left join comment_statistic_users as csu on $1 is not null and csu.user_id = $1 and csu.comment_statistic_id = cs.id
left join user_favorite_users as follower_count on follower_count.user_id != c.user_id and follower_count.favorite_user_id = c.user_id
left join user_favorite_users as following_count on following_count.user_id = c.user_id and following_count.favorite_user_id != c.user_id
left join comments as post_count on post_count.user_id = c.user_id and post_count.comment_to != $22
where c.reference_id in (select $2::bigint
union
select unnest(co.related_incident_ids)
from commentable_objects as co
where co.reference_id = $2
and co.type = $3)
and c.comment_to = $3
and c.comment_to_sub_type = $4
and ($5 is null or c.created_date <= $5)
and ($6 is null or c.created_date > $6)
and ($1 is null or c.user_id not in
(select ub.blocked_user_id from user_blocks as ub where ub.blocker_user_id = $1))
group by c.id, c.created_date, cs.id, u.id, csu.id
order by c.created_date desc
limit case when $7::bigint is not null then $7::bigint end offset case when $8::bigint is not null and $7::bigint is not null then $8::bigint * $7::bigint end
|
|
16 min
0.4%
|
210 ms
|
4,501
db_user
|
with device_owners as (
select distinct on (ua.device_id)
ua.device_id,
ua.user_id as attributed_user_id,
ua.updated_date as last_login_at
from user_activities ua
where ua.user_id is not null
order by ua.device_id, ua.updated_date desc
)
select ua.device_id::bigint as device_id,
device_owners.attributed_user_id::bigint as user_id,
u.ip_address::text as ip,
u.country::bigint as country_id,
count(distinct ua.session)::int as active_count,
max(ua.updated_date) as last_seen
from user_activities ua
left join device_owners on device_owners.device_id = ua.device_id
left join users u on u.id = device_owners.attributed_user_id
where ua.updated_date >= now() - make_interval(mins => $1)
and u.ip_address is not null
group by ua.device_id, device_owners.attributed_user_id, u.ip_address, u.country
|
|
16 min
0.4%
|
396 ms
|
2,372
db_user
|
select
(select count(distinct device_id) from user_activities where created_date >= now() - interval $1)::int as dau,
(select count(distinct device_id) from user_activities where created_date >= now() - interval $2)::int as mau,
(select count(distinct device_id) from user_activities where created_date >= now() - interval $3)::int as wau,
coalesce((select count(*) from (
select device_id, min(created_date) as first_seen
from user_activities group by device_id
) f where f.first_seen >= now() - interval $4)::int, $5) as signups_7d,
(select extract($6 from avg(s.dur))::float8 from (
select session, sum(time_spent) as dur
from user_activities
where created_date >= now() - interval $7
group by session having sum(time_spent) > interval $8
) s) as avg_session_secs
|
|
15 min
0.4%
|
0 ms
|
27,770,177
db_user
|
UPDATE commentable_objects SET reference_id = $1, type = $2, event_type = $3, home_team = $4, away_team = $5, home_coach = $6, away_coach = $7, league_id = $8, player_id = $9, secondary_player_id = $10, country_id = $11, match_id = $12, referee_id = $13, league_type = $14, match_time = $15, round = $16, stage_name = $17, stage_mode = $18, is_knockout = $19, season_id = $20, stage_order = $21, team = $22, related_incident_ids = $23, custom_names = $24 WHERE commentable_objects.id = $25
Covered by index on (id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
14 min
0.4%
|
64 ms
|
12,853
db_user
|
with sess as (
select session,
count(*) filter (where screen_name not in ($3,$4)) as screens,
extract($5 from coalesce(sum(time_spent), interval $6)) as total_secs
from user_activities
where created_date >= $1 and created_date <= $2
group by session
)
select
coalesce(avg(total_secs) filter (where total_secs > $7), $8)::float8 as avg_session_secs,
coalesce(avg(screens), $9)::float8 as avg_screens_per_session,
count(*)::int as total_sessions,
coalesce(count(*) filter (where screens <= $10 and total_secs < $11) * $12 / nullif(count(*), $13), $14)::float8 as bounce_rate,
coalesce(count(*) filter (where total_secs < $15), $16)::int as bucket_lt_10s,
coalesce(count(*) filter (where total_secs >= $17 and total_secs < $18), $19)::int as bucket_10_to_30s,
coalesce(count(*) filter (where total_secs >= $20 and total_secs < $21), $22)::int as bucket_30_to_60s,
coalesce(count(*) filter (where total_secs >= $23 and total_secs < $24), $25)::int as bucket_1_to_2m,
coalesce(count(*) filter (where total_secs >= $26 and total_secs < $27), $28)::int as bucket_2_to_5m,
coalesce(count(*) filter (where total_secs >= $29 and total_secs < $30), $31)::int as bucket_5_to_10m,
coalesce(count(*) filter (where total_secs >= $32 and total_secs < $33), $34)::int as bucket_10_to_30m,
coalesce(count(*) filter (where total_secs >= $35), $36)::int as bucket_gt_30m
from sess
|
|
13 min
0.4%
|
4 ms
|
171,554
db_user
|
with updated as (update comment_statistics
set view_count = greatest(coalesce(view_count, $3) + $1, $4)
where id = $2
returning *),
related_refs as (select u.reference_id as ref
from updated as u
union
select unnest(co.related_incident_ids)
from commentable_objects as co,
updated as u
where co.reference_id = u.reference_id
and co.type = u.comment_to),
others as (select coalesce(sum(s.comment_count), $5) as comment_count,
coalesce(sum(s.likes), $6) as likes,
coalesce(sum(s.view_count), $7) as view_count
from comment_statistics as s,
updated as u
where s.reference_id in (select ref from related_refs)
and s.comment_to = u.comment_to
and s.comment_to_sub_type = u.comment_to_sub_type
and s.cumulative = u.cumulative
and s.id != u.id)
select u.id,
u.comment_to,
u.comment_to_sub_type,
u.reference_id,
u.cumulative,
(u.likes + (select likes from others)) as likes,
(u.comment_count + (select comment_count from others)) as comment_count,
(u.view_count + (select view_count from others)) as view_count
from updated as u
|
|
13 min
0.4%
|
60 ms
|
12,850
db_user
|
select screen_name,
count(*)::int as visits,
coalesce(avg(extract($3 from time_spent)), $4)::float8 as avg_seconds
from user_activities
where created_date >= $1 and created_date <= $2
and screen_name not in ($5,$6) and time_spent is not null
group by screen_name
having count(*) >= $7
order by avg_seconds desc
limit $8
|
|
11 min
0.3%
|
3 ms
|
209,587
db_user
|
INSERT INTO commentable_objects (reference_id, type, event_type, home_team, away_team, league_id, player_id, match_id, league_type, match_time, round, stage_name, stage_mode, season_id, stage_order, team) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16) RETURNING id
|
|
11 min
0.3%
|
0 ms
|
27,815,041
db_user
|
INSERT INTO feeds (follower_score, commentable) VALUES ($1, $2) RETURNING id
|
|
10 min
0.3%
|
0 ms
|
28,445,521
db_user
|
UPDATE commentable_objects SET reference_id = $1, type = $2, event_type = $3, home_team = $4, away_team = $5, league_id = $6, player_id = $7, country_id = $8, match_id = $9, league_type = $10, match_time = $11, round = $12, stage_name = $13, stage_mode = $14, is_knockout = $15, season_id = $16, stage_order = $17, team = $18, related_incident_ids = $19 WHERE commentable_objects.id = $20
Covered by index on (id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
10 min
0.3%
|
0 ms
|
40,475,907
db_user
|
SELECT commentable_objects.id, commentable_objects.created_date, commentable_objects.reference_id, commentable_objects.type, commentable_objects.event_type, commentable_objects.home_team, commentable_objects.away_team, commentable_objects.home_coach, commentable_objects.away_coach, commentable_objects.league_id, commentable_objects.player_id, commentable_objects.secondary_player_id, commentable_objects.country_id, commentable_objects.match_id, commentable_objects.league_type, commentable_objects.match_time, commentable_objects.round, commentable_objects.stage_name, commentable_objects.stage_mode, commentable_objects.is_knockout, commentable_objects.season_id, commentable_objects.stage_order, commentable_objects.team, commentable_objects.related_incident_ids, commentable_objects.custom_names FROM commentable_objects WHERE commentable_objects.reference_id = $1 AND (commentable_objects.type = $2)
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (reference_id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- reference_id (=): 1
- type (=): 5859622
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
10 min
0.3%
|
0 ms
|
58,453,121
db_user
|
SELECT commentable_objects.id, commentable_objects.created_date, commentable_objects.reference_id, commentable_objects.type, commentable_objects.event_type, commentable_objects.home_team, commentable_objects.away_team, commentable_objects.league_id, commentable_objects.player_id, commentable_objects.country_id, commentable_objects.match_id FROM commentable_objects WHERE commentable_objects.reference_id = $1 AND (commentable_objects.type = $2)
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (reference_id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- reference_id (=): 1
- type (=): 5859622
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
9 min
0.3%
|
246 ms
|
2,228
db_user
|
with base as (select co.*,
coalesce(cs.comment_count, $6) as comment_count
from commentable_objects as co
left join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = $7 and
cs.comment_to_sub_type = $8 and
cs.cumulative = $9
where co.season_id = $1
and co.type = $10
and co.event_type is not null
and co.deleted = $11
and ($2 is null or co.player_id = $2)
and ($3 is null
or co.team = $3
or (co.event_type::text = $12 and (co.home_team = $3 or co.away_team = $3)))
order by coalesce(cs.comment_count, 0) desc, co.match_time desc, co.reference_id desc),
grouped as (select base.*,
case
when league_type <> $13 and round is not null and round <> $14
then case
when stage_order > $15
then coalesce(custom_names ->> $4::text, stage_name) || $16 || round
else $17 || round end
when league_type <> $18
and (round is null or round = $19)
and stage_name is not null
and stage_mode = $20
then coalesce(custom_names ->> $4::text, stage_name)
when league_type = $21
and (round is null or round = $22)
and stage_name is not null
and stage_mode = $23
then coalesce(custom_names ->> $4::text, stage_name) ||
case when is_knockout then $24 when is_knockout is null then $25 else $26 end
when league_type = $27
and (round is null or round = $28)
and stage_name is not null
and stage_mode = $29
then coalesce(custom_names ->> $4::text, stage_name)
when league_type = $30
and round is not null
and round <> $31
and stage_name is not null
and stage_mode = $32
then coalesce(custom_names ->> $4::text, stage_name) || $33 || round
else $34
end as group_name
from base)
select count(*)
from grouped
where ($5::text is null or group_name = $5)
|
|
9 min
0.2%
|
17 ms
|
30,446
db_user
|
INSERT INTO comment_statistic_users (user_id, comment_statistic_id, viewed) VALUES ($1, $2, $3) RETURNING id
|
|
9 min
0.2%
|
187 ms
|
2,793
db_user
|
with device_owners as (
select distinct on (ua.device_id)
ua.device_id,
ua.user_id as attributed_user_id,
ua.updated_date as last_login_at
from user_activities ua
where ua.user_id is not null
order by ua.device_id, ua.updated_date desc
)
select ua.device_id::bigint as device_id,
u.country::bigint as country_id,
host(coalesce(d.ip_address, u.ip_address)) as ip,
max(ua.updated_date) as last_seen
from user_activities ua
left join devices d on d.id = ua.device_id
left join device_owners on device_owners.device_id = ua.device_id
left join users u on u.id = device_owners.attributed_user_id
where ua.updated_date >= now() - make_interval(mins => $1)
group by ua.device_id, u.country, d.ip_address, u.ip_address
|
|
8 min
0.2%
|
8 ms
|
61,768
db_user
|
INSERT INTO comment_statistic_users (user_id, comment_statistic_id, liked) VALUES ($1, $2, $3) RETURNING id
|
|
8 min
0.2%
|
0 ms
|
29,107,279
db_user
|
UPDATE commentable_objects SET reference_id = $1, type = $2, event_type = $3, home_team = $4, away_team = $5, league_id = $6, player_id = $7, country_id = $8, match_id = $9, league_type = $10, match_time = $11, round = $12, stage_name = $13, stage_mode = $14, is_knockout = $15, season_id = $16, stage_order = $17 WHERE commentable_objects.id = $18
Covered by index on (id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
8 min
0.2%
|
175 ms
|
2,793
db_user
|
with device_owners as (
select distinct on (ua.device_id)
ua.device_id,
ua.user_id as attributed_user_id,
ua.updated_date as last_login_at
from user_activities ua
where ua.user_id is not null
order by ua.device_id, ua.updated_date desc
)
select ua.id::bigint as id,
ua.device_id::bigint as device_id,
device_owners.attributed_user_id::bigint as user_id,
u.country::bigint as country_id,
host(coalesce(d.ip_address, u.ip_address)) as ip,
u.username::text as username,
ua.screen_name::text as screen_name,
ua.session::text as session,
ua.updated_date as ts
from user_activities ua
left join devices d on d.id = ua.device_id
left join device_owners on device_owners.device_id = ua.device_id
left join users u on u.id = device_owners.attributed_user_id
where ua.updated_date >= now() - interval $1
order by ua.updated_date desc
limit $2
|
|
8 min
0.2%
|
0 ms
|
28,791,992
db_user
|
SELECT commentable_objects.id, commentable_objects.created_date, commentable_objects.reference_id, commentable_objects.type, commentable_objects.event_type, commentable_objects.home_team, commentable_objects.away_team, commentable_objects.league_id, commentable_objects.player_id, commentable_objects.country_id, commentable_objects.match_id, commentable_objects.league_type, commentable_objects.match_time, commentable_objects.round, commentable_objects.stage_name, commentable_objects.stage_mode, commentable_objects.is_knockout, commentable_objects.season_id, commentable_objects.stage_order, commentable_objects.team, commentable_objects.related_incident_ids FROM commentable_objects WHERE commentable_objects.reference_id = $1 AND (commentable_objects.type = $2)
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (reference_id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- reference_id (=): 1
- type (=): 5859622
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
7 min
0.2%
|
223 ms
|
2,019
db_user
|
with base as (select to_jsonb(co) || jsonb_build_object($8, coalesce(cs.comment_count, $9)) as jsonb,
co.league_type as league_type,
co.round as round_num,
co.stage_name as stage_name,
co.stage_mode as stage_mode,
co.is_knockout as is_knockout,
coalesce(cs.comment_count, $10) as comment_count,
co.match_time as match_time,
co.stage_order as stage_order,
co.reference_id as reference_id
from commentable_objects as co
left join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = $11 and
cs.comment_to_sub_type = $12 and
cs.cumulative = $13
where co.season_id = $1
and co.type = $14
and co.event_type is not null
and co.deleted = $15
and ($2 is null or co.player_id = $2)
and ($3 is null or co.team = $3)
order by coalesce(cs.comment_count, 0) desc, co.match_time desc, co.reference_id desc),
grouped as (select base.*,
case
when league_type <> $16 and round_num is not null and round_num <> $17
then case
when stage_order > $18
then stage_name || $19 || round_num
else $20 || round_num end
when league_type <> $21
and (round_num is null or round_num = $22)
and stage_name is not null
and stage_mode = $23
then stage_name
when league_type = $24
and (round_num is null or round_num = $25)
and stage_name is not null
and stage_mode = $26
then stage_name ||
case when is_knockout then $27 when is_knockout is null then $28 else $29 end
when league_type = $30
and (round_num is null or round_num = $31)
and stage_name is not null
and stage_mode = $32
then stage_name
when league_type = $33
and round_num is not null
and round_num <> $34
and stage_name is not null
and stage_mode = $35
then stage_name || $36 || round_num
else $37
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by comment_count desc, match_time desc, reference_id desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($38, match_time,
$39, round_num,
$40, league_type,
$41, stage_name,
$42, stage_mode,
$43, is_knockout)
order by ranked.comment_count desc, ranked.match_time desc) filter (where rn <= $4) as data,
count(ranked.*) filter (where rn <= $4) as inner_total,
count(ranked.*) as total
from ranked
group by group_name, stage_order, round_num, is_knockout
order by stage_order desc, case when is_knockout then $44 else $45 end desc, round_num desc),
target_group as (select stage_order,
case when is_knockout then $46 else $47 end as ko_order,
round_num
from grouped
where match_time <= $5::timestamptz + interval $48
order by stage_order desc,
case when is_knockout then 1 else 0 end desc,
round_num desc
limit $49)
select *
from data
where inner_total > $50
and ($5::timestamptz is null or
(stage_order, case when is_knockout then $51 else $52 end, round_num) <= (select t.stage_order, t.ko_order, t.round_num from target_group t))
order by stage_order desc, case when is_knockout then $53 else $54 end desc, round_num desc
limit case when $6 is not null then $6::bigint end offset case when $6::bigint is not null and $7::bigint is not null then $7::bigint * $6::bigint end
|
|
7 min
0.2%
|
1 ms
|
547,861
db_user
|
select (array_agg(pt.push_token order by pt.created_date desc))[$19] as push_token,
host(max(u.ip_address)) as ip,
max(u.country) as country_id,
max(u.last_dialect) as last_dialect
from notification_preferences as np
left join push_tokens as pt on pt.user_id = np.user_id
left join users as u on u.id = np.user_id
left join notification_preferences as g
on g.user_id = np.user_id and g.type = $20::notification_type
where np.reference_id = $1
and np.type = $2::notification_type
and ($3::boolean is null or np.score = $3::boolean)
and ($4::boolean is null or np.match_result = $4::boolean)
and ($5::boolean is null or np.match_status = $5::boolean)
and ($6::boolean is null or np.goal = $6::boolean)
and ($7::boolean is null or np.yellow_card = $7::boolean)
and ($8::boolean is null or np.red_card = $8::boolean)
and ($9::boolean is null or np.substitution = $9::boolean)
and ($10::boolean is null or np.var_result = $10::boolean)
and ($11::boolean is null or np.assist = $11::boolean)
and ($12::boolean is null or np.referee = $12::boolean)
and ($13::boolean is null or np.lineup = $13::boolean)
and ($14::boolean is null or np.transfers = $14::boolean)
and ($15::boolean is null or np.most_valuable_player = $15::boolean)
and ($3::boolean is null or coalesce(g.score, $21))
and ($4::boolean is null or coalesce(g.match_result, $22))
and ($5::boolean is null or coalesce(g.match_status, $23))
and ($6::boolean is null or coalesce(g.goal, $24))
and ($7::boolean is null or coalesce(g.yellow_card, $25))
and ($8::boolean is null or coalesce(g.red_card, $26))
and ($9::boolean is null or coalesce(g.substitution, $27))
and ($10::boolean is null or coalesce(g.var_result, $28))
and ($11::boolean is null or coalesce(g.assist, $29))
and ($12::boolean is null or coalesce(g.referee, $30))
and ($13::boolean is null or coalesce(g.lineup, $31))
and ($14::boolean is null or coalesce(g.transfers, $32))
and ($15::boolean is null or coalesce(g.most_valuable_player, $33))
and (
np.type = $34::notification_type
or $16::bigint is null
or not exists (
select $35
from notification_preferences as m
where m.user_id = np.user_id
and m.type = $36::notification_type
and m.reference_id = $16
and m.updated_date > np.updated_date
)
)
group by np.user_id
having count(pt.push_token) > $37
limit $17 offset $18 * $17
|
|
7 min
0.2%
|
0 ms
|
10,787,946
db_user
|
with related_refs as (select $1::bigint as ref
union
select unnest(co.related_incident_ids)
from commentable_objects as co
where co.reference_id = $1
and co.type = $2),
related_stats as (select s.id, s.reference_id, s.comment_count, s.likes, s.view_count
from comment_statistics as s
where s.reference_id in (select ref from related_refs)
and s.comment_to = $2
and s.comment_to_sub_type = $3
and s.cumulative = $4),
aggregated_object as (select coalesce(sum(comment_count), $6) as comment_count,
coalesce(sum(likes), $7) as likes,
coalesce(sum(view_count), $8) as view_count
from related_stats),
aggregated_user as (select bool_or(csu.liked) as liked,
bool_or(csu.viewed) as viewed,
coalesce(sum(csu.comment_count), $9) as comment_count,
count(*) as row_count,
(array_agg(csu.id
order by csu.liked desc nulls last,
(rs.reference_id = $1) desc,
csu.id))[$10] as id,
(array_agg(csu.comment_statistic_id
order by csu.liked desc nulls last,
(rs.reference_id = $1) desc,
csu.id))[$11] as comment_statistic_id
from comment_statistic_users as csu
join related_stats as rs on rs.id = csu.comment_statistic_id
where $5 is not null
and csu.user_id = $5)
select to_jsonb(cs.*) || jsonb_build_object(
$12, (select comment_count from aggregated_object),
$13, (select likes from aggregated_object),
$14, (select view_count from aggregated_object)
) as object_statistics,
case
when (select row_count from aggregated_user) > $15 then
jsonb_build_object(
$16, (select id from aggregated_user),
$17, $5::bigint,
$18, (select comment_statistic_id from aggregated_user),
$19, (select liked from aggregated_user),
$20, (select viewed from aggregated_user),
$21, (select comment_count from aggregated_user)
)
end as user_based_object_statistics
from comment_statistics as cs
where cs.reference_id = $1
and cs.comment_to = $2
and cs.comment_to_sub_type = $3
and cs.cumulative = $4
limit $22
Details
CREATE INDEX CONCURRENTLY ON comment_statistics (reference_id)
Rows: 37685
Row progression: 37685, 1
Row estimates
- reference_id (=): 1
- comment_to (=): 3769
- comment_to_sub_type (=): 7537
- cumulative (=): 18843
Existing indexes
- id PRIMARY
- comment_to, comment_to_sub_type, reference_id, cumulative UNIQUE
- comment_to, reference_id, comment_to_sub_type, cumulative
|
|
7 min
0.2%
|
6 ms
|
70,927
db_user
|
select
count(*) filter (where nullif($1, $2)::timestamptz is not null
and created_date >= nullif($1, $3)::timestamptz)::int as since_marker,
count(*) filter (where created_date >= now() - interval $4)::int as last1h,
count(*) filter (where created_date >= now() - interval $5)::int as last24h,
count(*) filter (where created_date >= (date_trunc($6, now() at time zone $7)) at time zone $8)::int as today,
count(*) filter (where created_date >= (date_trunc($9, now() at time zone $10) - interval $11) at time zone $12
and created_date < now() - interval $13)::int as yesterday_same_time,
count(*)::int as total
from devices
|
|
7 min
0.2%
|
35 ms
|
11,394
db_user
|
with favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $1),
favorite_leagues as (select array_agg(league_id) as ids
from user_favorite_leagues
where user_id = $1),
favorite_teams as (select array_agg(team_id) as ids
from user_favorite_teams
where user_id = $1),
favorite_matches as (select array_agg(match_id) as ids
from user_favorite_matches
where user_id = $1 and following),
favorite_players as (select array_agg(player_id) as ids
from user_favorite_players
where user_id = $1),
top_feeds as (select distinct f.id as feed_id,
c.id as comment_id,
co.id as commentable_object_id,
case
when $2::text = $14 or $3::boolean = $15 then co.created_epoch
else (f.base_score + f.extra_score + f.follower_score) end as score
from feeds as f
left join commentable_objects as co on co.id = f.commentable
left join comments as c on c.id = co.reference_id and co.type = $16
cross join favorite_leagues as fl
cross join favorite_teams as ft
cross join favorite_matches as fm
cross join favorite_players as fp
cross join favorite_users as fu
where f.commentable_type = $17
and c.comment_to != $18
and (co.type != $19 or $4::bigint[] is null or c.language = any (array [$4::bigint[]]))
and ((co.type = $20 and c.id is not null) or co.type != $21)
and (co.type = $22 or $5::bigint[] is null or co.league_id = any (array [$5::bigint[]]))
and (co.type = $23 or $6::bigint[] is null or co.home_team = any (array [$6::bigint[]]) or co.away_team = any (array [$6::bigint[]]))
and (co.type = $24 or $7::bigint[] is null or co.player_id = any (array [$7::bigint[]]))
and (co.type = $25 or $8::event_type[] is null or co.event_type = any (array [$8::event_type[]]))
and (co.type = $26 or $9::comment_type[] is null or co.type = any (array [$9::comment_type[]]))
and (co.type = $27 or $10 is null or exists (select $28
from generate_subscripts($10::bigint[][], $29) s
where array [
($10::bigint[][])[s][$30],
($10::bigint[][])[s][$31]
] = array [co.home_team, co.away_team]))
and ($11::timestamptz is null or co.created_date <= $11::timestamptz)
and ($3::boolean is null or $3::boolean = $32 or
co.league_id = any (fl.ids) or
co.home_team = any (ft.ids) or
co.away_team = any (ft.ids) or
co.match_id = any (fm.ids) or
co.player_id = any (fp.ids) or
c.user_id = any (fu.ids))
and ($1 is null or c.user_id not in
(select ub.blocked_user_id from user_blocks as ub where ub.blocker_user_id = $1))
order by score desc
limit $12 offset $13 * $12)
select to_jsonb(fe.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
to_jsonb(c.*) || jsonb_build_object(
$33, coalesce(fe.display_reference_id, c.reference_id),
$34, coalesce(cs.likes, $35),
$36, coalesce(csu.liked, $37),
$38, coalesce(cs.comment_count, $39),
$40, coalesce(cs.view_count, $41),
$42, jsonb_build_object(
$43, (u.id = any (array [(select ids from favorite_users)])),
$44, (select count(*)
from user_favorite_users as fc
where fc.favorite_user_id = c.user_id and fc.user_id != c.user_id),
$45, (select count(*)
from user_favorite_users as fg
where fg.user_id = c.user_id and fg.favorite_user_id != c.user_id),
$46, (select count(*)
from comments as pc
where pc.user_id = c.user_id and pc.comment_to != $47),
$48, (to_jsonb(u.*) || jsonb_build_object($49,
(select max(ust.team_id)
from user_support_teams as ust
where ust.user_id = u.id))))) as comment_response
from top_feeds as f
left join feeds as fe on fe.id = f.feed_id
left join commentable_objects as co on co.id = f.commentable_object_id
left join comments as c on c.id = f.comment_id
left join users as u on u.id = c.user_id
left join comment_statistics as cs on cs.comment_to = $50 and
cs.reference_id = c.id and
cs.comment_to_sub_type = $51 and
cs.cumulative = $52
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $1 and
$1 is not null
order by f.score desc
|
|
6 min
0.2%
|
217 ms
|
1,770
db_user
|
with base as (select to_jsonb(co) || jsonb_build_object($8, coalesce(cs.comment_count, $9)) as jsonb,
co.league_type as league_type,
co.round as round_num,
co.stage_name as stage_name,
co.stage_mode as stage_mode,
co.is_knockout as is_knockout,
coalesce(cs.comment_count, $10) as comment_count,
co.match_time as match_time,
co.stage_order as stage_order,
co.reference_id as reference_id
from commentable_objects as co
left join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = $11 and
cs.comment_to_sub_type = $12 and
cs.cumulative = $13
where co.season_id = $1
and co.type = $14
and co.event_type is not null
and co.deleted = $15
and ($2 is null or co.player_id = $2)
and ($3 is null or co.team = $3)
order by coalesce(cs.comment_count, 0) desc, co.match_time desc, co.reference_id desc),
grouped as (select base.*,
case
when league_type <> $16 and round_num is not null and round_num <> $17
then $18 || round_num
when league_type = $19
and (round_num is null or round_num = $20)
and stage_name is not null
and stage_mode = $21
then stage_name ||
case when is_knockout then $22 when is_knockout is null then $23 else $24 end
when league_type = $25
and (round_num is null or round_num = $26)
and stage_name is not null
and stage_mode = $27
then stage_name
when league_type = $28
and round_num is not null
and round_num <> $29
and stage_name is not null
and stage_mode = $30
then stage_name || $31 || round_num
else $32
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by comment_count desc, match_time desc, reference_id desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($33, match_time,
$34, round_num,
$35, league_type,
$36, stage_name,
$37, stage_mode,
$38, is_knockout)
order by ranked.comment_count desc, ranked.match_time desc) filter (where rn <= $4) as data,
count(ranked.*) filter (where rn <= $4) as inner_total,
count(ranked.*) as total
from ranked
group by group_name, stage_order, round_num, is_knockout
order by stage_order desc, case when is_knockout then $39 else $40 end desc, round_num desc),
target_group as (select stage_order,
case when is_knockout then $41 else $42 end as ko_order,
round_num
from grouped
where match_time <= $5::timestamptz + interval $43
order by stage_order desc,
case when is_knockout then 1 else 0 end desc,
round_num desc
limit $44)
select *
from data
where inner_total > $45
and ($5::timestamptz is null or
(stage_order, case when is_knockout then $46 else $47 end, round_num) <= (select t.stage_order, t.ko_order, t.round_num from target_group t))
order by stage_order desc, case when is_knockout then $48 else $49 end desc, round_num desc
limit case when $6 is not null then $6::bigint end offset case when $6::bigint is not null and $7::bigint is not null then $7::bigint * $6::bigint end
|
|
6 min
0.2%
|
0 ms
|
36,386,745
db_user
|
SELECT commentable_objects.id, commentable_objects.created_date, commentable_objects.reference_id, commentable_objects.type, commentable_objects.event_type, commentable_objects.home_team, commentable_objects.away_team, commentable_objects.league_id, commentable_objects.player_id, commentable_objects.country_id, commentable_objects.match_id, commentable_objects.league_type, commentable_objects.match_time, commentable_objects.round, commentable_objects.stage_name, commentable_objects.stage_mode, commentable_objects.is_knockout, commentable_objects.season_id, commentable_objects.stage_order FROM commentable_objects WHERE commentable_objects.reference_id = $1 AND (commentable_objects.type = $2)
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (reference_id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- reference_id (=): 1
- type (=): 5859622
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
6 min
0.2%
|
0 ms
|
21,003,896
db_user
|
UPDATE commentable_objects SET reference_id = $1, type = $2, event_type = $3, home_team = $4, away_team = $5, league_id = $6, player_id = $7, country_id = $8, match_id = $9 WHERE commentable_objects.id = $10
Covered by index on (id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
6 min
0.2%
|
0 ms
|
20,655,678
db_user
|
INSERT INTO commentable_objects (created_date, reference_id, type) VALUES ($1, $2, $3) RETURNING id
|
|
6 min
0.2%
|
0 ms
|
9,644,301
db_user
|
INSERT INTO commentable_objects (reference_id, type, home_team, league_id, player_id, match_id) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id
|
|
6 min
0.2%
|
6 ms
|
53,701
db_user
|
UPDATE comment_statistic_users SET user_id = $1, comment_statistic_id = $2, liked = $3, viewed = $4, comment_count = $5 WHERE comment_statistic_users.id = $6
Covered by index on (id)
Rows: 86596
Row progression: 86596, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- user_id, comment_statistic_id UNIQUE
|
|
6 min
0.2%
|
0 ms
|
9,533,772
db_user
|
INSERT INTO commentable_objects (reference_id, type, away_team, league_id, player_id, match_id) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id
|
|
5 min
0.1%
|
1 ms
|
414,538
db_user
|
select ufp.player_id as reference_id,
count(ufp.user_id) as followers
from user_favorite_players as ufp
where ufp.player_id = any(array [$1::bigint[]])
group by ufp.player_id
Details
CREATE INDEX CONCURRENTLY ON user_favorite_players (player_id)
Rows: 29351
Row progression: 29351, 23
Row estimates
- player_id (=): 23
Existing indexes
- id PRIMARY
- user_id, player_id UNIQUE
|
|
5 min
0.1%
|
0 ms
|
64,599,608
db_user
|
SELECT $2 FROM ONLY "public"."commentable_objects" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
5 min
0.1%
|
25 ms
|
11,646
db_user
|
with base as (select (to_jsonb(co) - $10) || jsonb_build_object($11, coalesce(cs.comment_count, $12)) as jsonb,
co.league_type as league_type,
co.round as round_num,
coalesce(co.custom_names ->> $1::text, co.stage_name) as stage_name,
co.stage_mode as stage_mode,
co.is_knockout as is_knockout,
coalesce(cs.comment_count, $13) as comment_count,
co.match_time as match_time,
co.stage_order as stage_order,
co.reference_id as reference_id
from commentable_objects as co
left join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = $14 and
cs.comment_to_sub_type = $15 and
cs.cumulative = $16
where co.season_id = $2
and co.type = $17
and co.event_type is not null
and co.deleted = $18
and ($3 is null or co.player_id = $3)
and ($4 is null
or co.team = $4
or (co.event_type::text = $19 and (co.home_team = $4 or co.away_team = $4)))
and ($5 is null
or (co.home_coach = $5 and (co.team = co.home_team or co.event_type::text = $20))
or (co.away_coach = $5 and (co.team = co.away_team or co.event_type::text = $21)))
order by coalesce(cs.comment_count, 0) desc, co.match_time desc, co.reference_id desc),
grouped as (select base.*,
case
when league_type <> $22 and round_num is not null and round_num <> $23
then case
when stage_order > $24
then stage_name || $25 || round_num
else $26 || round_num end
when league_type <> $27
and (round_num is null or round_num = $28)
and stage_name is not null
and stage_mode = $29
then stage_name
when league_type = $30
and (round_num is null or round_num = $31)
and stage_name is not null
and stage_mode = $32
then stage_name ||
case when is_knockout then $33 when is_knockout is null then $34 else $35 end
when league_type = $36
and (round_num is null or round_num = $37)
and stage_name is not null
and stage_mode = $38
then stage_name
when league_type = $39
and round_num is not null
and round_num <> $40
and stage_name is not null
and stage_mode = $41
then stage_name || $42 || round_num
else $43
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by comment_count desc, match_time desc, reference_id desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($44, match_time,
$45, round_num,
$46, league_type,
$47, stage_name,
$48, stage_mode,
$49, is_knockout)
order by ranked.comment_count desc, ranked.match_time desc) filter (where rn <= $6) as data,
count(ranked.*) filter (where rn <= $6) as inner_total,
count(ranked.*) as total
from ranked
group by group_name, stage_order, round_num, is_knockout
order by stage_order desc, case when is_knockout then $50 else $51 end desc, round_num desc),
target_group as (select stage_order,
case when is_knockout then $52 else $53 end as ko_order,
round_num
from grouped
where match_time <= $7::timestamptz + interval $54
order by stage_order desc,
case when is_knockout then 1 else 0 end desc,
round_num desc
limit $55)
select *
from data
where inner_total > $56
and ($7::timestamptz is null or
(stage_order, case when is_knockout then $57 else $58 end, round_num) <= (select t.stage_order, t.ko_order, t.round_num from target_group t))
order by stage_order desc, case when is_knockout then $59 else $60 end desc, round_num desc
limit case when $8 is not null then $8::bigint end offset case when $8::bigint is not null and $9::bigint is not null then $9::bigint * $8::bigint end
|
|
4 min
0.1%
|
367 ms
|
713
db_user
|
with favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $1)
select c.*,
coalesce(cs.likes, $3) as likes,
coalesce(csu.liked, $4) as liked,
coalesce(cs.comment_count, $5) as comment_count,
coalesce(cs.view_count, $6) as view_count,
jsonb_build_object(
$7, (u.id = any (array [(select ids from favorite_users)])),
$8, count(distinct follower_count.*),
$9, count(distinct following_count.*),
$10, count(distinct post_count.*),
$11, (to_jsonb(u.*) || jsonb_build_object($12, max(ust.team_id)))) as "user"
from comments as c
left join comment_statistics as cs on cs.reference_id = c.id and
cs.comment_to = $13 and
cs.comment_to_sub_type = $14 and
cs.cumulative = $15
left join users as u on u.id = c.user_id
left join user_support_teams as ust on ust.user_id = u.id
left join comment_statistic_users as csu on $1 is not null and csu.user_id = $1 and csu.comment_statistic_id = cs.id
left join user_favorite_users as follower_count on follower_count.user_id != c.user_id and follower_count.favorite_user_id = c.user_id
left join user_favorite_users as following_count on following_count.user_id = c.user_id and following_count.favorite_user_id != c.user_id
left join comments as post_count on post_count.user_id = c.user_id and post_count.comment_to != $16
where c.id = any ($2::bigint[])
group by c.id, cs.id, u.id, csu.id
|
|
4 min
0.1%
|
6 ms
|
45,715
db_user
|
with favorite_leagues as (select array_agg(league_id) as ids
from user_favorite_leagues
where user_id = $1),
favorite_teams as (select array_agg(team_id) as ids
from user_favorite_teams
where user_id = $1),
favorite_matches as (select array_agg(match_id) as ids
from user_favorite_matches
where user_id = $1 and following),
favorite_players as (select array_agg(player_id) as ids
from user_favorite_players
where user_id = $1),
favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $1)
select count(distinct f.id)
from feeds as f
left join commentable_objects as co on co.id = f.commentable
left join comments as c on c.id = co.reference_id and co.type = $12
cross join favorite_leagues as fl
cross join favorite_teams as ft
cross join favorite_matches as fm
cross join favorite_players as fp
cross join favorite_users as fu
where f.commentable_type = $13
and c.comment_to != $14
and (co.type != $15 or $2::bigint[] is null or c.language = any (array [$2::bigint[]]))
and ((co.type = $16 and c.id is not null) or co.type != $17)
and (co.type = $18 or $3::bigint[] is null or co.league_id = any (array [$3::bigint[]]))
and (co.type = $19 or $4::bigint[] is null or co.home_team = any (array [$4::bigint[]]) or co.away_team = any (array [$4::bigint[]]))
and (co.type = $20 or $5::bigint[] is null or co.player_id = any (array [$5::bigint[]]))
and (co.type = $21 or $6::event_type[] is null or co.event_type = any (array [$6::event_type[]]))
and (co.type = $22 or $7::comment_type[] is null or co.type = any (array [$7::comment_type[]]))
and (co.type = $23 or $8 is null or exists (select $24
from generate_subscripts($8::bigint[][], $25) s
where array [
($8::bigint[][])[s][$26],
($8::bigint[][])[s][$27]
] = array [co.home_team, co.away_team]))
and ($9::timestamptz is null or co.created_date <= $9::timestamptz)
and ($10::timestamptz is null or co.created_date > $10::timestamptz)
and ($11::boolean is null or $11::boolean = $28 or
co.league_id = any (fl.ids) or
co.home_team = any (ft.ids) or
co.away_team = any (ft.ids) or
co.match_id = any (fm.ids) or
co.player_id = any (fp.ids) or
c.user_id = any (fu.ids))
and ($1 is null or c.user_id not in
(select ub.blocked_user_id from user_blocks as ub where ub.blocker_user_id = $1))
|
|
4 min
0.1%
|
0 ms
|
19,227,876
db_user
|
UPDATE commentable_objects SET created_date = $1, reference_id = $2, type = $3, event_type = $4, home_team = $5, away_team = $6, league_id = $7, player_id = $8, country_id = $9, match_id = $10 WHERE commentable_objects.id = $11
Covered by index on (id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
4 min
0.1%
|
35 ms
|
6,765
db_user
|
SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*pghero*/
|
|
4 min
0.1%
|
0 ms
|
8,210,282
db_user
|
UPDATE commentable_objects SET reference_id = $1, type = $2, event_type = $3, home_team = $4, away_team = $5, home_coach = $6, away_coach = $7, league_id = $8, player_id = $9, country_id = $10, match_id = $11, league_type = $12, match_time = $13, round = $14, stage_name = $15, stage_mode = $16, is_knockout = $17, season_id = $18, stage_order = $19, team = $20, related_incident_ids = $21, custom_names = $22 WHERE commentable_objects.id = $23
Covered by index on (id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
4 min
0.1%
|
4 ms
|
56,713
db_user
|
insert into comment_statistics (reference_id, comment_to, comment_to_sub_type, cumulative)
values ($1, $2, $3, $4)
on conflict (comment_to, comment_to_sub_type, reference_id, cumulative)
do update set reference_id = excluded.reference_id
returning *
|
|
4 min
0.1%
|
200 ms
|
1,117
db_user
|
with base as (select to_jsonb(co) || jsonb_build_object($8, coalesce(cs.comment_count, $9)) as jsonb,
co.league_type as league_type,
co.round as round_num,
co.stage_name as stage_name,
co.stage_mode as stage_mode,
co.is_knockout as is_knockout,
coalesce(cs.comment_count, $10) as comment_count,
co.match_time as match_time,
co.stage_order as stage_order,
co.reference_id as reference_id
from commentable_objects as co
left join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = $11 and
cs.comment_to_sub_type = $12 and
cs.cumulative = $13
where co.season_id = $1
and co.type = $14
and co.event_type is not null
and co.deleted = $15
and ($2 is null or co.player_id = $2)
and ($3 is null or co.team = $3)
order by coalesce(cs.comment_count, 0) desc, co.match_time desc, co.reference_id desc),
grouped as (select base.*,
case
when league_type <> $16 and round_num is not null and round_num <> $17
then $18 || round_num
when league_type = $19
and (round_num is null or round_num = $20)
and stage_name is not null
and stage_mode = $21
then stage_name || $22 ||
case when is_knockout then $23 else $24 end
when league_type = $25
and (round_num is null or round_num = $26)
and stage_name is not null
and stage_mode = $27
then stage_name
when league_type = $28
and round_num is not null
and round_num <> $29
and stage_name is not null
and stage_mode = $30
then stage_name || $31 || round_num
else $32
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by comment_count desc, match_time desc, reference_id desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($33, match_time,
$34, round_num,
$35, league_type,
$36, stage_name,
$37, stage_mode,
$38, is_knockout)
order by ranked.comment_count desc, ranked.match_time desc) filter (where rn <= $4) as data,
count(ranked.*) filter (where rn <= $4) as inner_total,
count(ranked.*) as total
from ranked
group by group_name, stage_order, round_num, is_knockout
order by stage_order desc, case when is_knockout then $39 else $40 end desc, round_num desc),
target_group as (select stage_order,
case when is_knockout then $41 else $42 end as ko_order,
round_num
from grouped
where match_time <= $5::timestamptz + interval $43
order by stage_order desc,
case when is_knockout then 1 else 0 end desc,
round_num desc
limit $44)
select *
from data
where inner_total > $45
and ($5::timestamptz is null or
(stage_order, case when is_knockout then $46 else $47 end, round_num) <= (select t.stage_order, t.ko_order, t.round_num from target_group t))
order by stage_order desc, case when is_knockout then $48 else $49 end desc, round_num desc
limit case when $6 is not null then $6::bigint end offset case when $6::bigint is not null and $7::bigint is not null then $7::bigint * $6::bigint end
|
|
4 min
0.1%
|
0 ms
|
23,906,988
db_user
|
SELECT commentable_objects.id, commentable_objects.created_date, commentable_objects.reference_id, commentable_objects.type, commentable_objects.event_type, commentable_objects.home_team, commentable_objects.away_team, commentable_objects.league_id, commentable_objects.player_id, commentable_objects.country_id FROM commentable_objects WHERE commentable_objects.reference_id = $1 AND (commentable_objects.type = $2)
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (reference_id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- reference_id (=): 1
- type (=): 5859622
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
3 min
< 0.1%
|
0 ms
|
728,485
db_user
|
update commentable_objects
set deleted = $3
where reference_id = any(array[$1::bigint[]])
and type = $2
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (reference_id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- reference_id (=): 1
- type (=): 5859622
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
3 min
< 0.1%
|
3 ms
|
62,239
db_user
|
with favorite_leagues as (select array_agg(league_id) as ids
from user_favorite_leagues
where user_id = $1),
favorite_teams as (select array_agg(team_id) as ids
from user_favorite_teams
where user_id = $1),
favorite_matches as (select array_agg(match_id) as ids
from user_favorite_matches
where user_id = $1),
favorite_players as (select array_agg(player_id) as ids
from user_favorite_players
where user_id = $1),
favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $1)
select count(distinct f.id)
from feeds as f
left join commentable_objects as co on co.id = f.commentable
left join comments as c on c.id = co.reference_id and co.type = $12
cross join favorite_leagues as fl
cross join favorite_teams as ft
cross join favorite_matches as fm
cross join favorite_players as fp
cross join favorite_users as fu
where f.commentable_type = $13
and c.comment_to != $14
and (co.type != $15 or $2::bigint[] is null or c.language = any (array [$2::bigint[]]))
and ((co.type = $16 and c.id is not null) or co.type != $17)
and (co.type = $18 or $3::bigint[] is null or co.league_id = any (array [$3::bigint[]]))
and (co.type = $19 or $4::bigint[] is null or co.home_team = any (array [$4::bigint[]]) or co.away_team = any (array [$4::bigint[]]))
and (co.type = $20 or $5::bigint[] is null or co.player_id = any (array [$5::bigint[]]))
and (co.type = $21 or $6::event_type[] is null or co.event_type = any (array [$6::event_type[]]))
and (co.type = $22 or $7::comment_type[] is null or co.type = any (array [$7::comment_type[]]))
and (co.type = $23 or $8 is null or exists (select $24
from generate_subscripts($8::bigint[][], $25) s
where array [
($8::bigint[][])[s][$26],
($8::bigint[][])[s][$27]
] = array [co.home_team, co.away_team]))
and ($9::timestamptz is null or co.created_date <= $9::timestamptz)
and ($10::timestamptz is null or co.created_date > $10::timestamptz)
and ($11::boolean is null or $11::boolean = $28 or
co.league_id = any (fl.ids) or
co.home_team = any (ft.ids) or
co.away_team = any (ft.ids) or
co.match_id = any (fm.ids) or
co.player_id = any (fp.ids) or
c.user_id = any (fu.ids))
and ($1 is null or c.user_id not in
(select ub.blocked_user_id from user_blocks as ub where ub.blocker_user_id = $1))
|
|
3 min
< 0.1%
|
32 ms
|
5,815
db_user
|
with base as (select (to_jsonb(co) - $10) || jsonb_build_object($11, coalesce(cs.comment_count, $12)) as jsonb,
co.league_type as league_type,
co.round as round_num,
coalesce(co.custom_names ->> $1::text, co.stage_name) as stage_name,
co.stage_mode as stage_mode,
co.is_knockout as is_knockout,
coalesce(cs.comment_count, $13) as comment_count,
co.match_time as match_time,
co.stage_order as stage_order,
co.reference_id as reference_id
from commentable_objects as co
left join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = $14 and
cs.comment_to_sub_type = $15 and
cs.cumulative = $16
where co.season_id = $2
and co.type = $17
and co.event_type is not null
and co.deleted = $18
and ($3 is null or co.player_id = $3 or co.secondary_player_id = $3)
and ($4 is null
or co.team = $4
or (co.event_type::text = $19 and (co.home_team = $4 or co.away_team = $4)))
and ($5 is null
or (co.home_coach = $5 and (co.team = co.home_team or co.event_type::text = $20))
or (co.away_coach = $5 and (co.team = co.away_team or co.event_type::text = $21)))
order by coalesce(cs.comment_count, 0) desc, co.match_time desc, co.reference_id desc),
grouped as (select base.*,
case
when league_type <> $22 and round_num is not null and round_num <> $23
then case
when stage_order > $24
then stage_name || $25 || round_num
else $26 || round_num end
when league_type <> $27
and (round_num is null or round_num = $28)
and stage_name is not null
and stage_mode = $29
then stage_name
when league_type = $30
and (round_num is null or round_num = $31)
and stage_name is not null
and stage_mode = $32
then stage_name ||
case when is_knockout then $33 when is_knockout is null then $34 else $35 end
when league_type = $36
and (round_num is null or round_num = $37)
and stage_name is not null
and stage_mode = $38
then stage_name
when league_type = $39
and round_num is not null
and round_num <> $40
and stage_name is not null
and stage_mode = $41
then stage_name || $42 || round_num
else $43
end as group_name
from base),
ranked as (select grouped.*,
row_number() over (partition by group_name order by comment_count desc, match_time desc, reference_id desc) as rn
from grouped),
data as (select ranked.stage_order,
ranked.is_knockout,
ranked.round_num,
ranked.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($44, match_time,
$45, round_num,
$46, league_type,
$47, stage_name,
$48, stage_mode,
$49, is_knockout)
order by ranked.comment_count desc, ranked.match_time desc) filter (where rn <= $6) as data,
count(ranked.*) filter (where rn <= $6) as inner_total,
count(ranked.*) as total
from ranked
group by group_name, stage_order, round_num, is_knockout
order by stage_order desc, case when is_knockout then $50 else $51 end desc, round_num desc),
target_group as (select stage_order,
case when is_knockout then $52 else $53 end as ko_order,
round_num
from grouped
where match_time <= $7::timestamptz + interval $54
order by stage_order desc,
case when is_knockout then 1 else 0 end desc,
round_num desc
limit $55)
select *
from data
where inner_total > $56
and ($7::timestamptz is null or
(stage_order, case when is_knockout then $57 else $58 end, round_num) <= (select t.stage_order, t.ko_order, t.round_num from target_group t))
order by stage_order desc, case when is_knockout then $59 else $60 end desc, round_num desc
limit case when $8 is not null then $8::bigint end offset case when $8::bigint is not null and $9::bigint is not null then $9::bigint * $8::bigint end
|
|
3 min
< 0.1%
|
0 ms
|
14,825,632
db_user
|
SELECT users.* FROM users WHERE users.id = $1 LIMIT $2
Covered by index on (id)
Rows: 6751
Row progression: 6751, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- email UNIQUE
- phone UNIQUE
- username UNIQUE
|
|
3 min
< 0.1%
|
0 ms
|
8,729,174
db_user
|
SELECT commentable_objects.id, commentable_objects.created_date, commentable_objects.reference_id, commentable_objects.type, commentable_objects.event_type, commentable_objects.home_team, commentable_objects.away_team, commentable_objects.home_coach, commentable_objects.away_coach, commentable_objects.league_id, commentable_objects.player_id, commentable_objects.country_id, commentable_objects.match_id, commentable_objects.league_type, commentable_objects.match_time, commentable_objects.round, commentable_objects.stage_name, commentable_objects.stage_mode, commentable_objects.is_knockout, commentable_objects.season_id, commentable_objects.stage_order, commentable_objects.team, commentable_objects.related_incident_ids, commentable_objects.custom_names FROM commentable_objects WHERE commentable_objects.reference_id = $1 AND (commentable_objects.type = $2)
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (reference_id)
Rows: 46876976
Row progression: 46876976, 1
Row estimates
- reference_id (=): 1
- type (=): 5859622
Existing indexes
- id PRIMARY
- CREATE UNIQUE INDEX unique_commentable_objects_ref_type_event_idx ON public.commentable_objects USING btree (reference_id, type, event_type) NULLS NOT DISTINCT UNIQUE
- away_coach WHERE away_coach IS NOT NULL
- created_date
- created_epoch DESC, id
- created_epoch DESC, id WHERE type = 'Comment'::comment_type
- event_type, type
- home_coach WHERE home_coach IS NOT NULL
- referee_id, league_id WHERE referee_id IS NOT NULL
- reference_id WHERE type = 'Comment'::comment_type
- season_id, match_time DESC, reference_id DESC WHERE (type = 'Event'::comment_type) AND (deleted = false)
- secondary_player_id WHERE secondary_player_id IS NOT NULL
- type
|
|
3 min
< 0.1%
|
0 ms
|
542,599,653
db_user
|
COMMIT
|
|
3 min
< 0.1%
|
2 ms
|
70,927
db_user
|
select
count(*) filter (where nullif($1, $2)::timestamptz is not null
and created_date >= nullif($1, $3)::timestamptz)::int as since_marker,
count(*) filter (where created_date >= now() - interval $4)::int as last1h,
count(*) filter (where created_date >= now() - interval $5)::int as last24h,
count(*) filter (where created_date >= (date_trunc($6, now() at time zone $7)) at time zone $8)::int as today,
count(*) filter (where created_date >= (date_trunc($9, now() at time zone $10) - interval $11) at time zone $12
and created_date < now() - interval $13)::int as yesterday_same_time,
count(*)::int as total
from users
where status <> $14
|