Re: [HACKERS] PoC: full merge join on comparison clause

From: Alexander Kuzmenkov <a(dot)kuzmenkov(at)postgrespro(dot)ru>
To: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Jeff Davis <pgsql(at)j-davis(dot)com>
Subject: Re: [HACKERS] PoC: full merge join on comparison clause
Date: 2018-02-22 18:42:08
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Here are some updates on this patch.

I split it into two parts. The preparatory part contains some mechanical
changes to prepare for the main part. Most importantly, a new field is
added, `RestrictInfo.is_mj_equality`. It is a marker of mergejoinable
equality clauses, and `RestrictInfo.mergeopfamilies` is a more general
marker of clauses that are mergejoinable but not necessarily equality.
The usages are changed accordingly.

The main part consists of executor and planner changes required to
support inequality merge joins.

The executor changes are as described in the original post.

The planner part has changed significantly since the last version. It
used to apply some shady hacks to ensure we have the required sort
orders of inner and outer paths. Now I think I found a reasonable way to
generate the pathkeys we need. When we sort outer relation in
`sort_inner_and_outer()`, the pathkeys are generated by
`select_outer_pathkeys_for_merge()`. When we use the pathkeys we already
have for the outer relation in `match_unsorted_outer()`, mergeclauses
are selected by `find_mergeclauses_for_pathkeys()`. I changed these
functions to select the right pathkey direction for merge clauses, and
also ensure that we only have a single inequality merge clause and it is
the last one. Also, to use the index paths, I changed
`pathkeys_useful_for_merging()` to keep both pathkey directions for
inequality merge clauses.

Some basic joins work, but I couldn't properly test all the corner cases
with different orderings, because they depend on a bug in vanilla merge
joins [1].

To sum up, the preparatory and executor changes are stable, and the
planner part is WIP.


Alexander Kuzmenkov
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
ineq-merge-join-v7-01-main.patch text/x-patch 39.5 KB
ineq-merge-join-v7-00-prep.patch text/x-patch 22.0 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Magnus Hagander 2018-02-22 18:54:31 Re: Allow workers to override datallowconn
Previous Message Peter Eisentraut 2018-02-22 18:26:48 Re: SHA-2 functions