N1QL Performance Issue


#1

Hi,

we have a test system with 4 nodes all running couchbase server 4.1.
All three services (data, index, query) are running on every node.

Nodes:

When i execute the following query on a bucket with 2.000.000 documents and a secondary index on country,
the query takes about 8 minutes.

SELECT country, COUNT(*) FROM Test GROUP BY country

If i run the same query on our sql server with 20.000.000 entries and no index on country
it takes about 3 minutes.

I haven’t found any statements about the performance of n1ql.
But i think the query should run a lot faster.

Is there a way to speed up the query?

Regards,
Patrick


#2

Have you created a secondary index on the field country?


#3

Yes i have a Primary index and a secondary index on country


#4

You might get better performances if you use separated nodes running the index and query services independently.
@geraldss and @cihangirb can tell you more on expected results.


#5

Hello,

Please do the following in order to get the benefit of a covering index, and then rerun your test:


#6

Thx for the fast response.

IS NOT MISSING reduced the execution time from 8 minutes to about 40 seconds.