Re: Merge join for GiST

2017-04-13 7:01 GMT+05:00 Jeff Davis <pgsql(at)j-davis(dot)com>:
> On Tue, Apr 11, 2017 at 8:35 AM, Alexander Korotkov
> <a(dot)korotkov(at)postgrespro(dot)ru> wrote:
>> On Tue, Apr 11, 2017 at 5:46 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>>> Do you have a sense of how this might compare with range merge join?
>>
>>
>> If you have GiST indexes over ranges for both sides of join, then this
>> method could be used for range join. Hence, it could be compared with range
>> merge join.
>> However, particular implementation in pgsphere uses hardcoded datatypes and
>> operations.
>> Thus, for range join we need either generalized version of GiST-based join
>> or special implementation for ranges.
>
> Alexander, Andrew,
>
> How do you think we should proceed? Which projects do you think should
> eventually be in core, versus which are fine as extensions?

Some points in favor of Range joins via nbtree:
1. It's more efficient than B-tree over GiST
2. It is already in a patch form

Point against:
1. Particular implementation is somewhat leaked abstraction. Inside
the planner, you check not for capabilities of operator and type, but
for specific op and type. But I do not know how to fix this.

So, here is my opinion: if we can inside the planner understand that
join condition involves range specifics (for all available ranges) and
do Range Merge Join, then this is preferred solution.

Yes, Spatial Join, when available, will provide roughly the same scan
performance. But B-trees are more well known to users new to
PostgreSQL, and B-trees are faster.

Best regards, Andrey Borodin.