On Sun, Nov 14, 2010 at 2:27 PM, Daniel Farina <drfar...@acm.org> wrote: > 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.
Like Tom, I'm not sure this is really a type-system problem. This sounds like a complaint that operations on "numeric" are much slower than operations on "int4" and "int8", even for values that could be represented by either type. I think that's a valid complaint, but I don't see how changing the type system would help. I think what you'd need to is optimize the existing numeric type, or provide a new numeric-ish type with optimizations for dealing with small-to-medium-sized integers. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
