From: | Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> |
---|---|
To: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BRIN minmax multi - incorrect distance for infinite timestamp/date |
Date: | 2023-10-19 09:05:45 |
Message-ID: | 589f9827-2d4c-90ac-0ce2-84b83c5e4c0c@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 10/19/23 09:04, Dean Rasheed wrote:
> On Thu, 19 Oct 2023, 05:32 Ashutosh Bapat, <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com
> <mailto:ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>> wrote:
>
> On Wed, Oct 18, 2023 at 8:23 PM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com
> <mailto:tomas(dot)vondra(at)enterprisedb(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Bapat | 2023-10-19 09:22:42 | Re: BRIN minmax multi - incorrect distance for infinite timestamp/date |
Previous Message | Tomas Vondra | 2023-10-19 09:01:44 | Re: BRIN minmax multi - incorrect distance for infinite timestamp/date |