title: Optimising Postgresql Queries
body: <p>I have two tables and i have to query my postgresql database. The table 1 has about 140 million records and table 2 has around 50 million records of the following. </p>
  
  <p>the table 1 has the following structure:</p>
  
  <pre><code>tr_id bigint NOT NULL, # this is the primary key
  query_id numeric(20,0),       # indexed column
  descrip_id numeric(20,0)      # indexed column
  </code></pre>
  
  <p>and table 2 has the following structure</p>
  
  <pre><code>query_pk  bigint # this is the primary key
  query_id  numeric(20,0)    # indexed column
  query_token numeric(20,0)
  </code></pre>
  
  <p>The sample db of table1 would be</p>
  
  <pre><code>1 25 96
  2 28 97
  3 27 98
  4 26 99
  </code></pre>
  
  <p>The sample db of table2 would be</p>
  
  <pre><code> 1 25 9554
   2 25 9456
   3 25 9785
   4 25 9514
   5 26 7412
   6 26 7433
   7 27 545
   8 27 5789
   9 27 1566
  10 28 122
  11 28 1456
  </code></pre>
  
  <p>I am preferring queries in which i would be able to query in blocks of tr_id. In range of 10,000 as this is my requirement.</p>
  
  <p>I would like to get output in the following manner</p>
  
  <pre><code>25  {9554,9456,9785,9514}
  26  {7412,7433}
  27  {545,5789,1566}
  28  {122,1456}
  </code></pre>
  
  <p>I tried in the following manner</p>
  
  <pre><code>select query_id, 
         array_agg(query_token) 
  from sch.table2 
  where query_id in (select query_id 
                     from sch.table1 
                     where tr_id between 90001 and 100000) 
  group by query_id 
  </code></pre>
  
  <p>I am performing the following query which takes about 121346 ms and when some 4 such queries are fired it still takes longer time. Can you please help me to optimise the same.</p>
  
  <p>I have a machine which runs on windows 7 with i7 2nd gen proc with 8GB of RAM.</p>
  
  <p>The following is my postgresql configuration</p>
  
  <pre><code>shared_buffers = 1GB    
  effective_cache_size = 5000MB
  work_mem = 2000MB
  </code></pre>
  
  <p>What should I do to optimise it.</p>
  
  <p>Thanks</p>
  
  <p>EDIT : it would be great if the results ordered according to the following format</p>
  
  <pre><code>25  {9554,9456,9785,9514}
  28  {122,1456}
  27  {545,5789,1566}
  26  {7412,7433}
  </code></pre>
  
  <p>ie according to the order of the queryid  present in table1 ordered by tr_id. If this is computationally expensive may be in the client code i would try to optimise it. But I am not sure how efficient it would be.</p>
  
  <p>Thanks</p>