zbtzbtzbt opened a new issue #6499:
URL: https://github.com/apache/incubator-doris/issues/6499
Here is a simplified SQL in the meituan，data is over than 600w rows：
`````
select
shop_id,sum(view_shop_pv) as view_shop_pv
from rpt_ad_shop_view_overall_rank
where partition_date between '2020-08-12' and '2021-08-12'
and (-2 = 2 or city_id = 2)
and (-2 = 14 or district_id = 14)
and (-2 = 23006 or region_id = 23006)
and (-2 = 35 or cat0_id = 35)
and (-2 = 50025 or cat1_id = 50025)
and (-2 = 3 or star_range = 3)
group by shop_id
order by view_shop_pv desc
limit 100
`````
This SQL will exceed limit time(over 20s)，we find ‘-2=14’ is obvious
false，But if you rewrite the SQL as：
`````
select
shop_id,sum(view_shop_pv) as view_shop_pv
from rpt_ad_shop_view_overall_rank
where partition_date between '2020-08-12' and '2021-08-12'
and (city_id = 2)
and (district_id = 14)
and (region_id = 23006)
and (cat0_id = 35)
and (cat1_id = 50025)
and (star_range = 3)
group by shop_id
order by view_shop_pv desc
limit 100
`````
it only costs 2s.
we explain a test SQL and find：
`````
explain select * from table1 where (2=-2) OR (citycode=0 AND 1=1);
#PREDICATES: (FALSE OR (`citycode` = 0 AND TRUE))
`````
compoundPredicate ‘OR’ will cause the invalidation of the prefix index，which
will lead to the exceed time of SQL.
so i am trying to rewrite the compoundpredicate rule,
after optimized rewrite rule,prefix index will work again.
`````
explain select * from table1 where (2=-2) OR (citycode=0 AND 1=1);
#PREDICATES: `citycode` = 0
`````
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
