If multiple paths are available after the rule-based path selection, OceanBase Database calculates the cost of each path and selects the one with the lowest cost.
The cost model of OceanBase Database takes into account CPU costs (such as the CPU utilization for processing a predicate) and I/O costs (such as the costs for reading macroblocks and microblocks sequentially or randomly). The CPU and I/O costs are summed to get total costs.
OceanBase Database shows the cost of each access path in the execution plan. Here is an example:
obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, INDEX k1(b));
Query OK, 0 rows affected
/*Cost of primary access path*/
obclient>EXPLAIN SELECT/*+INDEX(t1 PRIMARY)*/ * FROM t1 WHERE b < 10;
+-----------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------+
| ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |200 |622 |
===================================
Outputs & filters:
-------------------------------------
0 - output([t1.a], [t1.b], [t1.c]), filter([t1.b < 10]),
access([t1.b], [t1.a], [t1.c]), partitions(p0)
/*Cost of path k1*/
obclient> EXPLAIN SELECT/*+INDEX(t1 k1)*/ * FROM t1 WHERE b < 10;
+--------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------+
| =====================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-------------------------------------
|0 |TABLE SCAN|t1(k1)|200 |1114|
=====================================
Outputs & filters:
-------------------------------------
0 - output([t1.a], [t1.b], [t1.c]), filter(nil),
access([t1.b], [t1.a], [t1.c]), partitions(p0)
The cost of an access path includes the cost of path scanning and the cost of table access by index primary key. The latter part is zero if table access by index primary key is not required.
In OceanBase Database, the cost of an access path depends on many factors, such as the number of scanned rows, rows returned by table access by index primary key operations, projected columns, and predicates. Among them, the cost largely depends on the number of rows. The following example analyzes the costs in terms of the number of rows.
Cost of scanning the access path
The cost is proportional to the number of scanned rows. Technically, an execution takes more time if more rows are scanned. For an access path, the query range determines the scan range or the number of rows to be scanned. The query range also indicates that the scanning is performed sequentially.
Cost of table access by index primary key
The cost is also positively correlated to the number of rows returned by table access by index primary key operations. The more the rows, the longer the execution time. Here, the rows returned by table access by index primary key operations are rows that satisfy all predicates executable on the index. Because the scan for table access by index primary key operations is performed randomly, the cost is much higher than that of scanning by query range.
To evaluate the performance of an access path, you can compare the number of rows returned by scanning by query range and the number of rows returned by table access by index primary key operations. You can execute an SQL statement to obtain the number of rows.
In the following example, for the query SELECT * FROM t1 WHERE c2 > 20 AND c2 < 800 AND c3 < 200, the access path by index k1 first needs an execution plan display to obtain the predicates for extracting the query range. The predicate c2 > 20 AND c2 < 800 is used to extract the query range, and the predicate c3 < 200 is taken as the one executed before the table access by index primary key operation. You can use the following two queries to obtain the number of rows extracted for the query range and the number of rows returned by the table access by index primary key operation.
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT, c4 INT, c5 INT, INDEX k1(c2,c3));
Query OK, 0 rows affected
obclient> EXPLAIN EXTENDED_NOADDR SELECT/*+INDEX(t1 k1)*/ * FROM t1 WHERE
c2 > 20 AND c2 < 800 AND c3 < 200;
+--------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------+
| =====================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-------------------------------------
|0 |TABLE SCAN|t1(k1)|156 |1216|
=====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4], [t1.c5]), filter([t1.c3 < 200]),
access([t1.c2], [t1.c3], [t1.c1], [t1.c4], [t1.c5]), partitions(p0),
is_index_back=true, filter_before_indexback[true],
range_key([t1.c2], [t1.c3], [t1.c1]), range(20,MAX,MAX ; 800,MIN,MIN),
range_cond([t1.c2 > 20], [t1.c2 < 800])
/* The number of rows scanned with query range */
obclient> SELECT/*+INDEX(t1 k1)*/ COUNT(*) FROM t1 WHERE c2 > 20 AND c2 < 800;
+----------+
| count(*) |
+----------+
| 779 |
+----------+
1 row in set
/* The number of rows returned by the table access by index primary key operation */
obclient> SELECT/*+INDEX(t1 k1)*/ COUNT(*) FROM t1 WHERE c2 > 20 AND c2 < 800
AND c3 < 200;
+----------+
| count(*) |
+----------+
| 179 |
+----------+
1 row in set