1

DB: Postgres 9.4

schema:

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 name

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 b_ids.

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 name and 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 name.

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 a_ids.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.