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

On Wed, 23 Dec 2009 01:09:40 +0100
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

> On Wed, 23 Dec 2009 00:00:31 +0100
> Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:
> 
> > 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:
> 
> as unexpected...

As even more unexpected... when all row are >0 and most of them are
equal to 1 the generate_series performs appreciably better (roughly
15% faster).
And I think your version can be further optimised:
select count(*) from (select (generate_series(1,a))::int from
data limit 90000000) s;
This perform 30% faster.

So what's so slow in the plpgsql version?

Fortunately as expected when "enough" rows are >1 the for loop
solution perform much better.

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