From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andrew Ayers <aayers(at)eldocomp(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Performance differences using varchar, char and text |
Date: | 2003-06-19 18:18:01 |
Message-ID: | 10214.1056046681@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Andrew Ayers <aayers(at)eldocomp(dot)com> writes:
> 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.
I believe what you're reporting here is problems on the Access side, not
problems in the underlying database. (That doesn't make them any less
of a real problem if you're using Access, of course.) Access doesn't
work very well with datatypes that aren't found in MS SQL Server...
I think if you look in the pgsql-odbc list archives you will find some
discussion of workarounds for Access with TEXT fields.
As far as the original question goes: there is no reason within Postgres
to choose one of these three types on performance grounds; you should
make the choice based on the semantics you want. Do you really want
every value blank-padded to exactly N characters? Use char(N). If you
don't want padding, but do want a specific upper limit on the field
width, use varchar(N). If you haven't got any specific upper limit in
mind (and if you're putting in numbers like 100000 then you don't ;-))
then use text. The performance differences that exist come directly
from the cycles expended to add padding blanks, check that the width
limit is not exceeded, etc.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2003-06-19 18:28:01 | Re: Finding Current Page Size |
Previous Message | Pavel Stehule | 2003-06-19 18:11:38 | Re: How to process mail using pgSQL? |