Query giving results some times and not giving some time

Couchbase Server
1

I am running one query through query workbench. It is giving results some times and when i run same query again it is not giving results. Could someone provide the solution for this. Below is my query:

select rollrBI.* from rollr_BI rollrBI where type=‘dayWise’
and travelledDate between 3432432432 and 4343243243242
order by travelledDate

2

Provide EXPLAIN output and index definition.

CREATE INDEX ix1 ON rollr_BI(travelledDate) WHERE  type="dayWise";

SELECT r.* 
FROM  `rollr_BI` AS r 
WHERE r.type="dayWise" AND  r.travelledDate BETWEEN  3432432432 AND 4343243243242
ORDER BY  r.travelledDate;
3

------------------------------Plan text for creating Index ------------------------
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3
},
“privileges”: {
“List”: [
{
“Target”: “:rollr_BI”,
“Priv”: 12
}
]
},
“~child”: {
“#operator”: “CreateIndex”,
“#stats”: {
“#phaseSwitches”: 2,
“servTime”: “29.8037612s”
},
“index”: “ix1”,
“keys”: [
{
“expr”: “travelledDate”
}
],
“keyspace”: “rollr_BI”,
“namespace”: “default”,
“using”: “default”,
“where”: “(type = “dayWise”)”,
“#time_normal”: “00:29.8037”,
“#time_absolute”: 29.8037612
}
},
{
“#operator”: “Stream”,
“#stats”: {
“#phaseSwitches”: 3,
“kernTime”: “29.8037612s”
}
}
],
“~versions”: [
“2.0.0-N1QL”,
“5.5.0-2958-enterprise”
]
}

--------------------------------------- Query plan text--------------------------------------------------
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3
},
“privileges”: {
“List”: [
{
“Target”: “default:rollr_BI”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 75,
“#phaseSwitches”: 303,
“servTime”: “1.0004ms”
},
“as”: “r”,
“index”: “ix1”,
“index_id”: “2953dd0f4a6457f5”,
“index_order”: [
{
“keypos”: 0
}
],
“index_projection”: {
“primary_key”: true
},
“keyspace”: “rollr_BI”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “4343243243242”,
“inclusion”: 3,
“low”: “3432432432”
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:00.0010”,
“#time_absolute”: 0.0010004
},
{
“#operator”: “Fetch”,
“#stats”: {
“#itemsIn”: 75,
“#itemsOut”: 75,
“#phaseSwitches”: 313,
“kernTime”: “1.0004ms”,
“servTime”: “16.0112ms”
},
“as”: “r”,
“keyspace”: “rollr_BI”,
“namespace”: “default”,
“#time_normal”: “00:00.0160”,
“#time_absolute”: 0.0160112
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “Filter”,
“#stats”: {
“#itemsIn”: 75,
“#itemsOut”: 75,
“#phaseSwitches”: 303,
“execTime”: “1.0007ms”,
“kernTime”: “17.0116ms”
},
“condition”: “(((r.type) = “dayWise”) and ((r.travelledDate) between 3432432432 and 4343243243242))”,
“#time_normal”: “00:00.0010”,
“#time_absolute”: 0.0010007
},
{
“#operator”: “InitialProject”,
“#stats”: {
“#itemsIn”: 75,
“#itemsOut”: 75,
“#phaseSwitches”: 230,
“execTime”: “2.0014ms”,
“kernTime”: “15.0041ms”
},
“result_terms”: [
{
“expr”: “r”,
“star”: true
}
],
“#time_normal”: “00:00.0020”,
“#time_absolute”: 0.0020014
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#itemsIn”: 75,
“#itemsOut”: 75,
“#phaseSwitches”: 226,
“kernTime”: “1.0068ms”
}
}
]
}
]
}
},
{
“#operator”: “Stream”,
“#stats”: {
“#itemsIn”: 75,
“#itemsOut”: 75,
“#phaseSwitches”: 303,
“kernTime”: “18.0123ms”
}
}
],
“~versions”: [
“2.0.0-N1QL”,
“5.5.0-2958-enterprise”
]
}

4

The plan looks good. Are u still having issue