[
https://issues.apache.org/jira/browse/TRAFODION-1546?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14974574#comment-14974574
]
Selvaganesan Govindarajan commented on TRAFODION-1546:
------------------------------------------------------
Plan for the upsert command with 3 indexes with multiple tuple list is shown
below; (When there is only one tuple flowing in, all the IM operations will be
non-vsbb)
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
15 . 16 root x 6.00E+001
1 14 15 tuple_flow 6.00E+001
2 13 14 nested_join 6.00E+000
9 12 13 merge_union 6.00E+000
10 11 12 blocked_union 2.00E+000
. . 11 trafodion_insert T12I3 1.00E+000
. . 10 trafodion_vsbb_delet T12I3 1.00E+000
5 8 9 merge_union 4.00E+000
6 7 8 blocked_union 2.00E+000
. . 7 trafodion_vsbb_upser T12I2 1.00E+000
. . 6 trafodion_vsbb_delet T12I2 1.00E+000
3 4 5 blocked_union 2.00E+000
. . 4 trafodion_vsbb_upser T12I1 1.00E+000
. . 3 trafodion_vsbb_delet T12I1 1.00E+000
. . 2 trafodion_merge T12 1.00E+000
. . 1 tuplelist 1.00E+001
T12I3 is a unique index and hence it can’t choose VSBB_UPSERT. The chosen plan
has no issue. With series of changes, we have ensured that this plan is chosen
for the upsert command when there are indexes. The problem is the merge
operator. The merge operates one row at a time. After processing one row, it
lets the scheduler to schedule other operators. The VSBB operators are unable
to see more than one queue entry in their down queue and hence it acts one row
at a time. The merge operator is not conducive to operate on more than one row
at time. The merge operator does 3 operations within one tcb.
1) Select rows given a batch of row keys evaluated from queue entries
2) When the row is found, do the match operation
3) When the row is not found, do the unmatched operation
To improve the upsert command performance the merge operator in the above plan
tree can be split further
Nested_join
/ \
/ \
/ \
/ \
Trafodion_merge VSSB_Upsert
Rowset
And introduce group by to sort the incoming rows and choose the last row to
insert or update the row. Without group by operator, it is possible to
introduce inconsistency between index and the table. The above tree will be
enabled when the upsert command is transformed into merge and it is not
available for generic merge sql statement. The group by would need to sort only
a rowset of rows when rows are upserted into the table using ODB. We might
consider enabling this feature using CQD. This requires change in the compiler
and hence a new sub-JIRA is created
> upsert into table with indexes performs slower than insert
> ----------------------------------------------------------
>
> Key: TRAFODION-1546
> URL: https://issues.apache.org/jira/browse/TRAFODION-1546
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Selvaganesan Govindarajan
> Original Estimate: 1m
> Remaining Estimate: 1m
>
> The plan for upsert and insert are different, but both plans have vsbb
> operations enabled for index maintenance. But, it is observed that vsbb
> feature kicks in for insert while the upsert command inserts one row at a
> time in the index. Hence upsert command performs slower than the insert
> command.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
