Re: short-cutting if sum()>constant

2009/12/23 Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>:
> On Wed, 23 Dec 2009 01:09:40 +0100
> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:
>
>> On Wed, 23 Dec 2009 00:00:31 +0100
>> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:
>>
>> > 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:
>>
>> 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?

don't forget - plpgsql is interpret - it is best as glue for SQL
statement. I don't thing so plpgsql is slow - speed is similar to
using buildin functionality. But I am sure, rewritening your function
to C could help. If you need maximal speed.

I thing, so there are other trick, I am not sure if it is faster. You
can create own aggregate. In state function you can calculate and
check state value. If it is over your limit, then you can raise
exception. So if your query will be finished with custom exception,
then sum(c) > n is true.

Regards
Pavel Stehule

>
> Fortunately as expected when "enough" rows are >1 the for loop
> solution perform much better.
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>