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

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.

Thanks,
-Adam

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

> 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
>