Horton

Re: Infinite Interval

Dean Rasheed <dean.a.rasheed@gmail.com>

View thread View on postgr.es
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: jian he <jian.universality@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Joseph Koshakow <koshy44@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, "Gregory Stark (as CFM)" <stark.cfm@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-09-19T11:14:20Z
Lists: pgsql-hackers 
On Sat, 16 Sept 2023 at 01:00, jian he <jian.universality@gmail.com> wrote:
>
> I refactor the avg(interval), sum(interval), so moving aggregate,
> plain aggregate both work with +inf/-inf.
> no performance degradation, in fact, some performance gains.
>

I haven't reviewed this part in any detail yet, but I can confirm that
there are some impressive performance improvements for avg(). However,
for me, sum() seems to be consistently a few percent slower with this
patch.

The introduction of an internal transition state struct seems like a
promising approach, but I think there is more to be gained by
eliminating per-row pallocs, and IntervalAggState's MemoryContext
(interval addition, unlike numeric addition, doesn't require memory
allocation, right?).

Also, this needs to include serialization and deserialization
functions, otherwise these aggregates will no longer be able to use
parallel workers. That makes a big difference to queryE, if the size
of the test data is scaled up.

This comment:

+   int64       N;              /* count of processed numbers */

should be "count of processed intervals".

Regards,
Dean

Commits

  1. Support +/- infinity in the interval data type.

  2. Avoid integer overflow hazard in interval_time().

  3. Guard against overflow in make_interval().

  4. Fix minmax-multi on infinite date/timestamp values

  5. Optimize various aggregate deserialization functions, take 2

  6. Remove dead code in DecodeInterval()

  7. Accept "+infinity" in date and timestamp[tz] input.

  8. Fix overflow hazards in interval input and output conversions.