Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Optimization/ Checking the Implicit Conversion Performance
Updated on 2024-06-03 GMT+08:00
View PDF
Share

Checking the Implicit Conversion Performance

In some scenarios, implicit data type conversion may cause performance problems. See the following scenario:

SET enable_fast_query_shipping = off;
CREATE TABLE t1(c1 VARCHAR, c2 VARCHAR);
CREATE INDEX on t1(c1);
EXPLAIN verbose SELECT * FROM t1 WHERE c1 = 10;

The execution plan of the preceding query is as follows.

The data type of c1 is varchar. When the filter criterion is c1 = 10, the optimizer implicitly converts the data type of c1 to bigint by default. As a result, the following two consequences occur:

  • DN tailoring is not allowed. The plan is delivered to all DNs for execution.
  • The Index Scan mode cannot be used to scan data in the plan.

These may cause performance problems.

After knowing the causes, you can rewrite the SQL statements. In the preceding scenario, you only need to convert the constant display in the filter criteria to the varchar type. The result is as follows:

EXPLAIN verbose SELECT * FROM t1 WHERE c1 = 10::varchar;

To identify the performance impact of implicit type conversion in advance, GaussDB provides a GUC option: check_implicit_conversions. After this parameter is enabled, the system checks the index columns that are implicitly converted in the query in the path generation phase. If no candidate index scan path is generated for the index columns, an error message is displayed. For example:

SET check_implicit_conversions = on;
SELECT * FROM t1 WHERE c1 = 10;
ERROR:  There is no optional index path for index column: "t1"."c1".
  • The check_implicit_conversions parameter is used only to check for potential performance problems caused by implicit type conversion. In the formal production environment, set this parameter to off (default value) to disable it.
  • When enabling check_implicit_conversions, you must disable enable_fast_query_shipping. Otherwise, you cannot view the result of restoring the implicit type conversion.
  • A candidate path of a table may include multiple possible data scan modes such as sequential scan and index scan. A table scan mode used in the final execution plan is determined by the cost of the execution plan. Therefore, even if a candidate path for index scan is generated, other scan modes may also be used in the final execution plan.
Parent topic: SQL Optimization