Very slow ORDER BY on 100K docs, even with index CB 4.5

SQL++
, ,
1

Hi All I need your valuable support. I have a few buckets and i need to join data between them selecting some attributes from several buckets. Bucket zStore has 100k Docs with Type “ItemId” and 200 Docs with Type “ItemsGroupId” , zDB has 1 Doc with Type “CustomerId”, 1 Doc with Type “TagId” and 1 Doc with Type “DiscountId”
The problem that i need the pagination on filtered data which can be achieved with 2 ways

  1. with standard count(*) filters followed by selection of needed data with same filters, means 2 queries
  2. use meta.metrics.sortCount from meta on executed query

I prefer second way, but here is problem rises. if remove order by, the query is fast (150-250 ms) but sortCount is missing from meta, when the Order By is back it sortCount exists, but response time is 90-115 s. How I can improve the response time keeping pagination working and with performance maximum around 1 second?

Indexes:
CREATE INDEX i_ItemBrowser_Tags ONzStore(DISTINCT ARRAY tag FOR tag IN ItemTags END, ItemTags, ItemsGroupId, CustomerId, DateCreated) WHERE Type = "ItemId"

CREATE INDEX i_ItemBrowser_DateCreated ONzStore(DateCreated) WHERE Type = "ItemId"

CREATE INDEX i_ItemBrowser_CustomerId ONzStore(CustomerId) WHERE Type = "ItemId"

Here is the N1QL:
SELECT zstore.*
customers.CustomerName CustomerName,
itemsgroups.ItemsGroupName ItemsGroupName,
ARRAY {discount.DiscountId, discount.Desc} FOR discount IN discounts END as Discounts ,
ARRAY {tag.TagId, tag.TagName} FOR tag IN tags END as Tags
FROM zStore zstore
LEFT JOIN zDB customers ON KEYS zstore.CustomerId
LEFT JOIN zStore itemsgroups ON KEYS zstore.ItemsGroupId
LEFT NEST zDB discount ON KEYS ARRAY x FOR x IN itemsgroups.Discounts END
LEFT NEST zDB tags ON KEYS ARRAY tag FOR tag IN zstore.ItemTags END
WHERE
zstore.Type = “ItemId”
AND zstore.ItemId IS NOT MISSING
AND zstore.ItemId IS NOT NULL
ORDER BY zstore.DateCreated ASC
LIMIT 10

Above Query uses only Primary index, none of created indexes.

Here is entities structure:
Item
{
“CustomerId”:“cid_6ecbc0dd-ef> a9-4c6c-a048-4c47fea6ef80”,
“DateCreated”:1526377962555,
“DateModified”:152637796255,
“ItemDescription”:“ItemDescription”,
“ItemId”:“ItemId_89125352587396737040”,
“ItemName”:“ItemName”,
“ItemOrder”:0,
“ItemTags”:[
“TagId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478”
],
“ItemsGroupId”:“ItemsGroupId_2e966c6b-66c6-4116-8028-6e3666c68028”,
“Type”:“ItemId”
}
ItemGroupe
{
“CustomerId”:“cid_6ecbc0dd-efa9-4c6c-a048-4c47fea6ef80”,
“DateCreated”:1526377962155,
“DateModified”:1526377962155,
“ItemsGroupDescription”:“ItemsGroupDescription”,
“ItemsGroupName”:“ItemsGroupName”,
“Discounts”:[
“DiscId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478”
],
“ItemsGroupId”:“ItemsGroupId_6e36ea01a314-66c6-4116-8028-2e938a8b”,
“Type”:“ItemsGroupId”
}
Discount
{
“DateCreated”:1526377961166,
“DateModified”:1526377961166,
“Desc”:“DESCRIPTION CONTENT”,
“DiscountId”:“DiscountId_2e938a8b-66c6-4116-8028-6e36ea01a314”,
“Type”:“DiscountId”
}
Tag
{
“DateCreated”:1526377962299,
“DateModified”:1526377962299,
“TagName”:“TagName”,
“TagId”:“TagId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478”,
“Type”:“TagId”
}
Customer
{
“DateCreated”:1526377963377,
“DateModified”:1526377963377,
“CustomerName”:“CustomerName”,
“CustomerId”:“cid_6ecbc0dd-efa9-4c6c-a048-4c47fea6ef80”,
“Type”:“CustomerId”
}

Thanks for your time.

2

Can you confirm this right because i have not seen items defined any where

3

