phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-4288) Indexes not used when ordering by primary key
Date Tue, 28 Nov 2017 02:13:01 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-4288?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16267971#comment-16267971
] 

ASF GitHub Bot commented on PHOENIX-4288:
-----------------------------------------

Github user JamesRTaylor commented on a diff in the pull request:

    https://github.com/apache/phoenix/pull/281#discussion_r153378386
  
    --- Diff: phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java ---
    @@ -91,8 +91,23 @@ public QueryPlan optimize(PhoenixStatement statement, SelectStatement
select, Co
         }
         
         public QueryPlan optimize(QueryPlan dataPlan, PhoenixStatement statement, List<?
extends PDatum> targetColumns, ParallelIteratorFactory parallelIteratorFactory) throws
SQLException {
    -        List<QueryPlan>plans = getApplicablePlans(dataPlan, statement, targetColumns,
parallelIteratorFactory, true);
    -        return plans.get(0);
    +        List<QueryPlan> plans = getApplicablePlans(dataPlan, statement, targetColumns,
parallelIteratorFactory, false);
    +        if (plans.size() == 1) {
    +            return plans.get(0);
    +        }
    +
    +        // Get the best plan based on their costs. Costs will be ZERO if stats are not
    +        // available, thus the first plan will be returned.
    +        Cost minCost = null;
    +        QueryPlan bestPlan = null;
    +        for (QueryPlan plan : plans) {
    +            Cost cost = plan.getCost();
    +            if (minCost == null || cost.compareTo(minCost) < 0) {
    +                minCost = cost;
    +                bestPlan = plan;
    +            }
    +        }
    +        return bestPlan;
    --- End diff --
    
    QueryOptimize.optimize() is not the only entry point to the optimizer. For example, DeleteCompiler
calls both getApplicablePlans() and getBestPlan(). I think it'd be best if you hid this logic
at the end of the private getApplicablePlans since all calls channel through that. That way
all optimizer decisions will use cost if it's enabled. You could conditional order the plans
based on their cost if cost-based is enabled here:
    
                return hintedPlan != null ? plans : isCostBasedEnabled ? orderPlansBasedOnCost(plans)
: orderPlansBestToWorst(select, plans, stopAtBestPlan);



> Indexes not used when ordering by primary key
> ---------------------------------------------
>
>                 Key: PHOENIX-4288
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-4288
>             Project: Phoenix
>          Issue Type: Sub-task
>            Reporter: Marcin Januszkiewicz
>            Assignee: Maryann Xue
>              Labels: CostBasedOptimization
>
> We have a table
> CREATE TABLE t (
>   rowkey VARCHAR PRIMARY KEY,
>   c1 VARCHAR,
>   c2 VARCHAR
> )
> which we want to query by doing partial matches on c1, and keep the ordering of the source
table:
> SELECT rowkey, c1, c2 FROM t where c1 LIKE 'X0%' ORDER BY rowkey;
> We expect most queries to select a small subset of the table, so we create an index to
speed up searches:
> CREATE LOCAL INDEX t_c1_ix ON t (c1);
> However, this index will not be used since Phoenix will always choose not to resort the
data.
> In our actual use case, adding index hints is not a practical solution.
> See also discussion at:
> https://lists.apache.org/thread.html/26ab58288eb811d2f074c3f89067163d341e5531fb581f3b2486cf43@%3Cuser.phoenix.apache.org%3E



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message