Re: HAVING push-down

"Simon Riggs" <simon(at)2ndquadrant(dot)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=1 width=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