From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: John Naylor <john.naylor@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: WIP: BRIN multi-range indexes
Date: Tue, 26 Jan 2021 23:59:05 +0100
Message-ID: <d4aa7fa0-d06d-6584-9234-8c1696924dde@enterprisedb.com> (raw)
In-Reply-To: <CAFBsxsFudhzy1gUMp6fyj7xDXqZf5VPGC3krqsz42_0QGwcBBQ@mail.gmail.com>
References: <c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com>
<160434032777.4948.12542847889193566385.pgcf@coridan.postgresql.org>
<20201102232545.zw4xir577gq55ogj@development>
<88b3831d-8afa-4135-a420-27b12bf9fae4@enterprisedb.com>
<CAFBsxsFwLTg3qfs6RcoQouGDh54GUZLr7+BEXfo86qYPuNaZjA@mail.gmail.com>
<1fdcbd2a-ff0f-fb81-d227-22279d5cbcb7@enterprisedb.com>
<CAFBsxsF0spTU=NMBNER4KwJ5NLmkoU7ModUg7g8HAqZ9k+7OgA@mail.gmail.com>
<061b24c0-87eb-51a7-17d6-e1ba8c59fd23@enterprisedb.com>
<CAFBsxsE05b=iYEFm9Aw--vodne80DSqbzYfUYLy0FYQWOJRmTw@mail.gmail.com>
<8a40b7e4-357e-b0c2-e8b7-72001993fc32@enterprisedb.com>
<CAFBsxsEOX8JztU3-DWu95rVdfzcOdxn_9LyyJqGxBRS+agcuXQ@mail.gmail.com>
<c970fcb3-b483-1578-b33f-d61ebc56a948@enterprisedb.com>
<d288a5f2-3225-64c4-2be0-62379a138a34@enterprisedb.com>
<CAFBsxsEa9eN=sJaz7Px+i2Oh5TXZiW7hhxCrEMribebdtxUguQ@mail.gmail.com>
<2e903689-c518-f77d-551a-5e3e279d0838@enterprisedb.com>
<CAFBsxsFudhzy1gUMp6fyj7xDXqZf5VPGC3krqsz42_0QGwcBBQ@mail.gmail.com>
On 1/26/21 7:52 PM, John Naylor wrote:
> On Fri, Jan 22, 2021 at 10:59 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com <mailto:tomas.vondra@enterprisedb.com>>
> wrote:
> >
> >
> > On 1/23/21 12:27 AM, John Naylor wrote:
>
> > > Still, it would be great if multi-minmax can be a drop in
> replacement. I
> > > know there was a sticking point of a distance function not being
> > > available on all types, but I wonder if that can be remedied or worked
> > > around somehow.
> > >
> >
> > Hmm. I think Alvaro also mentioned he'd like to use this as a drop-in
> > replacement for minmax (essentially, using these opclasses as the
> > default ones, with the option to switch back to plain minmax). I'm not
> > convinced we should do that - though. Imagine you have minmax indexes in
> > your existing DB, it's working perfectly fine, and then we come and just
> > silently change that during dump/restore. Is there some past example
> > when we did something similar and it turned it to be OK?
>
> I was assuming pg_dump can be taught to insert explicit opclasses for
> minmax indexes, so that upgrade would not cause surprises. If that's
> true, only new indexes would have the different default opclass.
>
Maybe, I suppose we could do that. But I always found such changes
happening silently in the background a bit suspicious, because it may be
quite confusing. I certainly wouldn't expect such difference between
creating a new index and index created by dump/restore. Did we do such
changes in the past? That might be a precedent, but I don't recall any
example ...
> > As for the distance functions, I'm pretty sure there are data types
> > without "natural" distance - like most strings, for example. We could
> > probably invent something, but the question is how much we can rely on
> > it working well enough in practice.
> >
> > Of course, is minmax even the right index type for such data types?
> > Strings are usually "labels" and not queried using range queries,
> > although sometimes people encode stuff as strings (but then it's very
> > unlikely we'll define the distance definition well). So maybe for those
> > types a hash / bloom would be a better fit anyway.
>
> Right.
>
> > But I do have an idea - maybe we can do without distances, in those
> > cases. Essentially, the primary issue of minmax indexes are outliers, so
> > what if we simply sort the values, keep one range in the middle and as
> > many single points on each tail?
>
> That's an interesting idea. I think it would be a nice bonus to try to
> do something along these lines. On the other hand, I'm not the one
> volunteering to do the work, and the patch is useful as is.
>
IMO it's fairly small amount of code, so I'll take a stab at in in the
next version of the patch.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@enterprisedb.com, john.naylor@enterprisedb.com, pgsql-hackers@lists.postgresql.org
Subject: Re: WIP: BRIN multi-range indexes
In-Reply-To: <d4aa7fa0-d06d-6584-9234-8c1696924dde@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox