You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@geode.apache.org by "Thacker, Dharam" <dh...@jpmorgan.com> on 2018/11/16 13:06:04 UTC
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:image001.png@01D47DDA.720A30C0]
[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.
Re: Incosistent behaviour with functional indexes
Posted by Jason Huynh <jh...@pivotal.io>.
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 <ag...@pivotal.io>
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 <
> dharam.thacker@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.
>>
>
Re: Incosistent behaviour with functional indexes
Posted by Anilkumar Gingade <ag...@pivotal.io>.
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 <dh...@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.
>
RE: Incosistent behaviour with functional indexes
Posted by "Thacker, Dharam" <dh...@jpmorgan.com>.
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
[cid:image001.png@01D47DDC.FA548FF0]
[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.