Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 49A5C9FA3B3 for ; Mon, 15 Jan 2007 06:16:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 53103-08 for ; Mon, 15 Jan 2007 06:16:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx01.bfk.de (mx01.bfk.de [193.227.124.2]) by postgresql.org (Postfix) with ESMTP id E4B739FA4B6 for ; Mon, 15 Jan 2007 06:16:41 -0400 (AST) Received: from mx00.int.bfk.de ([10.119.110.2]) by mx01.bfk.de with esmtp id 1H6OtK-00018o-69 for pgsql-performance@postgresql.org; Mon, 15 Jan 2007 11:16:38 +0100 Received: from fweimer by bfk.de with local id 1H6OtI-0004MZ-ES for pgsql-performance@postgresql.org; Mon, 15 Jan 2007 11:16:36 +0100 To: pgsql-performance@postgresql.org Subject: pg_trgm performance From: Florian Weimer Date: Mon, 15 Jan 2007 11:16:36 +0100 Message-ID: <82mz4k4mt7.fsf@mid.bfk.de> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/230 X-Sequence-Number: 22831 I've got a table with a few million rows, consisting of a single text column. The average length is about 17 characters. For the sake of an experiment, I put a trigram index on that table. Unfortunately, % queries without smallish LIMITs are ridiculously slow (they take longer than an hour). A full table scan with a "WHERE similarity(...) >=3D 0.4" clause completes in just a couple of minutes. The queries only select a few hundred rows, so an index scan has got a real chance to be faster than a sequential scan. Am I missing something? Or are trigrams just a poor match for my data set? Are the individual strings too long, maybe? (This is with PostgreSQL 8.2.0, BTW.) --=20 Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstra=DFe 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99