"Simon Riggs" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
