From: Pavel Stehule <pavel.stehule@gmail.com>
To: Ivan Sergio Borgonovo <mail@webthatworks.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: short-cutting if sum()>constant
Date: Wed, 23 Dec 2009 08:38:52 +0100
Message-ID: <162867790912222338v73261b73u5c4e88a5ce85918f@mail.gmail.com> (raw)
In-Reply-To: <20091223024635.48d66554@dawn.webthatworks.it>
References: <20091222133527.3007bd96@dawn.webthatworks.it>
<162867790912221147r7e3041fej44332a05faf915d6@mail.gmail.com>
<20091223000031.54ad09b5@dawn.webthatworks.it>
<20091223010940.51c5c069@dawn.webthatworks.it>
<20091223024635.48d66554@dawn.webthatworks.it>
2009/12/23 Ivan Sergio Borgonovo <mail@webthatworks.it>:
> 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?
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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, mail@webthatworks.it
Subject: Re: short-cutting if sum()>constant
In-Reply-To: <162867790912222338v73261b73u5c4e88a5ce85918f@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox