Re: Plan to support predicate push-down into subqueries with aggregates?

Adam Brusselback <adambrusselback(at)gmail(dot)com> 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