X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D00C1528A8 for ; Thu, 23 Jun 2005 07:40:14 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02750-08 for ; Thu, 23 Jun 2005 10:40:13 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.207]) by svr1.postgresql.org (Postfix) with ESMTP id E4D495288E for ; Thu, 23 Jun 2005 07:40:11 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so399403wra for ; Thu, 23 Jun 2005 03:40:15 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type; b=J7GZTUm8ePF4I20A0zX2jdhYtbkAEHIyVoLewTNV63LwAXU5vw1SqU8dE1eI8aK/ymVfpCTaHsdSHrtbPpNcgcP2wqWvtDEIFk2+ifp9Gf4+JUd7LxpbF7fDRneILcy7H9TTeX7QrKG3uZARYi/YgK5O9ySK63MZ7MwX+t70Mrc= Received: by 10.54.29.41 with SMTP id c41mr1055513wrc; Thu, 23 Jun 2005 03:40:15 -0700 (PDT) Received: by 10.54.42.25 with HTTP; Thu, 23 Jun 2005 03:40:14 -0700 (PDT) Message-ID: Date: Thu, 23 Jun 2005 12:40:15 +0200 From: Meetesh Karia Reply-To: meetesh.karia@alumni.duke.edu To: pgsql-performance@postgresql.org Subject: How are text columns stored? Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2959_22010652.1119523215002" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.081 tagged_above=0 required=5 tests=HTML_30_40, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200506/528 X-Sequence-Number: 13165 ------=_Part_2959_22010652.1119523215002 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi all, I'm running PG 8.0.3 on WinXP and I'm coming across some performance issues= =20 related to text columns. Basically, it appears as though PG is storing the= =20 text data inline with the rest of the row data as queries that don't touch= =20 the text column are slower when there is data in the text column than when= =20 there isn't. According to section 8.3 of the doc: "Long values are also stored in background tables so they do not interfere= =20 with rapid access to the shorter column values." So, how long does a value have to be to be considered "long"? If necessary, here is some more specific information about what I'm doing: 1) I create a new table and use 'COPY FROM' to populate it. When the data i= n=20 the text column is limited to a max of 60 characters, this part takes 2-3= =20 minutes less than when the data is at its full size. The table will be=20 populated with ~750k rows. Here's an example of the table I create (no, I= =20 didn't name the fields "vc_field1", "vc_field2", etc ;) ): create table my_table_import ( vc_field1 varchar(255), vc_field2 varchar(255), vc_field3 varchar(255), f_field1 float8, text_field1 text, ts_field1 timestamp, v_field4 varchar(255), i_field1 int8, i_field2 int8 ); 2) I populate i_field1 and i_field2 from lookup tables. This step takes=20 about 7 mins longer with the full text data than with the limited data. update my_table_import set i_field1 =3D f.i_field1, i_field2 =3D u.i_field2 from lookup1 as f, lookup2 as u where vc_field2 =3D f.vc_field2 and vc_field1 =3D u.vc_field1; 3) I then create an index on this table and run a couple of queries on it.= =20 Each of these queries takes about 10 minutes longer with the full text data= =20 then without it. Here's the index that I create and an example of one of th= e=20 queries that I run: create index idx_my_table_import_i1_i2 on my_table_import (i_field1,=20 i_field2); analyze my_table_import; select i_field1, i_field2, max(ts_field1) as ts_field1, count(*) as=20 dup_count from my_table_import where i_field1 between 0 and 9999 group by i_field1, i_field2 Thanks for the help, Meetesh Karia ------=_Part_2959_22010652.1119523215002 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi all,

I'm running PG 8.0.3 on WinXP and I'm coming across some performance issues related to text columns.  Basically, it appears as though PG is storing the text data inline with the rest of the row data as queries that don't touch the text column are slower when there is data in the text column than when there isn't.  According to section 8.3 of the doc:

"Long values are also stored in background tables so they do not inter= fere with=20 rapid access to the shorter column values."

So, how long does a value have to be to be considered "long"?

If necessary, here is some more specific information about what I'm doing:<= br>
1) I create a new table and use 'COPY FROM' to populate it.  When the data in the text column is limited to a max of 60 characters, this part takes 2-3 minutes less than when the data is at its full size.  The table will be populated with ~750k rows.  Here's an example of the table I create (no, I didn't name the fields "vc_field1", "vc_field2", etc ;) ):

    create table my_table_import
    (
        vc_field1 varchar(255),
        vc_field2 varchar(255),
        vc_field3 varchar(255),
        f_field1 float8,
        text_field1 text,
        ts_field1 timestamp,
        v_field4 varchar(255),
        i_field1 int8,
        i_field2 int8
    );

2) I populate i_field1 and i_field2 from lookup tables.  This step takes about 7 mins longer with the full text data than with the limited data.

    update my_table_import
        set i_field1 =3D f.i_field1,
            i_field2= =3D u.i_field2
        from lookup1 as f, lookup2 as u<= br>         where vc_field2 =3D f.vc_field2<= br>             and vc_f= ield1 =3D u.vc_field1;

3) I then create an index on this table and run a couple of queries on it.  Each of these queries takes about 10 minutes longer with the full text data then without it.  Here's the index that I create and an example of one of the queries that I run:

    create index idx_my_table_import_i1_i2 on my_table_impor= t (i_field1, i_field2);
    analyze my_table_import;

    select i_field1, i_field2, max(ts_field1) as ts_field1, = count(*) as dup_count
        from my_table_import
        where i_field1 between 0 and 999= 9
        group by i_field1, i_field2

Thanks for the help,
Meetesh Karia
------=_Part_2959_22010652.1119523215002--