phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marcin Januszkiewicz <katamaran...@gmail.com>
Subject Re: Indexes not used when ordering by primary key.
Date Mon, 30 Oct 2017 15:46:39 GMT
On a possibly related note, how does Phoenix choose which index to use
if there multiple indexes are applicable? Right now it seems that the
one with the lower sequence number is used. If this is the case,
similar optimizations could be made to choose the index that will scan
over a smaller dataset.

On Sat, Oct 14, 2017 at 8:26 AM, James Taylor <jamestaylor@apache.org> wrote:
> 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 <jamestaylor@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
>> <katamaran666@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
>>> <katamaran666@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.
>>
>>
>

Mime
View raw message