Hi Andrey, This query remains stuck "EXPLAIN SELECT store.entryID,store.attrName,store.attrValue, store.attrsType FROM \"dn\".Ignite_DN dn, \"Ignite_DSAttributeStore\".Ignite_DSAttributeStore store WHERE dn.entryID in (" +"(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectClass at1 WHERE at1.attrValue = 'subentry' )" +"UNION " +"(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectClass at1 WHERE at1.attrValue = 'ldapsubentry' )" +")" +" AND ( dn.parentDN like 'dc=ignite,%')" +"AND dn.entryID = store.entryID AND store.attrKind IN ('u','o') order by store.entryID";
The corresponding explain plan is [[SELECT STORE__Z1.ENTRYID AS __C0_0, STORE__Z1.ATTRNAME AS __C0_1, STORE__Z1.ATTRVALUE AS __C0_2, STORE__Z1.ATTRSTYPE AS __C0_3 FROM "dn".IGNITE_DN DN__Z0 /* "dn".RP_DN_IDX: PARENTDN >= 'dc=ignite,' AND PARENTDN < 'dc=ignite-' */ /* WHERE (DN__Z0.PARENTDN LIKE 'dc=ignite,%') AND (DN__Z0.ENTRYID IN( (SELECT AT1__Z2.ENTRYID FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = 'subentry' ++/ WHERE AT1__Z2.ATTRVALUE = 'subentry') UNION (SELECT AT1__Z3.ENTRYID FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = 'ldapsubentry' ++/ WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))) */ INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE STORE__Z1 /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: ENTRYID = DN__Z0.ENTRYID */ ON 1=1 WHERE (STORE__Z1.ATTRKIND IN('u', 'o')) AND ((DN__Z0.ENTRYID = STORE__Z1.ENTRYID) AND ((DN__Z0.PARENTDN LIKE 'dc=ignite,%') AND (DN__Z0.ENTRYID IN( (SELECT AT1__Z2.ENTRYID FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = 'subentry' */ WHERE AT1__Z2.ATTRVALUE = 'subentry') UNION (SELECT AT1__Z3.ENTRYID FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = 'ldapsubentry' */ WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))))) ORDER BY 1], [SELECT __C0_0 AS ENTRYID, __C0_1 AS ATTRNAME, __C0_2 AS ATTRVALUE, __C0_3 AS ATTRSTYPE FROM PUBLIC.__T0 /* "dn"."merge_sorted" */ ORDER BY 1 /* index sorted */]] Note that the subquery has no record +"(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectClass at1 WHERE at1.attrValue = 'subentry' )" +"UNION " +"(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectClass at1 WHERE at1.attrValue = 'ldapsubentry' )" Any pointers on this ? -Rajesh On Fri, Feb 2, 2018 at 10:26 PM, Rajesh Kishore <rajesh10si...@gmail.com> wrote: > Hey Andrey, > > Now , I am getting the result within 3 mins, need to analyze why its > slower , probably I have to brushup my sql and indexing skills > this is my explain plan for new query > > > [[SELECT > ST__Z0.ENTRYID AS __C0_0, > ST__Z0.ATTRNAME AS __C0_1, > ST__Z0.ATTRVALUE AS __C0_2, > ST__Z0.ATTRSTYPE AS __C0_3 > FROM "dn".IGNITE_DN DN__Z1 > /* "dn".RP_DN_IDX: PARENTDN >= 'dc=ignite,' > AND PARENTDN < 'dc=ignite-' > */ > /* WHERE DN__Z1.PARENTDN LIKE 'dc=ignite,%' > */ > INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST__Z0 > /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: > ENTRYID = DN__Z1.ENTRYID */ > ON 1=1 > /* WHERE (ST__Z0.ATTRKIND IN('u', 'o')) > AND (DN__Z1.ENTRYID = ST__Z0.ENTRYID) > */ > INNER JOIN "objectclass".IGNITE_OBJECTCLASS AT1__Z2 > /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = 'person' > AND ENTRYID = DN__Z1.ENTRYID > */ > ON 1=1 > WHERE ((ST__Z0.ATTRKIND IN('u', 'o')) > AND ((AT1__Z2.ATTRVALUE = 'person') > AND (DN__Z1.PARENTDN LIKE 'dc=ignite,%'))) > AND ((DN__Z1.ENTRYID = AT1__Z2.ENTRYID) > AND (DN__Z1.ENTRYID = ST__Z0.ENTRYID)) > ORDER BY 1], [SELECT > __C0_0 AS ENTRYID, > __C0_1 AS ATTRNAME, > __C0_2 AS ATTRVALUE, > __C0_3 AS ATTRSTYPE > FROM PUBLIC.__T0 > /* "dn"."merge_sorted" */ > ORDER BY 1 > /* index sorted */]] > > -Rajesh > > On Fri, Feb 2, 2018 at 9:38 PM, Andrey Mashenkov < > andrey.mashen...@gmail.com> wrote: > >> Rajesh, >> >> How much entries returns by subquery inside IN clause? >> You can try to reduce it with replacing condition like "X.ID in (Select >> T.ID From T ...)" with " (Select ID From T Where T.ID= X.ID ... Limit 1) >> == ID". >> >> On Fri, Feb 2, 2018 at 6:57 PM, Andrey Mashenkov < >> andrey.mashen...@gmail.com> wrote: >> >>> Hi Rajesh, >>> >>> I've also suggested you to replace IN with JOIN in one of prev. >>> messages. Seems, it was overlooked. >>> Would you please try this as well? >>> >>> On Fri, Feb 2, 2018 at 6:52 PM, Rajesh Kishore <rajesh10si...@gmail.com> >>> wrote: >>> >>>> Hi Andrey, >>>> >>>> Yes , I also came to know about OR but the query is still unresponsive >>>> when I removed the OR >>>> >>>> done [[SELECT >>>> STORE__Z1.ENTRYID AS __C0_0, >>>> STORE__Z1.ATTRNAME AS __C0_1, >>>> STORE__Z1.ATTRVALUE AS __C0_2, >>>> STORE__Z1.ATTRSTYPE AS __C0_3 >>>> FROM "dn".IGNITE_DN DN__Z0 >>>> /* "dn".RP_DN_IDX: PARENTDN >= 'dc=ignite,' >>>> AND PARENTDN < 'dc=ignite-' >>>> */ >>>> /* WHERE (DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>> AND (DN__Z0.ENTRYID IN( >>>> (SELECT >>>> AT1__Z2.ENTRYID >>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 >>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE >>>> = 'subentry' ++/ >>>> WHERE AT1__Z2.ATTRVALUE = 'subentry') >>>> UNION >>>> (SELECT >>>> AT1__Z3.ENTRYID >>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 >>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE >>>> = 'ldapsubentry' ++/ >>>> WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))) >>>> */ >>>> INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE STORE__Z1 >>>> /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: >>>> ENTRYID = DN__Z0.ENTRYID */ >>>> ON 1=1 >>>> WHERE (STORE__Z1.ATTRKIND IN('u', 'o')) >>>> AND ((DN__Z0.ENTRYID = STORE__Z1.ENTRYID) >>>> AND ((DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>> AND (DN__Z0.ENTRYID IN( >>>> (SELECT >>>> AT1__Z2.ENTRYID >>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 >>>> /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = >>>> 'subentry' */ >>>> WHERE AT1__Z2.ATTRVALUE = 'subentry') >>>> UNION >>>> (SELECT >>>> AT1__Z3.ENTRYID >>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 >>>> /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = >>>> 'ldapsubentry' */ >>>> WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))))) >>>> ORDER BY 1], [SELECT >>>> __C0_0 AS ENTRYID, >>>> __C0_1 AS ATTRNAME, >>>> __C0_2 AS ATTRVALUE, >>>> __C0_3 AS ATTRSTYPE >>>> FROM PUBLIC.__T0 >>>> /* "dn"."merge_sorted" */ >>>> ORDER BY 1 >>>> /* index sorted */]] >>>> >>>> >>>> Pls advise >>>> >>>> thanks >>>> Rajesh >>>> >>>> On Fri, Feb 2, 2018 at 8:39 PM, Andrey Mashenkov < >>>> andrey.mashen...@gmail.com> wrote: >>>> >>>>> Hi Rajesh, >>>>> >>>>> >>FROM "dn".IGNITE_DN DN__Z0 >>>>> >> /* "dn".IGNITE_DN.__SCAN_ */ >>>>> >> /* WHERE ((DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>>> >> OR ((DN__Z0.RDN = 'dc=ignite') >>>>> >>>>> Most probably a table full scan is a reason. >>>>> Underlying H2 can't use indices when 'OR' condition is used. >>>>> >>>>> Try to replace OR with UNION ALL. >>>>> >>>>> >>>>> >>>>> >>>>> On Fri, Feb 2, 2018 at 10:33 AM, Rajesh Kishore < >>>>> rajesh10si...@gmail.com> wrote: >>>>> >>>>>> Thanks Dmitriy, >>>>>> >>>>>> The EXPLAIN PLAN >>>>>> >>>>>> [[SELECT >>>>>> STORE__Z1.ENTRYID AS __C0_0, >>>>>> STORE__Z1.ATTRNAME AS __C0_1, >>>>>> STORE__Z1.ATTRVALUE AS __C0_2, >>>>>> STORE__Z1.ATTRSTYPE AS __C0_3 >>>>>> FROM "dn".IGNITE_DN DN__Z0 >>>>>> /* "dn".IGNITE_DN.__SCAN_ */ >>>>>> /* WHERE ((DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>>>> OR ((DN__Z0.RDN = 'dc=ignite') >>>>>> AND (DN__Z0.PARENTDN = ','))) >>>>>> AND (DN__Z0.ENTRYID IN( >>>>>> (SELECT >>>>>> AT1__Z2.ENTRYID >>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 >>>>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: >>>>>> ATTRVALUE = 'subentry' ++/ >>>>>> WHERE AT1__Z2.ATTRVALUE = 'subentry') >>>>>> UNION >>>>>> (SELECT >>>>>> AT1__Z3.ENTRYID >>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 >>>>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: >>>>>> ATTRVALUE = 'ldapsubentry' ++/ >>>>>> WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))) >>>>>> */ >>>>>> INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE >>>>>> STORE__Z1 >>>>>> /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: >>>>>> ENTRYID = DN__Z0.ENTRYID */ >>>>>> ON 1=1 >>>>>> WHERE (STORE__Z1.ATTRKIND IN('u', 'o')) >>>>>> AND ((DN__Z0.ENTRYID = STORE__Z1.ENTRYID) >>>>>> AND (((DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>>>> OR ((DN__Z0.RDN = 'dc=ignite') >>>>>> AND (DN__Z0.PARENTDN = ','))) >>>>>> AND (DN__Z0.ENTRYID IN( >>>>>> (SELECT >>>>>> AT1__Z2.ENTRYID >>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 >>>>>> /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = >>>>>> 'subentry' */ >>>>>> WHERE AT1__Z2.ATTRVALUE = 'subentry') >>>>>> UNION >>>>>> (SELECT >>>>>> AT1__Z3.ENTRYID >>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 >>>>>> /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = >>>>>> 'ldapsubentry' */ >>>>>> WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))))) >>>>>> ORDER BY 1], [SELECT >>>>>> __C0_0 AS ENTRYID, >>>>>> __C0_1 AS ATTRNAME, >>>>>> __C0_2 AS ATTRVALUE, >>>>>> __C0_3 AS ATTRSTYPE >>>>>> FROM PUBLIC.__T0 >>>>>> /* "Ignite_DSAttributeStore"."merge_sorted" */ >>>>>> ORDER BY 1 >>>>>> /* index sorted */]] >>>>>> >>>>>> >>>>>> Thanks >>>>>> -Rajesh >>>>>> >>>>>> On Fri, Feb 2, 2018 at 5:32 AM, Dmitriy Setrakyan < >>>>>> dsetrak...@apache.org> wrote: >>>>>> >>>>>>> Rajesh, can you please show your query here together with execution >>>>>>> plan? >>>>>>> >>>>>>> D. >>>>>>> >>>>>>> >>>>>>> On Thu, Feb 1, 2018 at 8:36 AM, Rajesh Kishore < >>>>>>> rajesh10si...@gmail.com> wrote: >>>>>>> >>>>>>>> Hi Andrey >>>>>>>> Thanks for your response. >>>>>>>> I am using native ignite persistence, saving data locally and as of >>>>>>>> now I don't have distributed cache, having only one node. >>>>>>>> >>>>>>>> By looking at the doc, it does not look like affinity key is >>>>>>>> applicable here. >>>>>>>> >>>>>>>> Pls suggest. >>>>>>>> >>>>>>>> Thanks Rajesh >>>>>>>> >>>>>>>> On 1 Feb 2018 6:27 p.m., "Andrey Mashenkov" < >>>>>>>> andrey.mashen...@gmail.com> wrote: >>>>>>>> >>>>>>>>> Hi Rajesh, >>>>>>>>> >>>>>>>>> >>>>>>>>> Possibly, you data is not collocated and subquery return less >>>>>>>>> retults as it executes locally. >>>>>>>>> Try to rewrite IN into JOIN and check if query with >>>>>>>>> query#setDistributedJoins(true) will return expected result. >>>>>>>>> >>>>>>>>> It is recommended >>>>>>>>> 1. replace IN with JOIN due to performance issues [1]. >>>>>>>>> 2. use data collocation [2] if possible rather than turning on >>>>>>>>> distributed joins. >>>>>>>>> >>>>>>>>> [1] https://apacheignite-sql.readme.io/docs/performance-and- >>>>>>>>> debugging#section-sql-performance-and-usability-considerations >>>>>>>>> [2] https://apacheignite.readme.io/docs/affinity-collocation >>>>>>>>> #section-collocate-data-with-data >>>>>>>>> >>>>>>>>> On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore < >>>>>>>>> rajesh10si...@gmail.com> wrote: >>>>>>>>> >>>>>>>>>> Hi All, >>>>>>>>>> >>>>>>>>>> As of now, we have less than 1 M records , and attribute split >>>>>>>>>> into few(3) tables >>>>>>>>>> with index created. >>>>>>>>>> We are using combination of join & IN clause(sub query) in the >>>>>>>>>> SQL query , for some reason this query does not return any response. >>>>>>>>>> But, the moment we remove the IN clause and use just the join, >>>>>>>>>> the query returns the result. >>>>>>>>>> Note that as per EXPLAIN PLAN , the sub query also seems to be >>>>>>>>>> using the defined >>>>>>>>>> indexes. >>>>>>>>>> >>>>>>>>>> What are the recommendations for using such queries , are there >>>>>>>>>> any guidelines, What we are doing wrong here? >>>>>>>>>> >>>>>>>>>> Thanks, >>>>>>>>>> Rajesh >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> Best regards, >>>>>>>>> Andrey V. Mashenkov >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> Best regards, >>>>> Andrey V. Mashenkov >>>>> >>>> >>>> >>> >>> >>> -- >>> Best regards, >>> Andrey V. Mashenkov >>> >> >> >> >> -- >> Best regards, >> Andrey V. Mashenkov >> > >