Checking the Implicit Conversion Performance

In some scenarios, implicit data type conversion may cause performance problems. For example:

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, you can use the GUC parameter 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".
Please check for potential performance problem.

• 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 scanning and index scanning. 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.