Search Postgresql Archives

Re: Query never completes with low work_mem (at least not within one hour)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux