Message-ID: <065bb749-71ee-6bd2-7a4b-1088cb26a31a@enterprisedb.com>
Date: Fri, 14 Jul 2023 16:21:11 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.12.0
Subject: Re: PATCH: Using BRIN indexes for sorted output
Content-Language: en-US
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Andres Freund <andres@anarazel.de>,
 Greg Stark <stark@mit.edu>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <1607e037-8c5b-1626-9f98-496f067b9eb5@enterprisedb.com>
 <20230216161016.GV1653@telsasoft.com>
 <9f73f798-1dac-c75d-d834-aa012ba13b65@enterprisedb.com>
 <8f1374d1-6f4b-ce99-84eb-ffa852c0dadb@enterprisedb.com>
 <CAEze2Whd=MCQ61EEUUWJgohHN5cm3bv027N+vS4o6zOuBTY3tw@mail.gmail.com>
 <30f37aae-57fa-e7ab-9444-7b82969ed68f@enterprisedb.com>
 <CAEze2WgGueCY+ihQDASaKAWNG9C2Ex0vzoNU5inrMLwWyj3KMA@mail.gmail.com>
 <CAEze2Wh5VsGQUyg9FC+xZBLUPLZnm0pOym5uceOM0aG1YnuESA@mail.gmail.com>
 <825f648f-2a31-1870-942f-03fba2b6b3cf@enterprisedb.com>
 <CAEze2WjWaP+vNC9Y_y0vVAWiX9NF7VtXDtY8+cCd27TQiCSsTg@mail.gmail.com>
 <0c331bf8-0cd3-e9fe-ae81-9e3d68f1f95c@enterprisedb.com>
 <CAEze2Wj_UjWdpMfDSpYWXFnr2K2yX9zpihsD4iEZ9+JFMLOknw@mail.gmail.com>
 <1992ff4f-bf0e-3ad0-a859-cf054f6abc74@enterprisedb.com>
 <CAEze2WjOrBXbMEM-xq8OOPJquNdrTn-t6qX1G7FzBfn9nY_a6w@mail.gmail.com>
 <5c92f99a-e83e-c1b3-2c47-d75857a70b73@enterprisedb.com>
 <CAEze2Wg00Pry5KsNEqJps-uX+T6f=YTm-jyLZPt=yDNO5+3v0Q@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CAEze2Wg00Pry5KsNEqJps-uX+T6f=YTm-jyLZPt=yDNO5+3v0Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/065bb749-71ee-6bd2-7a4b-1088cb26a31a%40enterprisedb.com>
Precedence: bulk


On 7/11/23 13:20, Matthias van de Meent wrote:
> On Mon, 10 Jul 2023 at 22:04, Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>> On 7/10/23 18:18, Matthias van de Meent wrote:
>>> On Mon, 10 Jul 2023 at 17:09, Tomas Vondra
>>> <tomas.vondra@enterprisedb.com> wrote:
>>>> On 7/10/23 14:38, Matthias van de Meent wrote:
>>>>>> I haven't really thought about geometric types, just about minmax and
>>>>>> minmax-multi. It's not clear to me what the benefit for these types be.
>>>>>> I mean, we can probably sort points lexicographically, but is anyone
>>>>>> doing that in queries? It seems useless for order by distance.
>>>>>
>>>>> Yes, that's why you would sort them by distance, where the distance is
>>>>> generated by the opclass as min/max distance between the summary and
>>>>> the distance's origin, and then inserted into the tuplesort.
>>>>>
>>>>
>>>> OK, so the query says "order by distance from point X" and we calculate
>>>> the min/max distance of values in a given page range.
>>>
>>> Yes, and because it's BRIN that's an approximation, which should
>>> generally be fine.
>>>
>>
>> Approximation in what sense? My understanding was we'd get a range of
>> distances that we know covers all rows in that range. So the results
>> should be accurate, no?
> 
> The distance is going to be accurate only to the degree that the
> summary can produce accurate distances for the datapoints it
> represents. That can be quite imprecise due to the nature of the
> contained datapoints: a summary of the points (-1, -1) and (1, 1) will
> have a minimum distance of 0 to the origin, where the summary (-1, 0)
> and (-1, 0.5) would have a much more accurate distance of 1.

Ummm, I'm probably missing something, or maybe my mental model of this
is just wrong, but why would the distance for the second summary be more
accurate? Or what does "more accurate" mean?

Is that about the range of distances for the summary? For the first
range the summary is a bounding box [(-1,1), (1,1)] so all we know the
points may have distance in range [0, sqrt(2)]. While for the second
summary it's [1, sqrt(1.25)].

> The point I was making is that the summary can only approximate the
> distance, and that approximation is fine w.r.t. the BRIN sort
> algoritm.
> 

I think as long as the approximation (whatever it means) does not cause
differences in results (compared to not using an index), it's OK.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company