Hi list,
As discussed on the pgsql-general list, the bool_and() and bool_or()
aggregate functions behave exactly like min() and max() would over
booleans. While it's not likely that people would have an appropriate
index on a boolean column, it seems it wouldn't cost us anything to
take advantage of this optimization, as it requires no code changes at
all, simply value changes in the pg_aggregate catalog.
Before:
db=# explain analyze select bool_and(b) from bools;Aggregate (cost=1693.01..1693.02 rows=1 width=1) -> Seq Scan on
bools (cost=0.00..1443.01 rows=100001 width=1)Total runtime: 29.736 ms
After:
db=# explain analyze select bool_and(b) from bools;Result (cost=0.03..0.04 rows=1 width=0) InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.03 rows=1 width=1) -> Index Scan using bools_b_idx on bools
(cost=0.00..3300.28 rows=100001 width=1) Index Cond: (b IS NOT NULL)Total runtime: 0.109 ms
Original discussion here:
http://archives.postgresql.org/message-id/CABRT9RAGwQEP+EFhVpZ6=B4cJEcUE2-QCpb_ZdrNPgQNa8xKuA@mail.gmail.com
PS: It seems that the min/max optimization isn't documented in the
manual (apart from release notes), so I didn't include any doc changes
in this patch.
Regards,
Marti