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 <t...@sss.pgh.pa.us> wrote: > Adam Brusselback <adambrusselb...@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. > > regards, tom lane >
