Query Performance Issue when using Order by

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"
            }
        ]
    }
}

@aponnath ORDER BY requires a sort to be done (or in your specific case, multiple sorts), so omitting it saves time by avoiding the sorts. If the number of records being sorted is high, the sort can be the bottleneck, as best case for sort is O(N * log(N)).

I get that it takes a bit longer but it should not cause a 10 + times in increase of query time. Also in my case the records that are sorted is around 5 K, so that should not be a big deal, the other records which are joined are several million but i would assume that f should be sorted first, then take the offset and lit and then do the join

CREATE INDEX ix1 ON Contacts (LOWER(Record.PropertyAddress.streetName), TONUMBER(Record.PropertyAddress.houseNumber)) WHERE _type="farm";

try above index it should use index order and will be faster

Didn’t do much, has a average time of 8.5 sec to complete after the new Index

execute query in UI and go to Plan Text (Execution Plan) and post the details

{
  "#operator": "Authorize",
  "#stats": {
    "#phaseSwitches": 4,
    "execTime": "6.434µs",
    "servTime": "28.688µs"
  },
  "privileges": {
    "List": [
      {
        "Target": "default:Contacts",
        "Priv": 7,
        "Props": 0
      },
      {
        "Target": "default:rets",
        "Priv": 7,
        "Props": 0
      }
    ]
  },
  "~child": {
    "#operator": "Sequence",
    "#stats": {
      "#phaseSwitches": 1,
      "execTime": "36.44µs"
    },
    "~children": [
      {
        "#operator": "With",
        "#stats": {
          "#phaseSwitches": 2,
          "execTime": "10.632652ms"
        },
        "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",
          "#stats": {
            "#phaseSwitches": 1,
            "execTime": "26.109µs"
          },
          "~children": [
            {
              "#operator": "IndexScan3",
              "#stats": {
                "#itemsOut": 4170,
                "#phaseSwitches": 16683,
                "execTime": "46.471596ms",
                "kernTime": "4.74208061s",
                "servTime": "21.060201ms"
              },
              "as": "f",
              "index": "farmstreetNamehouseNumberSort",
              "index_id": "71ef2a886d1281f1",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "Contacts",
              "namespace": "default",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "inclusion": 1,
                      "index_key": "lower(((`Record`.`PropertyAddress`).`streetName`))",
                      "low": "null"
                    },
                    {
                      "inclusion": 1,
                      "index_key": "to_number(((`Record`.`PropertyAddress`).`houseNumber`))",
                      "low": "null"
                    }
                  ]
                }
              ],
              "using": "gsi",
              "#time_normal": "00:00.067",
              "#time_absolute": 0.067531797
            },
            {
              "#operator": "Fetch",
              "#stats": {
                "#heartbeatYields": 3,
                "#itemsIn": 4170,
                "#itemsOut": 4170,
                "#phaseSwitches": 16718,
                "execTime": "49.616824ms",
                "kernTime": "5.978531631s",
                "servTime": "362.490131ms"
              },
              "as": "f",
              "keyspace": "Contacts",
              "namespace": "default",
              "#time_normal": "00:00.412",
              "#time_absolute": 0.412106955
            },
            {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 1,
                "execTime": "4.925µs"
              },
              "~children": [
                {
                  "#operator": "Filter",
                  "#stats": {
                    "#itemsIn": 4170,
                    "#itemsOut": 4170,
                    "#phaseSwitches": 16684,
                    "execTime": "637.716705ms",
                    "kernTime": "6.798305546s"
                  },
                  "condition": "(((`f`.`_type`) = \"farm\") and (lower((((`f`.`Record`).`PropertyAddress`).`streetName`)) is not missing) and (to_number((((`f`.`Record`).`PropertyAddress`).`houseNumber`)) is not missing))",
                  "#time_normal": "00:00.637",
                  "#time_absolute": 0.637716705
                },
                {
                  "#operator": "NestedLoopNest",
                  "#stats": {
                    "#itemsIn": 4170,
                    "#itemsOut": 4170,
                    "#phaseSwitches": 40100,
                    "execTime": "138.239008ms",
                    "kernTime": "8.24430406s"
                  },
                  "alias": "p",
                  "on_clause": "((((`f`.`Record`).`apn`) = replace((`p`.`ParcelNumber`), \"-\", \"\")) and ((`p`.`_type`) = \"Residential\"))",
                  "outer": true,
                  "~child": {
                    "#operator": "Sequence",
                    "#stats": {
                      "#phaseSwitches": 8340,
                      "execTime": "267.797731ms",
                      "kernTime": "18.829817ms",
                      "state": "running"
                    },
                    "~children": [
                      {
                        "#operator": "IndexScan3",
                        "#stats": {
                          "#itemsOut": 1283,
                          "#phaseSwitches": 17642,
                          "execTime": "73.185577ms",
                          "kernTime": "6.2401ms",
                          "servTime": "6.740312194s"
                        },
                        "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",
                        "#time_normal": "00:06.813",
                        "#time_absolute": 6.813497771000001
                      },
                      {
                        "#operator": "Fetch",
                        "#stats": {
                          "#itemsIn": 1283,
                          "#itemsOut": 1283,
                          "#phaseSwitches": 23950,
                          "execTime": "118.105237ms",
                          "kernTime": "6.904115298s",
                          "servTime": "1.05611211s"
                        },
                        "as": "p",
                        "keyspace": "rets",
                        "namespace": "default",
                        "nested_loop": true,
                        "#time_normal": "00:01.174",
                        "#time_absolute": 1.174217347
                      }
                    ],
                    "#time_normal": "00:00.267",
                    "#time_absolute": 0.267797731
                  },
                  "#time_normal": "00:00.138",
                  "#time_absolute": 0.13823900800000002
                },
                {
                  "#operator": "InitialProject",
                  "#stats": {
                    "#itemsIn": 4170,
                    "#itemsOut": 4170,
                    "#phaseSwitches": 16684,
                    "execTime": "3.987585005s",
                    "kernTime": "4.432526973s"
                  },
                  "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])"
                    }
                  ],
                  "#time_normal": "00:03.987",
                  "#time_absolute": 3.987585005
                },
                {
                  "#operator": "Distinct",
                  "#stats": {
                    "#itemsIn": 4170,
                    "#itemsOut": 4170,
                    "#phaseSwitches": 16684,
                    "execTime": "646.025303ms",
                    "kernTime": "7.77429543s"
                  },
                  "#time_normal": "00:00.646",
                  "#time_absolute": 0.646025303
                }
              ],
              "#time_normal": "00:00.000",
              "#time_absolute": 0.0000049249999999999994
            },
            {
              "#operator": "Distinct",
              "#stats": {
                "#itemsIn": 4170,
                "#itemsOut": 4170,
                "#phaseSwitches": 16684,
                "execTime": "568.644327ms",
                "kernTime": "7.851865066s"
              },
              "#time_normal": "00:00.568",
              "#time_absolute": 0.568644327
            }
          ],
          "#time_normal": "00:00.000",
          "#time_absolute": 0.000026109
        },
        "#time_normal": "00:00.010",
        "#time_absolute": 0.010632652
      },
      {
        "#operator": "Order",
        "#stats": {
          "#itemsIn": 4170,
          "#itemsOut": 100,
          "#phaseSwitches": 8445,
          "execTime": "87.489701ms",
          "kernTime": "8.338394814s",
          "state": "running"
        },
        "limit": "100",
        "sort_terms": [
          {
            "expr": "lower((((`f`.`Record`).`PropertyAddress`).`streetName`))"
          },
          {
            "expr": "to_number((((`f`.`Record`).`PropertyAddress`).`houseNumber`))"
          }
        ],
        "#time_normal": "00:00.087",
        "#time_absolute": 0.087489701
      },
      {
        "#operator": "Limit",
        "#stats": {
          "#itemsIn": 100,
          "#itemsOut": 100,
          "#phaseSwitches": 301,
          "execTime": "354.67µs",
          "kernTime": "11.38869ms"
        },
        "expr": "100",
        "#time_normal": "00:00.000",
        "#time_absolute": 0.00035467
      },
      {
        "#operator": "Stream",
        "#stats": {
          "#itemsIn": 100,
          "#itemsOut": 100,
          "#phaseSwitches": 204,
          "execTime": "10.457801ms",
          "kernTime": "8.433850843s"
        },
        "#time_normal": "00:00.010",
        "#time_absolute": 0.010457801
      }
    ],
    "#time_normal": "00:00.000",
    "#time_absolute": 0.00003644
  },
  "~versions": [
    "7.1.0-N1QL",
    "7.1.0-2556-enterprise"
  ],
  "#time_normal": "00:00.000",
  "#time_absolute": 0.000035122
}

