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

Tom Lane 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.

>
>>RAM: 2.4GB, maybe? Not that familiar with HPUX mem setup...

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?

Ed