Hey Thomas, Can you post the plan/query that you were expecting after the rewriting? Is the behavior you observed specific to timestamps?
Best, Stamatis On Thu, Jan 27, 2022 at 4:07 AM Thomas D'Silva <[email protected]> wrote: > 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 <[email protected]> > wrote: > > > Have you experimented with flooring the query predicate? > > > > -Justin > > > > On Wed, Jan 26, 2022 at 10:54 AM Thomas D'Silva > > <[email protected]> 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 > > >
