> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
