Re: pg_trgm performance

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/