N1ql query is taking too much time

SQL++
, ,
1

HI my query is taking too much time when i uploaded the data on it. previosly when i have few hundreds records it was working fine and smooth but now when i uploaded the records around 10 k docs it fails to get the record in supposed time around 6 to 9 seconds it taking .i would have millions of record when start in production . I have created the indexes which is covering the query dont know why this is slow.

MY QUERY

SELECT inv.id as invoice_id,ins.due_time as inv_time ,inv.cus_id, customer.first_name,customer.last_name, inv.total_bill,ins.due_date as inv_date ,ins.id as sales_id FROM stitchit_data_bucket inv USE INDEX (adv_ALL_invoice_services_type_store_id_due_date_cus_id_id_total_bill) UNNEST inv.invoice_services as ins INNER JOIN stitchit_initialization_hq customer ON customer.customer_id = inv.cus_id AND customer.type=‘customer’ WHERE inv.type=‘invoice’ AND inv.due_date BETWEEN ‘2020-03-29’ AND ‘2020-04-04’ AND inv.store_id = ‘3221’ AND ins.working_status = ‘rack’ AND ins.express = ‘express’ ORDER BY ins.due_time ASC

MY INDEXS

CREATE INDEX adv_type_customer_id_last_name_first_name ON stitchit_initialization_hq(type,customer_id,last_name,first_name)

CREATE INDEX adv_ALL_invoice_services_type_store_id_due_date_cus_id_id_total_bill ON stitchit_data_bucket((all (invoice_services)),type,store_id,due_date,cus_id,id,total_bill)

index scan result

IndexScan3
  • as - inv
  • covers
    • cover (ins)
    • cover ((inv.type))
    • cover ((inv.store_id))
    • cover ((inv.due_date))
    • cover ((inv.cus_id))
    • cover ((inv.id))
    • cover ((inv.total_bill))
    • cover ((meta(inv).id))
  • filter_covers
    • cover (((inv.invoice_services) < {})) - true
    • cover (( <= (inv.invoice_services))) - true
    • cover (is_array((inv.invoice_services))) - true
  • index - adv_ALL_invoice_services_type_store_id_due_date_cus_id_id_total_bill
  • index_id - c680c74c65a986e8
  • keyspace - stitchit_data_bucket
  • namespace - default
  • spans
    • range
      • inclusion - 0
      • low - null
  • using - gsi
IndexScan3
  • as - customer
  • covers
    • cover ((customer.type))
    • cover ((customer.customer_id))
    • cover ((customer.last_name))
    • cover ((customer.first_name))
    • cover ((meta(customer).id))
  • index - adv_type_customer_id_last_name_first_name
  • index_id - c3f2dfa31cf1e2bf
  • keyspace - stitchit_initialization_hq
  • namespace - default
  • nested_loop - true
  • spans
    • exact - true
    • range
      • high - “customer”
      • inclusion - 3
      • low - “customer”
      • high - cover ((inv.cus_id))
      • inclusion - 3
      • low - cover ((inv.cus_id))
  • using - gsi

