"Simon Riggs" <simon@2ndquadrant.com> writes:
> I've just read a paper that says PostgreSQL doesn't do this. My reading
> of the code is that we *do* evaluate the HAVING clause prior to
> calculating the aggregates for it. I thought I'd check to resolve the
> confusion.
>
> - - -
>
> If not, it seems fairly straightforward to push down some or all of a
> HAVING clause so that the qual clause is tested prior to aggregation,
> not after aggregation. This could, for certain queries, significantly
> reduce the amount of effort that the final Agg node performs.
You mean in cases like this?
postgres=# explain select count(*) from customer group by c_w_id,c_d_id,c_id having c_w_id = 1 and c_d_id=1 and
c_id=1; QUERY PLAN
------------------------------------------------------------------------------------GroupAggregate (cost=0.00..13.61
rows=1width=12) -> Index Scan using pk_customer on customer (cost=0.00..13.56 rows=4 width=12) Index Cond:
((c_w_id= 1) AND (c_d_id = 1) AND (c_id = 1))
(3 rows)
I think we push having clauses into WHERE clauses whenever there are no
aggregates in them.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com