Stamatis,
For the materialized view and query that I had mentioned in the earlier
email, I got the following final plan that used the table :
EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2])
EnumerableAggregate(group=[{0, 1}], EXPR$1=[COUNT()])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[FLAG(MINUTE)],
expr#3=[FLOOR($t1, $t2)], expr#4=[Sarg[[2018-01-01 00:01:00..2018-01-01
00:05:00)]], expr#5=[SEARCH($t1, $t4)], eventid=[$t0], $f1=[$t3],
$condition=[$t5])
EnumerableTableScan(table=[[hr, events]])
but I would expect that the view would be used with a plan that includes a
filter:
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[Sarg[[2018-01-01
00:01:00..2018-01-01 00:05:00)]], expr#4=[SEARCH($t1, $t3)], EXPR$0=[$t1],
EXPR$1=[$t2], $condition=[$t4])
EnumerableTableScan(table=[[hr, MV0]])
If I run the following query without a predicate on the ts column the view
is used as expected :
select floor(ts to minute), count(*)
from events
group by eventid, floor(ts to minute)
EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2])
EnumerableTableScan(table=[[hr, MV0]])
Thank you,
Thomas
On Thu, Jan 27, 2022 at 1:20 AM Stamatis Zampetakis <[email protected]>
wrote:
> 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
> > >
> >
>
