Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 39DBA9FC204 for ; Fri, 23 Feb 2007 21:04:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 23671-07 for ; Fri, 23 Feb 2007 21:04:37 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.233]) by postgresql.org (Postfix) with ESMTP id 0BE409FA47A for ; Fri, 23 Feb 2007 21:04:37 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so688040wxd for ; Fri, 23 Feb 2007 17:04:37 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=BlN+HSX1N3m7gpakHeNqHv3wdrg95Jz+sGbDi54ydnVP79WhVZuGOg231FPmZv6zhOt3BwLPTBW1R1H6CeBvPvhYsnLwqDLfc5Gk7fVh98jziyeg2yQeZ6rmFdlcKWq4AP8gzOBm2i4t0++TqLqZ9VRJ8FXqjzfZ9Q1TEGnoc04= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ZfbA2/38DYQ/saufYD05/BWpNKriisa+oIhFPajd9/A3SMpqkRkx+JttaWEal/wcywwXUVAaOcfHPKgfh5xDOVUc6mfw6chzzWEf8tIZlfO2kxgwclyQ7yBNNNgqFg/uOGWHTaHFoqVAPMBxXWCOjVmpk11s62L45UsR4bourvw= Received: by 10.90.93.6 with SMTP id q6mr2852890agb.1172279076955; Fri, 23 Feb 2007 17:04:36 -0800 (PST) Received: by 10.90.113.7 with HTTP; Fri, 23 Feb 2007 17:04:36 -0800 (PST) Message-ID: <1d4e0c10702231704y46270252o494b5d6180d7574f@mail.gmail.com> Date: Sat, 24 Feb 2007 02:04:36 +0100 From: "Guillaume Smet" To: "Guillaume Smet" , "Florian Weimer" , pgsql-performance@postgresql.org Subject: Re: pg_trgm performance In-Reply-To: <20070224003105.GA11010@uio.no> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <82mz4k4mt7.fsf@mid.bfk.de> <20070115125834.GA28248@uio.no> <1d4e0c10702231509k7ad24e2ck3a149a2e3a2899f7@mail.gmail.com> <20070224003105.GA11010@uio.no> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/270 X-Sequence-Number: 23324 Hi Steinar, On 2/24/07, Steinar H. Gunderson wrote: > I'm sorry, I can no longer remember where I needed pg_trgm. Simple testing of > your patch seems to indicate that the GiN version is about 65% _slower_ (18ms > vs. 30ms) for a test data set I found lying around, but I remember that on > the data set I needed it, the GIST version was a lot slower than that (think > 3-400ms). The 18 vs. 30ms test is a random Amarok database, on 8.2.3 > (Debian). Could you post EXPLAIN ANALYZE for both queries (after 2 or 3 runs)? And if you can provide EXPLAIN ANALYZE for a couple of searches (short length, medium length and long) in both cases, it could be nice too. The GiN version is not selective enough currently compared to GiST. It generally finds the matching rows faster but it has a slower recheck cond so it's sometimes interesting (in my case) and sometimes not that interesting (it seems to be your case). Thanks. -- Guillaume