Re: Refactoring the Type System

On Sun, Nov 14, 2010 at 7:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniel Farina <drfarina@acm.org> writes:
>> Here are some weaknesses in the SUM aggregate that run up against the
>> type system. Maybe they'll help crystallize some discussion:
>
>> SUM(int2) => int4
>> SUM(int4) => int8
>> SUM(int8) => numeric
>
>> Some weaknesses:
>
>> SUM, of any precision, assumes that the precision being accumulated
>> into (which is also the return-precision) is enough to avoid overflow.
>
> This is not a flaw of the type system, it's just an implementation
> choice in the SUM() aggregates.  We could easily have chosen wider
> accumulation and/or result types.

That's true, but there are downsides to escalating the precision so
aggressively.

The case I was thinking about in particular involves composition of
SUM. If one can assume that a relation has int4s and that will never
overflow an int8 (as is done now), I don't see a great way to optimize
the following case without special exceptions in the optimizer for
particular aggregates known a-priori. Here's what would happen now:

SELECT SUM(x::int8)::numeric
FROM (SELECT SUM(x::int4)::int8 AS x     FROM rel     GROUP BY y) some_name;

Could be rendered, by this assumption, as:

SELECT SUM(x::int8)::int8
....(same FROM clause)

(Why would anyone write a query like this? Views. Possibly inlined SQL
UDFs, too.)

This can be measurably faster. It also more properly constrains the
result type, as numeric can also handle non-integer quantities.

I should have underscored that a positive aspect of having a
type-class like facility that allows declaration things like this
hypothetical Integer when backed by concrete types that might support
a superset of functionality.

fdr