title: Why do I have one PostgreSQL server doing a Hash Join and another doing a Nested Loop Semi Join?
body: <p>I have two different machines both running psql 8.4.7 with the same data (using pg_restore), but I'm getting different queries from the two. There are other functional differences between the two machines (one is CentOS and the other Ubuntu, and they use different gcc's), but I would have thought that they'd use more or less the same logic.</p>
  
  <p>One machine is using a Hash Join, and it's going super fast (50ms). The other uses a Nested Loop and takes super long (10s). Any hints at how I can get to the bottom of this?</p>
  
  <p>On one:</p>
  
  <pre><code>database_production=&gt; EXPLAIN ANALYZE SELECT tags.*, COUNT(*) AS count FROM "tags" LEFT OUTER JOIN taggings ON tags.id = taggings.tag_id AND taggings.context = 'categories' INNER JOIN vendors ON vendors.id = taggings.taggable_id WHERE (taggings.taggable_type = 'Vendor') AND (taggings.taggable_id IN(SELECT vendors.id FROM "vendors" INNER JOIN "deals" ON "deals"."vendor_id" = "vendors"."id" INNER JOIN "programs" ON "programs"."id" = "deals"."program_id" INNER JOIN "memberships" ON "memberships"."program_id" = "programs"."id" WHERE (memberships.user_id = 1) AND (vendors.id NOT IN (SELECT vendor_id FROM vendor_ignores WHERE user_id = 1)))) GROUP BY tags.id, tags.name HAVING COUNT(*) &gt; 0 ORDER BY count DESC;
                                                                                     QUERY PLAN                                                                                   
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Sort  (cost=164.89..164.89 rows=1 width=520) (actual time=9444.003..9444.009 rows=6 loops=1)
     Sort Key: (count(*))
     Sort Method:  quicksort  Memory: 17kB
     -&gt;  HashAggregate  (cost=164.86..164.88 rows=1 width=520) (actual time=9443.936..9443.942 rows=6 loops=1)
           Filter: (count(*) &gt; 0)
           -&gt;  Nested Loop Semi Join  (cost=14.92..164.85 rows=1 width=520) (actual time=67.355..9443.645 rows=94 loops=1)
                 Join Filter: (public.vendors.id = deals.vendor_id)
                 -&gt;  Nested Loop  (cost=9.35..29.93 rows=1 width=528) (actual time=3.570..154.104 rows=7636 loops=1)
                       -&gt;  Nested Loop  (cost=9.35..21.65 rows=1 width=524) (actual time=3.534..83.165 rows=7636 loops=1)
                             -&gt;  Bitmap Heap Scan on taggings  (cost=9.35..13.37 rows=1 width=8) (actual time=3.476..12.277 rows=7636 loops=1)
                                   Recheck Cond: (((taggable_type)::text = 'Vendor'::text) AND ((context)::text = 'categories'::text))
                                   -&gt;  BitmapAnd  (cost=9.35..9.35 rows=1 width=0) (actual time=3.410..3.410 rows=0 loops=1)
                                         -&gt;  Bitmap Index Scan on index_taggings_on_taggable_type  (cost=0.00..4.55 rows=40 width=0) (actual time=1.664..1.664 rows=7636 loops=1)
                                               Index Cond: ((taggable_type)::text = 'Vendor'::text)
                                         -&gt;  Bitmap Index Scan on index_taggings_on_context  (cost=0.00..4.55 rows=40 width=0) (actual time=1.727..1.727 rows=7648 loops=1)
                                               Index Cond: ((context)::text = 'categories'::text)
                             -&gt;  Index Scan using tags_pkey on tags  (cost=0.00..8.27 rows=1 width=520) (actual time=0.004..0.005 rows=1 loops=7636)
                                   Index Cond: (tags.id = taggings.tag_id)
                       -&gt;  Index Scan using vendors_pkey on vendors  (cost=0.00..8.27 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=7636)
                             Index Cond: (public.vendors.id = taggings.taggable_id)
                 -&gt;  Nested Loop  (cost=5.57..134.62 rows=24 width=8) (actual time=0.035..1.117 rows=93 loops=7636)
                       -&gt;  Nested Loop  (cost=4.54..100.19 rows=24 width=4) (actual time=0.028..0.344 rows=93 loops=7636)
                             -&gt;  Nested Loop  (cost=0.00..9.57 rows=1 width=8) (actual time=0.010..0.035 rows=3 loops=7636)
                                   -&gt;  Seq Scan on memberships  (cost=0.00..1.29 rows=1 width=4) (actual time=0.004..0.009 rows=3 loops=7636)
                                         Filter: (user_id = 1)
                                   -&gt;  Index Scan using programs_pkey on programs  (cost=0.00..8.27 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=22810)
                                         Index Cond: (programs.id = memberships.program_id)
                             -&gt;  Bitmap Heap Scan on deals  (cost=4.54..90.16 rows=37 width=8) (actual time=0.012..0.042 rows=31 loops=22810)
                                   Recheck Cond: (deals.program_id = programs.id)
                                   -&gt;  Bitmap Index Scan on index_deals_on_program_id  (cost=0.00..4.53 rows=37 width=0) (actual time=0.008..0.008 rows=31 loops=22810)
                                         Index Cond: (deals.program_id = programs.id)
                       -&gt;  Index Scan using vendors_pkey on vendors  (cost=1.03..1.42 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=713413)
                             Index Cond: (public.vendors.id = deals.vendor_id)
                             Filter: (NOT (hashed SubPlan 1))
                             SubPlan 1
                               -&gt;  Seq Scan on vendor_ignores  (cost=0.00..1.02 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=1)
                                     Filter: (user_id = 1)
   Total runtime: 9444.501 ms
  (38 rows)
  </code></pre>
  
  <p>On the other, the result actually shows up in a separate screen:</p>
  
  <pre><code>    QUERY PLAN                                                                                        
  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Sort  (cost=977.70..978.94 rows=496 width=23) (actual time=49.859..49.863 rows=6 loops=1)
     Sort Key: (count(*))
     Sort Method:  quicksort  Memory: 17kB
     -&gt;  HashAggregate  (cost=946.81..955.49 rows=496 width=23) (actual time=49.793..49.801 rows=6 loops=1)
           Filter: (count(*) &gt; 0)
           -&gt;  Hash Join  (cost=440.07..875.99 rows=7082 width=23) (actual time=28.661..49.599 rows=94 loops=1)
                 Hash Cond: (taggings.tag_id = tags.id)
                 -&gt;  Hash Join  (cost=424.91..763.45 rows=7082 width=4) (actual time=27.388..48.105 rows=94 loops=1)
                       Hash Cond: (taggings.taggable_id = public.vendors.id)
                       -&gt;  Seq Scan on taggings  (cost=0.00..184.72 rows=7378 width=8) (actual time=0.030..12.791 rows=7636 loops=1)
                             Filter: (((context)::text = 'categories'::text) AND ((taggable_type)::text = 'Vendor'::text))
                       -&gt;  Hash  (cost=331.68..331.68 rows=7458 width=12) (actual time=27.226..27.226 rows=94 loops=1)
                             -&gt;  Nested Loop  (cost=134.67..331.68 rows=7458 width=12) (actual time=26.056..27.084 rows=94 loops=1)
                                   -&gt;  HashAggregate  (cost=134.67..134.98 rows=31 width=8) (actual time=26.047..26.153 rows=94 loops=1)
                                         -&gt;  Nested Loop  (cost=1.03..134.59 rows=31 width=8) (actual time=24.422..25.890 rows=94 loops=1)
                                               -&gt;  Nested Loop  (cost=0.00..49.95 rows=59 width=4) (actual time=14.902..15.359 rows=94 loops=1)
                                                     -&gt;  Nested Loop  (cost=0.00..9.57 rows=1 width=8) (actual time=0.108..0.143 rows=3 loops=1)
                                                           -&gt;  Seq Scan on memberships  (cost=0.00..1.29 rows=1 width=4) (actual time=0.050..0.057 rows=3 loops=1)
                                                                 Filter: (user_id = 1)
                                                           -&gt;  Index Scan using programs_pkey on programs  (cost=0.00..8.27 rows=1 width=4) (actual time=0.020..0.022 rows=1 loops=3)
                                                                 Index Cond: (programs.id = memberships.program_id)
                                                     -&gt;  Index Scan using index_deals_on_program_id on deals  (cost=0.00..39.64 rows=59 width=8) (actual time=4.943..5.005 rows=31 loops=3)
                                                           Index Cond: (deals.program_id = programs.id)
                                               -&gt;  Index Scan using vendors_pkey on vendors  (cost=1.03..1.42 rows=1 width=4) (actual time=0.106..0.108 rows=1 loops=94)
                                                     Index Cond: (public.vendors.id = deals.vendor_id)
                                                     Filter: (NOT (hashed SubPlan 1))
                                                     SubPlan 1
                                                       -&gt;  Seq Scan on vendor_ignores  (cost=0.00..1.02 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=1)
                                                             Filter: (user_id = 1)
                                   -&gt;  Index Scan using vendors_pkey on vendors  (cost=0.00..6.33 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=94)
                                         Index Cond: (public.vendors.id = deals.vendor_id)
                 -&gt;  Hash  (cost=8.96..8.96 rows=496 width=23) (actual time=1.257..1.257 rows=496 loops=1)
                       -&gt;  Seq Scan on tags  (cost=0.00..8.96 rows=496 width=23) (actual time=0.051..0.619 rows=496 loops=1)
   Total runtime: 50.357 ms
  (34 rows)
  </code></pre>