CHAR vs NVARCHAR vs TEXT performance

From: Rob <postgresql(at)mintsoft(dot)net>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: CHAR vs NVARCHAR vs TEXT performance
Date: 2019-04-29 16:40:01
Message-ID: fdf296a6ed074e63dc6d8d54043fc532@mintsoft.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Guys,

I wanted to get some thoughts about a type-specific performance problem
we hit
through our application tier.

The full conversation is here:
https://github.com/npgsql/npgsql/issues/2283

Basically, if a table exists with a PK which is CHAR(n) and a query is
sent with
VARCHAR or CHAR then it uses an Index Scan. If the query is sent with
TEXT as the
type then postgresql casts the column to TEXT (rather than the value to
CHAR) and
it does a Seq Scan.

So far this has only showed itself on npgsql (I've been unable to
reproduce on
other drivers), I think it's because npgsql only sends TEXT whereas
other drivers
tend to send VARCHAR (other drivers includes the official JDBC driver).

I guess the root question is: is TEXT supposed to be identical to
VARCHAR in all scenarios?

Thanks,
Rob

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2019-04-29 16:55:31 Re: Race conditions with checkpointer and shutdown
Previous Message Andres Freund 2019-04-29 16:35:11 Re: Race conditions with checkpointer and shutdown