>> I have set work_mem to a very low value intentionally for demonstration >> purposes: >> >> postgres=# show work_mem; >> work_mem >> ---------- >> 16MB >> (1 row) >> >> postgres=# show shared_buffers ; >> shared_buffers >> ---------------- >> 128MB >> (1 row) >> >> >> When I run the following query ( I know that "not in" is not a good choice >> here ): >> >> postgres=# select count(user_id) from users where user_id not in ( select id >> from ids);
>"NOT IN" where the predate is a table column can lead to very poor >query plans especially where the haystack is not provably known (at >plan time) to contain only not null values. By reducing work_mem, the >server has decided has to repeatedly search the table to search for >the presence of null values. Try converting the query to NOT EXISTS. Thank you, Merlin. As said I know that "not in" is not a good choice in this case but I still do not get what is going here. Why does the server repeatedly search for NULL values when I decrease work_mem and why not when increasing work_mem? Regards Daniel
