Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E725A9FB699 for ; Sat, 24 Feb 2007 06:08:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 38519-01 for ; Sat, 24 Feb 2007 06:08:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from cassarossa.samfundet.no (cassarossa.samfundet.no [129.241.93.19]) by postgresql.org (Postfix) with ESMTP id 700379FB6FC for ; Sat, 24 Feb 2007 06:08:06 -0400 (AST) Received: from trofast.ipv6.sesse.net ([2001:700:300:1803:20e:cff:fe36:a766] helo=trofast.sesse.net) by cassarossa.samfundet.no with esmtp (Exim 4.50) id 1HKtox-00084U-G4 for pgsql-performance@postgresql.org; Sat, 24 Feb 2007 11:08:04 +0100 Received: from sesse by trofast.sesse.net with local (Exim 3.36 #1 (Debian)) id 1HKtoX-0003Xj-00 for ; Sat, 24 Feb 2007 11:07:37 +0100 Date: Sat, 24 Feb 2007 11:07:37 +0100 From: "Steinar H. Gunderson" To: pgsql-performance@postgresql.org Subject: Re: pg_trgm performance Message-ID: <20070224100737.GA13557@uio.no> Mail-Followup-To: pgsql-performance@postgresql.org References: <82mz4k4mt7.fsf@mid.bfk.de> <20070115125834.GA28248@uio.no> <1d4e0c10702231509k7ad24e2ck3a149a2e3a2899f7@mail.gmail.com> <20070224003105.GA11010@uio.no> <1d4e0c10702231704y46270252o494b5d6180d7574f@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <1d4e0c10702231704y46270252o494b5d6180d7574f@mail.gmail.com> X-Operating-System: Linux 2.6.19 on a x86_64 X-Message-Flag: Outlook? --> http://www.mozilla.org/products/thunderbird/ User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/272 X-Sequence-Number: 23326 On Sat, Feb 24, 2007 at 02:04:36AM +0100, Guillaume Smet wrote: > Could you post EXPLAIN ANALYZE for both queries (after 2 or 3 runs)? GIST version, short: amarok=# explain analyze select count(*) from tags where title % 'foo'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=147.84..147.85 rows=1 width=0) (actual time=16.873..16.875 rows=1 loops=1) -> Bitmap Heap Scan on tags (cost=4.59..147.74 rows=41 width=0) (actual time=16.828..16.850 rows=7 loops=1) Recheck Cond: (title % 'foo'::text) -> Bitmap Index Scan on trgm_idx (cost=0.00..4.58 rows=41 width=0) (actual time=16.818..16.818 rows=7 loops=1) Index Cond: (title % 'foo'::text) Total runtime: 16.935 ms (6 rows) GiN version, short: amarok=# explain analyze select count(*) from tags where title % 'foo'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=151.89..151.90 rows=1 width=0) (actual time=30.197..30.199 rows=1 loops=1) -> Bitmap Heap Scan on tags (cost=8.64..151.79 rows=41 width=0) (actual time=5.555..30.157 rows=7 loops=1) Filter: (title % 'foo'::text) -> Bitmap Index Scan on trgm_idx (cost=0.00..8.63 rows=41 width=0) (actual time=2.857..2.857 rows=5555 loops=1) Index Cond: (title % 'foo'::text) Total runtime: 30.292 ms (6 rows) GIST version, medium: amarok=# explain analyze select count(*) from tags where title % 'chestnuts roasting on an 0pen fire'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=147.84..147.85 rows=1 width=0) (actual time=216.149..216.151 rows=1 loops=1) -> Bitmap Heap Scan on tags (cost=4.59..147.74 rows=41 width=0) (actual time=216.135..216.137 rows=1 loops=1) Recheck Cond: (title % 'chestnuts roasting on an 0pen fire'::text) -> Bitmap Index Scan on trgm_idx (cost=0.00..4.58 rows=41 width=0) (actual time=216.124..216.124 rows=1 loops=1) Index Cond: (title % 'chestnuts roasting on an 0pen fire'::text) Total runtime: 216.214 ms (6 rows) amarok=# explain analyze select count(*) from tags where title % 'chestnuts roasting on an 0pen fire'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=151.89..151.90 rows=1 width=0) (actual time=156.310..156.312 rows=1 loops=1) -> Bitmap Heap Scan on tags (cost=8.64..151.79 rows=41 width=0) (actual time=156.205..156.299 rows=1 loops=1) Filter: (title % 'chestnuts roasting on an 0pen fire'::text) -> Bitmap Index Scan on trgm_idx (cost=0.00..8.63 rows=41 width=0) (actual time=155.748..155.748 rows=36 loops=1) Index Cond: (title % 'chestnuts roasting on an 0pen fire'::text) Total runtime: 156.376 ms (6 rows) GIST version, long: amarok=# explain analyze select count(*) from tags where title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'; ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=147.84..147.85 rows=1 width=0) (actual time=597.115..597.117 rows=1 loops=1) -> Bitmap Heap Scan on tags (cost=4.59..147.74 rows=41 width=0) (actual time=597.102..597.104 rows=1 loops=1) Recheck Cond: (title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'::text) -> Bitmap Index Scan on trgm_idx (cost=0.00..4.58 rows=41 width=0) (actual time=597.093..597.093 rows=1 loops=1) Index Cond: (title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'::text) Total runtime: 597.173 ms (6 rows) GiN version, long: amarok=# explain analyze select count(*) from tags where title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'; ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=151.89..151.90 rows=1 width=0) (actual time=435.789..435.791 rows=1 loops=1) -> Bitmap Heap Scan on tags (cost=8.64..151.79 rows=41 width=0) (actual time=435.777..435.779 rows=1 loops=1) Filter: (title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'::text) -> Bitmap Index Scan on trgm_idx (cost=0.00..8.63 rows=41 width=0) (actual time=435.729..435.729 rows=1 loops=1) Index Cond: (title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'::text) Total runtime: 435.851 ms (6 rows) So, the GiN version seems to be a bit faster for long queries, but it's still too slow -- in fact, _unindexed_ versions give 141ms, 342ms, 725ms for these three queries, so for the longer queries, the gain is only about a factor two. (By the way, I would like to stress that this is not my personal music collection! :-P) /* Steinar */ -- Homepage: http://www.sesse.net/