Justin, Thanks for your response. If I change the query predicate to "floor(ts to minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts to minute) < timestamp'2018-01-01 00:05:00'" it does end up querying the view. However I want to be able to query an arbitrary time range and have the union rewriting work so that the view can be used along with the table to return results. For eg. for query predicate "ts >= timestamp'2018-01-01 00:01:30' AND ts < timestamp'2018-01-01 00:05:00'", we could do a union with time range [2018-01-01 00:02:00, 2018-01-01 00:05:00) used for the view and [2018-01-01 00:01:30, 2018-01-01 00:02:00) used for the table.
Thank you, Thomas On Wed, Jan 26, 2022 at 2:25 PM Justin Swett <jsw...@google.com.invalid> wrote: > Have you experimented with flooring the query predicate? > > -Justin > > On Wed, Jan 26, 2022 at 10:54 AM Thomas D'Silva > <tdsi...@twilio.com.invalid> wrote: > > > > Hi, > > > > The existing MaterializedViewRule is not applied when the materialized > view > > does not have a view predicate but the query contains a predicate. For eg > > for the following materialized view > > > > SELECT eventid, floor(ts to minute), count(*) as cnt > > FROM events > > GROUP BY eventid, floor(ts TO minute) > > > > If we have the following query the view is not chosen in the final plan. > > > > SELECT floor(ts to minute), count(*) > > FROM events > > WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts < > timestamp'2018-01-01 > > 00:05:00' > > GROUP BY eventid, floor(ts TO minute) > > > > If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts < > > timestamp'2018-01-01 00:05:00'" to the materialized view then the > > materialized view is chosen in the final plan. Would it be possible to > > modify the MaterializedViewRule to automatically add a view predicate > based > > on the query when the materialized view being considerent aggregates > over a > > column that contains a floor()? This predicate would also need to be > > applied to the rewritten view. I would like to file a JIRA and work on > this > > improvement if this makes sense. > > > > Thank you, > > Thomas >
