It seems that that the push-down plan should be used even before the index is created. This could due to the reason that the parallel plan was generated in the optimizer which prevents a transformation to the push-down plan later on. Seems like a bug.
Thanks --Qifan On Thu, Jun 2, 2016 at 8:27 AM, Anoop Sharma <[email protected]> wrote: > RE: select count(*) go to index or not? > > when explain shows 'hbase_aggr', then aggregation has been pushed down to > > hbase coprocessor. > > But explain currently doesn't show the table it has pushed it down to. > > This is something we should enhance so the table or index name is also > > add and shown as part of coproc operator. > > In the meantime, if you do a showplan, you can see where it has > > been pushed down to. It will show something like: > > For ComTdbHbaseAccess : > > accessType_ = COPROC_ > > accessDetail_ = EX_HBASE_COPROC_AGGR > > *tableName_ = TRAFODION.**SCH.T* > > -----Original Message----- > From: Liu, Ming (Ming) [mailto:[email protected] <[email protected]>] > Sent: Thursday, June 2, 2016 12:23 AM > To: [email protected] > Subject: select count(*) go to index or not? > > Hi, all, > > I have a table which have 100 columns, and PK on c1, c2 , c3. When I do a > count(*), it generate such a plan for it: > > >>explain options 'f' select count(*) from BLTEST192; > > LC RC OP OPERATOR OPT DESCRIPTION CARD > > ---- ---- ---- -------------------- -------- -------------------- > --------- > > 4 . 5 root > 1.00E+000 > > 3 . 4 sort_partial_aggr_ro > 1.00E+000 > > 2 . 3 esp_exchange 1:16(hash2) > 5.00E+006 > > 1 . 2 sort_partial_aggr_le > 5.00E+006 > > . . 1 trafodion_scan BLTEST192 > 1.00E+007 > > Then I create an index on column c4, and Trafodion gives me another plan, > but I cannot understand it: > > >>explain options 'f' select count(*) from bltest192; > > LC RC OP OPERATOR OPT DESCRIPTION CARD > > ---- ---- ---- -------------------- -------- -------------------- > --------- > > 1 . 2 root > 1.00E+000 > > . . 1 hbase_aggr > 1.00E+000 > > The second one is much faster. So my question is: when there is an idex, > Trafodion will put the aggregation down to the index coprocessor or the > base table's coprocessor? I am hoping when do count(*), it go to the index. > But from these two plans, I cannot tell. Maybe in the second case, it just > push down the aggregation to the coprocessor. > > So could someone help to clarify here? > > Thanks in advance. > > Ming > -- Regards, --Qifan
