Re: [GENERAL] Query too slow with "not in" condition
On Sun, Nov 30, 2008 at 3:28 PM, David Rowley <[EMAIL PROTECTED]> wrote: > You might find this page interesting: > http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/ > > Thanks David. Another issue I was faced with was exactly what the link you provided discusses. Best regards, Ma Sivakumar மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com
Re: [GENERAL] Query too slow with "not in" condition [Resolved]
Thanks a lot. On Sun, Nov 30, 2008 at 11:28 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "David Rowley" <[EMAIL PROTECTED]> writes: > > I assume workmem, effective_cache_size and random_page_cost are all the > same > > in the 2 postgresql.conf? > > Indeed, work_mem is probably the problem. The critical difference > between the two plans seems to be that the first one is using a > "hashed subplan" and the second one isn't. Assuming the same datatypes in > both databases, the only reason not to use a hashed subplan is if the > hashtable is estimated not to fit in work_mem. > I changed work_mem in test machine to be implemented in production server later. Completely forgot that when faced with this issue. Sorry for the trouble. I learnt about "hashed subplan" requiring sufficient work_mem, however. Thanks and regards, Ma Sivakumar மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com
Re: [GENERAL] Query too slow with "not in" condition
"David Rowley" <[EMAIL PROTECTED]> writes: > I assume workmem, effective_cache_size and random_page_cost are all the same > in the 2 postgresql.conf? Indeed, work_mem is probably the problem. The critical difference between the two plans seems to be that the first one is using a "hashed subplan" and the second one isn't. Assuming the same datatypes in both databases, the only reason not to use a hashed subplan is if the hashtable is estimated not to fit in work_mem. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query too slow with "not in" condition
> 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query too slow with "not in" condition
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