Sorry it must be zstore, actually all example entities is a mirror of actual entities

4

If Order BY requires sort it takes time because it needs to process all qualified items and sort even you are limiting 1 item

You can optimize the query such that it uses index order.

Try following index and re-written query

CREATE INDEX i_ItemBrowser_Tags ON zStore(DateCreated) WHERE Type = "ItemId"

SELECT zstore.*,
       ig[0].ItemsGroupName, ig[0].Discounts,
      (SELECT RAW c.CustomerName FROM zDB AS c USE KEYS zstore.CustomerId)[0] AS CustomerName,
      (SELECT t.TagId,t.TagName FROM zDB AS t USE KEYS zstore.ItemTags) AS Tags,
FROM zStore AS zstore
LET ig = (SELECT i.ItemsGroupName, (SELECT d.DiscountId, d.Desc FROM zDB AS d USE KEYS i.Discounts) AS Discounts FROM zStore AS i USE KEYS zstore.ItemsGroupId)
WHERE zstore.Type = "ItemId" AND zstore.DateCreated IS NOT MISSING
ORDER BY zstore.DateCreated ASC
LIMIT 10 ;
5

Ok, Thanks will try, but the i_ItemBrowser_Tags was created in a such way because i need to filter items by tags. So i’m must use that index
Here is full Query variant which can exist
SELECT zstore.*
customers.CustomerName CustomerName,
itemsgroups.ItemsGroupName ItemsGroupName,
ARRAY {discount.DiscountId, discount.Desc} FOR discount IN discounts END as Discounts ,
ARRAY {tag.TagId, tag.TagName} FOR tag IN tags END as Tags
FROM zStore zstore
LEFT JOIN zDB customers ON KEYS zstore.CustomerId
LEFT JOIN zStore itemsgroups ON KEYS zstore.ItemsGroupId
LEFT NEST zDB discount ON KEYS ARRAY x FOR x IN itemsgroups.Discounts END
LEFT NEST zDB tags ON KEYS ARRAY tag FOR tag IN zstore.ItemTags END
WHERE
zstore.Type = “ItemId”
AND zstore.ItemId IS NOT MISSING
AND zstore.ItemId IS NOT NULL
AND ANY tag IN zstore.ItemTags SATISFIES tag IN [“TagId_a9f02e0d-d769-415a-a330-334b35d96c88”,“TagId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478”] END
AND LOWER(zstore.ItemName) LIKE ‘%itemname%’ AND zstore.ItemsGroupId IN [“ItemsGroupId_6249d04f-3cea-4bce-9b22-e6c690700cf7”]
ORDER BY zstore.DateCreated ASC
LIMIT 10

6

As you are using LEFT JOIN any predicate on right of LEFT JOIN causes null projected row throw away(makes it INNER JOIN).

You can do same thing in the USE KEYS like below, this eliminates fetch of unwanted documents vs elminate after fetch

USE KEYS (ARRAY tag FOR tag IN zstore.ItemTags WHEN tag IN [“TagId_a9f02e0d-d769-415a-a330-334b35d96c88”,“TagId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478”] END)
OR
USE KEYS ARRAY_INTERSECT(zstore.ItemTags ,[“TagId_a9f02e0d-d769-415a-a330-334b35d96c88”,“TagId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478”] )

SELECT zstore.*,
       ig[0].ItemsGroupName, ig[0].Discounts,
      (SELECT RAW c.CustomerName FROM zDB AS c USE KEYS zstore.CustomerId)[0] AS CustomerName,
      (SELECT t.TagId,t.TagName FROM zDB AS t USE KEYS ARRAY_INTERSECT(zstore.ItemTags, ["TagId_a9f02e0d-d769-415a-a330-334b35d96c88","TagId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478"])) AS Tags,
FROM zStore AS zstore
LET ig = (SELECT i.ItemsGroupName, (SELECT d.DiscountId, d.Desc FROM zDB AS d USE KEYS i.Discounts) AS Discounts FROM zStore AS i USE KEYS ARRAY_INTERSECT(zstore.ItemsGroupId, ["ItemsGroupId_6249d04f-3cea-4bce-9b22-e6c690700cf7"]))
WHERE zstore.Type = "ItemId" AND zstore.DateCreated IS NOT MISSING AND LOWER(zstore.ItemName) LIKE "%itemname%"
ORDER BY zstore.DateCreated ASC
LIMIT 10 ;
7

Nice hint, will try updated query and reply