Re: BRIN minmax multi - incorrect distance for infinite timestamp/date

On 10/19/23 09:04, Dean Rasheed wrote:
> On Thu, 19 Oct 2023, 05:32 Ashutosh Bapat, <ashutosh.bapat.oss@gmail.com
> <mailto:ashutosh.bapat.oss@gmail.com>> wrote:
> 
>     On Wed, Oct 18, 2023 at 8:23 PM Tomas Vondra
>     <tomas.vondra@enterprisedb.com
>     <mailto:tomas.vondra@enterprisedb.com>> wrote:
> 
>     >
>     > I did use that many values to actually force "compaction" and
>     merging of
>     > points into ranges. We only keep 32 values per page range, so with 2
>     > values we'll not build a range. You're right it may still trigger the
>     > overflow (we probably still calculate distances, I didn't realize
>     that),
>     > but without the compaction we can't check the query plans.
>     >
>     > However, I agree 60 values may be a bit too much. And I realized
>     we can
>     > reduce the count quite a bit by using the values_per_range option. We
>     > could set it to 8 (which is the minimum).
>     >
> 
>     I haven't read BRIN code, except the comments in the beginning of the
>     file. From what you describe it seems we will store first 32 values as
>     is, but later as the number of values grow create ranges from those?
>     Please point me to the relevant source code/documentation. Anyway, if
>     we can reduce the number of rows we insert, that will be good.
> 
> 
> I don't think 60 values is excessive, but instead of listing them out by
> hand, perhaps use generate_series().
> 

I tried to do that, but I ran into troubles with the "date" tests. I
needed to build values that close to the min/max values, so I did
something like

SELECT '4713-01-01 BC'::date + (i || ' days')::interval FROM
generate_series(1,10) s(i);

And then the same for the max date, but that fails because of the
date/timestamp conversion in date plus operator.

However, maybe two simple generate_series() would work ...

regards

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