Adam Brusselback <adambrusselback@xxxxxxxxx> writes: > I was wondering if there were any plans to support predicate push-down > optimization for subqueries (and views) with aggregates? Right offhand I would say that that's a complete mischaracterization of the problem. I've not tried to run your test case, but you say > --Quick, is able to push down because it's a simple equality check > SELECT * > FROM header > INNER JOIN header_total > USING (header_id) > WHERE header.header_id = 26; > > --Slow, no pushdown > SELECT * > FROM header > INNER JOIN header_total > USING (header_id) > WHERE header.header_id < 200; > > --Slow, no pushdown > SELECT * > FROM header > INNER JOIN header_total > USING (header_id) > WHERE header.description like '%5%'; There's no preference for equalities over other kinds of predicates as far as subquery pushdown is concerned. I think what your real problem is is that in the first case, the system will derive the additional condition "header_total.header_id = 26", while in the second case it will not deduce "header_total.header_id < 200". That's because the machinery for deducing such implied constraints works only with equalities. That's not very likely to change anytime soon, and even if it did, the inference would only extend to operators that are members of the same btree family as the join equality operator. Your example with a LIKE clause is always going to be out in the cold, because there is no principled basis for the planner to decide that "a = b" means that "a LIKE x" and "b LIKE x" will give the same result. It hasn't got enough information about the behavior of LIKE to know if that's safe or not. (It does, on the other hand, know very well that SQL equality operators don't necessarily guarantee bitwise identity.) So I'd suggest just modifying your queries to write out both constraints explicitly. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
