In the below lava operator tree, there is a big difference in in the logical estimated IO and actual IO (l:874794 el:556). I'm wondering what kind of actions can contribute to this logical Io during an insert and how we can reduce this difference to improve the performance.
============================================================
The type of query is INSERT.
4 operator(s) under root
|ROOT:EMIT Operator (VA = 4)
|
| |INSERT Operator (VA = 3)
| | The update mode is direct.
| |
| | |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Left Outer Join)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | #step1
| | | | leb
| | | | Table Scan.
| | | | Forward Scan.
| | | | Positioning at start of table.
| | | | Using I/O Size 32 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | # test1234
| | | | lsp
| | | | Using Clustered Index.
| | | | Index : test1234_idx
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Keys are:
| | | | cusip_id ASC
| | | | wi_in ASC
| | | | Using I/O Size 4 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | TO TABLE
| | calc_data
| | Using I/O Size 4 Kbytes for data pages.
Total estimated I/O cost for statement 1 (at line 1): 953911.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
==================== Lava Operator Tree ====================
Emit
(VA = 4)
r:128879 er:128879
cpu: 5700
/
Insert
calc_data
(VA = 3)
r:128879 er:128879
l:874794 el:556
p:0 ep:556
/
NestLoopJoin
Left Outer Join
(VA = 2)
r:128879 er:128879
/ \
TableScan IndexScan
#step1 (leb) test1234 (lsp)
(VA = 0) (VA = 1)
r:128879 er:128879 r:128879 er:128879
l:4957 el:4957 l:386637 el:386637
p:0 ep:622 p:0 ep:2492