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.
>
Really? It seems to me just below of the ExecProcNode() in MultiExecHash()
is my expected location to filter out obviously unmatched tuples.
As long as we can construct a qualifier based on CHECK() constraint
of the other side, ExecQual() makes a decision whether fetched tuple
should be loaded to inner hash-table, or not.

Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>