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?