Re: Performance differences using varchar, char and text

Yusuf wrote:
> In the Postgres 7.3.3. User Guide section 5.3, it says that there's no
> performance difference between the three type. But in 'PostgreSQL
> Database Performance Tuning' by Jean-Paul ARGUDO (section 5: use correct
> datatype), it says there is a performance difference because of you
> might need more I/O to read the data (which makes sense).
>
> So, is there a performance difference?

Yusuf,

Here is what I noticed - I am using Postgres 7.3.2 (on a Sun box, not
sure what OS version) with the latest ODBC driver on a Windows XP Pro
box. I am in the process of conversion of a legacy VB app from using
Access 97 to PostgreSQL via an ODBC connection (DSN-less).

I had in my Access DB several tables which utilized Memo-type fields to
store data in a "multi-value" delimited format (will never do that
again). I tried to first convert them to TEXT type fields on the
PostgreSQL tables. These didn't work right (more on that later), so I
converted them to large VARCHAR field (ie, VARCHAR(100000), and larger,
in some cases).

I noticed when doing selects (via the ODBC driver) that any accesses to
these fields caused MASSIVE slowdowns on the select - whether I was
selecting for them, or if the field was part of the WHERE clause of the
SQL statement. I found that if I decreased the size of the field, the
speed would increase.

One thing I did note was that in using psql, selects on these fields
didn't matter - they seemed fast. I tend to think it was the ODBC driver
in some manner.

Later, I determined that the reason I was having troubles with the TEXT
fields was because I was using DAO. Switching the code to use ADO calls
instead fixed the issue, and I switched to using the TEXT type on the
fields. However, by then I had installed many "workarounds" to avoid
those fields as much as possible in my code that I don't know if the
selects on them would be the same, faster, or slower...

Hope this helps a little...

Andrew

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.