From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: short-cutting if sum()>constant |
Date: | 2009-12-22 23:00:31 |
Message-ID: | 20091223000031.54ad09b5@dawn.webthatworks.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tue, 22 Dec 2009 20:47:18 +0100
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2009-12-23 00:09:40 | Re: short-cutting if sum()>constant |
Previous Message | Pavel Stehule | 2009-12-22 19:47:18 | Re: short-cutting if sum()>constant |