Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-39597

[N1QL][ADVISE][RQG] advise returns an empty index recommendation

    XMLWordPrintable

Details

    • Untriaged
    • 1
    • Unknown

    Description

      I have these buckets in the system:

      multiple_table_db_79919484_simple_table_1
      multiple_table_db_79919484_simple_table_10
      multiple_table_db_79919484_simple_table_2
      multiple_table_db_79919484_simple_table_3
      multiple_table_db_79919484_simple_table_4

      Running advise on this query
      ADVISE SELECT t_1.int_field1 , t_1.decimal_field1 , t_1.primary_key_id , t_1.bool_field1 FROM bucket_01 t_1 LEFT JOIN bucket_05 t_1SiRAdlidCB ON ( t_1.primary_key_id = t_1SiRAdlidCB.primary_key_id ) LEFT JOIN bucket_010 t_1fLjtGGSnKf ON ( t_1.primary_key_id = t_1fLjtGGSnKf.primary_key_id ) INNER JOIN bucket_04 t_1GHMwUzdukS ON ( t_1.primary_key_id = t_1GHMwUzdukS.primary_key_id ) INNER JOIN bucket_04 t_1LjVrlSNfDc ON ( t_1.primary_key_id = t_1LjVrlSNfDc.primary_key_id ) WHERE ((t_1.primary_key_id IS NOT NULL AND t_1.int_field1 IS NULL)) OR ((t_1.int_field1 >= 47635891 OR t_1.varchar_field1 IS NOT NULL));

      Gets this recommendation

      Covered Index Recommendations
      CREATE INDEX adv_primary_key_id ON `bucket_05`(`primary_key_id`)

      CREATE INDEX adv_primary_key_id ON `bucket_010`(`primary_key_id`)

      CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)

      CREATE INDEX adv_primary_key_id ON `bucket_04`()

      Index Recommendations
      CREATE INDEX adv_int_field1_primary_key_id ON `bucket_01`(`int_field1`,`primary_key_id`)
      CREATE INDEX adv_varchar_field1 ON `bucket_01`(`varchar_field1`)

      There is one blank index being recommended, plus it says those indexes are covering but they do not include fields from the predicate?

      Attachments

        Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            People

              ajay.bhullar Ajay Bhullar
              ajay.bhullar Ajay Bhullar
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty