Hi guys,
I’m facing a problem with how to create a proper index for a specific N1QL that I have. I already created a composite index, alternative index, and also a single index, and when I run this N1QL in my database it takes 2 min.
Today I have a server on AWS with 3 machines r5.2xlarge (64GB RAM each one) and my bucket has 6.7 mi documents. The current setup is a couchbase 5.0.1 community, however, I created another new server with the same setup to start using couchbase 6.5.1 community and I didn’t have any improvement.
Here is the N1QL:
SELECT invoices.syncID,
invoices.type,
invoices.ownerName,
invoices.entryNumber,
invoices.date,
invoices.dateShort,
invoices.dueDate,
invoices.dueDateShort,
invoices.hasDiscount,
invoices.ledgerId,
invoices.matterName,
invoices.matter,
invoices.totalAmountInclGst,
invoices.totalReceivedInclGst,
invoices.totalWriteOffInclGst,
invoices.totalOutstandingInclGst,
invoices.amountInclGst,
invoices.hasFixedFee,
invoices.isDraft,
invoices.owner,
invoices.totalDisbursementsInclGst,
invoices.totalAllowancesInclGst,
invoices.totalTasksInclGst,
matters.archived,
firms.syncID AS firmSyncID,
firms.name AS firmName,
firms.email AS firmEmail,
solicitors.syncID solicitorSyncID,
solicitors.firstName,
solicitors.lastName,
solicitors.email AS solicitorEmail,
barristerSummary.totalClerkFeeInclGst,
barristerSummary.totalClerkFeeExclGst,
barristerSummary.totalClerkFeeGst,
barristerSummary.capAmount
FROM barbooks invoices LEFT
JOIN barbooks matters ON KEYS invoices.matter LEFT
JOIN barbooks solicitors ON KEYS matters.solicitor LEFT
JOIN barbooks firms ON KEYS matters.contact.firm.syncID LEFT
JOIN barbooks barristerSummary ON KEYS "barristerSummary:" || invoices.owner
WHERE IFMISSINGORNULL(invoices.isDraft, FALSE) = FALSE
AND invoices.bbDeleted = FALSE
AND invoices.docVersion = 1
AND invoices.type IN ["RegularInvoice", "InterestInvoice"]
AND invoices.chamberId IN ['chamber:1'];
Someone could advise me on how can I achieve better performance on it?