DB: Postgres 9.4
table a: id, score table ab: a_id, b_id, b_type table b: id, name, type
I am trying to get top 10 result in the following format:
name, count(group by name), average(score)
count is the total occurrences of
a group by
Basically I can do a 3 table join:
select name, count(*), average(score) from a joins ab on a.id = ab.a_id joins b on b.id = ab.b_id where type = 'sometype' group by name order by count(*) desc limit 10
This works, but the performance is very slow since my middle table ab is like 200M record, and the index is pretty large as well.
Fortunately I can filter on the table ab and make the result to around 1000 rows.
select filtered_ab.b_id, count(1) as ct from ( select b_id from ab where b_type = 'sometype' ) as filtered_ab group by filtered_ab.b_id order by ct desc limit 1000
This will give me the top 1000
Now I only have to joins table b with this result to append name column to it.
select name, b_id, ct from b join ( select filtered_ab.b_id, count(1) as ct from ( select b_id from ab where b_type = 'sometype' ) as filtered_ab group by filtered_ab.b_id order by ct desc limit 1000 ) as b_with_counts on b_with_counts.b_id = b.id
I am returning top 1000 is because currently
b.id is not 1 to 1, same
name can have like 1 to 5
b.ids, so just return 1000, and then group by
name later to approximately get all the counts for the same
But after this, I cannot figure out a way to append the average score from table a.
Currently the data in table ab, for each
b_id, there can be 1 to 1000000 or more