X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 532C69DC8DC for ; Fri, 20 Jan 2006 19:22:41 -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 02779-08 for ; Fri, 20 Jan 2006 19:22:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C0DCC9DC83F for ; Fri, 20 Jan 2006 19:22:37 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0KNMd5c006377; Fri, 20 Jan 2006 18:22:39 -0500 (EST) To: Stephan Vollmer cc: pgsql-general@postgresql.org Subject: Re: Creation of tsearch2 index is very slow In-reply-to: <43D114B1.5030802@gmx.de> References: <43D0ED57.1020608@gmx.de> <17454.1137771321@sss.pgh.pa.us> <43D114B1.5030802@gmx.de> Comments: In-reply-to Stephan Vollmer message dated "Fri, 20 Jan 2006 17:49:53 +0100" Date: Fri, 20 Jan 2006 18:22:39 -0500 Message-ID: <6376.1137799359@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.095 required=5 tests=[AWL=0.095] X-Spam-Score: 0.095 X-Spam-Level: X-Archive-Number: 200601/1044 X-Sequence-Number: 89989 Stephan Vollmer writes: > Tom Lane wrote: >> However, I'm not sure that anyone's tried to do any performance >> optimization on the GIST insert code ... there might be some low-hanging >> fruit there. > Unfortunately, I'm not able to investigate it further myself as I'm > quite a Postgres newbie. But I could provide someone else with the > example table. Maybe someone else could find out why it is so slow. The problem seems to be mostly tsearch2's fault rather than the general GIST code. I've applied a partial fix to 8.1 and HEAD branches, which you can find here if you're in a hurry for it: http://archives.postgresql.org/pgsql-committers/2006-01/msg00283.php (the gistidx.c change is all you need for tsearch2) There is some followup discussion in the pgsql-performance list. It seems possible that we can get another factor of 10 or better with a smarter picksplit algorithm --- but that patch will probably be too large to be considered for back-patching into the stable branches. regards, tom lane