Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
10.13.1.1
-
None
-
None
Description
Problem:
It is appeared that query optimizer mises dramatically for a fairly simple query:
SELECT * FROM Customer WHERE ID >= 0 ORDER BY ID -- optionally: FETCH NEXT 2 ROWS ONLY;
The "ID >= 0" predicate is expected to return almost entire table (true), so optimizer decides to utilize Table Scan to avoid having to resolve index row references, but it should take into account that then it need to order the result set (sort), which is a way more expensive operation. Especially if it's followed by, let's say, "FETCH NEXT 2 ROWS ONLY" which would explicitly indicate that there is no need to iterate through the entire table.
Steps to recreate:
CREATE TABLE Customer ( ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY, Name VARCHAR(1024) NOT NULL ); INSERT INTO Customer (Name) VALUES ('Alex'), ('Peter'), ('Bob'), ('Fred'); CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); SELECT * FROM Customer WHERE ID >= 0 ORDER BY ID; VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
Result:
Statement Name: null Statement Text: SELECT * FROM Customer WHERE ID >= 0 ORDER BY ID Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 4 Rows returned = 4 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=4 Number of rows output=4 Sort type=internal constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.97 optimizer estimated cost: 39.32 Source result set: Table Scan ResultSet for CUSTOMER at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=4 Number of rows visited=4 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: < Ordered nulls: false Unknown return value: true Negate comparison result: true optimizer estimated row count: 2.97 optimizer estimated cost: 39.32
^ Note that Table Scan is picked and not an index over ID.