You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by "Liu, Ming (Ming)" <mi...@esgyn.cn> on 2016/06/02 07:22:36 UTC
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
答复: select count(*) go to index or not?
Posted by "Liu, Ming (Ming)" <mi...@esgyn.cn>.
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:dave.birdsall@esgyn.com]
发送时间: 2016年6月2日 23:38
收件人: dev@trafodion.incubator.apache.org
主题: 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:qifan.chen@esgyn.com]
Sent: Thursday, June 2, 2016 8:02 AM
To: dev <de...@trafodion.incubator.apache.org>
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 <an...@esgyn.com> 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:ming.liu@esgyn.cn <mi...@esgyn.cn>]
> Sent: Thursday, June 2, 2016 12:23 AM
> To: dev@trafodion.incubator.apache.org
> 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
RE: select count(*) go to index or not?
Posted by Dave Birdsall <da...@esgyn.com>.
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:qifan.chen@esgyn.com]
Sent: Thursday, June 2, 2016 8:02 AM
To: dev <de...@trafodion.incubator.apache.org>
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 <an...@esgyn.com> 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:ming.liu@esgyn.cn <mi...@esgyn.cn>]
> Sent: Thursday, June 2, 2016 12:23 AM
> To: dev@trafodion.incubator.apache.org
> 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
Re: select count(*) go to index or not?
Posted by Qifan Chen <qi...@esgyn.com>.
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 <an...@esgyn.com> 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:ming.liu@esgyn.cn <mi...@esgyn.cn>]
> Sent: Thursday, June 2, 2016 12:23 AM
> To: dev@trafodion.incubator.apache.org
> 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
RE: select count(*) go to index or not?
Posted by Anoop Sharma <an...@esgyn.com>.
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:ming.liu@esgyn.cn <mi...@esgyn.cn>]
Sent: Thursday, June 2, 2016 12:23 AM
To: dev@trafodion.incubator.apache.org
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