Proposal: Pre ordered aggregates, default ORDER BY clause for aggregates - median support

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Proposal: Pre ordered aggregates, default ORDER BY clause for aggregates - median support
Date: 2009-12-20 20:52:24
Message-ID: 162867790912201252q77ee2588me4c4bf76b5919f1c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello

I am thinking about implementation of median function. This function
should be implemented in two ways:

a) direct entering an ORDER BY clause for median funcall in gram.y
b) general support for "preordered aggregates".

I prefer plan b, because there are more similar aggregates - like
Quantiles. So with general support of preordered aggregates we can
move these aggregates to contrib, separate library or core (if we
would). I am for median in core - and others in contrib - as example
of this kind of aggregates.

What we need:

a) some new intelligence in parser - it use default ORDER BY clause
when explicit ORDER BY clause is missing.

b) for effective implementation we need to know real number of tuples
in state function. Without this knowledge we have to copy tuples to
tuple store or to array. It is useless, because we have to execute
sortby before - so we have this information. There could be one
optimalisation. We don't need to process all rows - for median we have
to process only 1/2 of rows - it could be nice, if state function can
send signal - don't call me more.

New syntax:

CREATE AGGREGATE name ( input_data_type [ORDER BY [(DESC|ASC)]] [ , ... ] ) (
SFUNC = sfunc,
STYPE = state_data_type
[ , FINALFUNC = ffunc ]
[ , INITCOND = initial_condition ]
[ , SORTOP = sort_operator ]
)

example:

CREATE AGGREGATE median (float8 ORDER BY) (
SFUNC = median_state,
STYPE = internal,
FINALFUNC = median_final,
SORTOP = '<'
)

I would to insure, so this idea is acceptable.

Regards
Pavel Stehule

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2009-12-20 21:04:15 Re: alpha3 release schedule?
Previous Message Simon Riggs 2009-12-20 20:42:07 Re: alpha3 release schedule?