|
11 min
9%
|
0 ms
|
118,127,450
db_user
|
select *
from feeds
where commentable = $1
limit $2
Covered by index on (commentable)
Rows: 18632482
Row progression: 18632482, 1
Row estimates
- commentable (=): 1
Existing indexes
- id PRIMARY
- ((((base_score)::numeric + extra_score) + follower_score)) DESC
- commentable UNIQUE
|
|
10 min
9%
|
0 ms
|
26,518,444
db_user
|
INSERT INTO feeds (follower_score, commentable) VALUES ($1, $2) RETURNING id
|
|
10 min
8%
|
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)
Covered by index on (reference_id, type)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- reference_id (=): 1
- type (=): 1864964
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
8 min
7%
|
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: 18649638
Row progression: 18649638, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
6 min
5%
|
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)
Covered by index on (reference_id, type)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- reference_id (=): 1
- type (=): 1864964
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
6 min
5%
|
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: 18649638
Row progression: 18649638, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
6 min
5%
|
0 ms
|
20,655,678
db_user
|
INSERT INTO commentable_objects (created_date, reference_id, type) VALUES ($1, $2, $3) RETURNING id
|
|
6 min
5%
|
0 ms
|
21,451,639
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: 18649638
Row progression: 18649638, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
5 min
4%
|
0 ms
|
141,572,180
db_user
|
BEGIN READ WRITE
|
|
4 min
4%
|
0 ms
|
21,633,615
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)
Covered by index on (reference_id, type)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- reference_id (=): 1
- type (=): 1864964
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
4 min
3%
|
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: 18649638
Row progression: 18649638, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
4 min
3%
|
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)
Covered by index on (reference_id, type)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- reference_id (=): 1
- type (=): 1864964
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
3 min
3%
|
65,826 ms
|
3
db_user
|
explain analyse verbose
with favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $1)
select to_jsonb(f.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
jsonb_build_object(
$2, coalesce(cs.likes, $3),
$4, coalesce(csu.liked, $5),
$6, coalesce(cs.comment_count, $7),
$8, coalesce(cs.view_count, $9),
$10, jsonb_build_object(
$11, (u.id = any (array [(select ids from favorite_users)])),
$12, count(distinct follower_count.*),
$13, count(distinct following_count.*),
$14, count(distinct post_count.*),
$15, u.*)
) as comment_response
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
c.comment_to = co.type
left join users as u on u.id = c.user_id
left join comment_statistics as cs on cs.comment_to = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $16
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $17 and
$18 is not null
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 != $19
where (co.type != $20 or $21::bigint[] is null or c.language = any (array [$22::bigint[]]))
and (co.type = $23 or $24::bigint[] is null or co.league_id = any (array [$25::bigint[]]))
and (co.type = $26 or $27::bigint[] is null or co.home_team = any (array [$28::bigint[]]) or co.away_team = any (array [$29::bigint[]]))
and (co.type = $30 or $31::bigint[] is null or co.player_id = any (array [$32::bigint[]]))
and (co.type = $33 or $34::event_type[] is null or co.event_type = any (array [$35::event_type[]]))
and (co.type = $36 or $37::comment_type[] is null or co.type = any (array [$38::comment_type[]]))
and (co.type = $39 or $40 is null or exists (select $41
from generate_subscripts($42::bigint[][], $43) s
where array [
($44::bigint[][])[s][$45],
($46::bigint[][])[s][$47]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
group by f.id, co.id, cs.id, csu.id, u.id
order by f.base_score + f.extra_score + f.follower_score desc
limit $48 offset $49 * $50
|
|
3 min
3%
|
201 ms
|
967
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
2%
|
0 ms
|
3,562,694
db_user
|
INSERT INTO event_feeds (country, commentable) VALUES ($1, $2) RETURNING id
|
|
2 min
2%
|
0 ms
|
30,086,413
db_user
|
SELECT $2 FROM ONLY "public"."commentable_objects" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
|
|
2 min
2%
|
0 ms
|
4,439,595
db_user
|
INSERT INTO commentable_objects (reference_id, type, home_team, away_team, player_id) VALUES ($1, $2, $3, $4, $5) RETURNING id
|
|
2 min
1%
|
0 ms
|
6,293,330
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 WHERE commentable_objects.id = $10
Covered by index on (id)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
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.9%
|
0 ms
|
2,801,411
db_user
|
INSERT INTO commentable_objects (created_date, reference_id, type, event_type, home_team, away_team, league_id, player_id, country_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id
|
|
1 min
0.8%
|
0 ms
|
26,518,444
db_user
|
SELECT feed_weights.id, feed_weights.weight, feed_weights.type FROM feed_weights WHERE feed_weights.type = $1
|
|
1 min
0.7%
|
0 ms
|
2,000,514
db_user
|
INSERT INTO commentable_objects (reference_id, type, player_id) VALUES ($1, $2, $3) RETURNING id
|
|
1 min
0.7%
|
51,725 ms
|
1
db_user
|
with favorite_users as (select array_agg(favorite_user_id) as ids
from user_favorite_users
where user_id = $1)
select to_jsonb(f.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
jsonb_build_object(
$2, coalesce(cs.likes, $3),
$4, coalesce(csu.liked, $5),
$6, coalesce(cs.comment_count, $7),
$8, coalesce(cs.view_count, $9),
$10, jsonb_build_object(
$11, (u.id = any (array [(select ids from favorite_users)])),
$12, count(distinct follower_count.*),
$13, count(distinct following_count.*),
$14, count(distinct post_count.*),
$15, u.*)
) as comment_response
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
left join users as u on u.id = c.user_id
left join comment_statistics as cs on cs.comment_to = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $16
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $17 and
$18 is not null
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 != $19
where (co.type != $20 or $21::bigint[] is null or c.language = any (array [$22::bigint[]]))
and (co.type = $23 or $24::bigint[] is null or co.league_id = any (array [$25::bigint[]]))
and (co.type = $26 or $27::bigint[] is null or co.home_team = any (array [$28::bigint[]]) or co.away_team = any (array [$29::bigint[]]))
and (co.type = $30 or $31::bigint[] is null or co.player_id = any (array [$32::bigint[]]))
and (co.type = $33 or $34::event_type[] is null or co.event_type = any (array [$35::event_type[]]))
and (co.type = $36 or $37::comment_type[] is null or co.type = any (array [$38::comment_type[]]))
and (co.type = $39 or $40 is null or exists (select $41
from generate_subscripts($42::bigint[][], $43) s
where array [
($44::bigint[][])[s][$45],
($46::bigint[][])[s][$47]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
group by f.id, co.id, cs.id, csu.id, u.id
order by f.base_score + f.extra_score + f.follower_score desc
limit $48 offset $49 * $50
|
|
1 min
0.6%
|
0 ms
|
1,684,634
db_user
|
INSERT INTO commentable_objects (reference_id, type, home_team, player_id) VALUES ($1, $2, $3, $4) RETURNING id
|
|
1 min
0.6%
|
0 ms
|
141,575,963
db_user
|
COMMIT
|
|
1 min
0.5%
|
0 ms
|
1,614,753
db_user
|
INSERT INTO commentable_objects (reference_id, type, away_team, player_id) VALUES ($1, $2, $3, $4) RETURNING id
|
|
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
|
|
1 min
0.5%
|
5,798 ms
|
6
db_user
|
explain analyse verbose
with favorite_users as (select favorite_user_id as id
from user_favorite_users
where user_id = $1),
top_feeds as (select f.id as feed_id,
c.id as comment_id,
co.id as commentable_object_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 = $2
where (co.type != $3 or $4::bigint[] is null or c.language = any (array [$5::bigint[]]))
and ((co.type = $6 and c.id is not null) or co.type != $7)
and (co.type = $8 or $9::bigint[] is null or co.league_id = any (array [$10::bigint[]]))
and (co.type = $11 or $12::bigint[] is null or co.home_team = any (array [$13::bigint[]]) or co.away_team = any (array [$14::bigint[]]))
and (co.type = $15 or $16::bigint[] is null or co.player_id = any (array [$17::bigint[]]))
and (co.type = $18 or $19::event_type[] is null or co.event_type = any (array [$20::event_type[]]))
and (co.type = $21 or $22::comment_type[] is null or co.type = any (array [$23::comment_type[]]))
and (co.type = $24 or $25 is null or exists (select $26
from generate_subscripts($27::bigint[][], $28) s
where array [
($29::bigint[][])[s][$30],
($31::bigint[][])[s][$32]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
group by f.id, f.base_score, f.extra_score, f.follower_score, c.id, co.id
order by f.base_score + f.extra_score + f.follower_score desc
limit $33 offset $34 * $35)
select to_jsonb(fe.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
to_jsonb(c.*) || jsonb_build_object($36, jsonb_build_object(
$37, coalesce(cs.likes, $38),
$39, coalesce(csu.liked, $40),
$41, coalesce(cs.comment_count, $42),
$43, coalesce(cs.view_count, $44),
$45, jsonb_build_object(
$46, (u.id = any (select id from favorite_users)),
$47, count(distinct follower_count.*),
$48, count(distinct following_count.*),
$49, count(distinct post_count.*),
$50, 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 = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $51
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $52 and
$53 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 != $54
group by fe.id, co.id, c.id, cs.id, csu.id, u.id
|
|
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%
|
5,519 ms
|
5
db_user
|
with favorite_users as (select favorite_user_id as id
from user_favorite_users
where user_id = $1),
top_feeds as (select f.id as feed_id,
c.id as comment_id,
co.id as commentable_object_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
where (co.type != $13 or $2::bigint[] is null or c.language = any (array [$2::bigint[]]))
and ((co.type = $14 and c.id is not null) or co.type != $15)
and (co.type = $16 or $3::bigint[] is null or co.league_id = any (array [$3::bigint[]]))
and (co.type = $17 or $4::bigint[] is null or co.home_team = any (array [$4::bigint[]]) or co.away_team = any (array [$4::bigint[]]))
and (co.type = $18 or $5::bigint[] is null or co.player_id = any (array [$5::bigint[]]))
and (co.type = $19 or $6::event_type[] is null or co.event_type = any (array [$6::event_type[]]))
and (co.type = $20 or $7::comment_type[] is null or co.type = any (array [$7::comment_type[]]))
and (co.type = $21 or $8 is null or exists (select $22
from generate_subscripts($8::bigint[][], $23) s
where array [
($8::bigint[][])[s][$24],
($8::bigint[][])[s][$25]
] = array [co.home_team, co.away_team]))
and ($9::timestamptz is null or co.created_date <= $9::timestamptz)
group by f.id, f.base_score, f.extra_score, f.follower_score, c.id, co.id
order by f.base_score + f.extra_score + f.follower_score desc
limit $10 offset $11 * $10)
select to_jsonb(fe.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
to_jsonb(c.*) || jsonb_build_object($26, jsonb_build_object(
$27, coalesce(cs.likes, $28),
$29, coalesce(csu.liked, $30),
$31, coalesce(cs.comment_count, $32),
$33, coalesce(cs.view_count, $34),
$35, jsonb_build_object(
$36, (u.id = any (select id from favorite_users)),
$37, count(distinct follower_count.*),
$38, count(distinct following_count.*),
$39, count(distinct post_count.*),
$40, 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 = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $41
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 != $42
group by fe.id, co.id, c.id, cs.id, csu.id, u.id
|
|
0 min
0.4%
|
0 ms
|
3,394,193
db_user
|
select * from jwt_secrets
order by created_date
|
|
0 min
0.4%
|
0 ms
|
1,256,323
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
|
|
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.4%
|
0 ms
|
1,221,206
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
|
|
0 min
0.3%
|
0 ms
|
2,220,145
db_user
|
INSERT INTO commentable_objects (created_date, reference_id, type, event_type, home_team, away_team, league_id, player_id, country_id, match_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING id
|
|
0 min
0.3%
|
20 ms
|
1,112
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.3%
|
247 ms
|
90
db_user
|
SELECT t.*
FROM public.commentable_objects t
ORDER BY created_date DESC
LIMIT $1
OFFSET $2
Covered by index on (created_date)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- created_date (sort): 1
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
0.3%
|
0 ms
|
3,562,713
db_user
|
select *
from event_feeds
where commentable = $1
|
|
0 min
0.3%
|
1,476 ms
|
14
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 = $2),
favorite_teams as (select array_agg(team_id) as ids
from user_favorite_teams
where user_id = $3),
favorite_matches as (select array_agg(match_id) as ids
from user_favorite_matches
where user_id = $4),
favorite_players as (select array_agg(player_id) as ids
from user_favorite_players
where user_id = $5),
top_feeds as (select distinct f.id as feed_id,
c.id as comment_id,
co.id as commentable_object_id,
case
when $6::text = $7 or $8::boolean = $9 then extract($10 from co.created_date)
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 = $11
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 != $12 or $13::bigint[] is null or c.language = any (array [$14::bigint[]]))
and ((co.type = $15 and c.id is not null) or co.type != $16)
and (co.type = $17 or $18::bigint[] is null or co.league_id = any (array [$19::bigint[]]))
and (co.type = $20 or $21::bigint[] is null or co.home_team = any (array [$22::bigint[]]) or co.away_team = any (array [$23::bigint[]]))
and (co.type = $24 or $25::bigint[] is null or co.player_id = any (array [$26::bigint[]]))
and (co.type = $27 or $28::event_type[] is null or co.event_type = any (array [$29::event_type[]]))
and (co.type = $30 or $31::comment_type[] is null or co.type = any (array [$32::comment_type[]]))
and (co.type = $33 or $34 is null or exists (select $35
from generate_subscripts($36::bigint[][], $37) s
where array [
($38::bigint[][])[s][$39],
($40::bigint[][])[s][$41]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
and ($42::boolean is null or $43::boolean = $44 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 $45 offset $46 * $47)
select to_jsonb(fe.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
to_jsonb(c.*) || jsonb_build_object(
$48, coalesce(cs.likes, $49),
$50, coalesce(csu.liked, $51),
$52, coalesce(cs.comment_count, $53),
$54, coalesce(cs.view_count, $55),
$56, jsonb_build_object(
$57, (u.id = any (array [(select ids from favorite_users)])),
$58, count(distinct follower_count.*),
$59, count(distinct following_count.*),
$60, count(distinct post_count.*),
$61, 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 = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $62
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $63 and
$64 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 != $65
group by fe.id, co.id, c.id, cs.id, csu.id, u.id
|
|
0 min
0.3%
|
253 ms
|
75
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.home_team = $3 or co.away_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.2%
|
0 ms
|
4,528,227
db_user
|
SELECT COUNT(user_favorite_teams.id) FROM user_favorite_teams WHERE user_favorite_teams.team_id = $1
|
|
0 min
0.2%
|
1,375 ms
|
12
db_user
|
SELECT t.*
FROM public.commentable_objects t
WHERE is_knockout = $1
LIMIT $2
OFFSET $3
Details
CREATE INDEX CONCURRENTLY ON commentable_objects (is_knockout)
Rows: 18649638
Row progression: 18649638, 941185
Row estimates
- is_knockout (=): 941185
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
0.2%
|
0 ms
|
762,227
db_user
|
INSERT INTO commentable_objects (created_date, reference_id, type, league_id, country_id) VALUES ($1, $2, $3, $4, $5) RETURNING id
|
|
0 min
0.2%
|
15,681 ms
|
1
db_user
|
alter table commentable_objects alter column season_id type text
|
|
0 min
0.2%
|
0 ms
|
743,751
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, is_knockout, season_id, stage_order) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16) RETURNING id
|
|
0 min
0.2%
|
184 ms
|
74
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.stage_order,
ranked.is_knockout,
ranked.round_num,
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, is_knockout
order by stage_order desc, case when is_knockout then $40 else $41 end desc, round_num desc)
select *
from data
where inner_total > $42
order by stage_order desc, case when is_knockout then $43 else $44 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
|
|
0 min
0.2%
|
0 ms
|
551,123
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 WHERE commentable_objects.id = $16
Covered by index on (id)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- id (=): 1
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
0.2%
|
264 ms
|
50
db_user
|
with base as (select co.*,
coalesce(cs.comment_count, $5) 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 = $6 and
cs.comment_to_sub_type = $7 and
cs.cumulative = $8
where co.season_id = $1
and co.type = $9
and co.event_type is not null
and co.deleted = $10
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 <> $11 and round is not null and round <> $12
then $13 || round
when league_type = $14
and (round is null or round = $15)
and stage_name is not null
and stage_mode = $16
then stage_name || $17 ||
case when is_knockout then $18 else $19 end
when league_type = $20
and (round is null or round = $21)
and stage_name is not null
and stage_mode = $22
then stage_name
when league_type = $23
and round is not null
and round <> $24
and stage_name is not null
and stage_mode = $25
then stage_name || $26 || round
else $27
end as group_name
from base)
select count(*)
from grouped
where ($4::text is null or group_name = $4)
|
|
0 min
0.2%
|
0 ms
|
1,297,630
db_user
|
SELECT users.* FROM users WHERE users.id = $1 LIMIT $2
|
|
0 min
0.2%
|
2 ms
|
5,893
db_user
|
SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/
|
|
0 min
0.2%
|
130 ms
|
88
db_user
|
SELECT t.*
FROM public.commentable_objects t
LIMIT $1
OFFSET $2
|
|
0 min
0.2%
|
5,649 ms
|
2
db_user
|
alter table commentable_objects
add column created_epoch numeric
generated always as (extract(epoch from created_date at time zone 'UTC')) stored
|
|
0 min
0.1%
|
842 ms
|
12
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, $13) as likes,
coalesce(csu.liked, $14) as liked,
coalesce(cs.comment_count, $15) as comment_count,
coalesce(cs.view_count, $16) as view_count,
jsonb_build_object(
$17, (u.id = any (array[(select ids from favorite_users)])),
$18, count(distinct follower_count.*),
$19, count(distinct following_count.*),
$20, count(distinct post_count.*),
$21, u.*) as "user"
from feeds as f
inner join commentable_objects as co on co.id = f.commentable
inner join comments as c on c.id = co.reference_id
left join comment_statistics as cs on cs.reference_id = c.id and
cs.comment_to = $22 and
cs.comment_to_sub_type = $23 and
cs.cumulative = $24
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 != $25
where c.comment_to != $26
and (co.type != $27 or $2::bigint[] is null or c.language = any (array [$2::bigint[]]))
and (co.type = $28 or $3::bigint[] is null or co.league_id = any (array [$3::bigint[]]))
and (co.type = $29 or $4::bigint[] is null or co.home_team = any (array [$4::bigint[]]) or co.away_team = any (array [$4::bigint[]]))
and (co.type = $30 or $5::bigint[] is null or co.player_id = any (array [$5::bigint[]]))
and (co.type = $31 or $6::event_type[] is null or co.event_type = any (array [$6::event_type[]]))
and (co.type = $32 or $7::comment_type[] is null or co.type = any (array [$7::comment_type[]]))
and ($8::timestamptz is null or co.created_date <= $8::timestamptz)
and ($9::boolean = $33 or c.user_id = any (array[(select ids from favorite_users)]))
group by c.id, c.created_date, cs.id, u.id, csu.id, f.base_score, f.extra_score, f.follower_score
order by case when $10 = $34 then extract($35 from c.created_date) else (f.base_score + f.extra_score + f.follower_score) end desc
limit $11 offset $12 * $11
|
|
0 min
0.1%
|
2,520 ms
|
4
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 = $2),
favorite_teams as (select array_agg(team_id) as ids
from user_favorite_teams
where user_id = $3),
favorite_matches as (select array_agg(match_id) as ids
from user_favorite_matches
where user_id = $4),
favorite_players as (select array_agg(player_id) as ids
from user_favorite_players
where user_id = $5),
top_feeds as (select distinct f.id as feed_id,
c.id as comment_id,
co.id as commentable_object_id,
case
when $6::text = $7 or $8::boolean = $9 then extract($10 from co.created_date)
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 = $11
where (co.type != $12 or $13::bigint[] is null or c.language = any (array [$14::bigint[]]))
and ((co.type = $15 and c.id is not null) or co.type != $16)
and (co.type = $17 or $18::bigint[] is null or co.league_id = any (array [$19::bigint[]]))
and (co.type = $20 or $21::bigint[] is null or co.home_team = any (array [$22::bigint[]]) or co.away_team = any (array [$23::bigint[]]))
and (co.type = $24 or $25::bigint[] is null or co.player_id = any (array [$26::bigint[]]))
and (co.type = $27 or $28::event_type[] is null or co.event_type = any (array [$29::event_type[]]))
and (co.type = $30 or $31::comment_type[] is null or co.type = any (array [$32::comment_type[]]))
and (co.type = $33 or $34 is null or exists (select $35
from generate_subscripts($36::bigint[][], $37) s
where array [
($38::bigint[][])[s][$39],
($40::bigint[][])[s][$41]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
and ($42::boolean is null or $43::boolean = $44 or
co.league_id = any (array [(select ids from favorite_leagues)]) or
co.home_team = any (array [(select ids from favorite_teams)]) or
co.away_team = any (array [(select ids from favorite_teams)]) or
co.match_id = any (array [(select ids from favorite_matches)]) or
co.player_id = any (array [(select ids from favorite_players)]) or
c.user_id = any (array [(select ids from favorite_users)]))
order by score desc
limit $45 offset $46 * $47)
select to_jsonb(fe.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
to_jsonb(c.*) || jsonb_build_object(
$48, coalesce(cs.likes, $49),
$50, coalesce(csu.liked, $51),
$52, coalesce(cs.comment_count, $53),
$54, coalesce(cs.view_count, $55),
$56, jsonb_build_object(
$57, (u.id = any (array [(select ids from favorite_users)])),
$58, count(distinct follower_count.*),
$59, count(distinct following_count.*),
$60, count(distinct post_count.*),
$61, 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 = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $62
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $63 and
$64 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 != $65
group by fe.id, co.id, c.id, cs.id, csu.id, u.id
|
|
0 min
0.1%
|
175 ms
|
57
db_user
|
with base as (select co.*,
coalesce(cs.comment_count, $5) 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 = $6 and
cs.comment_to_sub_type = $7 and
cs.cumulative = $8
where co.season_id = $1
and co.type = $9
and co.event_type is not null
and co.deleted = $10
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 <> $11 and round is not null and round <> $12
then $13 || round
when league_type = $14
and (round is null or round = $15)
and stage_name is not null
and stage_mode = $16
then stage_name || $17 ||
case when is_knockout then $18 else $19 end
when league_type = $20
and (round is null or round = $21)
and stage_name is not null
and stage_mode = $22
then stage_name
when league_type = $23
and round is not null
and round <> $24
and stage_name is not null
and stage_mode = $25
then stage_name || $26 || round
else $27
end as group_name
from base)
select count(*)
from grouped
where ($4::text is null or group_name = $4)
|
|
0 min
0.1%
|
225 ms
|
41
db_user
|
SELECT t.*
FROM public.commentable_objects t
WHERE type = $1
LIMIT $2
OFFSET $3
Covered by index on (type)
Rows: 18649638
Row progression: 18649638, 1864964
Row estimates
- type (=): 1864964
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
0.1%
|
1,308 ms
|
7
db_user
|
select *
from commentable_objects
where type = $1
Covered by index on (type)
Rows: 18649638
Row progression: 18649638, 1864964
Row estimates
- type (=): 1864964
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
0.1%
|
0 ms
|
945,856
db_user
|
INSERT INTO commentable_objects (created_date, reference_id, type, match_id) VALUES ($1, $2, $3, $4) RETURNING id
|
|
0 min
0.1%
|
0 ms
|
363,373
db_user
|
SELECT pg_type.oid, typarray, typname, typcategory FROM pg_catalog.pg_type LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r from pg_namespace as ns join ( select s.r, (current_schemas($1))[s.r] as nspname from generate_series($2, array_upper(current_schemas($3), $4)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespace WHERE typname IN ($5) ORDER BY sp.r, pg_type.oid DESC
|
|
0 min
0.1%
|
195 ms
|
44
db_user
|
with base as (select co.*,
coalesce(cs.comment_count, $7) 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 = $8 and
cs.comment_to_sub_type = $9 and
cs.cumulative = $10
where co.season_id = $1
and co.type = $11
and co.event_type is not null
and co.deleted = $12
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 <> $13 and round is not null and round <> $14
then $15 || round
when league_type = $16
and (round is null or round = $17)
and stage_name is not null
and stage_mode = $18
then stage_name || $19 ||
case when is_knockout then $20 else $21 end
when league_type = $22
and (round is null or round = $23)
and stage_name is not null
and stage_mode = $24
then stage_name
when league_type = $25
and round is not null
and round <> $26
and stage_name is not null
and stage_mode = $27
then stage_name || $28 || round
else $29
end as group_name
from base)
select *
from grouped
where ($4::text is null or group_name = $4)
order by comment_count desc, match_time desc, reference_id desc, stage_order desc, round desc
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.1%
|
1,691 ms
|
5
db_user
|
with favorite_users as (select favorite_user_id as id
from user_favorite_users
where user_id = $1),
top_feeds as (select f.*
from feeds as f
order by f.base_score + f.extra_score + f.follower_score desc
limit $2 offset $3 * $4)
select to_jsonb(f) as feed_entity,
to_jsonb(co.*) as commentable_object,
to_jsonb(c.*) || jsonb_build_object($5, jsonb_build_object(
$6, coalesce(cs.likes, $7),
$8, coalesce(csu.liked, $9),
$10, coalesce(cs.comment_count, $11),
$12, coalesce(cs.view_count, $13),
$14, case
when u.id is null then $15
else jsonb_build_object(
$16, (u.id = any (select id from favorite_users)),
$17, count(distinct follower_count.*),
$18, count(distinct following_count.*),
$19, count(distinct post_count.*),
$20, u.*) end)) as comment_response
from top_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 c.comment_to = co.type
left join users as u on u.id = c.user_id
left join comment_statistics as cs on cs.comment_to = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $21
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $22 and
$23 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 != $24
where (co.type != $25 or $26::bigint[] is null or c.language = any (array [$27::bigint[]]))
and (co.type = $28 or $29::bigint[] is null or co.league_id = any (array [$30::bigint[]]))
and (co.type = $31 or $32::bigint[] is null or co.home_team = any (array [$33::bigint[]]) or co.away_team = any (array [$34::bigint[]]))
and (co.type = $35 or $36::bigint[] is null or co.player_id = any (array [$37::bigint[]]))
and (co.type = $38 or $39::event_type[] is null or co.event_type = any (array [$40::event_type[]]))
and (co.type = $41 or array [$42]::comment_type[] is null or co.type = any (array [array [$43]::comment_type[]]))
and (co.type = $44 or $45 is null or exists (select $46
from generate_subscripts($47::bigint[][], $48) s
where array [
($49::bigint[][])[s][$50],
($51::bigint[][])[s][$52]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
group by f.*, f.base_score, f.extra_score, f.follower_score, c.id, co.id, cs.id, csu.id, u.id
order by f.base_score + f.extra_score + f.follower_score desc
|
|
0 min
0.1%
|
0 ms
|
579,621
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 FROM commentable_objects WHERE commentable_objects.reference_id = $1 AND (commentable_objects.type = $2)
Covered by index on (reference_id, type)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- reference_id (=): 1
- type (=): 1864964
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
< 0.1%
|
0 ms
|
311,149
db_user
|
INSERT INTO commentable_objects (created_date, reference_id, type, event_type, home_team, away_team, league_id, player_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id
|
|
0 min
< 0.1%
|
6,868 ms
|
1
db_user
|
delete from commentable_objects where match_id is null and type in ($1, $2, $3, $4)
Covered by index on (type)
Rows: 18649638
Row progression: 18649638, 1864964, 1288876
Row estimates
- type (=): 1864964
- match_id (null): 12888765
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
< 0.1%
|
36 ms
|
180
db_user
|
SELECT COUNT(*)
FROM public.commentable_objects t
|
|
0 min
< 0.1%
|
0 ms
|
312,796
db_user
|
update commentable_objects
set deleted = $3
where reference_id = any(array[$1::bigint[]])
and type = $2
Covered by index on (reference_id, type)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- reference_id (=): 1
- type (=): 1864964
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
< 0.1%
|
0 ms
|
607,162
db_user
|
INSERT INTO commentable_objects (created_date, reference_id, type, league_id, country_id, match_id) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id
|
|
0 min
< 0.1%
|
0 ms
|
2,860,987
db_user
|
with team_comments as (select c.*,
coalesce(cs.likes, $3) as likes,
coalesce(cs.comment_count, $4) as comment_count,
coalesce(cs.view_count, $5) as view_count
from comments as c
left join comment_statistics as cs on cs.reference_id = c.id
left join users as u on u.id = c.user_id
where c.reference_id = $1
and c.comment_to = $2)
select count(*) + sum(tc.comment_count)
from team_comments as tc
|
|
0 min
< 0.1%
|
1,070 ms
|
5
db_user
|
with favorite_users as (select favorite_user_id as id
from user_favorite_users
where user_id = $1),
top_feeds as (select f.*
from feeds as f
order by f.base_score + f.extra_score + f.follower_score desc
limit $2 offset $3 * $4)
select to_jsonb(f) as feed_entity,
to_jsonb(co.*) as commentable_object,
jsonb_build_object(
$5, coalesce(cs.likes, $6),
$7, coalesce(csu.liked, $8),
$9, coalesce(cs.comment_count, $10),
$11, coalesce(cs.view_count, $12),
$13, jsonb_build_object(
$14, (u.id = any (select id from favorite_users)),
$15, count(distinct follower_count.*),
$16, count(distinct following_count.*),
$17, count(distinct post_count.*),
$18, u.*)) as comment_response
from top_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 c.comment_to = co.type
left join users as u on u.id = c.user_id
left join comment_statistics as cs on cs.comment_to = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $19
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $20
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 != $21
where (co.type != $22 or $23::bigint[] is null or c.language = any (array [$24::bigint[]]))
and (co.type = $25 or $26::bigint[] is null or co.league_id = any (array [$27::bigint[]]))
and (co.type = $28 or $29::bigint[] is null or co.home_team = any (array [$30::bigint[]]) or co.away_team = any (array [$31::bigint[]]))
and (co.type = $32 or $33::bigint[] is null or co.player_id = any (array [$34::bigint[]]))
and (co.type = $35 or $36::event_type[] is null or co.event_type = any (array [$37::event_type[]]))
and (co.type = $38 or $39::comment_type[] is null or co.type = any (array [$40::comment_type[]]))
and (co.type = $41 or $42 is null or exists (select $43
from generate_subscripts($44::bigint[][], $45) s
where array [
($46::bigint[][])[s][$47],
($48::bigint[][])[s][$49]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
group by f.*, f.base_score, f.extra_score, f.follower_score, co.id, cs.id, csu.id, u.id
order by f.base_score + f.extra_score + f.follower_score desc
|
|
0 min
< 0.1%
|
5,081 ms
|
1
db_user
|
with favorite_users as (select favorite_user_id as id
from user_favorite_users
where user_id = $1),
top_feeds as (select f.id as feed_id,
c.id as comment_id,
co.id as commentable_object_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 = $2
where (co.type != $3 or $4::bigint[] is null or c.language = any (array [$5::bigint[]]))
and ((co.type = $6 and c.id is not null) or co.type != $7)
and (co.type = $8 or $9::bigint[] is null or co.league_id = any (array [$10::bigint[]]))
and (co.type = $11 or $12::bigint[] is null or co.home_team = any (array [$13::bigint[]]) or co.away_team = any (array [$14::bigint[]]))
and (co.type = $15 or $16::bigint[] is null or co.player_id = any (array [$17::bigint[]]))
and (co.type = $18 or $19::event_type[] is null or co.event_type = any (array [$20::event_type[]]))
and (co.type = $21 or $22::comment_type[] is null or co.type = any (array [$23::comment_type[]]))
and (co.type = $24 or $25 is null or exists (select $26
from generate_subscripts($27::bigint[][], $28) s
where array [
($29::bigint[][])[s][$30],
($31::bigint[][])[s][$32]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
group by f.id, c.id, co.id
order by f.base_score + f.extra_score + f.follower_score desc
limit $33 offset $34 * $35)
select to_jsonb(fe.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
to_jsonb(c.*) || jsonb_build_object($36, jsonb_build_object(
$37, coalesce(cs.likes, $38),
$39, coalesce(csu.liked, $40),
$41, coalesce(cs.comment_count, $42),
$43, coalesce(cs.view_count, $44),
$45, jsonb_build_object(
$46, (u.id = any (select id from favorite_users)),
$47, count(distinct follower_count.*),
$48, count(distinct following_count.*),
$49, count(distinct post_count.*),
$50, 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 = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $51
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $52 and
$53 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 != $54
group by fe.id, co.id, c.id, cs.id, csu.id, u.id
|
|
0 min
< 0.1%
|
0 ms
|
513,706
db_user
|
INSERT INTO commentable_objects (created_date, reference_id, type, player_id) VALUES ($1, $2, $3, $4) RETURNING id
|
|
0 min
< 0.1%
|
2,483 ms
|
2
db_user
|
select co.team_id as reference_id, count(cs.comment_count) as comment_count
from ((select type, reference_id, home_team as team_id from commentable_objects where home_team is not null)
union
distinct
(select type, reference_id, away_team as team_id from commentable_objects where away_team is not null)) as co
join comment_statistics as cs on cs.reference_id = co.reference_id and
cs.comment_to = co.type and
cs.cumulative = $1
join comment_statistic_users as csu on csu.comment_statistic_id = cs.id
where csu.user_id = $2
group by co.team_id
order by count(cs.comment_count) desc
limit case when $3::bigint is not null then $4::bigint end offset case when $5::bigint is not null and $6::bigint is not null then $7::bigint * $8::bigint end
|
|
0 min
< 0.1%
|
2,417 ms
|
2
db_user
|
create index concurrently test
on commentable_objects ((extract(epoch from created_date at time zone 'UTC')) desc, id)
include (type, league_id, home_team, away_team, match_id, player_id, reference_id)
|
|
0 min
< 0.1%
|
0 ms
|
744,962
db_user
|
SELECT COUNT(user_favorite_matches.id) FROM user_favorite_matches WHERE user_favorite_matches.match_id = $1
|
|
0 min
< 0.1%
|
0 ms
|
1,025,709
db_user
|
SELECT COUNT(user_favorite_players.id) FROM user_favorite_players WHERE user_favorite_players.player_id = $1
|
|
0 min
< 0.1%
|
100 ms
|
42
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.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 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.1%
|
0 ms
|
963,769
db_user
|
select cs.comment_count
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
Details
CREATE INDEX CONCURRENTLY ON comment_statistics (reference_id)
Rows: 1115
Row progression: 1115, 2
Row estimates
- reference_id (=): 2
- comment_to (=): 139
- comment_to_sub_type (=): 223
- cumulative (=): 558
Existing indexes
- id PRIMARY
- comment_to, comment_to_sub_type, reference_id, cumulative UNIQUE
- comment_to, reference_id, comment_to_sub_type, cumulative
|
|
0 min
< 0.1%
|
0 ms
|
202,558
db_user
|
INSERT INTO commentable_objects (reference_id, type, league_id, match_id, league_type, match_time, round, stage_name, stage_mode, is_knockout, season_id, stage_order) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) RETURNING id
|
|
0 min
< 0.1%
|
0 ms
|
199,246
db_user
|
INSERT INTO commentable_objects (reference_id, type, home_team, away_team, league_id, match_id) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id
|
|
0 min
< 0.1%
|
69 ms
|
57
db_user
|
SELECT COUNT(*)
FROM public.commentable_objects t
WHERE type = $1
Covered by index on (type)
Rows: 18649638
Row progression: 18649638, 1864964
Row estimates
- type (=): 1864964
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
< 0.1%
|
0 ms
|
358,668
db_user
|
select to_jsonb(cs.*) as object_statistics,
to_jsonb(csu.*) as user_based_object_statistics
from comment_statistics as cs
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
$1 is not null and
csu.user_id = $1
where reference_id = $2
and comment_to = $3
and comment_to_sub_type = $4
and cumulative = $5
limit $6
|
|
0 min
< 0.1%
|
487 ms
|
8
db_user
|
SELECT t.*
FROM public.commentable_objects t
ORDER BY created_date
LIMIT $1
OFFSET $2
Covered by index on (created_date)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- created_date (sort): 1
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
< 0.1%
|
65 ms
|
59
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 = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $45
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 != $46
group by fe.id, co.id, c.id, cs.id, csu.id, u.id
|
|
0 min
< 0.1%
|
1,899 ms
|
2
db_user
|
select f.*
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
where (co.type != $1 or $2::bigint[] is null or c.language = any (array [$3::bigint[]]))
and (co.type = $4 or $5::bigint[] is null or co.league_id = any (array [$6::bigint[]]))
and (co.type = $7 or $8::bigint[] is null or co.home_team = any (array [$9::bigint[]]) or co.away_team = any (array [$10::bigint[]]))
and (co.type = $11 or $12::bigint[] is null or co.player_id = any (array [$13::bigint[]]))
and (co.type = $14 or $15::event_type[] is null or co.event_type = any (array [$16::event_type[]]))
and (co.type = $17 or $18::comment_type[] is null or co.type = any (array [$19::comment_type[]]))
and (co.type = $20 or $21 is null or exists (select $22
from generate_subscripts($23::bigint[][], $24) s
where array [
($25::bigint[][])[s][$26],
($27::bigint[][])[s][$28]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
order by f.base_score + f.extra_score + f.follower_score desc
limit $29 offset $30 * $31
|
|
0 min
< 0.1%
|
0 ms
|
88,362
db_user
|
INSERT INTO commentable_objects (reference_id, type, event_type, home_team, away_team, league_id, player_id, country_id, match_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id
|
|
0 min
< 0.1%
|
657 ms
|
5
db_user
|
explain analyse verbose
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 = $2),
favorite_teams as (select array_agg(team_id) as ids
from user_favorite_teams
where user_id = $3),
favorite_matches as (select array_agg(match_id) as ids
from user_favorite_matches
where user_id = $4),
favorite_players as (select array_agg(player_id) as ids
from user_favorite_players
where user_id = $5),
top_feeds as (select distinct f.id as feed_id,
c.id as comment_id,
co.id as commentable_object_id,
case
when $6::text = $7 or $8::boolean = $9 then extract($10 from co.created_date)
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 = $11
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 != $12 or $13::bigint[] is null or c.language = any (array [$14::bigint[]]))
and ((co.type = $15 and c.id is not null) or co.type != $16)
and (co.type = $17 or $18::bigint[] is null or co.league_id = any (array [$19::bigint[]]))
and (co.type = $20 or $21::bigint[] is null or co.home_team = any (array [$22::bigint[]]) or co.away_team = any (array [$23::bigint[]]))
and (co.type = $24 or $25::bigint[] is null or co.player_id = any (array [$26::bigint[]]))
and (co.type = $27 or $28::event_type[] is null or co.event_type = any (array [$29::event_type[]]))
and (co.type = $30 or $31::comment_type[] is null or co.type = any (array [$32::comment_type[]]))
and (co.type = $33 or $34 is null or exists (select $35
from generate_subscripts($36::bigint[][], $37) s
where array [
($38::bigint[][])[s][$39],
($40::bigint[][])[s][$41]
] = array [co.home_team, co.away_team]))
and (now()::timestamptz is null or co.created_date <= now()::timestamptz)
and ($42::boolean is null or $43::boolean = $44 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 $45 offset $46 * $47)
select to_jsonb(fe.*) as feed_entity,
to_jsonb(co.*) as commentable_object,
to_jsonb(c.*) || jsonb_build_object(
$48, coalesce(cs.likes, $49),
$50, coalesce(csu.liked, $51),
$52, coalesce(cs.comment_count, $53),
$54, coalesce(cs.view_count, $55),
$56, jsonb_build_object(
$57, (u.id = any (array [(select ids from favorite_users)])),
$58, count(distinct follower_count.*),
$59, count(distinct following_count.*),
$60, count(distinct post_count.*),
$61, 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 = c.comment_to and
cs.reference_id = c.reference_id and
cs.comment_to_sub_type = c.comment_to_sub_type and
cs.cumulative = $62
left join comment_statistic_users as csu on csu.comment_statistic_id = cs.id and
csu.user_id = $63 and
$64 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 != $65
group by fe.id, co.id, c.id, cs.id, csu.id, u.id
|
|
0 min
< 0.1%
|
13 ms
|
232
db_user
|
with favorite_users as (select 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 (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.1%
|
1 ms
|
4,779
db_user
|
SELECT COUNT(*) AS count FROM pg_available_extensions WHERE name = $1 /*pghero*/
|
|
0 min
< 0.1%
|
359 ms
|
8
db_user
|
SELECT t.*
FROM public.commentable_objects t
ORDER BY created_date DESC
LIMIT $1
Covered by index on (created_date)
Rows: 18649638
Row progression: 18649638, 1
Row estimates
- created_date (sort): 1
Existing indexes
- id PRIMARY
- created_date
- created_epoch DESC, id
- reference_id, type UNIQUE
- type
|
|
0 min
< 0.1%
|
1,371 ms
|
2
db_user
|
create index idx_feeds_score on feeds ((base_score + extra_score + follower_score) desc)
|
|
0 min
< 0.1%
|
0 ms
|
639,578
db_user
|
select cs.view_count
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
Details
CREATE INDEX CONCURRENTLY ON comment_statistics (reference_id)
Rows: 1115
Row progression: 1115, 2
Row estimates
- reference_id (=): 2
- comment_to (=): 139
- comment_to_sub_type (=): 223
- cumulative (=): 558
Existing indexes
- id PRIMARY
- comment_to, comment_to_sub_type, reference_id, cumulative UNIQUE
- comment_to, reference_id, comment_to_sub_type, cumulative
|
|
0 min
< 0.1%
|
227 ms
|
12
db_user
|
with base as (select co.*,
coalesce(cs.comment_count, $7) 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 = $8 and
cs.comment_to_sub_type = $9 and
cs.cumulative = $10
where co.season_id = $1
and co.type = $11
and co.event_type is not null
and co.deleted = $12
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 <> $13 and round is not null and round <> $14
then $15 || round
when league_type = $16
and (round is null or round = $17)
and stage_name is not null
and stage_mode = $18
then stage_name || $19 ||
case when is_knockout then $20 else $21 end
when league_type = $22
and (round is null or round = $23)
and stage_name is not null
and stage_mode = $24
then stage_name
when league_type = $25
and round is not null
and round <> $26
and stage_name is not null
and stage_mode = $27
then stage_name || $28 || round
else $29
end as group_name
from base)
select *
from grouped
where ($4::text is null or group_name = $4)
order by comment_count, match_time desc, reference_id desc, stage_order desc, round desc
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.1%
|
0 ms
|
97,988
db_user
|
INSERT INTO commentable_objects (reference_id, type) VALUES ($1, $2) RETURNING id
|
|
0 min
< 0.1%
|
224 ms
|
12
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.stage_order,
ranked.round_num,
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
order by stage_order 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
|