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/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
