ratings.sql

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)
*/