X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8504C9DCAD7 for ; Thu, 8 Dec 2005 04:36:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59664-01 for ; Thu, 8 Dec 2005 04:36:49 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.186]) by postgresql.org (Postfix) with ESMTP id 7474C9DCAA6 for ; Thu, 8 Dec 2005 04:36:45 -0400 (AST) Received: from [80.142.65.56] (helo=swtexchange2.technology.de) by mrelayeu.kundenserver.de (node=mrelayeu5) with ESMTP (Nemesis), id 0ML25U-1EkHGe39Co-0008GK; Thu, 08 Dec 2005 09:36:45 +0100 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5FBD2.84AF3128" Subject: INSERTs becoming slower and slower X-MimeOLE: Produced By Microsoft Exchange V6.0.6556.0 Date: Thu, 8 Dec 2005 09:36:43 +0100 Message-ID: <16F953410A0F1346848DCB476A989CFE34DA@swtexchange2.technology.de> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: INSERTs becoming slower and slower Thread-Index: AcX70oSo3bV9cHG6To6DKgMh/25neA== From: =?iso-8859-1?Q?N=F6rder-Tuitje=2C_Marcus?= To: X-Provags-ID: kundenserver.de abuse@kundenserver.de login:24478085a861b01e05bdc3b8f80dc176 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.257 required=5 tests=[HTML_FONT_BIG=0.256, HTML_MESSAGE=0.001, UPPERCASE_25_50=0] X-Spam-Score: 0.257 X-Spam-Level: X-Archive-Number: 200512/238 X-Sequence-Number: 16059 This is a multi-part message in MIME format. ------_=_NextPart_001_01C5FBD2.84AF3128 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am breaking up huge texts (between 25K and 250K words) into single = words using PgPlsql. For this I am using a temp table in the first step : LOOP=09 vLeft :=3D vRight; vTmp :=3D vLeft; =09 LOOP vChr :=3D SUBSTRING ( pText FROM vTmp FOR 1); vTmp :=3D vTmp + 1; EXIT WHEN (vChr =3D ' ' OR vChr IS NULL OR vTmp =3D cBorder); END LOOP; =09 vRight :=3D vTmp; =09 vLit :=3D SUBSTRING(pText FROM vLeft FOR (vRight - vLeft - 1)); IF (LENGTH(vLit) > 0) THEN WRDCNT :=3D WRDCNT +1; INSERT INTO DEX_TEMPDOC(TMP_DOO_ID , TMP_SEQ_ID , TMP_RAWTEXT) VALUES (pDOO_ID , I , vLIT );=09 END IF; =09 I :=3D I + 1; vTmp :=3D LENGTH(vLIT); =09 IF ((WRDCNT % 100) =3D 0) THEN PROGRESS =3D ROUND((100 * I) / DOCLEN,0);=20 RAISE NOTICE '[PROC] % WORDS -- LAST LIT % (Len %) [% PCT / % of %]', = WRDCNT, vLIT, vTMP, PROGRESS, I, DOCLEN; END IF; =09 =09 EXIT WHEN vRight >=3D cBorder; END LOOP; The doc is preprocessed, between each word only a single blank can be. My problem is : The first 25K words are quite quick, but the insert = become slower and slower. starting with 1K words per sec I end up with = 100 words in 10 sec (when I reach 80K-100K words) the only (nonunique index) on tempdoc is on RAWTEXT. What can I do ? Should I drop the index ? Here is my config: shared_buffers =3D 2000 # min 16, at least max_connections*2, = 8KB each work_mem =3D 32768 # min 64, size in KB maintenance_work_mem =3D 16384 # min 1024, size in KB max_stack_depth =3D 8192 # min 100, size in KB enable_hashagg =3D true enable_hashjoin =3D true enable_indexscan =3D true enable_mergejoin =3D true enable_nestloop =3D true enable_seqscan =3D false The machine is a XEON 3GHz, 1GB RAM, SATA RAID 1 Array running 8.0.4 = i686 precompiled Thanks ! Mit freundlichen Gr=FC=DFen=20 Dipl.Inform.Marcus Noerder-Tuitje Entwickler software technology AG Kortumstra=DFe 16 =20 44787 Bochum Tel: 0234 / 52 99 6 26 Fax: 0234 / 52 99 6 22 E-Mail: noerder-tuitje@technology.de =20 Internet: www.technology.de=20 ------_=_NextPart_001_01C5FBD2.84AF3128 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable INSERTs becoming slower and slower

