|
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
|
|
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
|
|
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
|
|
71 min
2%
|
2,945 ms
|
1,446
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
|
|
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%
|
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
|
|
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
|
|
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%
|
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
|
|
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%
|
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%
|
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
|
|
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
|
|
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%
|
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
|
|
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%
|
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%
|
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
|
|
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%
|
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
|
|
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%
|
35 ms
|
6,764
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%
|
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
|
|
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
|