From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: pg_trgm performance
Date: Sat, 24 Feb 2007 11:07:37 +0100
Message-ID: <20070224100737.GA13557@uio.no> (raw)
In-Reply-To: <1d4e0c10702231704y46270252o494b5d6180d7574f@mail.gmail.com>
References: <82mz4k4mt7.fsf@mid.bfk.de>
<20070115125834.GA28248@uio.no>
<1d4e0c10702231509k7ad24e2ck3a149a2e3a2899f7@mail.gmail.com>
<20070224003105.GA11010@uio.no>
<1d4e0c10702231704y46270252o494b5d6180d7574f@mail.gmail.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgunderson@bigfoot.com
Subject: Re: pg_trgm performance
In-Reply-To: <20070224100737.GA13557@uio.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox