1

In writing this answer, I'm just wondering if there is any slow down in casting from varchar to text or if that cast is just a compile-time trick. I've been told they're represented the same way and they're binary-coercable. Would it be faster to do

SELECT varchar1
FROM table
WHERE varchar1::text = 'foo';

Or,

SELECT varchar1
FROM table
WHERE varchar1 = 'foo'::varchar;
1

All tests done on PostgreSQL 9.5. On the matter of the cast,

Sample Data

We first create a table with 100,000 rows with 3 varchar columns.

CREATE TABLE foo AS
  SELECT x::varchar AS x1, x::varchar AS x2, x::varchar AS x3
  FROM generate_series(1,1e6) AS gs(x);

In SELECT

Then we try to the casts,

SELECT x1::text, x2::text, x3::text FROM foo;
Time: 476.825 ms

SELECT x1::text, x2::text, x3::text FROM foo;
Time: 501.092 ms

SELECT x1, x2, x3 FROM foo;
Time: 360.493 ms

SELECT x1, x2, x3 FROM foo;
Time: 373.647 ms

Conclusion: substantial slowdown it's not a compile type coercion.

In WHERE clause

However, in the where conditional they seem to act effectively the same,

SELECT * FROM foo
WHERE x1::varchar = '5'::varchar;

Performs as well as,

SELECT * FROM foo
WHERE x1::text = '5';

And if I now run,

SELECT *
FROM foo
WHERE array_position(ARRAY['5', '6', '9'], x1::text) IS NOT NULL;

SELECT *
FROM foo
WHERE array_position(ARRAY['5', '6', '9']::varchar[], x1) IS NOT NULL;

Both of them perform the same, so there is an optimization in the WHERE clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.