that’s what i called the index you wanted me to create

@aponnath ,

May be due to NEST it is not using index order.

Try following index and query. Avoid JOIN and use correlated subquery (which only do 1 document in fetch in by using index order subquery).
Also parent query can use index order and scan only LIMIT documents.

CREATE INDEX ix2 ON rets(REPLACE(ParcelNumber, "-", ""), Record.Dates.ModificationTimestamp DESC ) 
WHERE _type = "Residential";
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 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 rets AS p1
           WHERE f.Record.apn = REPLACE(p1.ParcelNumber, "-", "") AND p1._type = "Residential"
           ORDER BY p1.Record.Dates.ModificationTimestamp DESC
           LIMIT 1)[0] AS lisiting
FROM Contacts AS f
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;

Another Option is HASH JOIN (avoid JOIN first) because some where i saw inner side has millions?

FROM Contacts AS f LEFT NEST rets AS p USE HASH (BUILD)

Yest the Rets Bucket has several million records and its growing by 25 k a day. I created the new Index and that got the sorted now into the same neighborhood as the sorted in the sub 400 ms.
What’s strange that the parcel number made such an impact on the sorted one. I assumed it would go and sort the farm docs based on order bby, then use the limit and offset and then go and do the join.In Anycase as always thanks for the great help

Glad able to help bring down the time.

JOIN followed by ORDER BY.

Some use cases we are improving on this MB-40499