github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/docs/RFCS/20160331_index_hints.md (about)

     1  - Feature Name: index_hints
     2  - Status: completed
     3  - Start Date: 2014-03-31
     4  - Authors: Radu
     5  - RFC PR: [#5762](https://github.com/cockroachdb/cockroach/pull/5762)
     6  - Cockroach Issue: [#5625](https://github.com/cockroachdb/cockroach/issues/5625)
     7  
     8  # Summary
     9  
    10  The proposal is to add syntax to allow the user to force using a specific index
    11  for a query. This is intended as a workaround when the index selection algorithm
    12  results in a bad choice.
    13  
    14  # Motivation
    15  
    16  The index selection algorithm takes into account a lot of factors but is far
    17  from perfect. One example we saw recently was from the photos app, where a
    18  `LIMIT` clause would have made using an index a much better choice. We have
    19  fixed that problem since, but there are other known issues, e.g. [#5589][5589].
    20  
    21  When we wanted a quick workaround for the photos app issue, we had to resort to
    22  using a crafted subquery.
    23  
    24  We want to provide an easy way to work around this kind of problem.
    25  
    26  [5589]: https://github.com/cockroachdb/cockroach/issues/5589
    27  
    28  # Current `@` syntax
    29  
    30  We currently support special syntax which allows us to use an index as a
    31  separate table, one which only has those columns that are stored in the index:
    32  
    33  ```sql
    34  CREATE TABLE test (
    35      k INT PRIMARY KEY,
    36      u INT,
    37      v INT,
    38      w INT,
    39      INDEX uv (u, v)
    40  );
    41  
    42  INSERT INTO test VALUES (1, 10, 100, 1000), (2, 20, 200, 2000);
    43  SELECT * FROM test@uv;
    44  ```
    45  
    46  | u  |  v  |
    47  |----|-----|
    48  | 10 | 100 |
    49  | 20 | 200 |
    50  
    51  This feature might be of some use internally (for debugging) but it is not of
    52  much value to a user. Notably, if we had a way to force the use of a specific
    53  index, that syntax could be used to produce a query equivalent to the one above:
    54  ```sql
    55  SELECT u,v FROM test USING INDEX uv
    56  ```
    57  
    58  # Other systems
    59  
    60  Below is a brief overview of what other DBMSs support. Since there is no common
    61  thread in terms of syntax, we shouldn't feel compelled to be compatible with any
    62  one of them.
    63  
    64  ### MySQL
    65  
    66  Basic syntax:
    67  ```sql
    68  -- Restricts index use to one of given indexes (or neither)
    69  SELECT * FROM table1 USE INDEX (col1_index,col2_index)
    70  
    71  -- Excludes some indexes from being used
    72  SELECT * FROM table1 IGNORE INDEX (col3_index)
    73  
    74  -- Forces use of one of the given indexes
    75  SELECT * FROM table1 FORCE INDEX (col1_index)
    76  ```
    77  
    78  More syntax and detailed information [here][1].
    79  
    80  [1]: http://dev.mysql.com/doc/refman/5.7/en/index-hints.html
    81  
    82  ### PostgreSQL
    83  
    84  PG does not provide support for hints. Instead they provide various knobs for
    85  tuning the optimizer to do the right thing. Details [here][2].
    86  
    87  [2]: http://blog.2ndquadrant.com/hinting_at_postgresql/
    88  
    89  ### Oracle
    90  
    91  Oracle provides [various options][3] for hints, a basic example is:
    92  ```sql
    93  SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */  * FROM v
    94  ```
    95  
    96  [3]: http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#i26205
    97  
    98  ### MS SQL Server
    99  
   100  SQL Server supports [many hints][4], the index hint is:
   101  
   102  ```sql
   103  SELECT * FROM t WITH (INDEX(myindex))
   104  ```
   105  
   106  [4]: https://msdn.microsoft.com/en-us/library/ms187373.aspx
   107  
   108  # Alternatives
   109  
   110  We want to address two questions:
   111  
   112  1. Change the semantics of the existing `@` syntax?
   113  
   114     We can leave `@` as it is or change `@` so that using it doesn't affect the
   115     semantics of the query - specifically, all table columns are accessible not
   116     just those in the index).  Using it simply forces the use of that index
   117     (potentially in conjunction with the primary index, as necessary).
   118  
   119  2. Add new syntax for index hints?
   120  
   121     We would add new syntax for forcing use of an index, and perhaps ignoring a
   122     set of indexes. The syntax we add must be part of the table clause so it will
   123     be usable when we have multiple tables or joins.
   124  
   125  # Proposal
   126  
   127  The current proposal is to do the following, in decreasing order of priority:
   128  
   129   1. change `@` as explained above: `table@foo` forces the use of index `foo`,
   130      errors out if the index does not exist.
   131   2. Add `table@{force_index=foo}` as an alias for `table@foo` (same behavior).
   132   3. Add a `no_index_join` option. When used alone (`table@{no_index_join}`), it
   133      directs the index selection to avoid all non-covering index. When used with
   134      `force_index` (`table@{force_index=index,no_index_join}`), it causes an
   135      error if the given index is non-covering.
   136   4. Add hints that tolerate missing indexes:
   137    * `table@{use_index=foo[,bar,...]}`: perform index selection among the
   138      specified indexes. Any index that doesn't exist is ignored. If none of the
   139      specified indexes exist, fall back to normal index selection.
   140    * `table@{ignore_index=foo[,bar,..]}`: do normal index selection but without
   141     considering the specified indexes.  Any index that doesn't exist is ignored. 
   142     
   143  We will hold off on 4 until we have a stronger case for their utility.