Re: a few crazy ideas about hash joins

On 3 Apr 2009, at 19:44, Lawrence, Ramon wrote:

>>> I would be especially interested in using a shared memory hash table
>>> that *all* backends can use - if the table is mostly read-only, as
>>> dimension tables often are in data warehouse applications. That
> would
>>> give zero startup cost and significantly reduced memory.
>>
>> I think that's a non-starter due to visibility issues and handling
>> inserts and updates. Even just reusing a hash from one execution in a
>> later execution of the same plan would be tricky since we would have
>> to expire it if the snapshot changes.
>
> If your data set is nearly read-only, materialized views would be a
> better way to go and would require no hash join changes.

I think what he means is that some of the tables in join are
effectively read-only. So materialized views are nono here. Unless you
mean just a partial ones.
I have to say, that frankly I got same problem, and plausibly my
schemas could benefit from changes discussed here.