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 0E3B99DCAD7 for ; Thu, 8 Dec 2005 04:40:09 -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 56681-07 for ; Thu, 8 Dec 2005 04:40:09 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from houston.familyhealth.com.au (houston.au.fhnetwork.com [203.22.197.21]) by postgresql.org (Postfix) with ESMTP id 38EF89DCAA6 for ; Thu, 8 Dec 2005 04:40:06 -0400 (AST) Received: from houston.familyhealth.com.au (localhost [127.0.0.1]) by houston.familyhealth.com.au (Postfix) with ESMTP id 9B02625077; Thu, 8 Dec 2005 16:39:56 +0800 (WST) Received: from [127.0.0.1] (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (Postfix) with ESMTP id A29C925070; Thu, 8 Dec 2005 16:39:53 +0800 (WST) Message-ID: <4397F253.4030401@familyhealth.com.au> Date: Thu, 08 Dec 2005 16:44:03 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Marcus Cc: pgsql-performance@postgresql.org Subject: Re: INSERTs becoming slower and slower References: <16F953410A0F1346848DCB476A989CFE34DA@swtexchange2.technology.de> In-Reply-To: <16F953410A0F1346848DCB476A989CFE34DA@swtexchange2.technology.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-familyhealth-MailScanner-Information: Please contact the ISP for more information X-familyhealth-MailScanner: Found to be clean X-familyhealth-MailScanner-From: chriskl@familyhealth.com.au X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.058 required=5 tests=[AWL=0.058, UPPERCASE_25_50=0] X-Spam-Score: 0.058 X-Spam-Level: X-Archive-Number: 200512/239 X-Sequence-Number: 16060 You might find it faster to install contrib/tsearch2 for text indexing sort of purposes... Nörder-Tuitje wrote: > > > 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 := vRight; > vTmp := vLeft; > > LOOP > vChr := SUBSTRING ( pText FROM vTmp FOR 1); > vTmp := vTmp + 1; > EXIT WHEN (vChr = ' ' OR vChr IS NULL OR vTmp = > cBorder); > END LOOP; > > vRight := vTmp; > > vLit := SUBSTRING(pText FROM vLeft FOR (vRight - > vLeft - 1)); > > IF (LENGTH(vLit) > 0) THEN > WRDCNT := WRDCNT +1; > INSERT INTO DEX_TEMPDOC(TMP_DOO_ID > , TMP_SEQ_ID > , TMP_RAWTEXT) > VALUES (pDOO_ID > , I > , vLIT > ); > END IF; > > I := I + 1; > vTmp := LENGTH(vLIT); > > > IF ((WRDCNT % 100) = 0) THEN > PROGRESS = ROUND((100 * I) / DOCLEN,0); > RAISE NOTICE '[PROC] % WORDS -- LAST LIT % (Len > %) [% PCT / % of %]', WRDCNT, vLIT, vTMP, PROGRESS, I, DOCLEN; > > END IF; > > > EXIT WHEN vRight >= 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 = 2000 # min 16, at least max_connections*2, > 8KB each > work_mem = 32768 # min 64, size in KB > maintenance_work_mem = 16384 # min 1024, size in KB > max_stack_depth = 8192 # min 100, size in KB > > enable_hashagg = true > enable_hashjoin = true > enable_indexscan = true > enable_mergejoin = true > enable_nestloop = true > enable_seqscan = false > > The machine is a XEON 3GHz, 1GB RAM, SATA RAID 1 Array running 8.0.4 > i686 precompiled > > > Thanks ! > > > > Mit freundlichen Grüßen > *Dipl.Inform.Marcus Noerder-Tuitje > **Entwickler > * > software technology AG > *Kortumstraße 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 * > >