Thanks Aditya. Looks like not much can be done until 0.9. The work around recommended there, cannot be used in views I guess.
On Feb 4, 2015, at 5:24 PM, Aditya <[email protected]> wrote: > This is because a PROJECT is getting introduced between the Scan and Filter > which cause the pushdown optimization rule to not trigger. > > This is a known limitation [1]. > > [1] https://issues.apache.org/jira/browse/DRILL-1651 > > On Wed, Feb 4, 2015 at 4:33 PM, Sudhakar Thota <[email protected]> wrote: > Aditya, > > I tried both with cast without cast. > > I have executed the statement you sent, it is not coming back, I believe it > is doing full-table-scan. I will let you know the time it took after it comes > back. But before doing that I ran the plan. Please take a look. I don’t see > it limiting with start key and stop keys. > > 0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> select > . . . . . . . . . . . . . . . . . . . . . . .> > trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from > . . . . . . . . . . . . . . . . . . . . . . .> maprfs.views.view_maprdb3 > where trans_id ='7654'; > +------------+------------+------------+------------+------------+------------+------------+------------+ > | trans_id | trans_date | device_id | game | trans_tpe | trans_amt > | state | country | > +------------+------------+------------+------------+------------+------------+------------+------------+ > > > 0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> explain plan for select > . . . . . . . . . . . . . . . . . . . . . . .> > trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from > . . . . . . . . . . . . . . . . . . . . . . .> maprfs.views.view_maprdb3 > where trans_id ='7654'; > +------------+------------+ > | text | json | > +------------+------------+ > | 00-00 Screen > 00-01 Project(trans_id=[$0], trans_date=[$1], device_id=[$2], game=[$3], > trans_tpe=[$4], trans_amt=[$5], state=[$6], country=[$7]) > 00-02 UnionExchange > 01-01 SelectionVectorRemover > 01-02 Filter(condition=[=($0, '7654')]) > 01-03 Project(trans_id=[CAST($0):VARCHAR(20) CHARACTER SET > "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], > trans_date=[CAST(CAST(ITEM($1, 'trans_date')):DATE):DATE NOT NULL], > device_id=[CAST(CAST(ITEM($1, 'device_id')):INTEGER):INTEGER NOT NULL], > game=[CAST(CAST(ITEM($1, 'game')):VARCHAR(50) CHARACTER SET "ISO-8859-1" > COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(50) CHARACTER SET "ISO-8859-1" > COLLATE "ISO-8859-1$en_US$primary" NOT NULL], trans_tpe=[CAST(CAST(ITEM($1, > 'trans_type')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" NOT NULL], trans_amt=[CAST(CAST(ITEM($1, > 'trans_amt')):INTEGER):INTEGER NOT NULL], state=[CAST(CAST(ITEM($1, > 'state')):VARCHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary"):VARCHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" NOT NULL], country=[CAST(CAST(ITEM($1, > 'country')):VARCHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary"):VARCHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" NOT NULL]) > 01-04 Scan(groupscan=[HBaseGroupScan > [HBaseScanSpec=HBaseScanSpec [tableName=trans, startRow=null, stopRow=null, > filter=null], columns=[`row_key`, `cf`.`trans_date`, `cf`.`device_id`, > `cf`.`game`, `cf`.`trans_type`, `cf`.`trans_amt`, `cf`.`state`, > `cf`.`country`]]]) > | { > "head" : { > "version" : 1, > "generator" : { > "type" : "ExplainHandler", > "info" : "" > }, > "type" : "APACHE_DRILL_PHYSICAL", > "options" : [ ], > "queue" : 0, > "resultMode" : "EXEC" > }, > "graph" : [ { > "pop" : "hbase-scan", > "@id" : 65540, > "hbaseScanSpec" : { > "tableName" : "trans", > "startRow" : "", > "stopRow" : "", > "serializedFilter" : null > }, > "storage" : { > "type" : "hbase", > "config" : { > "hbase.table.namespace.mappings" : "*:/user/epeck/tables" > }, > "size.calculator.enabled" : false, > "enabled" : true > }, > "columns" : [ "`row_key`", "`cf`.`trans_date`", "`cf`.`device_id`", > "`cf`.`game`", "`cf`.`trans_type`", "`cf`.`trans_amt`", "`cf`.`state`", > "`cf`.`country`" ], > "cost" : 2.8311552E7 > }, { > "pop" : "project", > "@id" : 65539, > "exprs" : [ { > "ref" : "`trans_id`", > "expr" : "cast( (`row_key` ) as VARCHAR(20) )" > }, { > "ref" : "`trans_date`", > "expr" : "cast( (cast( (`cf`.`trans_date` ) as DATE ) ) as DATE )" > }, { > "ref" : "`device_id`", > "expr" : "cast( (cast( (`cf`.`device_id` ) as INT ) ) as INT )" > }, { > "ref" : "`game`", > "expr" : "cast( (cast( (`cf`.`game` ) as VARCHAR(50) ) ) as VARCHAR(50) > )" > }, { > "ref" : "`trans_tpe`", > "expr" : "cast( (cast( (`cf`.`trans_type` ) as VARCHAR(20) ) ) as > VARCHAR(20) )" > }, { > "ref" : "`trans_amt`", > "expr" : "cast( (cast( (`cf`.`trans_amt` ) as INT ) ) as INT )" > }, { > "ref" : "`state`", > "expr" : "cast( (cast( (`cf`.`state` ) as VARCHAR(2) ) ) as VARCHAR(2) > )" > }, { > "ref" : "`country`", > "expr" : "cast( (cast( (`cf`.`country` ) as VARCHAR(2) ) ) as > VARCHAR(2) )" > } ], > "child" : 65540, > "initialAllocation" : 1000000, > "maxAllocation" : 10000000000, > "cost" : 2.8311552E7 > }, { > "pop" : "filter", > "@id" : 65538, > "child" : 65539, > "expr" : "equal(`trans_id`, '7654') ", > "initialAllocation" : 1000000, > "maxAllocation" : 10000000000, > "cost" : 4246732.8 > }, { > "pop" : "selection-vector-remover", > "@id" : 65537, > "child" : 65538, > "initialAllocation" : 1000000, > "maxAllocation" : 10000000000, > "cost" : 4246732.8 > }, { > "pop" : "union-exchange", > "@id" : 2, > "child" : 65537, > "initialAllocation" : 1000000, > "maxAllocation" : 10000000000, > "cost" : 4246732.8 > }, { > "pop" : "project", > "@id" : 1, > "exprs" : [ { > "ref" : "`trans_id`", > "expr" : "`trans_id`" > }, { > "ref" : "`trans_date`", > "expr" : "`trans_date`" > }, { > "ref" : "`device_id`", > "expr" : "`device_id`" > }, { > "ref" : "`game`", > "expr" : "`game`" > }, { > "ref" : "`trans_tpe`", > "expr" : "`trans_tpe`" > }, { > "ref" : "`trans_amt`", > "expr" : "`trans_amt`" > }, { > "ref" : "`state`", > "expr" : "`state`" > }, { > "ref" : "`country`", > "expr" : "`country`" > } ], > "child" : 2, > "initialAllocation" : 1000000, > "maxAllocation" : 10000000000, > "cost" : 4246732.8 > }, { > "pop" : "screen", > "@id" : 0, > "child" : 1, > "initialAllocation" : 1000000, > "maxAllocation" : 10000000000, > "cost" : 4246732.8 > } ] > } | > +------------+------------+ > 1 row selected (0.152 seconds) > 0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> > > Thanks > Sudhakar Thota > > > On Feb 4, 2015, at 4:18 PM, Aditya <[email protected]> wrote: > >> select >> trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from >> maprfs.views.view_maprdb3 where trans_id ='7654'; > >
