understanding bitmap index benefit - Mailing list pgsql-hackers

Tom,

I noticed that along with many improvements in join operations bitmap
index speed up execution of first time query. It's known complain about
slow full text searching when query runs for the first time. But in CVS
version I see very nice behaviour I'd like to understand.

Query below is full text search on titles from pgsql mailing list archive:

8.0.3:
mw=# explain analyze select title from titles where fts_index @@ to_tsquery('vacuum&analyze');
                             QUERY PLAN 
 

---------------------------------------------------------------------------------------------------------------------------
IndexScan using fts_idx on titles  (cost=0.00..1124.34 rows=378 width=41) (actual time=20.884..806.073 rows=291
loops=1)  Index Cond: (fts_index @@ '\'vacuum\' & \'analyz\''::tsquery) Total runtime: 808.095 ms
 
(3 rows)


8.1dev:
mw=# explain analyze select title from titles where fts_index @@ to_tsquery('vacuum&analyze');
                            QUERY PLAN 
 

------------------------------------------------------------------------------------------------------------------------
BitmapHeap Scan on titles  (cost=5.32..1349.79 rows=378 width=41) (actual time=207.304..258.964 rows=291 loops=1)
Filter:(fts_index @@ '\'vacuum\' & \'analyz\''::tsquery)   ->  Bitmap Index Scan on fts_idx  (cost=0.00..5.32 rows=378
width=0)(actual time=206.993..206.993 rows=291 loops=1)         Index Cond: (fts_index @@ '\'vacuum\' &
\'analyz\''::tsquery)Total runtime: 260.946 ms
 
(5 rows)

Now, more frequent terms:

8.0.3:
mw=# explain analyze select title from titles where fts_index @@ to_tsquery('create&table');
                            QUERY PLAN 
 

-----------------------------------------------------------------------------------------------------------------------------
IndexScan using fts_idx on titles  (cost=0.00..1124.34 rows=378 width=41) (actual time=117.904..4463.131 rows=551
loops=1)  Index Cond: (fts_index @@ '\'create\' & \'table\''::tsquery) Total runtime: 4467.814 ms
 
(3 rows)

8.1dev:
mw=# explain analyze select title from titles where fts_index @@ to_tsquery('create&table');
                          QUERY PLAN 
 

------------------------------------------------------------------------------------------------------------------------
BitmapHeap Scan on titles  (cost=5.32..1349.79 rows=378 width=41) (actual time=296.310..423.622 rows=551 loops=1)
Filter:(fts_index @@ '\'create\' & \'table\''::tsquery)   ->  Bitmap Index Scan on fts_idx  (cost=0.00..5.32 rows=378
width=0)(actual time=296.001..296.001 rows=551 loops=1)         Index Cond: (fts_index @@ '\'create\' &
\'table\''::tsquery)Total runtime: 426.573 ms
 
(5 rows)

Configurations for two postmasters are the same and they're running on the
same machine (my notebook IBM X40, Linux 2.6.8.1).

my applause, Tom !
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83