Re: SELECT + ORDER BY vs self-join

[Marcin Januszkiewicz]

Sorry, I forgot to include the DDL for the index:
CREATE LOCAL INDEX ix_0 ON traces (UPPER(number)) INCLUDE (time, +
some other columns used for filtering).
I'm going to file a JIRA with the details, but local indexes are being
used in both cases.
My suspicion is that the first query will attempt to load all 30M rows
of data from HBase and then sort it, and the second will only sort 30M
(rowkey, time) pairs.

On Tue, Oct 31, 2017 at 8:02 AM, Sergey Soldatov
 wrote:
> I agree with James that this happens because the index was not involved
> because it doesn't cover all columns. I believe that in the second case, the
> RHT  is using the index to create a list of rowkeys and they are used for
> point lookups by skipscan.
>
> bq. When is using the self-join a worse choice than the simple select?
>
> Hash join has it's own limitations:
> 1.  RHT is supposed to be small, so it's better to keep LIMIT small (far
> less than 30 mil).
> 2.  Client is always involved to collect data from RHT, build the hash join
> cache and send it to all RSes.
>
>
> bq. Is there a better way to construct this query?
>
> Using local index may help in this case.
>
> Thanks,
> Sergey
>
>
> On Mon, Oct 30, 2017 at 11:26 PM, James Taylor 
> wrote:
>>
>> Please file a JIRA and include the explain plan for each of the queries. I
>> suspect your index is not being used in the first query due to the selection
>> of all the columns. You can try hinting the query to force your index to be
>> used. See https://phoenix.apache.org/secondary_indexing.html#Index_Usage
>>
>> Thanks,
>> James
>>
>> On Mon, Oct 30, 2017 at 7:02 AM, Marcin Januszkiewicz
>>  wrote:
>>>
>>> We have a wide table with 100M records created with the following DDL:
>>>
>>> CREATE TABLE traces (
>>>   rowkey VARCHAR PRIMARY KEY,
>>>   time VARCHAR,
>>>   number VARCHAR,
>>>   +40 more columns)
>>>
>>> We want to select a large (~30M records) subset of this data with the
>>> query:
>>>
>>> SELECT *all columns*
>>>   FROM traces
>>>   WHERE (UPPER(number) LIKE 'PO %')
>>>   ORDER BY time DESC, ROWKEY
>>>   LIMIT 101;
>>>
>>> This times out after 15 minutes and puts a huge load on our cluster.
>>> We have an alternate way of selecting this data:
>>>
>>> SELECT t.rowkey, *all columns*
>>> FROM TRACES t
>>> JOIN (
>>>   SELECT rowkey
>>>   FROM TRACES
>>>   WHERE (UPPER(number) LIKE 'PO %')
>>>   ORDER BY time DESC, ROWKEY
>>>   LIMIT 101
>>> ) ix
>>> ON t.ROWKEY = ix.ROWKEY
>>> order by t.ROWKEY;
>>>
>>> Which completes in just under a minute.
>>> Is there a better way to construct this query?
>>> When is using the self-join a worse choice than the simple select?
>>> Given that we have a functional index on UPPER(number), could this
>>> potentially be a statistics-based optimizer decision?
>>>
>>> --
>>> Pozdrawiam,
>>> Marcin Januszkiewicz
>>
>>
>

Re: SELECT + ORDER BY vs self-join

[Sergey Soldatov]

I agree with James that this happens because the index was not involved
because it doesn't cover all columns. I believe that in the second case,
the RHT  is using the index to create a list of rowkeys and they are used
for point lookups by skipscan.

bq. When is using the self-join a worse choice than the simple select?

Hash join has it's own limitations:
1.  RHT is supposed to be small, so it's better to keep LIMIT small (far
less than 30 mil).
2.  Client is always involved to collect data from RHT, build the hash join
cache and send it to all RSes.


bq. Is there a better way to construct this query?

Using local index may help in this case.

Thanks,
Sergey


On Mon, Oct 30, 2017 at 11:26 PM, James Taylor 
wrote:

> Please file a JIRA and include the explain plan for each of the queries. I
> suspect your index is not being used in the first query due to the
> selection of all the columns. You can try hinting the query to force your
> index to be used. See https://phoenix.apache.org/secondary_indexing.html#
> Index_Usage
>
> Thanks,
> James
>
> On Mon, Oct 30, 2017 at 7:02 AM, Marcin Januszkiewicz <
> januszkiewicz.mar...@gmail.com> wrote:
>
>> We have a wide table with 100M records created with the following DDL:
>>
>> CREATE TABLE traces (
>>   rowkey VARCHAR PRIMARY KEY,
>>   time VARCHAR,
>>   number VARCHAR,
>>   +40 more columns)
>>
>> We want to select a large (~30M records) subset of this data with the
>> query:
>>
>> SELECT *all columns*
>>   FROM traces
>>   WHERE (UPPER(number) LIKE 'PO %')
>>   ORDER BY time DESC, ROWKEY
>>   LIMIT 101;
>>
>> This times out after 15 minutes and puts a huge load on our cluster.
>> We have an alternate way of selecting this data:
>>
>> SELECT t.rowkey, *all columns*
>> FROM TRACES t
>> JOIN (
>>   SELECT rowkey
>>   FROM TRACES
>>   WHERE (UPPER(number) LIKE 'PO %')
>>   ORDER BY time DESC, ROWKEY
>>   LIMIT 101
>> ) ix
>> ON t.ROWKEY = ix.ROWKEY
>> order by t.ROWKEY;
>>
>> Which completes in just under a minute.
>> Is there a better way to construct this query?
>> When is using the self-join a worse choice than the simple select?
>> Given that we have a functional index on UPPER(number), could this
>> potentially be a statistics-based optimizer decision?
>>
>> --
>> Pozdrawiam,
>> Marcin Januszkiewicz
>>
>
>

