N1ql does not use index for "order by"

Please post the query.

This is query

    select * from bucket
        where
        clientId = 'clientId'
        and form = 'creative'
        and username = 'username'
        and `values`.deleted_at is null
        and `values`.published = true
        and -STR_TO_MILLIS(meta.updatedAt) is not null
          order by -STR_TO_MILLIS(meta.updatedAt)  limit 30 offset 0

This query is using a different index. Can you add

USE INDEX ( `idx_artist_creatives` )

I’m sorry, that is my mistake.

It’s actually using the correct index. I renamed index right after I made the EXPLAIN output. It’s using the correct index.

I’ve tried USE INDEX, but it did not work either.

I’ve double checked it again locally just to make sure and it works just fine.

Only difference is that the prod has way more data than my local.

Ok, a few changes.

(1) Remove form from the index keys, because it is already defined in the index WHERE clause.

(2) Change SELECT * to SELECT specific fields, so you can use a covering index.

(3) If you are using cbq shell, \SET -pretty = false

DISREGARD PLEASE . I will make another reply.

DISREGARD PLEASE . I will make another reply.

I’ve removed ‘form’ from the index.

This is my new index definition.

CREATE INDEX `idx_creatives_by_artist_test` ON `catalog`((-str_to_millis((`meta`.`updatedAt`))),`clientId`,`username`,(`values`.`deleted_at`),(`values`.`published`)) WHERE (`form` = "creative")

This is EXPLAIN out:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IndexScan",
              "covers": [
                "cover ((-str_to_millis(((`bucket1`.`meta`).`updatedAt`))))",
                "cover ((`bucket1`.`clientId`))",
                "cover ((`bucket1`.`username`))",
                "cover (((`bucket1`.`values`).`deleted_at`))",
                "cover (((`bucket1`.`values`).`published`))",
                "cover ((meta(`bucket1`).`id`))"
              ],
              "filter_covers": {
                "cover ((`bucket1`.`form`))": "creative"
              },
              "index": "idx_creatives_by_artist_test",
              "index_id": "8430fa9158884cc8",
              "keyspace": "catalog",
              "namespace": "default",
              "spans": [
                {
                  "Range": {
                    "Inclusion": 1,
                    "Low": [
                      "true",
                      "\"clientId\"",
                      "\"username\"",
                      "null",
                      "true"
                    ]
                  }
                }
              ],
              "using": "gsi"
            },
            {
              "#operator": "Parallel",
              "maxParallelism": 1,
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Filter",
                    "condition": "((((((cover ((`bucket1`.`clientId`)) = \"clientId\") and (cover ((`bucket1`.`form`)) = \"creative\")) and (cover ((`bucket1`.`username`)) = \"username\")) and (cover (((`bucket1`.`values`).`deleted_at`)) is null)) and (cover (((`bucket1`.`values`).`published`)) = true)) and cover ((-str_to_millis(((`bucket1`.`meta`).`updatedAt`)))))"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "cover ((`bucket1`.`form`))"
                      }
                    ]
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Offset",
          "expr": "0"
        },
        {
          "#operator": "Limit",
          "expr": "30"
        },
        {
          "#operator": "FinalProject"
        }
      ]
    },
    "text": "select `bucket1`.`form` from catalog bucket1 \nwhere \nbucket1.clientId = 'clientId' \nand bucket1.form = 'creative' \nand bucket1.username = 'username' \nand `values`.deleted_at is null\nand `values`.published = true\nand -STR_TO_MILLIS(`meta`.`updatedAt`)\norder by -STR_TO_MILLIS(`meta`.`updatedAt`)\nlimit 30 offset 0"
  }
]

Query response time is between 900ms and 1.2 second.

Thank you for the help :slight_smile:

Nice :slight_smile:. Pretty=false will get you more still.

um…so there is no way to improve this query?

Even with pretty=false, it’s still between 900ms and 1.2 seconds, which is slower than the original query without new index

The index is helping with sorting.

The index and query can be improved. Can deleted and published be moved from the index keys to the index WHERE clause. Can any other fields be moved to the index WHERE clause.

If OFFSET is 0, the LIMIT can also be pushed down to the index.

I’m not sure if it is…response time is doubled with the new index.

I’ve decided to drop n1ql and move to views at this point. It’s not that n1ql is not good, but I think view is better choice for our use cases.

When it comes to counting and ordering, there is no benefit of using n1ql for our use cases since the performance is actually slower than view. Our use cases are not unique. They are normal pagination and ordering just like any other applications out there.

Noted. We are focused on use cases like yours, so we will keep at it. cc @keshav_m @prasad @vsr1

1 Like

I really appreciate all your help along the way :slight_smile:

I’m sure we will be using n1ql for internal stuff though, just not user facing features as they require counting and ordering.

1 Like

Hi @moon0326, Please use this.

Your index definition and query has been modified in a subtle way. This will still give you the same result.

Subtle is the optimizer, wrong results, it not. :slight_smile:

 CREATE INDEX idx_by_username ON bucket(clientId,username,  `values`.deleted_at,  `values`.published, -STR_TO_MILLIS(meta.updatedAt)) WHERE form = 'creative';
 
select * from bucket
        where
        clientId = 'clientId'
        and form = 'creative'
        and username = 'username'
        and `values`.deleted_at is null
        and `values`.published = true
        and -STR_TO_MILLIS(meta.updatedAt) is not null
          order by clientId, username, `values`.deleted_at , `values`.published, 
                  -STR_TO_MILLIS(meta.updatedAt) 
     limit 30 offset 0
2 Likes

Your queries should now be returning results dramatically faster!

Thank you @keshav_m

I will try that tonight and report back :slight_smile:

Hi @keshav_m @geraldss

The query from @keshav_m worked finally :slight_smile:

As I learn more about n1ql, I feel like that n1q index is like a view. Only difference is that n1ql indexes live in memory. It looks like I need to create an index per use case just like a view. That’s different than my initial impression. I thought n1ql indexes can be re-used just like other traditional RDMS and have good performance. I’m aware that n1ql indexes can be re-used, but then the response time is just not there at least for my use cases.

Thank you both of you. I will keep exploring, but I think we are still going back to view for now.

2 Likes

I’m glad it all worked out…

There are slight, subtle differences between indexes in RDBMS and Couchbase.
But, they’re very close to RDBMS indices than a map-reduce view/materialized view.

BTW, you can see Couchbase Connect lifestream here.
http://info.couchbase.com/Connect16_Livestream_Registration.html