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 [cid:[email protected]] [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<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.
