Re: Hash Join vs Nested Loops in 7.2.1 ...

Ed Loehr wrote:

>>
>>> Second, the same query sometimes takes 10-50 seconds shortly after
>>> possibly a dump or other high-data-volume queries are executed, after
>>> which it then returns to 1 second execution time. Getting crowded out
>>> of shared memory?
>>>
>>>> Sounds like it. What shared-buffers setting are you using? How much
>>>> RAM in the box?
>>
>>> shared_buffers = 256
>>
>> That's not a lot --- 256*8K = 2MB. You might try something in the low
>> thousands.
>
> SAM indicates 512MB of RAM. I upped the shared buffers from 256 to
> 4096, and the hashjoin query came down from ~90 seconds to 10, still 10x
> slower than the 1-sec nested loops. Is that a performance difference
> you'd expect between hash and nested loops on this query because of EXISTS?

What I neglected to mention was that the planner was *choosing* the slower
hashjoin plan over the much faster nested loop plan without any PGOPTIONS set
or any postgresql.conf changes to enable_*, thus the motivation for a "thumb
on the scales." After upping the number of shared buffers, it has begun
choosing the smart plan 1-second plan, apparently after a restart, not sure.
Thanks, Tom.

Ed