Thanks all, Yes, I will create a JIRA for this. Let me try out a simple reproducible steps. I run a full update statistics on the table and still see the 'table scan' plan for count(*), not push down to the coprocessor. If without an idex. The table has 10000000 rows, but very wide, contains some varchar(1000) columns, and many varchar(100) columns.
As Anoop suggested, I also use the showplan to see the 'coprocessor' plan, which goes to base table instead of the index. I think this deserve another JIRA. The base table is very wide, 100 columns, and the index is very thin, I am thinking go to index will be faster? Some users like to use 'select count(*)' to test performance of a database... And there will be some real user scenarios need to do a full count(*), so improvement is desired. Thanks, Ming -----邮件原件----- 发件人: Dave Birdsall [mailto:[email protected]] 发送时间: 2016年6月2日 23:38 收件人: [email protected] 主题: RE: select count(*) go to index or not? Hi, I have the same question as Qifan. I wonder why the push-down plan wasn't picked when there was no index. Would you be so kind as to produce a JIRA with this test case so we can investigate? Thanks, Dave -----Original Message----- From: Qifan Chen [mailto:[email protected]] Sent: Thursday, June 2, 2016 8:02 AM To: dev <[email protected]> Subject: Re: select count(*) go to index or not? 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
