I am running into a performance issue with a N1QL when i search for emails in the documents when stored in a ARRAY,
and there is only like 2700 docs in the _type “farm”
The query looks like this
SELECT
meta(f).id as DocId,
f.Owners.owner1FName || IFMISSINGORNULL((CASE WHEN (LENGTH(f.Owners.owner1SpouseFName) > 0) THEN " & " || f.Owners.owner1SpouseFName || " "
ELSE " " END), " ") || f.Owners.owner1LName as owner_1,
TRIM(f.Owners.owner2FName || IFMISSINGORNULL((CASE WHEN (LENGTH(f.Owners.owner2SpouseFName) > 0) THEN " & " || f.Owners.owner1SpouseFName || " "
ELSE " " END), " ") || f.Owners.owner2LName) as owner2,
f.Marketing.privacy,
ARRAY { CASE WHEN v.type IS NOT MISSING THEN lower(v.type)
ELSE "default"
END:
CASE WHEN LENGTH(v. `number`) = 10 THEN
"(" || SUBSTR(v.`number` , 0,3) || ") " || SUBSTR(v.`number` , 3,3) || "-" || SUBSTR(v.`number` , 6,4)
else
v.`number` END } FOR v IN f.phones END AS phones,
ARRAY {CASE WHEN v.type IS NOT MISSING THEN lower(v.type)
ELSE "default"
END :v.`address`} FOR v IN f.emails END AS emails,
f.Marketing.farm_id,
f.PropertyAddress.houseNumber,
f.PropertyAddress.streetName,
f.PropertyAddress.city,
f.PropertyAddress.state,
f.beds,
f.baths,
f.partialBaths,
f.`pool`,
f.fireplace,
f.ownerOccupied,
f.tract,
t.name AS tract_name,
f.mailingAddress.address || " " || f.mailingAddress.city || " " ||
f.mailingAddress.state || " " || f.mailingAddress.zip ||"-" || f.mailingAddress.zip4 AS mailing_address
FROM Contacts AS f
LEFT JOIN Contacts AS t
ON t._type ="tract_info" AND ANY b IN t.tract_id SATISFIES b = f.tract END
WHERE f._type="farm" AND f.PropertyAddress.streetName IS NOT NULL and ANY e IN f.emails SATISFIES LOWER(e.address) like LOWER("alex@test.com%") END order by f.PropertyAddress.streetName, TONUMBER(f.PropertyAddress.houseNumber) Limit 25 Offset 0
This is my current execution plan
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “5.826µs”
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “7.476µs”,
“servTime”: “1.921884ms”
},
“privileges”: {
“List”: [
{
“Target”: “default:Contacts”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “3.001µs”
},
“~children”: [
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 2,
“execTime”: “193.303µs”,
“kernTime”: “124ns”,
“state”: “running”
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 2596,
“#phaseSwitches”: 10387,
“execTime”: “23.043898ms”,
“kernTime”: “1.923394088s”,
“servTime”: “6.461892ms”
},
“as”: “f”,
“index”: “ix3”,
“index_id”: “6f197990a0147d0e”,
“index_order”: [
{
“keypos”: 0
},
{
“keypos”: 1
}
],
“index_projection”: {
“primary_key”: true
},
“keyspace”: “Contacts”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:00.0295”,
“#time_absolute”: 0.02950579
},
{
“#operator”: “Fetch”,
“#stats”: {
“#itemsIn”: 2596,
“#itemsOut”: 2596,
“#phaseSwitches”: 10713,
“execTime”: “27.279957ms”,
“kernTime”: “2.133884769s”,
“servTime”: “603.131602ms”
},
“as”: “f”,
“keyspace”: “Contacts”,
“namespace”: “default”,
“#time_normal”: “00:00.6304”,
“#time_absolute”: 0.630411559
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 2,
“execTime”: “307.946µs”,
“kernTime”: “157ns”,
“state”: “running”
},
“~children”: [
{
“#operator”: “NestedLoopJoin”,
“#stats”: {
“#itemsIn”: 2596,
“#itemsOut”: 2664,
“#phaseSwitches”: 24393,
“execTime”: “42.394898ms”,
“kernTime”: “3.121068801s”
},
“alias”: “t”,
“on_clause”: “((cover ((t._type)) = “tract_info”) and cover (any b in (t.tract_id) satisfies (b = (f.tract)) end))”,
“outer”: true,
“~child”: {
“#operator”: “DistinctScan”,
“#stats”: {
“#itemsIn”: 1743,
“#itemsOut”: 1743,
“#phaseSwitches”: 25144,
“execTime”: “94.792488ms”,
“kernTime”: “2.983604824s”
},
“scan”: {
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 1743,
“#phaseSwitches”: 19952,
“execTime”: “81.570404ms”,
“kernTime”: “10.731157ms”,
“servTime”: “2.864292657s”
},
“as”: “t”,
“covers”: [
“cover ((distinct ((t.tract_id))))”,
“cover ((t.name))”,
“cover ((meta(t).id))”
],
“filter_covers”: {
“cover ((t._type))”: “tract_info”,
“cover (any b in (t.tract_id) satisfies (b = (f.tract)) end)”: true
},
“index”: “ix4”,
“index_id”: “df142ed4cdbbd26c”,
“index_projection”: {
“entry_keys”: [
1
],
“primary_key”: true
},
“keyspace”: “Contacts”,
“namespace”: “default”,
“nested_loop”: true,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “(f.tract)”,
“inclusion”: 3,
“low”: “(f.tract)”
}
]
}
],
“using”: “gsi”
}
},
“#time_normal”: “00:00.0423”,
“#time_absolute”: 0.042394898
},
{
“#operator”: “Filter”,
“#stats”: {
“#itemsIn”: 2664,
“#itemsOut”: 2,
“#phaseSwitches”: 5335,
“execTime”: “246.775648ms”,
“kernTime”: “2.916732909s”
},
“condition”: “((((f._type) = “farm”) and (((f.PropertyAddress).streetName) is not null)) and any e in (f.emails) satisfies (lower((e.address)) like lower("alex@test.com%”)) end)",
“#time_normal”: “00:00.2467”,
“#time_absolute”: 0.246775648
},
{
“#operator”: “InitialProject”,
“#stats”: {
“#itemsIn”: 2,
“#itemsOut”: 2,
“#phaseSwitches”: 13,
“execTime”: “727.141µs”,
“kernTime”: “3.162784121s”
},
“result_terms”: [
{
“as”: “DocId”,
“expr”: “(meta(f).id)”
},
{
“as”: “owner_1”,
“expr”: “((((f.Owners).owner1FName) || ifmissingornull(case when (0 < length(((f.Owners).owner1SpouseFName))) then ((” & " || ((f.Owners).owner1SpouseFName)) || " ") else " " end, " “)) || ((f.Owners).owner1LName))”
},
{
“as”: “owner2”,
“expr”: “trim(((((f.Owners).owner2FName) || ifmissingornull(case when (0 < length(((f.Owners).owner2SpouseFName))) then ((” & " || ((f.Owners).owner1SpouseFName)) || " “) else " " end, " “)) || ((f.Owners).owner2LName)))”
},
{
“expr”: “((f.Marketing).privacy)”
},
{
“as”: “phones”,
“expr”: “array {case when ((v.type) is not missing) then lower((v.type)) else “default” end: case when (length((v.number)) = 10) then (((((”(” || substr0((v.number), 0, 3)) || ") “) || substr0((v.number), 3, 3)) || “-”) || substr0((v.number), 6, 4)) else (v.number) end} for v in (f.phones) end”
},
{
“as”: “emails”,
“expr”: “array {case when ((v.type) is not missing) then lower((v.type)) else “default” end: (v.address)} for v in (f.emails) end”
},
{
“expr”: “((f.Marketing).farm_id)”
},
{
“expr”: “((f.PropertyAddress).houseNumber)”
},
{
“expr”: “((f.PropertyAddress).streetName)”
},
{
“expr”: “((f.PropertyAddress).city)”
},
{
“expr”: “((f.PropertyAddress).state)”
},
{
“expr”: “(f.beds)”
},
{
“expr”: “(f.baths)”
},
{
“expr”: “(f.partialBaths)”
},
{
“expr”: “(f.pool)”
},
{
“expr”: “(f.fireplace)”
},
{
“expr”: “(f.ownerOccupied)”
},
{
“expr”: “(f.tract)”
},
{
“as”: “tract_name”,
“expr”: “cover ((t.name))”
},
{
“as”: “mailing_address”,
“expr”: "((((((((((f.mailingAddress).address) || " ") || ((f.mailingAddress).city)) || " ") || ((f.mailingAddress).state)) || " “) || ((f.mailingAddress).zip)) || “-”) || ((f.mailingAddress).zip4))”
}
],
“#time_normal”: “00:00.0007”,
“#time_absolute”: 0.0007271409999999999
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#itemsIn”: 2,
“#itemsOut”: 2,
“#phaseSwitches”: 5,
“execTime”: “3.742µs”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000003742
}
],
“#time_normal”: “00:00.0003”,
“#time_absolute”: 0.000307946
}
],
“#time_normal”: “00:00.0001”,
“#time_absolute”: 0.000193303
},
{
“#operator”: “Limit”,
“#stats”: {
“#itemsIn”: 2,
“#itemsOut”: 2,
“#phaseSwitches”: 7,
“execTime”: “3.192µs”,
“kernTime”: “4.337µs”
},
“expr”: “25”,
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000003192
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000030009999999999997
},
“#time_normal”: “00:00.0019”,
“#time_absolute”: 0.00192936
},
{
“#operator”: “Stream”,
“#stats”: {
“#itemsIn”: 2,
“#itemsOut”: 2,
“#phaseSwitches”: 11,
“execTime”: “6.71µs”,
“kernTime”: “3.16547783s”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.00000671
}
],
“~versions”: [
“2.0.0-N1QL”,
“6.0.0-1693-enterprise”
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000005826
}