|
3 min
3%
|
201 ms
|
965
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
|
|
1 min
1%
|
196 ms
|
426
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 (co.type != $17 or $4::bigint[] is null or c.language = any (array [$4::bigint[]]))
and ((co.type = $18 and c.id is not null) or co.type != $19)
and (co.type = $20 or $5::bigint[] is null or co.league_id = any (array [$5::bigint[]]))
and (co.type = $21 or $6::bigint[] is null or co.home_team = any (array [$6::bigint[]]) or co.away_team = any (array [$6::bigint[]]))
and (co.type = $22 or $7::bigint[] is null or co.player_id = any (array [$7::bigint[]]))
and (co.type = $23 or $8::event_type[] is null or co.event_type = any (array [$8::event_type[]]))
and (co.type = $24 or $9::comment_type[] is null or co.type = any (array [$9::comment_type[]]))
and (co.type = $25 or $10 is null or exists (select $26
from generate_subscripts($10::bigint[][], $27) s
where array [
($10::bigint[][])[s][$28],
($10::bigint[][])[s][$29]
] = 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 = $30 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))
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(
$31, coalesce(cs.likes, $32),
$33, coalesce(csu.liked, $34),
$35, coalesce(cs.comment_count, $36),
$37, coalesce(cs.view_count, $38),
$39, jsonb_build_object(
$40, (u.id = any (array [(select ids from favorite_users)])),
$41, count(distinct follower_count.*),
$42, count(distinct following_count.*),
$43, count(distinct post_count.*),
$44, u.*)) 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 = $45 and
cs.reference_id = c.id and
cs.comment_to_sub_type = $46 and
cs.cumulative = $47
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 != $48
group by fe.id, co.id, c.id, cs.id, csu.id, u.id, f.score
order by f.score desc
|
|
1 min
0.5%
|
41 ms
|
868
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, u.*) as "user"
from comments as c
left join comment_statistics as cs on cs.reference_id = c.id and
cs.comment_to = $17 and
cs.comment_to_sub_type = $18 and
cs.cumulative = $19
left join users as u on u.id = c.user_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 != $20
where c.user_id = $2
and (case when $3::boolean = $21 then c.comment_to = $22 else c.comment_to != $23 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
|
|
0 min
0.4%
|
210 ms
|
142
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)
and ($4::timestamptz is null or co.match_time <= $4::timestamptz + interval $16)
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 $19 || round_num
when league_type = $20
and (round_num is null or round_num = $21)
and stage_name is not null
and stage_mode = $22
then stage_name || $23 ||
case when is_knockout then $24 else $25 end
when league_type = $26
and (round_num is null or round_num = $27)
and stage_name is not null
and stage_mode = $28
then stage_name
when league_type = $29
and round_num is not null
and round_num <> $30
and stage_name is not null
and stage_mode = $31
then stage_name || $32 || round_num
else $33
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.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($34, match_time,
$35, round_num,
$36, league_type,
$37, stage_name,
$38, stage_mode,
$39, 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
order by stage_order desc, round_num desc)
select *
from data
where inner_total > $40
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
|
|
0 min
0.4%
|
235 ms
|
108
db_user
|
with base as (select to_jsonb(co) || jsonb_build_object($7, coalesce(cs.comment_count, $8)) 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, $9) 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 = $10 and
cs.comment_to_sub_type = $11 and
cs.cumulative = $12
where co.season_id = $1
and co.type = $13
and co.event_type is not null
and co.deleted = $14
and ($2 is null or co.player_id = $2)
and ($3 is null or 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 <> $15 and round_num is not null and round_num <> $16
then $17 || round_num
when league_type = $18
and (round_num is null or round_num = $19)
and stage_name is not null
and stage_mode = $20
then stage_name || $21 ||
case when is_knockout then $22 else $23 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 not null
and round_num <> $28
and stage_name is not null
and stage_mode = $29
then stage_name || $30 || round_num
else $31
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.group_name as group_name,
jsonb_agg(ranked.jsonb || jsonb_build_object($32, match_time,
$33, round_num,
$34, league_type,
$35, stage_name,
$36, stage_mode,
$37, 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
order by stage_order desc, round_num desc)
select *
from data
where inner_total > $38
limit case when $5 is not null then $5::bigint end offset case when $5::bigint is not null and $6::bigint is not null then $6::bigint * $5::bigint end
|
|
0 min
0.3%
|
20 ms
|
1,111
db_user
|
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT $6 /*pghero*/
|
|
0 min
< 0.1%
|
36 ms
|
180
db_user
|
SELECT COUNT(*)
FROM public.commentable_objects t
|