Re: [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Jan 19, 2014 at 5:27 PM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

>
>> >> It's probably far more worth it for the bool and/or aggregates. We
>> could just
>> >> keep track of the values aggregated and the count of values as "true"
>> and return
>> >> true if those are the same in the case of "AND", then check the true
>> count
>> >> is > 0 in the case of "OR". I'd feel more strongly to go and do that
>> if I'd
>> >> actually ever used those aggregates for anything.
>>
>> That, OTOH, would be worthwhile I think. I'll go do that, though probably
>> not today. I hope to get to it sometime tomorrow.
>>
>
> I've commited a patch to the github repo to do this.
>
> https://github.com/david-rowley/postgres/commit/121b0823753cedf33bb94f646df3176b77f28500
> but I'm not sure if we can keep it as I had to remove the sort op as I
> explained above.
>
>>
>>
I think I'm going to have to revert the patch which implements the inverse
transition function for bool_and and bool_or.
I tested on an instance of 9.3.2 and the following queries use index scans.

create table booltest (b boolean not null);
insert into booltest (b) select false from generate_series(1,20000) g(n);
insert into booltest (b) values(true);

create index booltest_b_idx ON booltest(b);
vacuum analyze booltest;

explain select bool_or(b) from booltest;
explain select bool_and(b) from booltest;

I'm guessing there is no way to have an internal state type on the
aggregate and a sort operator on the aggregate.

I wonder if it is worth creating naive inverse transition functions similar
to max()'s and min()'s inverse transition functions. I guess on average
they've got about a 50% chance of being used and likely for some work loads
it would be a win.

What's your thoughts?

Regards
David Rowley