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
