Couple of follow up comments: - if you use c1=‘X0’ the index should be used without a hint, because it’s still ordered by the PK when using index. - this wouldn’t necessarily be the case for c1 LIKE 'X0%'.
On Fri, Oct 13, 2017 at 8:33 PM James Taylor <jamestay...@apache.org> wrote: > Yes, this is expected behavior. Phoenix can either optimize based on the > filter in the WHERE clause or the ORDER BY. Since it's not cost based, > Phoenix always chooses to optimize out the ORDER BY (because in general > it's more expensive and it doesn't know how much data will be filtered out > by the WHERE clause). By using the data table, we know that rows are > already returned in PK order, so there's no reordering required. The hint > is available to override this decision. > > It wouldn't be difficult to introduce some simple cost-based decisions if > statistics collection is enabled. In that case, we can get an estimate at > compile-time on how much data would be scanned when the index is used. If > the amount is low enough, the optimizer could choose to use the index and > reorder the results. > > Please file a JIRA and we can discuss further. > > Thanks, > James > > On Fri, Oct 13, 2017 at 6:47 AM, Marcin Januszkiewicz < > katamaran...@gmail.com> wrote: > >> Small correction the index is local: >> CREATE LOCAL INDEX t_c1_ix ON t (c1); >> >> On Fri, Oct 13, 2017 at 3:43 PM, Marcin Januszkiewicz >> <katamaran...@gmail.com> wrote: >> > Hi, >> > >> > we have some data in a phoenix table that we always want to fetch in >> > the order determined by the primary key: >> > >> > CREATE TABLE t ( >> > rowkey VARCHAR PRIMARY KEY, >> > c1 VARCHAR, >> > c2 VARCHAR, >> > ) >> > >> > SELECT rowkey, c1, c2 FROM t where c1 LIKE 'X0%' ORDER BY rowkey; >> > >> > We wanted to speed up searches using an index on c1: >> > >> > CREATE INDEX t_c1_ix ON t (c1); >> > >> > However, this index is only used if we drop the ORDER BY clause or use >> > a query hint. If we sort by any other field, such as c2, the index >> > will be used. >> > >> > Is this expected behavior? Is there any way of influencing phoenix to >> > use the indexes without using an index hint? The actual table has more >> > columns & indexes, and queries are creating programatically. Adding >> > code which would decide which hint to generate would be a little >> > problematic. >> > >
