Message-ID: <3E19B06A.1080002@klaster.net>
Date: Mon, 06 Jan 2003 17:35:54 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: dev@archonet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: order by and aggregate
References: <3E197A39.8070808@klaster.net>
	<200301061341.32945.dev@archonet.com>
	<3E198E18.5090408@klaster.net>
	<1086.192.168.1.16.1041868791.squirrel@mainbox.archonet.com>
In-Reply-To: <3E197A39.8070808@klaster.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

dev@archonet.com wrote:

> Ah - so it's maximum of a running-total rather than a sum.

Sorry, my english still has a lot of black-holes :-(

> AFAIK you are out of luck with aggregate functions. The order data is
> supplied to them is *not* defined - the "order by" operates just before
> results are output. Not much you can do about this, the whole basis of
> relational DBs are sets and sets don't have any idea of ordering.

You are right, but if it is possible to use some tricks, why not to use 
them?

> However, there are three options:
>
> You could define a cursor (or a table-function in 7.3) which would handle
> the order-by and then calculate the running-total on the fly. You then
> just need a standard max(running_total) call to pick out the value.
> Actually, if you use the cursor you might need to implement the max() in
> the application.

>
>
> Alternatively, you could add a running_total column and use a trigger to
> ensure the value is kept up to date.

>
>
> Finally, you could do the work in the application.
>
> Difficult to say which is the best for you. If you have 7.3, don't need
> these figures often and do a lot of updates/inserts I'd recommend option
> 1. If you're using 7.2, don't do a lot of inserts and want the figures
> frequently I'd choose option 2.

All of these options look a bit difficult.
Currently I use pl/pgsql function and query with sorted subselect:
select maxsum(X.val) from (select val from some_table order by key) X
It isn't a very big problem for me. I was just wondering if I can change 
this pl/pgsql function in a view. Few weeks ago I asked on 
pgsql-performance about views and subselects. The conclusion was that 
postgresql planner doesn't work well when joining subselects - it wastes 
time on querying all rows of subselect.

1. I think it could rather slow down than speed up my solution.
2. I can't store this value, because each time data range changes.
3. I want to do as much as possible inside postgres.

I think I will have to stay with pl/pgsql function and sorted subquery.

Thanks for your help,
Tomasz Myrta