Hi all.
I was wondering if there were any plans to support predicate push-down optimization for subqueries (and views) with aggregates?
I was recently bit by this, as I had incorrectly assumed that this was an optimization that was in place, and designed quite a bit around that assumption, only to get hit with terrible performance when more data got loaded into the system.
Currently I had to solve the issue by having aggregate tables which store the data, which is maintained by triggers on IUD. This gets messy quick, as I have some aggregates which are dependent on 5-6 other tables. I'd love to be able to just store the logic for calculating aggregates in a view, and use that at query time instead of having to deal with it like this.
I have written a test case script that explains the type of queries I am talking about, and the issues. It's heavily simplified compared to a real system, but it'll do. Please see attached.
Thanks,
-Adam