MY doc
{
“canada_exempt”: “”,
“completed_notification”: “sent”,
“corp_emp”: “0”,
“corp_po”: “0”,
“corp_req”: “0”,
“cus_id”: “006020000008”,
“customer_signaure”: “”,
“date”: “2020-04-26”,
“defect_reason”: “”,
“description”: “”,
“discount”: “0”,
“discount_id”: “0”,
“discount_method”: “”,
“due_date”: “2020-04-26”,
“due_time”: “14:00”,
“employee_id”: “7019”,
“exempt_1”: “”,
“exempt_2”: “”,
“id”: “00602000000009”,
“invoice_payment”: [
{
“account_no”: “”,
“auth_code”: “”,
“card_balance”: “”,
“entry_mode”: “”,
“gift_card_ref”: “”,
“host_response”: “”,
“payment_type”: “Cash”,
“terminal_id”: “Manual”,
“trans_amount”: 11.5,
“trans_date”: “2020-04-26”,
“trans_id”: 1582576119,
“trans_ref”: “”,
“trans_status”: “000”,
“trans_time”: “14:00”,
“trans_total_amount”: 11.5,
“trans_type”: “Sale”
},
{
“account_no”: “”,
“auth_code”: “”,
“card_balance”: “”,
“entry_mode”: “”,
“gift_card_ref”: “”,
“host_response”: “”,
“payment_type”: “Cash”,
“terminal_id”: “Manual”,
“trans_amount”: 11.5,
“trans_date”: “2020-04-26”,
“trans_id”: 1582576513,
“trans_ref”: “”,
“trans_status”: “000”,
“trans_time”: “14:00”,
“trans_total_amount”: 11.5,
“trans_type”: “Sale”
}
],
“invoice_process”: [
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “IN”,
“time”: “14:00”,
“total_bill”: “10.00”
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “pending”,
“time”: “14:00”,
“total_bill”: 10
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “ready”,
“time”: “14:00”,
“total_bill”: 10
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “EDIT”,
“time”: “14:00”,
“total_bill”: “20.00”
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221200000000100”,
“item_name”: “Pants”,
“status”: “pending”,
“time”: “14:00”,
“total_bill”: “20”
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “ready”,
“time”: “14:00”,
“total_bill”: 20
},
{
“date”: “2020-04-26”,
“time”: “14:00”,
“branch_id”: “60”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“total_bill”: “20.00”,
“status”: “OUT”
}
],
“invoice_services”: [
{
“check_redo”: “no”,
“check_status”: “OUT”,
“completed_date”: “2020-02-25”,
“completed_time”: “01:36:30”,
“created_emp_id”: “7019”,
“date”: “2020-04-26”,
“description”: “”,
“discount”: 0,
“discount_id”: “0”,
“discount_method”: “”,
“discount_name”: “”,
“due_date”: “2020-04-26”,
“due_time”: “14:00”,
“express”: “no”,
“express_amt”: 0,
“express_notification”: “waiting”,
“id”: “0060200000000900”,
“inv_id”: “00602000000009”,
“item_list”: [
{
“item_detail_charges”: 0,
“item_id”: “3221190649”,
“item_name”: "Waist Clip (4 piece) ",
“item_qty”: 1,
“price”: 10,
“sales_item_id”: “006020000000090000”,
“tag_3”: “”
}
],
“qty”: 2,
“service_id”: “3221190019”,
“service_name”: “Pants”,
“start_date”: “2020-02-25”,
“start_time”: “01:36:05”,
“store_id”: “60”,
“tag_1”: “”,
“tag_2”: “”,
“tax”: 0,
“total_bill”: 20,
“upcharge_charges”: 0,
“upcharge_charges_per”: 0,
“working_employee”: “7019”,
“working_status”: “ready”
}
],
“invoice_transaction”: [
{
“branch_id”: “60”,
“credit_note”: 0,
“date”: “2020-04-26”,
“description”: “SALES”,
“invoice_id”: “00602000000009”,
“method”: “Cash”,
“proceed”: 11.5,
“sales”: 11.5,
“time”: “14:00”
},
{
“branch_id”: “60”,
“credit_note”: 0,
“date”: “2020-04-26”,
“description”: “EDIT”,
“invoice_id”: “00602000000009”,
“method”: “”,
“proceed”: 11.5,
“sales”: 23,
“time”: “14:00”
},
{
“branch_id”: “60”,
“credit_note”: 0,
“date”: “2020-04-26”,
“description”: “CHECKOUT”,
“invoice_id”: “00602000000009”,
“method”: “Cash”,
“proceed”: 11.5,
“sales”: 0,
“time”: “14:00”
}
],
“notification_status”: “sent”,
“payment_date”: “2020-02-25”,
“pickup_name”: “”,
“price_list_id”: “3221190011”,
“province_exempt”: “”,
“referal_or_corp”: “Walk in”,
“referal_or_corp_id”: “0”,
“status”: “OUT”,
“store_id”: “60”,
“tax_amount”: 14.975,
“tax_method”: “%”,
“tax_name”: “GST”,
“time_entrance”: “14:00”,
“total_bill”: 23,
“total_change”: 0,
“total_express”: 0,
“total_paid”: 23,
“total_qty”: 2,
“total_tendered”: 11.5,
“type”: “invoice”
}

2

Same as Data loading in N1ql