On Sun, Nov 14, 2010 at 7:47 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Daniel Farina <drfar...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers