query becomes fas on 'SET enable_hashjoin TO off;' - Mailing list pgsql-performance

Hi ,

I have a query in which two huge tables (A,B) are joined using an indexed
column and a search is made on tsvector on some column on B. Very limited
rows of B are  expected to match the query on tsvector column.

With default planner settings the query takes too long ( > 100 secs) , but
with hashjoin off  it returns almost immediately. The question is , is
it is advisable to
tweak planner settings for specific queries in application ?


The plans are as follows.

1. With default settings

explain    select lead_id from general.trade_leads join
general.profile_master as pm using(profile_id) where status ='m' and
co_name_vec @@ to_tsquery('plastic&tubes')  limit 20;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Limit  (cost=4109.11..11127.78 rows=20 width=4)
   ->  Hash Join  (cost=4109.11..90789.72 rows=247 width=4)
         Hash Cond: (trade_leads.profile_id = pm.profile_id)
         ->  Seq Scan on trade_leads  (cost=0.00..85752.52 rows=246832 width=8)
               Filter: ((status)::text = 'm'::text)
         ->  Hash  (cost=4095.68..4095.68 rows=1074 width=4)
               ->  Bitmap Heap Scan on profile_master pm
(cost=40.89..4095.68 rows=1074 width=4)
                     Filter: (co_name_vec @@ '''plastic'' & ''tube'''::tsquery)
                     ->  Bitmap Index Scan on
profile_master_co_name_vec  (cost=0.00..40.62 rows=1074 width=0)
                           Index Cond: (co_name_vec @@ '''plastic'' &
''tube'''::tsquery)
(10 rows)



2. with SET enable_hashjoin TO off;

explain  analyze  select lead_id from general.trade_leads join
general.profile_master as pm using(profile_id) where status ='m' and
co_name_vec @@ to_tsquery('plastic&tubes')  limit 20;

      QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3.42..13080.44 rows=20 width=4) (actual
time=1530.039..1530.039 rows=0 loops=1)
   ->  Nested Loop  (cost=3.42..161504.56 rows=247 width=4) (actual
time=1530.037..1530.037 rows=0 loops=1)
         ->  Index Scan using profile_master_co_name_vec on
profile_master pm  (cost=0.00..4335.36 rows=1074 width=4) (actual
time=220.821..1014.501 rows=7 loops=1)
               Index Cond: (co_name_vec @@ '''plastic'' & ''tube'''::tsquery)
               Filter: (co_name_vec @@ '''plastic'' & ''tube'''::tsquery)
         ->  Bitmap Heap Scan on trade_leads  (cost=3.42..145.75
rows=47 width=8) (actual time=73.640..73.640 rows=0 loops=7)
               Recheck Cond: (trade_leads.profile_id = pm.profile_id)
               Filter: ((status)::text = 'm'::text)
               ->  Bitmap Index Scan on trade_leads_profile_id
(cost=0.00..3.41 rows=47 width=0) (actual time=73.579..73.579 rows=0
loops=7)
                     Index Cond: (trade_leads.profile_id = pm.profile_id)
 Total runtime: 1530.137 ms



regds
mallah.