https://typesense.org logo
Join Slack
Powered by
Hi all! We are currently in the process of load te...
# community-help
t
Hi all! We are currently in the process of load testing a schema of 
ads
and 
products
, where each 
ads
has many 
products
We are currently testing 10,000 
ads
(with each 
ads
having 1000 associated 
products
) on Typesense Cloud Instance (16GB @ 8vCPU) We are seeing great performance when filtering directly on either 
ads
or 
products
collections, but performance declines steeply when filtering 
ads
via a join to 
products
Is the most performant approach going to be to lift properties up onto the 
ads
collection, or another approach?
k
Would be helpful if you can share a skeleton schema for both collections and a sample query which is slow.
✅ 1
t
Sure thing! `ads`:
Copy code
{
  "created_at": 1738025937,
  "default_sorting_field": "weighting",
  "enable_nested_fields": true,
  "fields": [
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "associated_skus",
      "optional": true,
      "reference": "products.sku",
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string[]"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "ad_id",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "dynamic_search_terms",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string[]"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "search_key",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "bid_amount",
      "optional": true,
      "sort": true,
      "stem": false,
      "store": true,
      "type": "float"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "currency",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "weighting",
      "optional": false,
      "range_index": true,
      "sort": true,
      "stem": false,
      "store": true,
      "type": "float"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "over_paced",
      "optional": false,
      "sort": true,
      "stem": false,
      "store": true,
      "type": "bool"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "monetization_type",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "delivery_type",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "media_type",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "locations",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string[]"
    },
    {
      "facet": false,
      "index": false,
      "infix": false,
      "locale": "",
      "name": "link_url",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "creatives",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "object[]"
    },
    {
      "facet": false,
      "index": false,
      "infix": false,
      "locale": "",
      "name": "creatives.src",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string[]"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "creatives.label",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string[]"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "creatives.dimensions",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "object[]"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "creatives.dimensions.width",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "int32[]"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "creatives.dimensions.height",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "int32[]"
    }
  ],
  "name": "banner_ads",
  "num_documents": 100000,
  "symbols_to_index": [],
  "token_separators": []
}
products
Copy code
{
  "created_at": 1738025937,
  "default_sorting_field": "",
  "enable_nested_fields": true,
  "fields": [
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "sku",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "title",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "description",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "search_key",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "category_paths",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string[]"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "search_terms",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string[]"
    },
    {
      "facet": false,
      "index": false,
      "infix": false,
      "locale": "",
      "name": "google_product_category",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": false,
      "infix": false,
      "locale": "",
      "name": "link",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": false,
      "infix": false,
      "locale": "",
      "name": "image_link",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": false,
      "infix": false,
      "locale": "",
      "name": "additional_image_link",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": false,
      "infix": false,
      "locale": "",
      "name": "video_link",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "brand",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "store_id",
      "optional": true,
      "sort": true,
      "stem": false,
      "store": true,
      "type": "int32"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "object"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "price",
      "optional": false,
      "range_index": true,
      "sort": true,
      "stem": false,
      "store": true,
      "type": "float"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "currency",
      "optional": false,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "sale_price",
      "optional": true,
      "range_index": true,
      "sort": true,
      "stem": false,
      "store": true,
      "type": "float"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "availability",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "on_sale",
      "optional": false,
      "sort": true,
      "stem": false,
      "store": true,
      "type": "bool"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "color",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "gender",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "size",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "condition",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "age_group",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_9",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_8",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_7",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_6",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_5",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_4",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_3",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_1",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_2",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "attributes.attribute_0",
      "optional": true,
      "sort": false,
      "stem": false,
      "store": true,
      "type": "string"
    }
  ],
  "name": "products",
  "num_documents": 500000,
  "symbols_to_index": [],
  "token_separators": []
}
Example query:
Copy code
{
  "collection": "banner_ads",
  "q": "*",
  "query_by": "locations",
  "exclude_fields": "$products(*, additional_image_link, google_product_category, image_link, link, video_link, item_group_id), associated_skus",
  "filter_by": "weighting:>0 &&  ((search_key:`Cheese` || locations:=`<https://weary-tremor.net/>`) || $products(search_key:`Cheese`))"
}
I should note the response times are not slow – on the above hardware we're seeing ~200ms, which may well be expected, and we may need to scale more. The main issue is we're hoping to see response times of more like 20ms
k
How big are the collections?
t
banner_ads
has been tested at various sizes, but predominantly 10K 
products
is 500K (with each 
banner_ads
have 1K associated 
products
)
Another thing I've noticed in testing (for close to those sample sizes) is that past a certain cluster size, performance stops improving – i.e. at 16GB / 8vCPU I am hitting CPU constraints. Anything larger 32+GB/16vCPU+, response don't decrease – i.e. there seems to be a relatively hard limit to the lower search time I can reach...
h
Hi @Tristan Kenney In the filter_by you've mentioned:
Copy code
(search_key:`Cheese` || ...) || $products(search_key:`Cheese`)
If the 
search_key
of 
banner_ads
is already associated with the correct products through 
associated_skus
field, we can omit the join in the filter by.
Copy code
{
    "collection": "banner_ads",
    "q": "*",
    "query_by": "locations",,
    "filter_by": "weighting:>0 && (search_key:`Cheese` || locations:=`<https://weary-tremor.net/>`)"
    "include_fields": "$products(...)
}
Since you're querying 
banner_ads
collection that has the reference field, you can include the referenced documents without specifying join in the 
filter_by
.
t
@Harpreet Sangar I might be misunderstanding, but 
search_key
is a property on both 
products
and 
banner_ads
(and we're interested in filtering on both). Wouldn't I need to specify 
$products(search_key...)
to filter by the property on products collection?
h
Let's suppose we have the following documents:
Copy code
banner_ads(id, search_key, associated_skus)
1, Cheese, [1, 2, 5]
2, Milk, [4]
3, Curd, [3]

products(id, title, search_key)
1, prod_1, Cheese
2, prod_2, Cheese
3, prod_3, Curd
4, prod_4, Milk
5, prod_5, Cheese
and we're interested in getting the 
banner_ads
where the 
search_key
is 
Cheese
, both
Copy code
filter_by: (search_key:`Cheese` || ...) || $products(search_key:`Cheese`)
and
Copy code
filter_by: search_key:`Cheese`
will produce the same result but the former query will take longer. If this is not your case or you'd want to join on a different 
search_key
, then this optimization won't work.
In the second query, you'll have to mention 
include_fields: $products(...)
too.
t
Yeah, so what we have is:
Copy code
banner_ads(id, search_key, associated_skus)
1, Cheese, [1, 2, 5]
2, Milk, [4]
3, Curd, [3]

products(id, title, search_key)
1, prod_1, Parmesan 
2, prod_2, Gouda
3, prod_3, Lemon
4, prod_4, Full Fat
5, prod_5, Feta
Which I presume won't work?
h
Okay, with
Copy code
filter_by: search_key:`Cheese`,
include_fields: $products(...)
you'll get
Copy code
Cheese {
  Parmesan,
  Gouda,
  Feta
}
in the result. But if you want to execute a query say, having 
banner_ads
search_key:`Cheese` or having 
products
search_key:`Lemon`, then you'll have no other option.
t
Gotcha. Nice (and thank you)! I'll give it a try. I completely missed this in the documentation – is it called out by chance?
h
https://typesense.org/docs/27.1/api/joins.html#one-to-one-relation example is the closest one where we mention getting referenced docs via 
include_fields
.
👍 1
thankyou 1
Open in Slack
PreviousNext
Powered by