The common character data types used by Postgresql are char, varchar and text, where char is a fixed length type and varchar and text are variable length types. When these three types are compared, implicit type conversion is performed. This conversion may cause the index to be unusable and affect the SQL execution plan. The following shows the conversion rules between different character data types of Postgresql in the form of examples.
1, Create test data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 create table test_char(id char(9),desc_info text); create table test_varchar(id varchar(9),desc_info text); create table test_text(id text,desc_info text); insert into test_char select generate_series(100001,200000),repeat('a',100); insert into test_varchar select generate_series(100001,200000),repeat('a',100); insert into test_text select generate_series(100001,200000),repeat('a',100); create index ind_test_char on test_char(id); create index ind_test_varchar on test_varchar(id); create index ind_test_text on test_text(id); analyze test_char; analyze test_varchar; analyze test_text;
2, Create SQL cursor
1 2 3 4 5 6 7 8 prepare test_char_bind_varchar(varchar) as select * from test_char where id=$1; prepare test_char_bind_text(text) as select * from test_char where id=$1; prepare test_varchar_bind_char(char) as select * from test_varchar where id=$1; prepare test_text_bind_char(char) as select * from test_text where id=$1; prepare test_varchar_bind_text(text) as select * from test_varchar where id=$1; prepare test_text_bind_varchar(varchar) as select * from test_text where id=$1;
3, Implicit conversion rules for Postgresql character types
1. For the comparison between varchar and char, the default is to convert varchar to char.
In example 2, the index cannot be used because of type conversion on the left side of the equation.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Example 1: testdb=# explain execute test_char_bind_varchar('123456'); QUERY PLAN --------------------------------------------------------------------------------- Index Scan using ind_test_char on test_char (cost=0.42..8.44 rows=1 width=111) Index Cond: (id = '123456'::bpchar) (2 rows) Example 2: type conversion occurs on the left side of the equation, and the index cannot be used testdb=# explain execute test_varchar_bind_char('123456'); QUERY PLAN ----------------------------------------------------------------- Seq Scan on test_varchar (cost=0.00..2975.00 rows=1 width=108) Filter: ((id)::bpchar = '123456'::bpchar) (2 rows)
2. For the comparison between text and char, char is converted to text by default.
In example 3, the index cannot be used because of the type conversion on the left side of the equation.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Example 3: type conversion occurs on the left side of the equation, and the index cannot be used. testdb=# explain execute test_char_bind_text('123456'); QUERY PLAN ---------------------------------------------------------------- Seq Scan on test_char (cost=0.00..3225.00 rows=500 width=111) Filter: ((id)::text = '123456'::text) (2 rows) Example 4: testdb=# explain execute test_text_bind_char('123456'); QUERY PLAN --------------------------------------------------------------------------------- Index Scan using ind_test_text on test_text (cost=0.29..8.31 rows=1 width=108) Index Cond: (id = '123456'::text) (2 rows)
3. For the comparison between varchar and text, the default is to convert varchar to text, but the conversion between them does not affect the use of the index.
1 2 3 4 5 6 7 8 9 10 11 12 13 testdb=# explain execute test_varchar_bind_text('123456'); QUERY PLAN --------------------------------------------------------------------------------------- Index Scan using ind_test_varchar on test_varchar (cost=0.29..8.31 rows=1 width=108) Index Cond: ((id)::text = '123456'::text) (2 rows) testdb=# explain execute test_text_bind_varchar('123456'); QUERY PLAN --------------------------------------------------------------------------------- Index Scan using ind_test_text on test_text (cost=0.29..8.31 rows=1 width=108) Index Cond: (id = '123456'::text) (2 rows)
PG character type data conversion rules: varchar - > char - > text
4, KingbaseES type conversion and optimization
Anyone who has used Oracle knows that the comparison between char and varchar will not be unable to use the index because of different types. Kingbase is close to Oracle in terms of characteristics, which provides convenience for users to migrate from Oracle to KingbaseES. KingbaseES inherits the characteristics of Postgresql, and avoids the conversion caused by the comparison between char, varchar and text and the inability to use the index through code optimization. The following example has been verified in Kingbase es v8r6.
1. For the comparison between varchar and char, varchar is also converted to char.
kingbase performs special optimization to solve this problem. Even if the varchar on the left of the equation is type converted, it will not affect the use of the index, such as example 6.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Example 5: testdb=# explain execute test_char_bind_varchar('123456'); QUERY PLAN --------------------------------------------------------------------------------- Index Scan using ind_test_char on test_char (cost=0.42..8.44 rows=1 width=111) Index Cond: (id = '123456'::bpchar) (2 rows) Example 6: the index cannot be used because of type conversion on the left side of the equation. testdb=# explain execute test_varchar_bind_char('123456'); QUERY PLAN --------------------------------------------------------------------------------------- Index Scan using ind_test_varchar on test_varchar (cost=0.29..8.31 rows=1 width=108) Index Cond: ((id)::text = '123456'::text) (2 rows)
2. For the comparison between text and char, kingbase performs special optimization, so that the conversion occurs on the right side of the equation and does not affect the use of the index.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Example 7: testdb=# explain execute test_char_bind_text('123456'); QUERY PLAN --------------------------------------------------------------------------------- Index Scan using ind_test_char on test_char (cost=0.42..8.44 rows=1 width=111) Index Cond: (id = '123456'::bpchar) (2 rows) Example 8: testdb=# explain execute test_text_bind_char('123456'); QUERY PLAN --------------------------------------------------------------------------------- Index Scan using ind_test_text on test_text (cost=0.29..8.31 rows=1 width=108) Index Cond: (id = '123456'::text) (2 rows)
3. For the comparison between varchar and text, the default is to convert varchar to text. Like PG, the conversion between them does not affect the use of indexes.
1 2 3 4 5 6 7 8 9 10 11 12 13 test=# explain execute test_varchar_bind_text('123456'); QUERY PLAN --------------------------------------------------------------------------------------- Index Scan using ind_test_varchar on test_varchar (cost=0.29..8.31 rows=1 width=108) Index Cond: ((id)::text = '123456'::text) (2 rows) test=# explain execute test_text_bind_varchar('123456'); QUERY PLAN --------------------------------------------------------------------------------- Index Scan using ind_test_text on test_text (cost=0.29..8.31 rows=1 width=108) Index Cond: (id = '123456'::text) (2 rows)
Tips: the above example is based on the test results of Postgresql 12.3 and KingbaseES V8R6.