Partial index with complex expression for covered queries

Couchbase Server
,
1

Hello.

I’m trying to create a partial index that would support this query:

select meta().id
	from feedgen
	where
		dt="f"
		and f=1466
		and mid=10076
		and ((`lua` is missing) or (`lua` < `luc`))
		where meta().id > '1466:41710848'
	order by f,mid,meta().id
	limit 10

As you can see, everything is simple except the expression with an or :
((`lua` is missing) or (`lua` < `luc`))
In my queries, I don’t care about the values of lua and luc. I only care whether the condition is fullfilled.

I’m testing two options which differ only by the inclusion of lua and luc in the index key.

Option A - fields from the advanced condition are included in the index key:

CREATE INDEX `adv`
	ON `feedgen`(`f`,`mid`,(meta().`id`),`lua`,`luc`)
	PARTITION BY hash(`f`)
	WHERE (((`dt` = "f") and (`mid` is valued)) and ((`lua` is missing) or (`lua` < `luc`)))

Option B - field from the advanced condition are not included in the index key:

CREATE INDEX `adv2`
	ON `feedgen`(`f`,`mid`,(meta().`id`))
	PARTITION BY hash(`f`)
	WHERE (((`dt` = "f") and (`mid` is valued)) and ((`lua` is missing) or (`lua` < `luc`)))

My assumption is that both of these indexes should be able to cover the query. However, that is not the case.
Option A is fine - only IndexScan3 is included in the plan.
Option B is not - besides IndexScan3, there’s a Fetch in the plan, presumably to recheck the condition.
This is the plan:

	{
	  "#operator": "Sequence",
	  "#stats": {
		"#phaseSwitches": 1,
		"execTime": "1.437µs"
	  },
	  "~children": [
		{
		  "#operator": "Authorize",
		  "#stats": {
			"#phaseSwitches": 3,
			"execTime": "3.349µs",
			"servTime": "1.132296ms"
		  },
		  "privileges": {
			"List": [
			  {
				"Target": "default:feedgen",
				"Priv": 7
			  }
			]
		  },
		  "~child": {
			"#operator": "Sequence",
			"#stats": {
			  "#phaseSwitches": 1,
			  "execTime": "1.914µs"
			},
			"~children": [
			  {
				"#operator": "Sequence",
				"#stats": {
				  "#phaseSwitches": 2,
				  "execTime": "91.084µs",
				  "kernTime": "238ns",
				  "state": "running"
				},
				"~children": [
				  {
					"#operator": "IndexScan3",
					"#stats": {
					  "#itemsOut": 75,
					  "#phaseSwitches": 303,
					  "execTime": "103.912µs",
					  "kernTime": "15.595µs",
					  "servTime": "1.449862ms"
					},
					"index": "adv2",
					"index_id": "3cb3a481961b609e",
					"index_order": [
					  {
						"keypos": 0
					  },
					  {
						"keypos": 1
					  },
					  {
						"keypos": 2
					  }
					],
					"index_projection": {
					  "primary_key": true
					},
					"keyspace": "feedgen",
					"namespace": "default",
					"spans": [
					  {
						"exact": true,
						"range": [
						  {
							"high": "1466",
							"inclusion": 3,
							"low": "1466"
						  },
						  {
							"high": "10076",
							"inclusion": 3,
							"low": "10076"
						  },
						  {
							"inclusion": 0,
							"low": "\"1466:41710848\""
						  }
						]
					  }
					],
					"using": "gsi",
					"#time_normal": "00:00.001",
					"#time_absolute": 0.001553774
				  },
				  {
					"#operator": "Fetch",
					"#stats": {
					  "#itemsIn": 33,
					  "#itemsOut": 16,
					  "#phaseSwitches": 106,
					  "execTime": "168.423µs",
					  "kernTime": "1.556676ms",
					  "servTime": "1.245337ms"
					},
					"keyspace": "feedgen",
					"namespace": "default",
					"#time_normal": "00:00.001",
					"#time_absolute": 0.00141376
				  },
				  {
					"#operator": "Sequence",
					"#stats": {
					  "#phaseSwitches": 2,
					  "execTime": "207.761µs",
					  "kernTime": "127ns",
					  "state": "running"
					},
					"~children": [
					  {
						"#operator": "Filter",
						"#stats": {
						  "#itemsIn": 12,
						  "#itemsOut": 11,
						  "#phaseSwitches": 51,
						  "execTime": "468.906µs",
						  "kernTime": "2.416957ms"
						},
						"condition": "((((((`feedgen`.`dt`) = \"f\") and ((`feedgen`.`f`) = 1466)) and ((`feedgen`.`mid`) = 10076)) and (((`feedgen`.`lua`) is missing) or ((`feedgen`.`lua`) < (`feedgen`.`luc`)))) and (\"1466:41710848\" < (meta(`feedgen`).`id`)))",
						"#time_normal": "00:00.000",
						"#time_absolute": 0.000468906
					  },
					  {
						"#operator": "InitialProject",
						"#stats": {
						  "#itemsIn": 11,
						  "#itemsOut": 10,
						  "#phaseSwitches": 35,
						  "execTime": "50.36µs",
						  "kernTime": "2.741915ms"
						},
						"result_terms": [
						  {
							"expr": "(meta(`feedgen`).`id`)"
						  }
						],
						"#time_normal": "00:00.000",
						"#time_absolute": 0.00005036
					  },
					  {
						"#operator": "FinalProject",
						"#stats": {
						  "#itemsIn": 10,
						  "#itemsOut": 10,
						  "#phaseSwitches": 24,
						  "execTime": "7.543µs",
						  "kernTime": "80ns"
						},
						"#time_normal": "00:00.000",
						"#time_absolute": 0.0000075430000000000005
					  }
					],
					"#time_normal": "00:00.000",
					"#time_absolute": 0.000207761
				  }
				],
				"#time_normal": "00:00.000",
				"#time_absolute": 0.000091084
			  },
			  {
				"#operator": "Limit",
				"#stats": {
				  "#itemsIn": 10,
				  "#itemsOut": 10,
				  "#phaseSwitches": 32,
				  "execTime": "7.848µs",
				  "kernTime": "73.347µs"
				},
				"expr": "10",
				"#time_normal": "00:00.000",
				"#time_absolute": 0.000007848
			  }
			],
			"#time_normal": "00:00.000",
			"#time_absolute": 0.000001914
		  },
		  "#time_normal": "00:00.001",
		  "#time_absolute": 0.0011356449999999998
		},
		{
		  "#operator": "Stream",
		  "#stats": {
			"#itemsIn": 10,
			"#itemsOut": 10,
			"#phaseSwitches": 23,
			"execTime": "62.535µs",
			"kernTime": "3.976623ms"
		  },
		  "#time_normal": "00:00.000",
		  "#time_absolute": 0.000062535
		}
	  ],
	  "~versions": [
		"6.5.0-N1QL",
		"6.6.0-7909-enterprise"
	  ],
	  "#time_normal": "00:00.000",
	  "#time_absolute": 0.000001437
	}

