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 <dharam.thac...@jpmorgan.com> 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:* user@geode.apache.org > *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. >
