title: Improving Subquery performance in Postgres
body: <p>I have these two tables in my database</p>
  
  <pre><code>  Student Table                   Student Semester Table
  | Column     : Type     |       | Column     : Type     |
  |------------|----------|       |------------|----------|
  | student_id : integer  |       | student_id : integer  |      
  | satquan    : smallint |       | semester   : integer  |
  | actcomp    : smallint |       | enrolled   : boolean  | 
  | entryyear  : smallint |       | major      : text     |
  |-----------------------|       | college    : text     |
                                  |-----------------------|
  </code></pre>
  
  <p>Where student_id is a unique key in the student table, and a foreign key in the student semester table. The semester integer is just a 1 for the first semester, 2 for the second, and so on.</p>
  
  <p>I'm doing queries where I want to get the students by their entryyear (and sometimes by their sat and/or act scores), then get all of those students associated data from the student semester table.</p>
  
  <p>Currently, my queries look something like this:</p>
  
  <pre><code>SELECT * FROM student_semester
  WHERE student_id IN(
      SELECT student_id FROM student_semester
      WHERE student_id IN(
          SELECT student_id FROM student WHERE entryyear = 2006
      ) AND college = 'AS' AND ...
  )
  ORDER BY student_id, semester;
  </code></pre>
  
  <p>But, this results in relatively long running queries (400ms) when I am selecting ~1k students. According to execution plan, most of the time is spent doing a hash join. To ameliorate this, I have added satquan, actpcomp, and entryyear columns to the student_semester table. This reduces the time to run the query by ~90%, but results in a lot of redundant data. Is there a better way to do this?</p>
  
  <p>These are the indexes that I currently have (Along with the implicit indexes on student_id):</p>
  
  <pre><code>CREATE INDEX act_sat_entryyear ON student USING btree (entryyear, actcomp, sattotal)
  CREATE INDEX student_id_major_college ON student_semester USING btree (student_id, major, college)
  </code></pre>
  
  <p><strong>Query Plan</strong></p>
  
  <pre><code>QUERY PLAN
  Hash Join  (cost=17311.74..35895.38 rows=81896 width=65) (actual time=121.097..326.934 rows=25680 loops=1)
    Hash Cond: (public.student_semester.student_id = public.student_semester.student_id)
    -&gt;  Seq Scan on student_semester  (cost=0.00..14307.20 rows=698820 width=65) (actual time=0.015..154.582 rows=698820 loops=1)
    -&gt;  Hash  (cost=17284.89..17284.89 rows=2148 width=8) (actual time=121.062..121.062 rows=1284 loops=1)
          Buckets: 1024  Batches: 1  Memory Usage: 51kB
          -&gt;  HashAggregate  (cost=17263.41..17284.89 rows=2148 width=8) (actual time=120.708..120.871 rows=1284 loops=1)
                -&gt;  Hash Semi Join  (cost=1026.68..17254.10 rows=3724 width=8) (actual time=4.828..119.619 rows=6184 loops=1)
                      Hash Cond: (public.student_semester.student_id = student.student_id)
                      -&gt;  Seq Scan on student_semester  (cost=0.00..16054.25 rows=42908 width=4) (actual time=0.013..109.873 rows=42331 loops=1)
                            Filter: ((college)::text = 'AS'::text)
                      -&gt;  Hash  (cost=988.73..988.73 rows=3036 width=4) (actual time=4.801..4.801 rows=3026 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 107kB
                            -&gt;  Bitmap Heap Scan on student  (cost=71.78..988.73 rows=3036 width=4) (actual time=0.406..3.223 rows=3026 loops=1)
                                  Recheck Cond: (entryyear = 2006)
                                  -&gt;  Bitmap Index Scan on student_act_sat_entryyear_index  (cost=0.00..71.03 rows=3036 width=0) (actual time=0.377..0.377 rows=3026 loops=1)
                                        Index Cond: (entryyear = 2006)
  Total runtime: 327.708 ms
  </code></pre>
  
  <p>I was mistaken about there not being a Seq Scan in the query. I think the Seq Scan is being done due to the number of rows that match the college condition; when I change it to one that has less students an index is used. Source: <a href="https://stackoverflow.com/a/5203827/880928">https://stackoverflow.com/a/5203827/880928</a></p>
  
  <p><strong>Query with entryyear column included student semester table</strong></p>
  
  <pre><code>SELECT * FROM student_semester
  WHERE student_id IN(
      SELECT student_id FROM student_semester
      WHERE entryyear = 2006 AND collgs = 'AS'
  ) ORDER BY student_id, semester;
  </code></pre>
  
  <p><strong>Query Plan</strong></p>
  
  <pre><code>Sort  (cost=18597.13..18800.49 rows=81343 width=65) (actual time=72.946..74.003 rows=25680 loops=1)
    Sort Key: public.student_semester.student_id, public.student_semester.semester
    Sort Method: quicksort  Memory: 3546kB
    -&gt;  Nested Loop  (cost=9843.87..11962.91 rows=81343 width=65) (actual time=24.617..40.751 rows=25680 loops=1)
          -&gt;  HashAggregate  (cost=9843.87..9845.73 rows=186 width=4) (actual time=24.590..24.836 rows=1284 loops=1)
                -&gt;  Bitmap Heap Scan on student_semester  (cost=1612.75..9834.63 rows=3696 width=4) (actual time=10.401..23.637 rows=6184 loops=1)
                      Recheck Cond: (entryyear = 2006)
                      Filter: ((collgs)::text = 'AS'::text)
                      -&gt;  Bitmap Index Scan on entryyear_act_sat_semester_enrolled_cumdeg_index  (cost=0.00..1611.82 rows=60192 width=0) (actual time=10.259..10.259 rows=60520 loops=1)
                            Index Cond: (entryyear = 2006)
          -&gt;  Index Scan using student_id_index on student_semester  (cost=0.00..11.13 rows=20 width=65) (actual time=0.003..0.010 rows=20 loops=1284)
                Index Cond: (student_id = public.student_semester.student_id)
  Total runtime: 74.938 ms
  </code></pre>