Query on Index fields are running slow

index

#1

Hi There,

We are doing a POC with 17M of records with just one cluster.

Here is the configuration details.
Service Memory Quotas in megabytes per server node
Data : 16000 MB
Index : 14336 MB
Search : 512 MB
Analytics : 1024 MB
Eventing : 256 MB
The overall machine has 32 GB of RAM.

Even though we created the necessary indices, Count queries on the index field is taking seconds to return the result.
If I am running with multiple indexes, its even worst.

Here is an exmaple document ;
{
“compacted”: {
“5”: 0,
“7”: 0,
“9”: 0,
“13”: 0,
“21”: 1521181126,
“25”: “00000810b599e6da52f303e3728614879fdb897bb375063ba404e974902f746b”,
“27”: 1490562003,
“31”: “642180315231845818”,
“33”: 5132932509,
“37”: “140140”,
“41”: “XXXXXX”,
“45”: 1,
“47”: “33”,
“49”: “510”,
“51”: “9258”,
“53”: “42974444”,
“55”: 1521181126,
“63”: “US”,
“67”: “201640”,
“73”: 0,
“77”: 4,
“79”: 0,
“89”: 0,
“91”: 0,
“97”: “TIER2”,
“103”: 0,
“105”: “”,
“109”: 1288846231,
“113”: “Partial”,
“119”: “20992556”,
“121”: “20749049”,
“161”: 0,
“241”: “SUBJECT”,
“261”: 0,
“273”: “20749049”,
“275”: “20992556”,
“297”: “9258”,
“299”: “SUBJECT”,
“311”: 1,
“315”: “510”,
“317”: 1521181126,
“321”: “Partial”,
“323”: “33”,
“333”: 1490562003,
“337”: “US”,
“343”: 585,
“349”: “10610”,
“-1”: “9258”,
“-2”: “42974444”,
“-3”: “510”
},
“updated_at”: “2018-03-16 09:14:44.0”
}

Here is an example query :
select count(*) from QUEST_DEV_USER_PROFILE where (compacted.21 >= 1535439600) AND (compacted.41 = ‘XXXXXX’) AND (compacted.45 = ‘0’) AND (compacted.63 = ‘US’)

We have GSI indexes created on the all the used fields in the above query. Having said that the query is returing me rsults in 5 - 10 seconds.

Not sure what is missing here. But reading about slowness on the forums, seems like the results should come in milliseconds with just 17 M of records.

Can you please help here and provide some pointers?


#2
CREATE INDEX ix11 ON  QUEST_DEV_USER_PROFILE( compacted.`63`, compacted.`45`, compacted.`41`, compacted.`21`);
SELECT  count(1) 
FROM QUEST_DEV_USER_PROFILE 
WHERE  (compacted.`21`  >= 1535439600) 
                  AND (compacted.`41`  = "XXXXXX") 
                 AND (compacted.`45`  = "0") 
                 AND (compacted.`63`  = "US");

Index Grouping And Aggregation in 5.5 EE

Create the Right Index, Get the Right Performance.


#3

Thanks. But I do have a composite index created which includes the above fields too.

CREATE INDEX ind_composite_whole ON QUEST_DEV_USER_PROFILE((compacted.35),(compacted.45),(compacted.51),(compacted.41),(compacted.37),(compacted.21),(compacted.-2),(compacted.19),(compacted.427),(compacted.287),(compacted.279),(compacted.107),(compacted.99),(compacted.63),(compacted.53))

Still the query is taking time. Also, the PLAN does not show that index “ind_composite_whole” is being used for the above query. Since the combination or the order of the fields are not always same, what else can we do here to achieve performance ?


#4

To use index leading index key must present as part of query predicate.
Once index is chosen the query predicate that has consecutive leading index key values only pushed to indexer. Rest of predicates are applied post scan.

You can read this understand more https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf