Re: Index not being used in MAX function (7.2.3)

Tom Lane wrote:

> There's been no change in the basic problem, which is that no one has
> proposed a reasonably general method of translating aggregates into
> index manipulations.  Postgres has an extremely general, extensible
> concept of aggregates, and we're not going to mess it up with some
> poorly-designed hack.  But show me a clean design and implementation,
> and it'll go in.

Just a quick idea, in CREATE AGGREGATE, add optional parameters of:

1. ORDER BY ASC|DESC|USING operator
2. LIMIT {count}

And modify INITCOND param to:
    INITCOND = COUNT | initial_condition
where INITCOND = COUNT forces pgsql to get all row counts into INITCOND
first before calling sfunc or ffunc.

Still ugly, but things might be able to be generallized to:

-- returnme is a function that returns its parameter.
Or else make SFUNC optional and would by default return its param.

CREATE AGGREGATE max
   (BASETYPE=int, SFUNC=returnme, STYPE=int, INITCOND=NULL,
   ORDER BY DESC LIMIT 1);

CREATE AGGREGATE min
   (BASETYPE=int, SFUNC=returnme, STYPE=int, INITCOND=NULL,
   ORDER BY ASC LIMIT 1);

CREATE AGGREGATE count
   (BASETYPE=int, SFUNC=returnme, STYPE=int, INITCOND=COUNT,
   LIMIT 0);

The implementation would be:

SELECT min(foo) FROM bar
translates to:
SELECT (SELECT sfunc FROM bar ORDER BY foo ASC LIMIT 1) as min;
(or similar, if you get the idea)

SELECT baz, min(foo) FROM bar GROUP BY baz ORDER BY baz;
translates to:
SELECT
   baz,
   (SELECT sfunc FROM bar
      WHERE baz = highlevel_bar.baz
      ORDER BY foo ASC LIMIT 1) as min
   FROM bar
   ORDER BY baz;
-- Hoping that the subselect would automagically use an index if it
-- exists, like normal queries.

SELECT baz, count(*) FROM bar GROUP BY baz ORDER BY baz;
translates to:
SELECT
   baz,
   (SELECT __COUNT__ FROM bar
     WHERE baz = highlevel_bar.baz) as count
   FROM bar
   ORDER BY baz;

Note how the GROUP BY gets pushed into the subselect as a WHERE
condition, possibly allowing generic optimization of SELECT count(*).

Lots of hand waving in parts, but I hope I got the idea across. Can't
tell how much work is it to do without in depth knowledge of pgsql
internals, though. :(

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
  11:00am  up 168 days,  1:56,  9 users,  load average: 5.08, 5.05, 5.04