Is there a potential for improvement in the query planner or am I missing something important?
I’m using version Enterprise Edition 6.6.

2

Have you tried running your statement through https://index-advisor.couchbase.com/indexadvisor/#1 and creating the suggested index(es) ?

3

It advices to create an index with all the fields included

CREATE INDEX adv_f_mid_dt_lua_luc ON `feedgen`(`f`,`mid`,`dt`,`lua`,`luc`)"

What I need is a partial index, as my condition is very selective.

4 
SELECT meta().id
FROM feedgen
WHERE dt="f" AND IFMISSINGORNULL(lua,false) < luc
      AND f=1466
      AND mid=10076
      meta().id > '1466:41710848'
ORDER BY f, mid,meta().id
LIMIT 10;

CREATE INDEX ix1 ON feedgen(f, mid,META().id) PARTITION BY HASH(f, mid) WHERE dt = "f" AND IFMISSINGORNULL(lua,false) < luc;

If f,mid is not immutable don’t use PARTITION index.
Check it is covered

5

Thanks @vsr1 vsr1 . With this expression, the index covers my queries.

Out of curiosity, do you know what caused the first expression not to be covered?

6

If Index WHERE has OR clause to make cover it may need all the fields as part of index keys (As it can’t determine individual expression values to re-apply predicates if indexer returns false positives) (checkout adding dt as leading key in adv index and see if it covers)