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

​

On Tue, Mar 8, 2016 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Adam Brusselback <adambrusselback@gmail.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.

​

​

Thanks Tom, appreciate the reply.

Sorry if I didn't call it the correct thing. I just know that with trying to encapsulate this aggregate logic in a view, I am unable to use that view in a query that I know is only going to touch a subset of the data without incurring a performance hit from the view doing seq scans on all of the rows in the detail_1 and detail_2 tables, and then throwing out 99% of the results when the filter is applied.

I had initially started creating functions that would take an array of ids as a parameter, and manually push them down in the subqueries.  That got really really messy though, and we moved away from doing that to having the aggregates eagerly materialized to a table with triggers.

Are there any other options for making this type of query faster?  It could be that I just am totally missing a better way to do this.  I do really want to be able to contain that logic within a view of some sort though, as a bunch of other stuff is built on top of that.  Having to push that aggregate query into all of those other queries would be hell.