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