> 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.)
You might find this page interesting:
http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/
I assume workmem, effective_cache_size and random_page_cost are all the same
in the 2 postgresql.conf?
Do you get the same after you ANALYZE the database?
If you read the page above you might realise NOT IN is not really what you
want. Maybe NOT EXISTS or a LEFT OUTER JOIN ... WHERE transaction_id IS NULL
?
David.