Hi,

I am breaking up huge texts (between = 25K and 250K words) into single words using PgPlsql.

For this I am using a temp table in the = first step :

        LOOP   

        =         vLeft   :=3D vRight;
        =         vTmp    :=3D vLeft;
        =        =20
        =         LOOP
        =         =         vChr :=3D SUBSTRING ( pText FROM vTmp FOR 1);
        =         =         vTmp :=3D vTmp + 1;
        =         =         EXIT WHEN (vChr =3D ' ' OR vChr IS NULL OR vTmp =3D = cBorder);
        =         END LOOP;
        =         =        =20
        =         vRight  :=3D vTmp;
        =        =20
        =         vLit    :=3D SUBSTRING(pText FROM vLeft = FOR (vRight - vLeft - 1));

        =         IF (LENGTH(vLit) > 0) THEN
        =         =         WRDCNT :=3D WRDCNT +1;
        =         =         INSERT INTO DEX_TEMPDOC(TMP_DOO_ID
        =         =         =         =         =         ,       TMP_SEQ_ID
        =         =         =         =         =         ,       = TMP_RAWTEXT)
        =         =         VALUES          =    (pDOO_ID
        =         =         =         =         =         ,       I
        =         =         =         =         =         ,       vLIT
        =         =         =         =         =             ); 
        =         END IF;
        =        =20
        =         I :=3D I + 1;
        =         vTmp :=3D LENGTH(vLIT);

        =        =20
        =         IF ((WRDCNT % 100) =3D 0) THEN
        =         =         PROGRESS =3D ROUND((100 * I) / DOCLEN,0);
        =         =         RAISE NOTICE '[PROC] % WORDS -- LAST LIT % (Len %) [% = PCT / % of %]', WRDCNT, vLIT, vTMP, PROGRESS, I, DOCLEN;

        =         END IF;
        =         =        =20
        =        =20
        =         EXIT WHEN vRight >=3D cBorder;
        END LOOP;


The doc is preprocessed, between each = word only a single blank can be.

My problem is : The first 25K words are = quite quick, but  the insert become slower and slower. starting = with 1K words per sec I end up with 100 words in 10 sec (when I reach = 80K-100K words)

the only (nonunique index) on tempdoc = is on RAWTEXT.

What can I do ? Should I drop the index = ?

Here is my config:

shared_buffers =3D = 2000           # min = 16, at least max_connections*2, 8KB each
work_mem =3D = 32768           &n= bsp;    # min 64, size in KB
maintenance_work_mem =3D = 16384    # min 1024, size in KB
max_stack_depth =3D = 8192          # min 100, = size in KB

enable_hashagg =3D true
enable_hashjoin =3D true
enable_indexscan =3D true
enable_mergejoin =3D true
enable_nestloop =3D true
enable_seqscan =3D false

The machine is a XEON 3GHz, 1GB RAM, = SATA RAID 1 Array running 8.0.4 i686 precompiled


Thanks !



Mit freundlichen Gr=FC=DFen
Dipl.Inform.Marcus Noerder-Tuitje
Entwickler

software technology AG
Kortumstra=DFe = 16  
44787 = Bochum
Tel:  0234 = / 52 99 6 26
Fax: 0234 / 52 = 99 6 22
E-Mail:   noerder-tuitje@technology.de  =
Internet: = www.technology.de


------_=_NextPart_001_01C5FBD2.84AF3128--