phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Indexes not used when ordering by primary key.
Date Sat, 14 Oct 2017 06:26:42 GMT
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