Обсуждение: pgsql: Add support of multirange matching to the existing range GiST in

Add support of multirange matching to the existing range GiST indexes

6df7a9698b has introduced a set of operators between ranges and multiranges.
Existing GiST indexes for ranges could easily support majority of them.
This commit adds support for new operators to the existing range GiST indexes.
New operators resides the same strategy numbers as existing ones.  Appropriate
check function is determined using the subtype.

Catversion is bumped.

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/db6335b5b1d6654b0e3104f36817800d127c1c91

Modified Files
--------------
doc/src/sgml/gist.sgml                        |  35 ++-
doc/src/sgml/rangetypes.sgml                  |  21 +-
src/backend/utils/adt/multirangetypes.c       |  21 ++
src/backend/utils/adt/rangetypes_gist.c       | 393 +++++++++++++++++++++-----
src/include/catalog/catversion.h              |   2 +-
src/include/catalog/pg_amop.dat               |  80 ++++++
src/include/catalog/pg_amproc.dat             |  18 ++
src/include/catalog/pg_opclass.dat            |   2 +
src/include/catalog/pg_opfamily.dat           |   2 +
src/include/catalog/pg_proc.dat               |   8 +
src/include/utils/multirangetypes.h           |   2 +
src/test/regress/expected/multirangetypes.out | 257 +++++++++++++++++
src/test/regress/expected/rangetypes.out      | 162 +++++++++++
src/test/regress/sql/multirangetypes.sql      |  63 +++++
src/test/regress/sql/rangetypes.sql           |  27 ++
15 files changed, 1024 insertions(+), 69 deletions(-)

On Tue, Dec 29, 2020 at 11:45 PM Alexander Korotkov
<akorotkov@postgresql.org> wrote:
> Add support of multirange matching to the existing range GiST indexes

I accidentally squash here a commit which implements GiST multirange
indexes.  That commit was intended to go separately with its own
commit message below.  Sorry for that.

>  Add GiST indexes for multiranges
>
> This commits adds a pretty trivial way for multirange indexing: approximate
> multirange as union range with no gaps.  New multiranges opclass shares the
> majority of functions with ranges opclass.
>
> This is not an ideal way to index multirages, but something we can easily have.

------
Regards,
Alexander Korotkov

On Tue, Dec 29, 2020 at 11:53:14PM +0300, Alexander Korotkov wrote:
> On Tue, Dec 29, 2020 at 11:45 PM Alexander Korotkov <akorotkov@postgresql.org> wrote:
>> Add support of multirange matching to the existing range GiST indexes
>
> I accidentally squash here a commit which implements GiST multirange
> indexes.  That commit was intended to go separately with its own
> commit message below.  Sorry for that.

The buildfarm got a bit angry after this commit:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=clam&dt=2020-12-30%2003%3A00%3A11

Here is a diff from member clam, that uses ppc64:
 select count(*) from test_multirange_gist where mr <@
 int4range(10,50);
- count
--------
-   500
-(1 row)
-
+ERROR:  unknown multirange operator 4540

This points out to OID_RANGE_MULTIRANGE_CONTAINED_OP.

Please note that you should add a "Discussion:" in those commits to be
able to track down where this stuff has been discussed.
--
Michael

On Wed, Dec 30, 2020 at 10:04 AM Michael Paquier <michael@paquier.xyz> wrote:
> On Tue, Dec 29, 2020 at 11:53:14PM +0300, Alexander Korotkov wrote:
> > On Tue, Dec 29, 2020 at 11:45 PM Alexander Korotkov <akorotkov@postgresql.org> wrote:
> >> Add support of multirange matching to the existing range GiST indexes
> >
> > I accidentally squash here a commit which implements GiST multirange
> > indexes.  That commit was intended to go separately with its own
> > commit message below.  Sorry for that.
>
> The buildfarm got a bit angry after this commit:
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=clam&dt=2020-12-30%2003%3A00%3A11
>
> Here is a diff from member clam, that uses ppc64:
>  select count(*) from test_multirange_gist where mr <@
>  int4range(10,50);
> - count
> --------
> -   500
> -(1 row)
> -
> +ERROR:  unknown multirange operator 4540
>
> This points out to OID_RANGE_MULTIRANGE_CONTAINED_OP.

Thank you for pointing this out.  I'll investigate this.

> Please note that you should add a "Discussion:" in those commits to be
> able to track down where this stuff has been discussed.

My oversight, sorry.

------
Regards,
Alexander Korotkov

On Wed, Dec 30, 2020 at 11:08 AM Alexander Korotkov
<aekorotkov@gmail.com> wrote:
> On Wed, Dec 30, 2020 at 10:04 AM Michael Paquier <michael@paquier.xyz> wrote:
> > The buildfarm got a bit angry after this commit:
> > https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=clam&dt=2020-12-30%2003%3A00%3A11
> >
> > Here is a diff from member clam, that uses ppc64:
> >  select count(*) from test_multirange_gist where mr <@
> >  int4range(10,50);
> > - count
> > --------
> > -   500
> > -(1 row)
> > -
> > +ERROR:  unknown multirange operator 4540
> >
> > This points out to OID_RANGE_MULTIRANGE_CONTAINED_OP.
>
> Thank you for pointing this out.  I'll investigate this.

Should be fixed by 62097a4cc8.

------
Regards,
Alexander Korotkov