From: | Ang Chin Han <angch(at)bytecraft(dot)com(dot)my> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | jim(at)nasby(dot)net, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Index not being used in MAX function (7.2.3) |
Date: | 2003-06-12 04:08:15 |
Message-ID: | 3EE7FCAF.8060103@bytecraft.com.my |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-06-12 04:19:47 | Re: LC_COLLATE=es_MX in PgSQL 7.3.2 |
Previous Message | Robert Fitzpatrick | 2003-06-12 03:56:57 | Running two versions on same server |