Query too slow with "not in" condition

["சிவகுமார் மா" <masivakumar@xxxxxxxxx>]

Dear Friends,

I have loaded the backup from a live database in a test system. Both run 8.3.5 versions. The plan for a query varies in these systems.

Test System
A. PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (SUSE Linux)

B. explain select * from stock_transaction_detail_106 where transaction_id not in (select transaction_id from transaction_value);
Seq Scan on stock_transaction_detail_106  (cost=1829.78..2867.74 rows=16478 width=128)
   Filter: (NOT (hashed subplan))
   SubPlan
     ->  Seq Scan on transaction_value  (cost=0.00..1598.02 rows=92702 width=4)

The query takes about 300 ms to run. 

Production System

1. PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 (SUSE Linux)

2. explain select * from stock_transaction_detail_106 where transaction_id not in (select transaction_id from transaction_value);
 
Seq Scan on stock_transaction_detail_106  (cost=2153.95..25245478.39 rows=17064 width=122)
   Filter: (NOT (subplan))
   SubPlan
     ->  Materialize  (cost=2153.95..3401.01 rows=92905 width=4)
           ->  Seq Scan on transaction_value  (cost=0.00..1743.05 rows=92905 width=4)

Here the query did not return any results after 1hour.

In both the computers same query with in condition runs fast (520 ms and 290 ms respectively)

Please help me to resolve this issue. (One configuration difference  between these machines are pg_hba.conf file. In production machine it is password enabled. In test machine it is trust mode.)

Thanks and regards,

Ma Sivakumar

மா சிவகுமார்
எல்லோரும் எல்லாமும் பெற வேண்டும்
http://masivakumar.blogspot.com