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