title: PostgreSQL complex summation query
body: <p>I have the following tables:</p>
  
  <pre><code>video (id, name) 
  
  keyframe (id, name, video_id) /*video_id has fk on video.id*/
  
  detector (id, concepts)
  
  score (detector_id, keyframe_id, score) /*detector_id has fk on detector .id and keyframe_id has fk on keyframe.id*/
  </code></pre>
  
  <p>In essence, a video has multiple keyframes associated with it, and each keyframe has been scored by all detectors. Each detector has a string of concepts it will score the keyframes on.</p>
  
  <p>Now, I want to find, in a single query if possible, the following:</p>
  
  <p>Given an array of detector id's (say, max 5), return the top 10 videos that have the best score on those detectors combined. Scoring them by averaging the keyframe scores per video per detector, and then summing the detector scores.</p>
  
  <p>Example:
  For a video with 3 associated keyframes with the following scores for 2 detectors:</p>
  
  <pre><code>detector_id | keyframe_id | score
  1             1             0.0281
  1             2             0.0012
  1             3             0.0269
  2             1             0.1341
  2             2             0.9726
  2             3             0.7125
  </code></pre>
  
  <p>This would give a score of the video:</p>
  
  <pre><code>sum(avg(0.0281, 0.0012, 0.0269), avg(0.1341, 0.9726, 0.7125))
  </code></pre>
  
  <p>Eventually I want the following result:</p>
  
  <pre><code>video_id | score
  1          0.417328
  2          ...
  </code></pre>
  
  <p>It has to be something like this I think, but I'm not quite there yet:</p>
  
  <pre><code>select
      (select
          (select sum(avg_score) summed_score
          from
          (select
              avg(s.score) avg_score
          from score s
          where s.detector_id = ANY(array[1,2,3,4,5]) and s.keyframe_id = kf.id) x)
      from keyframe kf
      where kf.video_id = v.id) y
  from video v
  </code></pre>
  
  <p>My score table is pretty big (100M rows), so I'd like it to be as fast as possible (all other options I tried take minutes to complete). I have a total of about 3000 videos, 500 detectors, and about 15 keyframes per video.</p>
  
  <p>If it's not possible to do this in less than ~2s, then I am also open to ways of restructuring the database schema's. There will probably be no inserts/deletions in the database at all.</p>
  
  <p><strong>EDIT</strong>:</p>
  
  <p>Thanks to GabrielsMessanger I have an answer, here is the query plan:</p>
  
  <pre><code>EXPLAIN (analyze, verbose)
  SELECT
      v_id, sum(fd_avg_score)
  FROM (
      SELECT 
          v.id as v_id, k.id as k_id, d.id as d_id,
          avg(s.score) as fd_avg_score
      FROM
          video v
          JOIN keyframe k ON k.video_id = v.id
          JOIN score s ON s.keyframe_id = k.id
          JOIN detector d ON d.id = s.detector_id
      WHERE
          d.id = ANY(ARRAY[1,2,3,4,5]) /*here goes detector's array*/
      GROUP BY
          v.id,
          k.id,
          d.id
  ) sub
  GROUP BY
      v_id
  ;
  </code></pre>
  
  <p>.</p>
  
  <pre><code>"GroupAggregate  (cost=1865513.09..1910370.09 rows=200 width=12) (actual time=52141.684..52908.198 rows=2991 loops=1)"
  "  Output: v.id, sum((avg(s.score)))"
  "  Group Key: v.id"
  "  -&gt;  GroupAggregate  (cost=1865513.09..1893547.46 rows=1121375 width=20) (actual time=52141.623..52793.184 rows=1121375 loops=1)"
  "        Output: v.id, k.id, d.id, avg(s.score)"
  "        Group Key: v.id, k.id, d.id"
  "        -&gt;  Sort  (cost=1865513.09..1868316.53 rows=1121375 width=20) (actual time=52141.613..52468.062 rows=1121375 loops=1)"
  "              Output: v.id, k.id, d.id, s.score"
  "              Sort Key: v.id, k.id, d.id"
  "              Sort Method: external merge  Disk: 37232kB"
  "              -&gt;  Hash Join  (cost=11821.18..1729834.13 rows=1121375 width=20) (actual time=120.706..51375.777 rows=1121375 loops=1)"
  "                    Output: v.id, k.id, d.id, s.score"
  "                    Hash Cond: (k.video_id = v.id)"
  "                    -&gt;  Hash Join  (cost=11736.89..1711527.49 rows=1121375 width=20) (actual time=119.862..51141.066 rows=1121375 loops=1)"
  "                          Output: k.id, k.video_id, s.score, d.id"
  "                          Hash Cond: (s.keyframe_id = k.id)"
  "                          -&gt;  Nested Loop  (cost=4186.70..1673925.96 rows=1121375 width=16) (actual time=50.878..50034.247 rows=1121375 loops=1)"
  "                                Output: s.score, s.keyframe_id, d.id"
  "                                -&gt;  Seq Scan on public.detector d  (cost=0.00..11.08 rows=5 width=4) (actual time=0.011..0.079 rows=5 loops=1)"
  "                                      Output: d.id, d.concepts"
  "                                      Filter: (d.id = ANY ('{1,2,3,4,5}'::integer[]))"
  "                                      Rows Removed by Filter: 492"
  "                                -&gt;  Bitmap Heap Scan on public.score s  (cost=4186.70..332540.23 rows=224275 width=16) (actual time=56.040..9961.040 rows=224275 loops=5)"
  "                                      Output: s.detector_id, s.keyframe_id, s.score"
  "                                      Recheck Cond: (s.detector_id = d.id)"
  "                                      Rows Removed by Index Recheck: 34169904"
  "                                      Heap Blocks: exact=192845 lossy=928530"
  "                                      -&gt;  Bitmap Index Scan on score_index  (cost=0.00..4130.63 rows=224275 width=0) (actual time=49.748..49.748 rows=224275 loops=5)"
  "                                            Index Cond: (s.detector_id = d.id)"
  "                          -&gt;  Hash  (cost=3869.75..3869.75 rows=224275 width=8) (actual time=68.924..68.924 rows=224275 loops=1)"
  "                                Output: k.id, k.video_id"
  "                                Buckets: 16384  Batches: 4  Memory Usage: 2205kB"
  "                                -&gt;  Seq Scan on public.keyframe k  (cost=0.00..3869.75 rows=224275 width=8) (actual time=0.003..33.662 rows=224275 loops=1)"
  "                                      Output: k.id, k.video_id"
  "                    -&gt;  Hash  (cost=46.91..46.91 rows=2991 width=4) (actual time=0.834..0.834 rows=2991 loops=1)"
  "                          Output: v.id"
  "                          Buckets: 1024  Batches: 1  Memory Usage: 106kB"
  "                          -&gt;  Seq Scan on public.video v  (cost=0.00..46.91 rows=2991 width=4) (actual time=0.005..0.417 rows=2991 loops=1)"
  "                                Output: v.id"
  "Planning time: 2.136 ms"
  "Execution time: 52914.840 ms"
  </code></pre>