Hi. I think the problem is that age and month have bad selectivity. For example, in the case of month, each month index will select in average 2000000 / 12 ~ 1666667 rows. In this case, it's probably faster scan all the rows in storage/disk order then using the index order.
Kind regards, Alex. On Wed, May 10, 2017 at 6:29 PM, Guillermo Ortiz [via Apache Ignite Users] < [email protected]> wrote: > Are indices used always that it'spossible or it depends how many records > data you have to read as Oracle? So, if You use and index but the result > it's to read 25% of the data, usually indices are not used because > sequencial read are faster. As this is in memory I understand that it > doesn't apply but I'm not sure and I didn't read anything about it in the > documentation. > > 2017-05-10 17:27 GMT+02:00 Guillermo Ortiz <[hidden email] > <http:///user/SendEmail.jtp?type=node&node=12599&i=0>>: > >> If I execute many times, time is similar in both. >> >> 2017-05-10 17:25 GMT+02:00 Guillermo Ortiz <[hidden email] >> <http:///user/SendEmail.jtp?type=node&node=12599&i=1>>: >> >>> I'm using 2.0.0. >>> Right now in my laptop with 2Mill objects. >>> >>> >>> *WITH INDICES* >>> ******Result SELECT distinct(MONTH) FROM PERSONWITHINDEX >>> September,February,March,August,June,April,July,January,May, >>> November,October, >>> Elapsed time SELECT distinct(MONTH) FROM PERSONWITHINDEX:1990ms >>> [SELECT DISTINCT >>> __Z0.MONTH AS __C0_0 >>> FROM "PersonWithIndex".PERSONWITHINDEX __Z0 >>> /* "PersonWithIndex".PERSONWITHINDEX_MONTH_IDX */] >>> [SELECT DISTINCT >>> __C0_0 AS MONTH >>> FROM PUBLIC.__T0 >>> /* "PersonWithIndex"."merge_scan" */] >>> >>> >>> *WITHOUT INDICES* >>> ******Result SELECT distinct(MONTH) FROM PERSONWITHOUTINDEX >>> September,November,February,March,May,April,July,August,Janu >>> ary,June,October, >>> Elapsed time SELECT distinct(MONTH) FROM PERSONWITHOUTINDEX:1690ms >>> [SELECT DISTINCT >>> __Z0.MONTH AS __C0_0 >>> FROM "PersonWithoutIndex".PERSONWITHOUTINDEX __Z0 >>> /* "PersonWithoutIndex".PERSONWITHOUTINDEX.__SCAN_ */] >>> [SELECT DISTINCT >>> __C0_0 AS MONTH >>> FROM PUBLIC.__T0 >>> /* "PersonWithoutIndex"."merge_scan" */] >>> *************************** >>> >>> public class PersonWithIndex implements Serializable { >>> @QuerySqlField(index = true) >>> public int id; >>> @QuerySqlField(index = true, orderedGroups = >>> {@QuerySqlField.Group(name="index_group_month_age", order=1)}) >>> public int age; >>> @QuerySqlField >>> public String dni; >>> @QuerySqlField(index = true, orderedGroups = >>> {@QuerySqlField.Group(name="index_group_month_age", order=0)}) >>> public String month; >>> >>> >>> 2017-05-10 17:06 GMT+02:00 afedotov <[hidden email] >>> <http:///user/SendEmail.jtp?type=node&node=12599&i=2>>: >>> >>>> Hi, >>>> >>>> Which Ignite version do you use? >>>> How many Ignite nodes do you run? >>>> What is your cache configuration? >>>> >>>> As well, could you please try the following query and see the >>>> performance? >>>> SELECT DISTINCT age FROM PersonWithindex; >>>> >>>> >>>> Kind regards, >>>> Alex. >>>> >>>> On Wed, May 10, 2017 at 5:57 PM, Guillermo Ortiz [via Apache Ignite >>>> Users] <[hidden email] >>>> <http:///user/SendEmail.jtp?type=node&node=12593&i=0>> wrote: >>>> >>>>> It's my laptop. 16gb, i7.. The collection size it's 5Mill objects >>>>> (1,2GB) and it takes about 30sec. >>>>> I have tried to execute the query with the same collection with and >>>>> without indices with the same time results. >>>>> >>>>> This is a test before to run in the real cluster with 6 nodes of 512Gb >>>>> and 48cores each one. We tried to execute there with a real dataset(22M >>>>> rows, 7gb) but it takes so long that query is aborted. >>>>> >>>>> >>>>> 2017-05-08 12:12 GMT+02:00 Andrey Mashenkov <[hidden email] >>>>> <http:///user/SendEmail.jtp?type=node&node=12592&i=0>>: >>>>> >>>>>> Hi, >>>>>> >>>>>> Why do you think query is slow? >>>>>> What its execution time? What is expected time? >>>>>> How many nodes do you use? How much data does cache contains? >>>>>> >>>>>> On Mon, May 8, 2017 at 1:47 AM, Guillermo Ortiz <[hidden email] >>>>>> <http:///user/SendEmail.jtp?type=node&node=12592&i=1>> wrote: >>>>>> >>>>>>> I have a simple cache where I have Person object (id, age and so >>>>>>> on..) >>>>>>> >>>>>>> I have created indeces by id and age to try some examples but >>>>>>> queries with "group by" go really slow. >>>>>>> >>>>>>> I'm trying this query: >>>>>>> SELECT age >>>>>>> FROM >>>>>>> PersonWithindex >>>>>>> group by age >>>>>>> >>>>>>> >>>>>>> SELECT >>>>>>> AGE AS __C0 >>>>>>> FROM "personCacheWithIndex".PERSONWITHINDEX >>>>>>> /* "personCacheWithIndex"."age_idx" */ >>>>>>> GROUP BY AGE >>>>>>> /* group sorted */ >>>>>>> >>>>>>> SELECT >>>>>>> __C0 AS AGE >>>>>>> FROM PUBLIC.__T0 >>>>>>> /* "personCacheWithIndex"."merge_scan" */ >>>>>>> GROUP BY __C0 >>>>>>> >>>>>>> Although it seems that it uses index, why is it going so slow? I >>>>>>> think that it should be pretty fast with an index. >>>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Best regards, >>>>>> Andrey V. Mashenkov >>>>>> >>>>> >>>>> >>>>> >>>>> ------------------------------ >>>>> If you reply to this email, your message will be added to the >>>>> discussion below: >>>>> http://apache-ignite-users.70518.x6.nabble.com/GroupBy-with- >>>>> index-is-really-slow-tp12526p12592.html >>>>> To start a new topic under Apache Ignite Users, email [hidden email] >>>>> <http:///user/SendEmail.jtp?type=node&node=12593&i=1> >>>>> To unsubscribe from Apache Ignite Users, click here. >>>>> NAML >>>>> <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> >>>>> >>>> >>>> >>>> ------------------------------ >>>> View this message in context: Re: GroupBy with index is really slow. >>>> <http://apache-ignite-users.70518.x6.nabble.com/GroupBy-with-index-is-really-slow-tp12526p12593.html> >>>> Sent from the Apache Ignite Users mailing list archive >>>> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com. >>>> >>> >>> >> > > > ------------------------------ > If you reply to this email, your message will be added to the discussion > below: > http://apache-ignite-users.70518.x6.nabble.com/GroupBy- > with-index-is-really-slow-tp12526p12599.html > To start a new topic under Apache Ignite Users, email > [email protected] > To unsubscribe from Apache Ignite Users, click here > <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=1&code=YWxleGFuZGVyLmZlZG90b2ZmQGdtYWlsLmNvbXwxfC0xMzYxNTU0NTg=> > . > NAML > <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > -- View this message in context: http://apache-ignite-users.70518.x6.nabble.com/GroupBy-with-index-is-really-slow-tp12526p12602.html Sent from the Apache Ignite Users mailing list archive at Nabble.com.
