title: Select records with IDs containing in another table
body: <pre><code>vit=# select count(*) from evtags;
    count  
  ---------
   4496914
  
  vit=# explain select tag from evtags where evid in (1002, 1023);
                                QUERY PLAN                                    
  ---------------------------------------------------------------------------------
   Index Only Scan using evtags_pkey on evtags  (cost=0.00..15.64 rows=12 width=7)
     Index Cond: (evid = ANY ('{1002,1023}'::integer[]))
  </code></pre>
  
  <p>This seems completely ok so far. Next, I want to use IDs from another table instead of specifying them in the query.</p>
  
  <pre><code>vit=# select count(*) from zzz;
   count 
  -------
   49738
  </code></pre>
  
  <p>Here we go...</p>
  
  <pre><code>vit=# explain select tag from evtags where evid in (select evid from zzz);
                                QUERY PLAN                               
  -----------------------------------------------------------------------
   Hash Semi Join  (cost=1535.11..142452.47 rows=291712 width=7)
     Hash Cond: (evtags.evid = zzz.evid)
     -&gt;  Seq Scan on evtags  (cost=0.00..69283.14 rows=4496914 width=11)
     -&gt;  Hash  (cost=718.38..718.38 rows=49738 width=4)
           -&gt;  Seq Scan on zzz  (cost=0.00..718.38 rows=49738 width=4)
  </code></pre>
  
  <p>Why index scan on the much more larger table and what's the correct way to do this?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I recreated my <code>zzz</code> table and now it is better for some reason:</p>
  
  <pre><code>vit=# explain analyze select tag from evtags where evid in (select evid from zzz);
                                                           QUERY PLAN                                                             
  ------------------------------------------------------------------------------------------------------------------------------------
   Nested Loop  (cost=708.00..2699.17 rows=2248457 width=7) (actual time=28.935..805.923 rows=244353 loops=1)
     -&gt;  HashAggregate  (cost=708.00..710.00 rows=200 width=4) (actual time=28.893..54.461 rows=38822 loops=1)
           -&gt;  Seq Scan on zzz  (cost=0.00..601.80 rows=42480 width=4) (actual time=0.032..10.985 rows=40000 loops=1)
     -&gt;  Index Only Scan using evtags_pkey on evtags  (cost=0.00..9.89 rows=6 width=11) (actual time=0.015..0.017 rows=6 loops=38822)
           Index Cond: (evid = zzz.evid)
           Heap Fetches: 0
   Total runtime: 825.651 ms
  </code></pre>
  
  <p>But after several executions it changes to</p>
  
  <pre><code>vit=# explain analyze select tag from evtags where evid in (select evid from zzz);
                                                                      QUERY PLAN                                                                     
  ---------------------------------------------------------------------------------------------------------------------------------------------------
   Merge Semi Join  (cost=4184.11..127258.48 rows=235512 width=7) (actual time=38.269..1461.755 rows=244353 loops=1)
     Merge Cond: (evtags.evid = zzz.evid)
     -&gt;  Index Only Scan using evtags_pkey on evtags  (cost=0.00..136736.89 rows=4496914 width=11) (actual time=0.038..899.647 rows=3630070 loops=1)
           Heap Fetches: 0
     -&gt;  Materialize  (cost=4184.04..4384.04 rows=40000 width=4) (actual time=38.212..61.038 rows=40000 loops=1)
           -&gt;  Sort  (cost=4184.04..4284.04 rows=40000 width=4) (actual time=38.208..51.104 rows=40000 loops=1)
                 Sort Key: zzz.evid
                 Sort Method: external sort  Disk: 552kB
                 -&gt;  Seq Scan on zzz  (cost=0.00..577.00 rows=40000 width=4) (actual time=0.018..8.833 rows=40000 loops=1)
   Total runtime: 1484.293 ms
  </code></pre>
  
  <p>...Which is actually slower. Is there any way to hint it a 'correct' execution plan?</p>
  
  <p>The point of these operations is that I want to perform number of queries on a subset of my data and wanted to use separate temporary table to hold IDs of records I want to process.</p>