Hi ,
I have created a bucket with the name Default on couch base server. Bucket Default has 20,000 documents.
And also created index for all the attributes in Document
I have written simple select query to fetch 5000 documents. It is taking 7 to 9 seconds to fetch the data.
select * from Default where idtDefault >= 1 and idtDefault <= 5000
Can any one please help me with this issue
Can you post your index definition here.
Below are the Definitions of Default Bucket
CREATE INDEX index_idtDefault ON Default(idtDefault) USING GSI
CREATE INDEX index_BusinessName ON Default(BusinessName) USING GSI
CREATE INDEX index_address1 ON Default(address1) USING GSI
CREATE INDEX index_address2 ON Default(address2) USING GSI
CREATE INDEX index_city ON Default(city) USING GSI
CREATE INDEX index_state ON Default(state) USING GSI
CREATE INDEX index_Zip ON Default(Zip) USING GSI
There are several things to check. What version of Couchbase are you using? Can you run your query in cbq shell and post the metrics here? Finally, a couple of tuning suggestions. Use EXPLAIN to make sure you are using the expected index. And if you want to return specific fields, you can create a covering index that includes those fields.
Couchbase Version we are using is 4.1.1
cbq metrics :
“metrics”: {
“elapsedTime”: “30.6596912s”,
“executionTime”: “30.6596912s”,
“resultCount”: 5000,
“resultSize”: 2446326
}
I have created covering index including all the fields.
Definition
CREATE INDEX index_all ON Default(idtDefaultr,BusinessName,address1,address2,city,state,Zip,Lat,Long,Lat_Radians,Long_Radians) USING GSI
To use the covering index, you must list out the specific fields in your SELECT clause, instead of using SELECT *.
Can you run that and post the metrics? Also, please post the output of EXPLAIN.
you can find the query i.e executed, explanation for the query and also cbq metric. Kindly provide some inputs to resolve this.
Time taken to complete execution of the below query is 9 to 12 seconds
select idtDGVendor,BusinessName,address1,address2,city,state,Zip,Lat,Long,Lat_Radians,Long_Radians from VendorDetails USE INDEX (index_all USING GSI) WHERE idtDGVendor >= 1 and idtDGVendor <= 5000
Explain :
[
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“covers”: [
“cover((meta(VendorDetails).id))”,
“cover((VendorDetails.idtDGVendor))”,
“cover((VendorDetails.BusinessName))”,
“cover((VendorDetails.address1))”,
“cover((VendorDetails.address2))”,
“cover((VendorDetails.city))”,
“cover((VendorDetails.state))”,
“cover((VendorDetails.Zip))”,
“cover((VendorDetails.Lat))”,
“cover((VendorDetails.Long))”,
“cover((VendorDetails.Lat_Radians))”,
“cover((VendorDetails.Long_Radians))”
],
“index”: “index_all”,
“keyspace”: “VendorDetails”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“successor(5000)”
],
“Inclusion”: 1,
“Low”: [
“1”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((1 <= cover((VendorDetails.idtDGVendor))) and (cover((VendorDetails.idtDGVendor)) <= 5000))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “cover((VendorDetails.idtDGVendor))”
},
{
“expr”: “cover((VendorDetails.BusinessName))”
},
{
“expr”: “cover((VendorDetails.address1))”
},
{
“expr”: “cover((VendorDetails.address2))”
},
{
“expr”: “cover((VendorDetails.city))”
},
{
“expr”: “cover((VendorDetails.state))”
},
{
“expr”: “cover((VendorDetails.Zip))”
},
{
“expr”: “cover((VendorDetails.Lat))”
},
{
“expr”: “cover((VendorDetails.Long))”
},
{
“expr”: “cover((VendorDetails.Lat_Radians))”
},
{
“expr”: “cover((VendorDetails.Long_Radians))”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
]
cbq metrics :
“status”: “success”,
“metrics”: {
“elapsedTime”: “9.6259926s”,
“executionTime”: “9.6259926s”,
“resultCount”: 5000,
“resultSize”: 1802282
}
Can you post your new CREATE INDEX statement. Also, are the metrics based on running with your latest index?
Create Index Statement :
CREATE INDEX index_all ON VendorDetails(idtDGVendor,BusinessName,address1,address2,city,state,Zip,Lat,Long,Lat_Radians,Long_Radians) USING GSI
Yes, metrics provided to you is using the latest index. i.e the above index ‘index_all’
The Plan looks fine. Index is wide it may be taking time. What is machine configuration(CPUs,OS,memory).
Could you please how long the following query takes.
CREATE INDEX index_idtdgvendor ON VendorDetails(idtDGVendor) USING GSI;
select * from VendorDetails USE INDEX ( index_idtdgvendor USING GSI) WHERE idtDGVendor >= 1 and idtDGVendor <= 5000;
Our Machine Configuration
CPU : INTEL® Xeon® CPU E5 - 2630L V2 2.4GHZ
RAM : 4GB
OS : Windows Server 2012 R2
Below query takes around 8 to 10 seconds
select * from VendorDetails USE INDEX ( index_idtdgvendor USING GSI) WHERE idtDGVendor >= 1 and idtDGVendor <= 5000
Kindly help to resolve the above performance issue.
Can you run the following query and post the time taken.
select idtDGVendor from VendorDetails USE INDEX ( index_idtdgvendor USING GSI) WHERE idtDGVendor >= 1 and idtDGVendor <= 5000
Your machine is not a suitable test env. If it is your laptop, even things like email apps and web browsers will contend for memory and CPU.