title: Why does this GROUP BY cause a full table scan?
body: <p>I have a table of <code>projects</code> and a table of <code>tasks</code>, with each task referencing a single project. I want to get a list of projects sorted by their due dates along with the number of tasks in each project. I can pretty easily write this query two ways. First, using <code>JOIN</code> and <code>GROUP BY</code>:</p>
  <pre><code>SELECT p.name, p.due_date, COUNT(t.id) as num_tasks
  FROM projects p
  LEFT OUTER JOIN tasks t ON t.project_id = p.id
  GROUP BY p.id
  ORDER BY p.due_date ASC LIMIT 20;
  </code></pre>
  <p>Second, using a subquery:</p>
  <pre><code>SELECT p.name, p.due_date, (SELECT
     COUNT(*) FROM tasks t WHERE t.project_id = p.id) as num_tasks
  FROM projects p
  ORDER BY p.due_date ASC LIMIT 20;
  </code></pre>
  <p>I'm using PostgreSQL 10, and I've got indices on <code>projects.id</code>, <code>projects.due_date</code> and <code>tasks.project_id</code>. Why does the first query using the <code>GROUP BY</code> clause do a full table scan while the second query makes proper use of the indices? It seems like these should compile down to the same thing.</p>
  <p>Note that if I remove the <code>GROUP BY</code> and the <code>COUNT(t.id)</code> from the first query it will run quickly, just with lots of duplicate rows. So the problem is with the <code>GROUP BY</code> clause, not the <code>JOIN</code>. This seems like it's about the simplest <code>GROUP BY</code> one could do, so I'd like to understand if/how to make it more efficient before moving on to more complicated queries.</p>
  <p><em>Edit</em> — here's the result of <code>EXPLAIN ANALYZE</code>. First query:</p>
  <pre><code>Limit  (cost=41919.58..41919.63 rows=20 width=53) (actual time=1046.762..1046.771 rows=20 loops=1)
    -&gt;  Sort  (cost=41919.58..42169.58 rows=100000 width=53) (actual time=1046.760..1046.765 rows=20 loops=1)
          Sort Key: p.due_date
          Sort Method: top-N heapsort  Memory: 29kB
          -&gt;  GroupAggregate  (cost=0.71..39258.62 rows=100000 width=53) (actual time=0.109..1002.890 rows=100000 loops=1)
                Group Key: p.id
                -&gt;  Merge Left Join  (cost=0.71..35758.62 rows=500000 width=49) (actual time=0.072..807.603 rows=500702 loops=1)
                      Merge Cond: (p.id = t.project_id)
                      -&gt;  Index Scan using projects_pkey on projects p  (cost=0.29..3542.29 rows=100000 width=45) (actual time=0.025..38.363 rows=100000 loops=1)
                      -&gt;  Index Scan using project_id_idx on tasks t  (cost=0.42..25716.33 rows=500000 width=8) (actual time=0.038..531.097 rows=500000 loops=1)
  Planning Time: 0.573 ms
  Execution Time: 1046.934 ms
  </code></pre>
  <p>Second query:</p>
  <pre><code>Limit  (cost=0.29..92.61 rows=20 width=49) (actual time=0.079..0.443 rows=20 loops=1)
    -&gt;  Index Scan using project_date_idx on projects p  (cost=0.29..461594.09 rows=100000 width=49) (actual time=0.076..0.432 rows=20 loops=1)
          SubPlan 1
            -&gt;  Aggregate  (cost=4.54..4.55 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=20)
                  -&gt;  Index Only Scan using project_id_idx on tasks t  (cost=0.42..4.53 rows=6 width=0) (actual time=0.009..0.011 rows=5 loops=20)
                        Index Cond: (project_id = p.id)
                        Heap Fetches: 0
  Planning Time: 0.284 ms
  Execution Time: 0.551 ms
  </code></pre>
  <p>And if anyone wants to try to exactly reproduce this, here's my setup:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE projects (
      id serial NOT NULL PRIMARY KEY,
      name varchar(100) NOT NULL,
      due_date timestamp NOT NULL
  );
  
  CREATE TABLE tasks (
      id serial NOT NULL PRIMARY KEY,
      project_id integer NOT NULL,
      data real NOT NULL
  );
  
  INSERT INTO projects (name, due_date) SELECT
      md5(random()::text),
      timestamp '2020-01-01 00:00:00' +
         random() * (timestamp '2030-01-01 20:00:00' - timestamp '2020-01-01 10:00:00')
      FROM generate_series(1, 100000);
  
  INSERT INTO tasks (project_id, data)
      SELECT CAST(1 + random()*99999 AS integer), random()
      FROM generate_series(1, 500000);
  
  CREATE INDEX project_date_idx ON projects (&quot;due_date&quot;);
  CREATE INDEX project_id_idx ON tasks (&quot;project_id&quot;);
  ALTER TABLE tasks ADD CONSTRAINT task_foreignkey FOREIGN KEY (&quot;project_id&quot;) REFERENCES &quot;projects&quot; (&quot;id&quot;) DEFERRABLE INITIALLY DEFERRED;
  </code></pre>