sql: support hints for which index to use

[RaduBerinde]

Selecting from table@index has different semantics than selecting from the table - we can only select the columns in the index and SELECT * resolves to only those columns. This feature is mildly useful for debugging but doesn't do much for a user.

I propose changing this to have the same semantics as the regular select, except that it forces index selection to choose that particular index. This would provide an easy workaround when the index selection algorithm doesn't identify the best index.

@petermattis - any objections?

[petermattis]

table@index currently guarantees you're not having to lookup the row using the primary key (i.e. perform an index join). That seems nice, though I agree it would be good if table@index could be used to force index selection while still having the same semantics as selecting from table.

The current semantics for table@index were implemented so that it treats the index as a table itself (which, if you squint, it kind of is). Once we have joins, you would be able to do something like table@index JOIN table to allow access to any of the table columns.

[RaduBerinde]

We would still get that guarantee whenever we only ask for columns in that index. The difference is that when we do need some of the other columns, it would do an index-join instead of erroring out.

[RaduBerinde]

MySQL supports a "hint clause" like USE INDEX/IGNORE INDEX. Postgres does not support hints.

I think we should support hints. I don't have a preference on the syntax or whether we should also keep the current @ functionality.