Hello! We had an issue with IN clause, which we thought fixed on a general case, but if you have a complex condition it may still manifest.
Does it change if you just use (product_name = 'Product1' OR product_name = 'Product2')? Regards, -- Ilya Kasnacheev ср, 10 июн. 2020 г. в 13:54, njcstreet <[email protected]>: > Hi, > > I am seeing poor performance on queries which use IN in the WHERE clause > with multiple items supplied. I have searched for issues related to this > and > I have seen replies suggesting that when you use IN, that indexes are not > used, but I guess this must have been fixed because I do see indexes being > used in the query plan (but the query is still slow). I've seen suggestions > to re-write IN as a JOIN but I've raised a similar issue where I am seeing > that Indexes aren't used on joins (unless the query is re-written in such a > way that makes it prohibitively slow). > What I am observing is that on a table with roughly 300 million entries in > it, if I use the IN clause, the query takes a few seconds, where as if I > re-write it is a UNION with each query filtering on only one item, it > returns in milliseconds. The issue is the user could be filtering on lots > of > items and on multiple attributes, so if I do this I will end up with a huge > number of unions. > > Example 1 - using IN clause, filtering on two items. Returns slowly (about > 8 > seconds). > > > SELECT product_name, location_name, > > SUM(revenue) > > FROM FactTableRevenue > > WHERE date_key = 20200604 > > AND product_name IN ('Product1', 'Product2') > > GROUP BY product_name, location_name > > Query plan: > SELECT > > __Z0.PRODUCT_NAME AS __C0_0, > > __Z0.LOCATION_NAME AS __C0_1, > > SUM(__Z0.REVENUE) AS __C0_2 > > FROM PUBLIC.FACTTABLEREVENUE __Z0 > > /* PUBLIC.IX_REVENUE_DATE_PRODUCT_LOCATION: PRODUCT_NAME IN('Product1', > 'Product2') > > AND DATE_KEY = 20200604 > > */ > > WHERE (__Z0.PRODUCT_NAME IN('Product1', 'Product2')) > > AND (__Z0.DATE_KEY = 20200604) > > GROUP BY __Z0.PRODUCT_NAME, __Z0.LOCATION_NAME > > > Example 2 - Re-written as a union. Returns very quickly (50 ms) > > SELECT product_name, location_name, > > SUM(revenue) > > FROM FactTableRevenue > > WHERE date_key = 20200604 > > AND product_name IN ('Product1') > > GROUP BY product_name, location_name > > > > UNION ALL > > > > SELECT product_name, location_name, > > SUM(revenue) > > FROM FactTableRevenue > > WHERE date_key = 20200604 > > AND product_name IN ('Product2') > > GROUP BY product_name, location_name > > > > SELECT > > __Z0.PRODUCT_NAME AS __C0_0, > > __Z0.LOCATION_NAME AS __C0_1, > > SUM(__Z0.REVENUE) AS __C0_2 > > FROM PUBLIC.FACTTABLEREVENUE __Z0 > > /* PUBLIC.IX_REVENUE_DATE_PRODUCT_LOCATION: DATE_KEY = 20200604 > > AND PRODUCT_NAME = 'Product1' > > */ > > WHERE (__Z0.DATE_KEY = 20200604) > > AND (__Z0.PRODUCT_NAME = 'Product1') > > GROUP BY __Z0.PRODUCT_NAME, __Z0.LOCATION_NAME > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
