Date: Wed, 23 Dec 2009 00:00:31 +0100
From: Ivan Sergio Borgonovo <mail@webthatworks.it>
To: pgsql-sql@postgresql.org
Subject: Re: short-cutting if sum()>constant
Message-ID: <20091223000031.54ad09b5@dawn.webthatworks.it>
In-Reply-To: <162867790912221147r7e3041fej44332a05faf915d6@mail.gmail.com>
References: <20091222133527.3007bd96@dawn.webthatworks.it>
	<162867790912221147r7e3041fej44332a05faf915d6@mail.gmail.com>
Organization: http://www.webthatworks.it
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Tue, 22 Dec 2009 20:47:18 +0100
Pavel Stehule <pavel.stehule@gmail.com> wrote:

> Hello
> 
> I found one ugly trick. You can multiply lines and SUM > cons
> could be replaced limit clause:

The trick is really smart (and fun), kudos, really, it's always a
pleasure to read your solutions, thanks.

But as expected:

In a table with 100000 rows with random values [0,9]

test=# select sum(x) from (select 1 as x,(a =
generate_series(1,a))::int from data limit 100000) s;
  sum
--------
 100000
(1 row)

Time: 71.423 ms

test=# create or replace function tano(a int, out b int)
returns int as
$$
declare
        row record;
begin
	b :=0;
        for row in select a as _a from data
        loop
                b := row._a + b;
                if (b>=a) then
                        return;
                end if;
        end loop;
        return;
end;
$$ language plpgsql;

test=# select * from tano(100000);
   b
--------
 100000
(1 row)

Time: 0.187 ms

I run both several times to avoid simple caching issues... anyway I
didn't really run a serious benchmark, but results were always in
the same order of magnitude.

I hope I didn't make any mistake.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it