Skip to content

Instantly share code, notes, and snippets.

@joevandyk
Last active January 22, 2024 05:53
Show Gist options
  • Save joevandyk/5215014 to your computer and use it in GitHub Desktop.
Save joevandyk/5215014 to your computer and use it in GitHub Desktop.
begin;
create table user_ratings (
id serial primary key,
user_id integer not null,
rating integer not null check (rating >= 0 and rating <= 5),
ratable_id integer not null
);
create function random_int(max integer) returns integer as $$
select (random() * max)::integer
$$ language sql stable;
insert into user_ratings (user_id, rating, ratable_id)
select
i,
random_int(5),
random_int(100000)
from generate_series(1, 1000000) i;
create index on user_ratings(user_id);
create index on user_ratings(ratable_id);
create unique index on user_ratings(user_id, rating, ratable_id);
create type rating_detail as (rating integer, user_id integer, rating_id integer);
create view ratings_v as (
select
ratable_id,
avg(rating) as average_rating,
array_agg(row(rating, user_id, id)::rating_detail order by id desc) as ratings
from user_ratings
group by ratable_id
);
explain analyze
select row_to_json(ratings_v.*) from ratings_v where ratable_id = 10;
select row_to_json(ratings_v.*) from ratings_v where ratable_id = 10;
/*
--- THE OUTPUT:
{
"ratable_id": 10,
"average_rating": 2.8,
"ratings": [{
"rating": 5,
"user_id": 88995,
"rating_id": 88995
}, {
"rating": 3,
"user_id": 86959,
"rating_id": 86959
}, {
"rating": 0,
"user_id": 50124,
"rating_id": 50124
}, {
"rating": 3,
"user_id": 34425,
"rating_id": 34425
}, {
"rating": 3,
"user_id": 13468,
"rating_id": 13468
}
]
}
--- THE QUERY PLAN:
Subquery Scan on ratings_v (cost=96.10..5715.10 rows=1 width=28) (actual time=0.185..0.185 rows=1 loops=1)
-> GroupAggregate (cost=96.10..5715.09 rows=1 width=16) (actual time=0.154..0.154 rows=1 loops=1)
-> Bitmap Heap Scan on user_ratings (cost=96.10..5677.57 rows=5000 width=16) (actual time=0.050..0.077 rows=6 loops=1)
Recheck Cond: (ratable_id = 10)
-> Bitmap Index Scan on user_ratings_ratable_id_idx (cost=0.00..94.85 rows=5000 width=0) (actual time=0.039..0.039 rows=6 loops=1)
Index Cond: (ratable_id = 10)
Total runtime: 0.261 ms
(7 rows)
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment