I have 2 Buckets which each holds docs which i want to join. I want to get all Docs from my rets bucket where the status is Active and there is a record also in Contacts of type farm. My query looks like this right now and it somewhat works but the performance is bad anywhere 6 to 8 sec
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.Record.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,
META(p).id AS DocId2,
p.Record.Dates.CancellationDate,
p.Record.Dates.CloseDate,
p.Record.Dates.OnMarketDate,
p.Record.Dates.PurchaseContractDate,
p.Record.Dates.StatusChangeTimestamp,
p.Record.StandardStatus,
p.Record.PreviousStandardStatus,
p.Record.MajorChangeType,
p.Record.Prices.OriginalListPrice,
p.Record.Prices.ListPrice,
p.Record.Prices.ClosePrice,
p.Record.Agents.BuyerAgent.BuyerAgentFirstName,
p.Record.Agents.BuyerAgent.BuyerAgentLastName,
p.Record.Agents.BuyerAgent.BuyerOfficeName,
p.Record.Agents.ListingAgent.ListAgentFirstName,
p.Record.Agents.ListingAgent.ListAgentLastName,
p.Record.Agents.ListingAgent.ListOfficeName,
DATE_DIFF_STR( p.Record.Dates.PurchaseContractDate , p.Record.Dates.OnMarketDate, 'day') AS DaysOnMarket
FROM Contacts AS f LEFT
JOIN rets AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "")
AND p._type = "Residential"
WHERE f._type="farm"
AND p.Record.StandardStatus = 'Active'
Here is what my plan currently looks like
{
"plan": {
"#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": "adv_Record_apn_type",
"index_id": "96e67c7a38d60df1",
"index_projection": {
"primary_key": true
},
"keyspace": "Contacts",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"inclusion": 0,
"index_key": "(`Record`.`apn`)",
"low": "null"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "f",
"keyspace": "Contacts",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`f`.`_type`) = \"farm\") and (((`f`.`Record`).`apn`) is not null))"
},
{
"#operator": "NestedLoopJoin",
"alias": "p",
"on_clause": "((((`f`.`Record`).`apn`) = replace((`p`.`ParcelNumber`), \"-\", \"\")) and ((`p`.`_type`) = \"Residential\"))",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "p",
"index": "adv_Record_StandardStatus_replace_ParcelNumber_sub_type",
"index_id": "c6e89537ed82fb3a",
"index_projection": {
"primary_key": true
},
"keyspace": "rets",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "\"Active\"",
"inclusion": 3,
"index_key": "(`Record`.`StandardStatus`)",
"low": "\"Active\""
},
{
"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": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`p`.`Record`).`StandardStatus`) = \"Active\") and ((`p`.`_type`) = \"Residential\") and ((`p`.`_type`) = \"Residential\"))"
}
]
}
}
]
}
},
{
"#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`.`Record`).`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": "DocId2",
"expr": "(meta(`p`).`id`)"
},
{
"expr": "(((`p`.`Record`).`Dates`).`CancellationDate`)"
},
{
"expr": "(((`p`.`Record`).`Dates`).`CloseDate`)"
},
{
"expr": "(((`p`.`Record`).`Dates`).`OnMarketDate`)"
},
{
"expr": "(((`p`.`Record`).`Dates`).`PurchaseContractDate`)"
},
{
"expr": "(((`p`.`Record`).`Dates`).`StatusChangeTimestamp`)"
},
{
"expr": "((`p`.`Record`).`StandardStatus`)"
},
{
"expr": "((`p`.`Record`).`PreviousStandardStatus`)"
},
{
"expr": "((`p`.`Record`).`MajorChangeType`)"
},
{
"expr": "(((`p`.`Record`).`Prices`).`OriginalListPrice`)"
},
{
"expr": "(((`p`.`Record`).`Prices`).`ListPrice`)"
},
{
"expr": "(((`p`.`Record`).`Prices`).`ClosePrice`)"
},
{
"expr": "((((`p`.`Record`).`Agents`).`BuyerAgent`).`BuyerAgentFirstName`)"
},
{
"expr": "((((`p`.`Record`).`Agents`).`BuyerAgent`).`BuyerAgentLastName`)"
},
{
"expr": "((((`p`.`Record`).`Agents`).`BuyerAgent`).`BuyerOfficeName`)"
},
{
"expr": "((((`p`.`Record`).`Agents`).`ListingAgent`).`ListAgentFirstName`)"
},
{
"expr": "((((`p`.`Record`).`Agents`).`ListingAgent`).`ListAgentLastName`)"
},
{
"expr": "((((`p`.`Record`).`Agents`).`ListingAgent`).`ListOfficeName`)"
},
{
"as": "DaysOnMarket",
"expr": "date_diff_str((((`p`.`Record`).`Dates`).`PurchaseContractDate`), (((`p`.`Record`).`Dates`).`OnMarketDate`), \"day\")"
}
]
},
{
"#operator": "Distinct"
}
]
}
},
{
"#operator": "Distinct"
}
]
}
}
}
````Preformatted text`