How to improve Index on a join with Condition

SQL++
1

I am currently using the below query to get all my records that are active. based on rets having 5 mil records and Contacts 5 K the query takes almost 9 sec

SELECT f.Record.PropertyAddress AS farm,
       (
           SELECT p1.Record.Dates
           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 p.Record.StandardStatus = "Active"
LIMIT 5

Blow is the Plan Text

{
    "#operator": "Sequence",
    "~children": [
        {
            "#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,
                                    "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": "NestedLoopNest",
                                "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,
                                                            "low": "\"Active\""
                                                        },
                                                        {
                                                            "high": "((`f`.`Record`).`apn`)",
                                                            "inclusion": 3,
                                                            "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",
                                "result_terms": [
                                    {
                                        "as": "farm",
                                        "expr": "((`f`.`Record`).`PropertyAddress`)"
                                    },
                                    {
                                        "as": "lisiting",
                                        "expr": "(correlated (select ((`p1`.`Record`).`Dates`) from (`p`) as `p1`  ORDER BY (((`p1`.`Record`).`Dates`).`ModificationTimestamp`) DESC limit 1)[0])"
                                    }
                                ]
                            }
                        ]
                    }
                }
            ]
        },
        {
            "#operator": "Limit",
            "expr": "5"
        }
    ]
}
2 
CREATE INDEX ix1 ON Contacts( Record.StandardStatus, Record.apn, Record.PropertyAddress ) 
WHERE _type = "farm";
3

That wont work, as Record.StandardStatus is in the rets bucket and Record.PropertyAddress is in the Contacts bucket

4

Could you please let me know CB version?

If CB 7.0 and above try this

SELECT f.Record.PropertyAddress AS farm, listings[0] AS listing
FROM Contacts AS f
LET listings = ( SELECT p.Record.Dates
                 FROM rets AS p
                 WHERE p._type = "Residential"
                       AND p.Record.StandardStatus = "Active"
                       AND REPLACE(p.ParcelNumber, "-", "") = f.Record.apn
                 ORDER BY p.Record.Dates.ModificationTimestamp DESC
                 LIMIT 1)
WHERE f._type = "farm" AND f.Record.apn IS NOT NULL
LIMIT 5;

CREATE INDEX ix1 ON Contacts(Record.apn, Record.PropertyAddress ) WHERE _type = "farm";
CREATE INDEX ix2 ON rets( REPLACE(ParcelNumber, "-", ""), Record.StandardStatus,
                         Record.Dates.ModificationTimestamp DESC, Record.Dates)
WHERE _type = "Residential";

If you need listingCount (remove LIMIT 1) and do ARRAY_LENGTH(listings) AS listingCount in projection

5

That doesn’t work…

The first sequence says it completes successfull but the second one fails. Intresting, the query returns all Records from farm but nothing for listing
FYI, yes i am on 7.x

Below is the error

 {
    "_sequence_num": 2,
    "_sequence_query": "\nLIMIT 5;",
    "_sequence_query_status": "Bad Request",
    "_sequence_result": [
      {
        "code": 3000,
        "msg": "syntax error - at LIMIT",
        "query_from_user": "explain \nLIMIT 5;"
      }
    ]
  }
6

@aponnath ,

There is ; before LIMIT remove that,
If listing empty means your document and fields did not match. You can check that or post farm, Residential document

7

Yes that fixes the error but does not get me any listings. The point was to get all farms that have a listing where the status is active. So not sure what we are missing

8

Query is right please post sample documents of both