I have the following below complex query which has quite a performance difference if i apply the order by
ORDER BY LOWER(f.Record.PropertyAddress.streetName),
TONUMBER(f.Record.PropertyAddress.houseNumber)
if i use the order by the query takes between 8 and 14 seconds to complete, if I remove the order by i get into the 300 ms range. Intresting is that after i went from 7.0.x to 7.1 the performance got actually worse by about 1 to 2 sec on the query that uses the order by. So is there a way to get the performance to sub 1 sec even with the order by
WITH tracknames AS (
SELECT DISTINCT track_id,
t.name
FROM Contacts AS t
UNNEST t.tract_id AS track_id
WHERE t._type ="tract_info")
SELECT DISTINCT META(f).id AS DocId,
SUBSTR(f.Record.apn,0,3) || "-" || SUBSTR(f.Record.apn,3,3) || "-" || SUBSTR(f.Record.apn,6,2) AS apn,
f.Record.Owners.owner1FName || IFMISSINGORNULL((CASE WHEN (LENGTH(f.Record.Owners.owner1SpouseFName) > 0) THEN " & " || f.Record.Owners.owner1SpouseFName || " " ELSE " " END), " ") || f.Owners.owner1LName AS owner_1,
TRIM(f.Record.Owners.owner2FName || IFMISSINGORNULL((CASE WHEN (LENGTH(f.Record.Owners.owner2SpouseFName) > 0) THEN " & " || f.Record.Owners.owner1SpouseFName || " " ELSE " " END), " ") || f.Record.Owners.owner2LName) AS owner2,
f.Record.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.Record.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.Record.emails END AS emails,
f.Marketing.farm_id,
f.Record.PropertyAddress.houseNumber,
f.Record.PropertyAddress.streetName || IFMISSINGORNULL(" " || f.Record.PropertyAddress.streetType, "") || IFMISSINGORNULL(" " || f.Record.PropertyAddress.streetSuffix, "") AS streetName,
f.Record.PropertyAddress.city,
f.Record.PropertyAddress.state,
f.Record.beds,
f.Record.baths,
f.Record.partialBaths,
f.Record.`pool`,
f.Record.fireplace,
f.Record.ownerOccupied,
f.Record.tract,
f.Record.propertyType,
f.Record.History.updated_flag,
FIRST v.name FOR v IN tracknames WHEN v.track_id = f.Record.tract END AS tract_name,
f.Record.mailingAddress.address || " " || f.Record.mailingAddress.city || " " || f.Record.mailingAddress.state || " " || f.Record.mailingAddress.zip ||"-" || f.Record.mailingAddress.zip4 AS mailing_address,
(
SELECT META(p1).id AS DocId,
p1.Record.Dates.CancellationDate,
p1.Record.Dates.CloseDate,
p1.Record.Dates.OnMarketDate,
p1.Record.Dates.PurchaseContractDate,
p1.Record.Dates.StatusChangeTimestamp,
p1.Record.StandardStatus,
p1.Record.PreviousStandardStatus,
p1.Record.MajorChangeType,
p1.Record.Prices.OriginalListPrice,
p1.Record.Prices.ListPrice,
p1.Record.Prices.ClosePrice,
p1.Record.Agents.BuyerAgent.BuyerAgentFirstName,
p1.Record.Agents.BuyerAgent.BuyerAgentLastName,
p1.Record.Agents.BuyerAgent.BuyerOfficeName,
p1.Record.Agents.ListingAgent.ListAgentFirstName,
p1.Record.Agents.ListingAgent.ListAgentLastName,
p1.Record.Agents.ListingAgent.ListOfficeName
FROM p AS p1
ORDER BY p1.Record.Dates.ModificationTimestamp DESC
LIMIT 1)[0] AS lisiting
FROM Contacts AS f LEFT NEST rets AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "")
AND p._type = "Residential"
WHERE f._type="farm"
AND LOWER(f.Record.PropertyAddress.streetName) IS NOT MISSING
AND TONUMBER(f.Record.PropertyAddress.houseNumber) IS NOT MISSING
ORDER BY LOWER(f.Record.PropertyAddress.streetName),
TONUMBER(f.Record.PropertyAddress.houseNumber)
LIMIT 100
OFFSET 0
Here is the currently used plan
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "With",
"bindings": [
{
"expr": "(select distinct `track_id`, (`t`.`name`) from `default`:`Contacts` as `t` unnest (`t`.`tract_id`) as `track_id` where ((`t`.`_type`) = \"tract_info\"))",
"static": true,
"var": "tracknames"
}
],
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "f",
"index": "Ottoman__type",
"index_id": "ab04d8be5f77c6ca",
"index_projection": {
"primary_key": true
},
"keyspace": "Contacts",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"farm\"",
"inclusion": 3,
"index_key": "`_type`",
"low": "\"farm\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "f",
"keyspace": "Contacts",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`f`.`_type`) = \"farm\") and (lower((((`f`.`Record`).`PropertyAddress`).`streetName`)) is not missing) and (to_number((((`f`.`Record`).`PropertyAddress`).`houseNumber`)) is not missing))"
},
{
"#operator": "NestedLoopNest",
"alias": "p",
"on_clause": "((((`f`.`Record`).`apn`) = replace((`p`.`ParcelNumber`), \"-\", \"\")) and ((`p`.`_type`) = \"Residential\"))",
"outer": true,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "p",
"index": "adv_replace_ParcelNumber_sub_type",
"index_id": "7173f85cc8e9aff",
"index_projection": {
"primary_key": true
},
"keyspace": "rets",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "((`f`.`Record`).`apn`)",
"inclusion": 3,
"index_key": "replace(`ParcelNumber`, \"-\", \"\")",
"low": "((`f`.`Record`).`apn`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "p",
"keyspace": "rets",
"namespace": "default",
"nested_loop": true
}
]
}
},
{
"#operator": "InitialProject",
"distinct": true,
"result_terms": [
{
"as": "DocId",
"expr": "(meta(`f`).`id`)"
},
{
"as": "apn",
"expr": "(substr0(((`f`.`Record`).`apn`), 0, 3) || \"-\" || substr0(((`f`.`Record`).`apn`), 3, 3) || \"-\" || substr0(((`f`.`Record`).`apn`), 6, 2))"
},
{
"as": "owner_1",
"expr": "((((`f`.`Record`).`Owners`).`owner1FName`) || ifmissingornull(case when (0 < length((((`f`.`Record`).`Owners`).`owner1SpouseFName`))) then (\" & \" || (((`f`.`Record`).`Owners`).`owner1SpouseFName`) || \" \") else \" \" end, \" \") || ((`f`.`Owners`).`owner1LName`))"
},
{
"as": "owner2",
"expr": "trim(((((`f`.`Record`).`Owners`).`owner2FName`) || ifmissingornull(case when (0 < length((((`f`.`Record`).`Owners`).`owner2SpouseFName`))) then (\" & \" || (((`f`.`Record`).`Owners`).`owner1SpouseFName`) || \" \") else \" \" end, \" \") || (((`f`.`Record`).`Owners`).`owner2LName`)))"
},
{
"expr": "(((`f`.`Record`).`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`.`Record`).`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`.`Record`).`emails`) end"
},
{
"expr": "((`f`.`Marketing`).`farm_id`)"
},
{
"expr": "(((`f`.`Record`).`PropertyAddress`).`houseNumber`)"
},
{
"as": "streetName",
"expr": "((((`f`.`Record`).`PropertyAddress`).`streetName`) || ifmissingornull((\" \" || (((`f`.`Record`).`PropertyAddress`).`streetType`)), \"\") || ifmissingornull((\" \" || (((`f`.`Record`).`PropertyAddress`).`streetSuffix`)), \"\"))"
},
{
"expr": "(((`f`.`Record`).`PropertyAddress`).`city`)"
},
{
"expr": "(((`f`.`Record`).`PropertyAddress`).`state`)"
},
{
"expr": "((`f`.`Record`).`beds`)"
},
{
"expr": "((`f`.`Record`).`baths`)"
},
{
"expr": "((`f`.`Record`).`partialBaths`)"
},
{
"expr": "((`f`.`Record`).`pool`)"
},
{
"expr": "((`f`.`Record`).`fireplace`)"
},
{
"expr": "((`f`.`Record`).`ownerOccupied`)"
},
{
"expr": "((`f`.`Record`).`tract`)"
},
{
"expr": "((`f`.`Record`).`propertyType`)"
},
{
"expr": "(((`f`.`Record`).`History`).`updated_flag`)"
},
{
"as": "tract_name",
"expr": "first (`v`.`name`) for `v` in `tracknames` when ((`v`.`track_id`) = ((`f`.`Record`).`tract`)) end"
},
{
"as": "mailing_address",
"expr": "((((`f`.`Record`).`mailingAddress`).`address`) || \" \" || (((`f`.`Record`).`mailingAddress`).`city`) || \" \" || (((`f`.`Record`).`mailingAddress`).`state`) || \" \" || (((`f`.`Record`).`mailingAddress`).`zip`) || \"-\" || (((`f`.`Record`).`mailingAddress`).`zip4`))"
},
{
"as": "lisiting",
"expr": "(correlated (select (meta(`p1`).`id`) as `DocId`, (((`p1`.`Record`).`Dates`).`CancellationDate`), (((`p1`.`Record`).`Dates`).`CloseDate`), (((`p1`.`Record`).`Dates`).`OnMarketDate`), (((`p1`.`Record`).`Dates`).`PurchaseContractDate`), (((`p1`.`Record`).`Dates`).`StatusChangeTimestamp`), ((`p1`.`Record`).`StandardStatus`), ((`p1`.`Record`).`PreviousStandardStatus`), ((`p1`.`Record`).`MajorChangeType`), (((`p1`.`Record`).`Prices`).`OriginalListPrice`), (((`p1`.`Record`).`Prices`).`ListPrice`), (((`p1`.`Record`).`Prices`).`ClosePrice`), ((((`p1`.`Record`).`Agents`).`BuyerAgent`).`BuyerAgentFirstName`), ((((`p1`.`Record`).`Agents`).`BuyerAgent`).`BuyerAgentLastName`), ((((`p1`.`Record`).`Agents`).`BuyerAgent`).`BuyerOfficeName`), ((((`p1`.`Record`).`Agents`).`ListingAgent`).`ListAgentFirstName`), ((((`p1`.`Record`).`Agents`).`ListingAgent`).`ListAgentLastName`), ((((`p1`.`Record`).`Agents`).`ListingAgent`).`ListOfficeName`) from (`p`) as `p1` ORDER BY (((`p1`.`Record`).`Dates`).`ModificationTimestamp`) DESC limit 1)[0])"
}
]
},
{
"#operator": "Distinct"
}
]
}
},
{
"#operator": "Distinct"
}
]
}
},
{
"#operator": "Order",
"limit": "100",
"sort_terms": [
{
"expr": "lower((((`f`.`Record`).`PropertyAddress`).`streetName`))"
},
{
"expr": "to_number((((`f`.`Record`).`PropertyAddress`).`houseNumber`))"
}
]
},
{
"#operator": "Limit",
"expr": "100"
}
]
}
}