Thread: TEXT field and Postgresql Perfomance

TEXT field and Postgresql Perfomance

From
"Loren M. Lang"
Date:
Do large TEXT or VARCHAR entries in postgresql cause any performance
degradation when a query is being executed to search for data in a table
where the TEXT/VARCHAR fields aren't being searched themselves?

Since, according to the postgresql docs, theirs no performance
difference between VARCHAR and TEXT, I'm assuming VARCHAR is identical
to TEXT entries with a restriction set on the length.  And since TEXT
can be of any possible size, then they must be stored independently of
the rest of the table which is probably all stored in a fixed size rows
since all or nearly all of the other types have a specific size
including CHAR.  Therefore TEXT entries must be in some other hash table
that only needs to be looked up when that column is referenced.  If this
is the case then all other row data will need to be read in for an
unindexed query, but the TEXT columns will only be read if their being
searched though or queried.  And if they're only being queried, then only
the rows that matched will need the TEXT columns read in which should
have minimal impact on performance even if they contain kilobytes of
information.

--
I sense much NT in you.
NT leads to Bluescreen.
Bluescreen leads to downtime.
Downtime leads to suffering.
NT is the path to the darkside.
Powerful Unix is.

Public Key: ftp://ftp.tallye.com/pub/lorenl_pubkey.asc
Fingerprint: B3B9 D669 69C9 09EC 1BCD  835A FAF3 7A46 E4A3 280C


Re: TEXT field and Postgresql Perfomance

From
Bruno Wolff III
Date:
On Fri, Jan 07, 2005 at 19:36:47 -0800,
  "Loren M. Lang" <lorenl@alzatex.com> wrote:
> Do large TEXT or VARCHAR entries in postgresql cause any performance
> degradation when a query is being executed to search for data in a table
> where the TEXT/VARCHAR fields aren't being searched themselves?

Yes in that the data is more spread out because of the wider rows and that
results in more disk blocks being looked at to get the desired data.

> Since, according to the postgresql docs, theirs no performance
> difference between VARCHAR and TEXT, I'm assuming VARCHAR is identical
> to TEXT entries with a restriction set on the length.  And since TEXT
> can be of any possible size, then they must be stored independently of

No.

> the rest of the table which is probably all stored in a fixed size rows

No, Postgres uses variable length records.

Re: TEXT field and Postgresql Perfomance

From
Alex Turner
Date:
I guess my question that would follow is, when does it work best to
start using BLOBs/CLOBs (I forget if pg has CLOBs) instead of
textfields because your table is going to balloon in disk blocks if
you have large amounts of data, and all fields you want to search on
would have to be indexed, increasing insert time substantialy.

Does it ever pay to use text and not CLOB unless your text is going to
be short, in which case why not just varchar, leading to the thought
that the text datatype is just bad?

Alex Turner
NetEconomist


On Fri, 7 Jan 2005 22:03:23 -0600, Bruno Wolff III <bruno@wolff.to> wrote:
> On Fri, Jan 07, 2005 at 19:36:47 -0800,
>   "Loren M. Lang" <lorenl@alzatex.com> wrote:
> > Do large TEXT or VARCHAR entries in postgresql cause any performance
> > degradation when a query is being executed to search for data in a table
> > where the TEXT/VARCHAR fields aren't being searched themselves?
>
> Yes in that the data is more spread out because of the wider rows and that
> results in more disk blocks being looked at to get the desired data.
>
> > Since, according to the postgresql docs, theirs no performance
> > difference between VARCHAR and TEXT, I'm assuming VARCHAR is identical
> > to TEXT entries with a restriction set on the length.  And since TEXT
> > can be of any possible size, then they must be stored independently of
>
> No.
>
> > the rest of the table which is probably all stored in a fixed size rows
>
> No, Postgres uses variable length records.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Re: TEXT field and Postgresql Perfomance

From
Michael Fuhr
Date:
On Fri, Jan 07, 2005 at 10:03:23PM -0600, Bruno Wolff III wrote:
> On Fri, Jan 07, 2005 at 19:36:47 -0800, "Loren M. Lang" <lorenl@alzatex.com> wrote:
>
> > Since, according to the postgresql docs, theirs no performance
> > difference between VARCHAR and TEXT, I'm assuming VARCHAR is identical
> > to TEXT entries with a restriction set on the length.  And since TEXT
> > can be of any possible size, then they must be stored independently of
>
> No.
>
> > the rest of the table which is probably all stored in a fixed size rows
>
> No, Postgres uses variable length records.

A discussion of TOAST and ALTER TABLE SET STORAGE might be appropriate
here, but I'll defer that to somebody who understands such things
better than me.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: TEXT field and Postgresql Perfomance

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
> On Fri, Jan 07, 2005 at 19:36:47 -0800,
>   "Loren M. Lang" <lorenl@alzatex.com> wrote:
>> Do large TEXT or VARCHAR entries in postgresql cause any performance
>> degradation when a query is being executed to search for data in a table
>> where the TEXT/VARCHAR fields aren't being searched themselves?

> Yes in that the data is more spread out because of the wider rows and that
> results in more disk blocks being looked at to get the desired data.

You are overlooking the effects of TOAST.  Fields wider than a kilobyte
or two will be pushed out-of-line and will thereby not impose a penalty
on queries that only access the other fields in the table.

(If Loren's notion of "large" is "a hundred bytes" then there may be a
measurable impact.  If it's "a hundred K" then there won't be.)

            regards, tom lane