Re: [idea] table partition + hash join

On 2015-06-10 PM 01:42, Kouhei Kaigai wrote:
>
> Let's assume a table which is partitioned to four portions,
> and individual child relations have constraint by hash-value
> of its ID field.
>
> tbl_parent
> + tbl_child_0 ... CHECK(hash_func(id) % 4 = 0)
> + tbl_child_1 ... CHECK(hash_func(id) % 4 = 1)
> + tbl_child_2 ... CHECK(hash_func(id) % 4 = 2)
> + tbl_child_3 ... CHECK(hash_func(id) % 4 = 3)
>
> If someone tried to join another relation with tbl_parent
> using equivalence condition, like X = tbl_parent.ID, we
> know inner tuples that does not satisfies the condition
> hash_func(X) % 4 = 0
> shall be never joined to the tuples in tbl_child_0.
> So, we can omit to load these tuples to inner hash table
> preliminary, then it potentially allows to split the
> inner hash-table.
>

Unless I am missing something (of your idea or how hash join works), it seems
that there is no way to apply the filter qual (hash_func(X) % 4 = 0, etc.) at
the Hash node. So, that qual would not be able to limit what gets into the
inner hash table, right? Perhaps the qual needs to be pushed all the way down
to the Hash's underlying scan if that makes sense.

Thanks,
Amit