The query tracing might have some bugs around it, I would guess this is the case if you are getting the expected/correct results from the query itself..
The second case seems like a good fit for the join optimizations we made a while back. I am not sure if the toString() is causing the optimization not to take effect or not. We had only tested for leaf nodes being of an identifier/field type and not necessarily a method invocation. Maybe try using c.dealRefId = i.dealRefId and see if that helps? On Fri, Nov 16, 2018 at 10:16 AM Anilkumar Gingade <[email protected]> wrote: > Hi Dharam, > > Here is what i think may be happening, please note i haven't tried it on > my side. > > In Query 1, its using index "group_name_indx_1" as a input and doing index > lookup using "indexoption_dealref_1", reason why it only shows results=632 > with indexoption_dealref_1. I was expecting it to be 640 not sure why its > 632. > > In Query 2 case, its using the "indexoption_expirydt_indx" and ignoring > the others; with "AND" condition, in certain (or all) cases, query uses > only one index as it is found to be faster. > Have you tried using hint in this case? If its not working there could be > some issue with hint; please hint is just a suggestion to query engine. > > Can you try changing the order of the condition to, > where c.dealRefId.toString() = i.dealRefId.toString() and i.expiryDate = > '2018-11-21' > With and without hint. > > -Anil. > > > > > > > On Fri, Nov 16, 2018 at 5:19 AM Thacker, Dharam < > [email protected]> wrote: > >> Missed to clarify few things, >> >> >> >> 1. I am using GEODE VERSION 1.6.0 >> >> 2. expiryDate in below query is also a ISO string only >> (YYYY-MM-DD) format >> >> >> >> Thanks, >> >> Dharam >> >> >> >> *From:* Thacker, Dharam >> *Sent:* Friday, November 16, 2018 6:36 PM >> *To:* [email protected] >> *Subject:* Incosistent behaviour with functional indexes >> >> >> >> Hi Team, >> >> >> >> I am seeing inconsistent behavior in usage of indexes in GEODE OQL >> queries. >> >> >> >> Could someone help me to validate if there is any known issue/new issue? >> >> >> >> gfsh>list indexes >> >> >> >> >> >> [Here I see 2 indexes being used correctly but I am not sure why >> group_name_indx_1 has 0 results] >> >> >> >> [info 2018/11/16 18:05:44.092 IST StarfishServer <Function Execution >> Processor1> tid=0x5b] Query Executed in 26.6535 ms; rowCount = 640; >> indexesUsed(2):group_name_indx_1(Results: >> 0),indexoption_dealref_1(Results: 632) "select c as >> assignment,i.cptySpn,i.cptyName,i.bookName from /GroupAssignment >> c,/IndexOption i where c.dealRefId.toString() = i.dealRefId.toString() >> LIMIT 1000" >> >> >> >> >> >> [Here it takes > 3 seconds even though I have index on expiryDate and >> dealRef attribute in both regions] >> >> >> >> [info 2018/11/16 18:07:14.632 IST StarfishServer <Function Execution >> Processor1> tid=0x5b] Query Executed in 3840.7922 ms; rowCount = 640; >> indexesUsed(1):indexoption_expirydt_indx(Results: >> 640) "select c as >> assignment,i.cptySpn,i.cptyName,i.bookName,i.expiryDate from >> /GroupAssignment c,/IndexOption i where i.expiryDate = '2018-11-21' and >> c.dealRefId.toString() = i.dealRefId.toString() LIMIT 1000" >> >> >> >> [Even though I give hints to query, it’s not using it well. I even tried >> to tweak query but does not look good yet] >> >> >> >> [info 2018/11/16 18:17:44.236 IST StarfishServer <Function Execution >> Processor1> tid=0x5b] Query Executed in 325.6136 ms; rowCount = 229; >> indexesUsed(1):indexoption_region_indx(Results: 229) "<HINT >> 'indexoption_dealref_indx', >> 'indexoption_expirydt_indx','indexoption_cptySpn_indx','ga_dealref_indx'>select >> c as assignment, r.bookName from /GroupAssignment c,(select >> i.dealRefId,i.bookName,i.cptySpn,i.cptyName from /IndexOption i where >> i.expiryDate = '2018-11-21' and i.agentCity = 'NA') r where c.dealRefId = >> r.dealRefId" >> >> >> >> >> >> Thanks, >> >> Dharam >> >> This message is confidential and subject to terms at: https:// >> www.jpmorgan.com/emaildisclaimer including on confidentiality, legal >> privilege, viruses and monitoring of electronic messages. If you are not >> the intended recipient, please delete this message and notify the sender >> immediately. Any unauthorized use is strictly prohibited. >> >> This message is confidential and subject to terms at: https:// >> www.jpmorgan.com/emaildisclaimer including on confidentiality, legal >> privilege, viruses and monitoring of electronic messages. If you are not >> the intended recipient, please delete this message and notify the sender >> immediately. Any unauthorized use is strictly prohibited. >> >
