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/