Re: SELECT + ORDER BY vs self-join

[Maryann Xue]

I suspect this problem is similar to PHOENIX-4288.
On Mon, Oct 30, 2017 at 11:26 PM James Taylor 
wrote:

> Please file a JIRA and include the explain plan for each of the queries. I
> suspect your index is not being used in the first query due to the
> selection of all the columns. You can try hinting the query to force your
> index to be used. See
> https://phoenix.apache.org/secondary_indexing.html#Index_Usage
>
> Thanks,
> James
>
> On Mon, Oct 30, 2017 at 7:02 AM, Marcin Januszkiewicz <
> januszkiewicz.mar...@gmail.com> wrote:
>
>> We have a wide table with 100M records created with the following DDL:
>>
>> CREATE TABLE traces (
>>   rowkey VARCHAR PRIMARY KEY,
>>   time VARCHAR,
>>   number VARCHAR,
>>   +40 more columns)
>>
>> We want to select a large (~30M records) subset of this data with the
>> query:
>>
>> SELECT *all columns*
>>   FROM traces
>>   WHERE (UPPER(number) LIKE 'PO %')
>>   ORDER BY time DESC, ROWKEY
>>   LIMIT 101;
>>
>> This times out after 15 minutes and puts a huge load on our cluster.
>> We have an alternate way of selecting this data:
>>
>> SELECT t.rowkey, *all columns*
>> FROM TRACES t
>> JOIN (
>>   SELECT rowkey
>>   FROM TRACES
>>   WHERE (UPPER(number) LIKE 'PO %')
>>   ORDER BY time DESC, ROWKEY
>>   LIMIT 101
>> ) ix
>> ON t.ROWKEY = ix.ROWKEY
>> order by t.ROWKEY;
>>
>> Which completes in just under a minute.
>> Is there a better way to construct this query?
>> When is using the self-join a worse choice than the simple select?
>> Given that we have a functional index on UPPER(number), could this
>> potentially be a statistics-based optimizer decision?
>>
>> --
>> Pozdrawiam,
>> Marcin Januszkiewicz
>>
>
>

Re: SELECT + ORDER BY vs self-join

[James Taylor]

Please file a JIRA and include the explain plan for each of the queries. I
suspect your index is not being used in the first query due to the
selection of all the columns. You can try hinting the query to force your
index to be used. See
https://phoenix.apache.org/secondary_indexing.html#Index_Usage

Thanks,
James

On Mon, Oct 30, 2017 at 7:02 AM, Marcin Januszkiewicz <
januszkiewicz.mar...@gmail.com> wrote:

> We have a wide table with 100M records created with the following DDL:
>
> CREATE TABLE traces (
>   rowkey VARCHAR PRIMARY KEY,
>   time VARCHAR,
>   number VARCHAR,
>   +40 more columns)
>
> We want to select a large (~30M records) subset of this data with the
> query:
>
> SELECT *all columns*
>   FROM traces
>   WHERE (UPPER(number) LIKE 'PO %')
>   ORDER BY time DESC, ROWKEY
>   LIMIT 101;
>
> This times out after 15 minutes and puts a huge load on our cluster.
> We have an alternate way of selecting this data:
>
> SELECT t.rowkey, *all columns*
> FROM TRACES t
> JOIN (
>   SELECT rowkey
>   FROM TRACES
>   WHERE (UPPER(number) LIKE 'PO %')
>   ORDER BY time DESC, ROWKEY
>   LIMIT 101
> ) ix
> ON t.ROWKEY = ix.ROWKEY
> order by t.ROWKEY;
>
> Which completes in just under a minute.
> Is there a better way to construct this query?
> When is using the self-join a worse choice than the simple select?
> Given that we have a functional index on UPPER(number), could this
> potentially be a statistics-based optimizer decision?
>
> --
> Pozdrawiam,
> Marcin Januszkiewicz
>

Re: Indexes not used when ordering by primary key.

[James Taylor]

You can see the logic in QueryOptimizer.orderPlansBestToWorst():
- choose plan that optimizes out an ORDER BY
- otherwise, choose plan that uses the most leading columns in the primary
key (i.e. smallest range scan)
- if multiple options, choose the one in which the GROUP BY can be done in
place
- choose smaller table (index) over larger table

We're looking at adding some simple cost-based decisions to improve this as
we know how many bytes will be scanned for each possible table/index being
used. This won't be perfect, but it'll help most cases (and you can always
fallback to hints if you know better).

Thanks,
James

On Mon, Oct 30, 2017 at 8:46 AM, Marcin Januszkiewicz <
katamaran...@gmail.com> wrote:

> 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 
> 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 
> 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
> >>  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
> >>>  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.
> >>
> >>
> >
>