title: Query performance on join with json_agg in postgres
body: <p>I have two tables</p>
  
  <p>Table <strong>entries</strong></p>
  
  <pre>
  
   id    | val1 | val2
  -------+------+------+
   ent_1 | xxx  | xxx
   ent_2 | xyy  | yyy
   ent_3 | zxz  | zzz
   ent_4 | zxz  | zzz
  </pre>
  
  <p>Table <strong>entries_list</strong></p>
  
  <pre>
  
   id  | entry_id | val1 | val2
  -----+----------+------+-------
     1 |   ent_1  | xxx  | xxx
     2 |   ent_1  | xyy  | yyy
     3 |   ent_2  | zxz  | zzz
     4 |   ent_2  | zxz  | zzz
  </pre>
  
  <p><strong><code>entries_list.entry_id</code></strong> is forigen key from <strong><code>entries.id</code></strong></p>
  
  <p>So I need to find the <code>entries</code> which have a corresponding <code>entry_list</code> references. I don't want the <code>entries</code> which doesn't have any reference in <code>entry_list</code> and the result I am expecting from this is </p>
  
  <pre><code>[{
      id: ent_1,
      entries: [{
          id: 1,
          val1: xxx,
          val2: xxx
      }, {
          id: 1,
          val1: xxx,
          val2: xxx
      }]
  }, {
      id: ent_2,
      entries: [{
          id: 3,
          val1: xxx,
          val2: xxx
      }, {
          id: 4,
          val1: xxx,
          val2: xxx
      }]
  }]
  </code></pre>
  
  <p>because of the desired result and structure, I decided to use <strong>Json_agg</strong> and <strong>Json_build_object</strong> the query looks like this</p>
  
  <pre><code>SELECT entries.id, 
         Json_agg(Json_build_object('id', list.id, 'val1', list.val2, 'val2', 
         list.val2)) AS sub_list 
  FROM   entries 
         INNER JOIN (SELECT id,val1,val2 
                     FROM   entries_list) AS list 
                 ON entries.id = list.entry_id
  GROUP  BY entries.id 
  ORDER  BY entries.id 
  </code></pre>
  
  <p>But it performs very bad looks like almost 10 sec for 1M records. So what is the better way to change this?</p>
  
  <p>I think of getting the data in the plan way and do grouping it in code outside sql, but how the query should be modified in both approach.?</p>
  
  <p>I have <code>nodejs</code> backend and <code>pg</code> module as a connector.</p>