Re: understanding bitmap index benefit - Mailing list pgsql-hackers

yOn Thu, 19 May 2005, Oleg Bartunov wrote:

> 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.
>

Hmm, after restarting postmasters  I don't see any benefit :( 
Sometimes, I see reversed behaviour. Seems, combination of system's and 
postgres cacheing.

I tried to see io statistics, but it was weird in 8.0X and in  8.1dev I still
don't understand it :)
Below is a stats for fully cached query:

heap_blk, idx_blk show stats in form of 'blks_read:blks_hit' and 
seq_tup,idx_tup - in form of 'number of scan:tuples fetched'.

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

---------------------------------------------------------------------------------------------------------------------------
IndexScan using fts_idx on titles  (cost=0.00..1124.34 rows=378 width=42) (actual time=0.088..160.026 rows=4153
loops=1)  Index Cond: (fts_index @@ '\'list\''::tsquery) Total runtime: 184.834 ms
 
(3 rows)

mw=# select * from iostat where relname='titles'; relname | heap_blk | idx_blk | seq_tup | idx_tup 
---------+----------+---------+---------+--------- titles  | 0:3078   | 0:6925  | 0:0     | 1:4154
(1 row)

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

-------------------------------------------------------------------------------------------------------------------------
BitmapHeap Scan on titles  (cost=5.32..1349.79 rows=378 width=41) (actual time=105.663..136.422 rows=4153 loops=1)
Filter:(fts_index @@ '\'list\''::tsquery)   ->  Bitmap Index Scan on fts_idx  (cost=0.00..5.32 rows=378 width=0)
(actualtime=104.012..104.012 rows=4154 loops=1)         Index Cond: (fts_index @@ '\'list\''::tsquery) Total runtime:
158.258ms
 
(5 rows)

mw=# select * from iostat where relname='titles';                                relname | heap_blk | idx_blk | seq_tup
|idx_tup relname | heap_blk | idx_blk | seq_tup | idx_tup 
 
---------+----------+---------+---------+--------- titles  | 0:2704   | 0:2797  | 0:0     | 0:0
(1 row)

I see that in 8.1dev, there is no stats for idx_tup !
If I disable bitmap indices in 8.1dev I got

mw=# select * from iostat where relname='titles';                                relname | heap_blk | idx_blk | seq_tup
|idx_tup relname | heap_blk | idx_blk | seq_tup | idx_tup 
 
---------+----------+---------+---------+--------- titles  | 0:3446   | 0:2797  | 0:0     | 1:4154
(1 row)

notice, stats is different from 8.0.3 as one could expect, especially
in the numbers of idx_blk. 
Since, everything is cached there is no visible difference in timings.


after restarting postmasters (pg_ctl restart):

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

----------------------------------------------------------------------------------------------------------------------------
IndexScan using fts_idx on titles  (cost=0.00..1124.34 rows=378 width=42) (actual time=0.159..5845.181 rows=4153
loops=1)  Index Cond: (fts_index @@ '\'list\''::tsquery) Total runtime: 5870.105 ms
 
(3 rows)

mw=# select * from iostat where relname='titles'; relname | heap_blk  |  idx_blk  | seq_tup | idx_tup 
---------+-----------+-----------+---------+--------- titles  | 2072:1006 | 1386:5539 | 0:0     | 1:4154
(1 row)


8.1dev:

mw=# explain analyze select title from titles where fts_index @@ to_tsquery('list');
                  QUERY PLAN 
 

-------------------------------------------------------------------------------------------------------------------------
BitmapHeap Scan on titles  (cost=5.32..1349.79 rows=378 width=41) (actual time=577.541..2630.110 rows=4153 loops=1)
Filter:(fts_index @@ '\'list\''::tsquery)   ->  Bitmap Index Scan on fts_idx  (cost=0.00..5.32 rows=378 width=0)
(actualtime=575.808..575.808 rows=4154 loops=1)         Index Cond: (fts_index @@ '\'list\''::tsquery) Total runtime:
2654.472ms
 
(5 rows)

mw=# select * from iostat where relname='titles'; relname | heap_blk |  idx_blk  | seq_tup | idx_tup 
---------+----------+-----------+---------+--------- titles  | 2704:0   | 1399:1398 | 0:0     | 0:0
(1 row)





iostat is my view defined as
    View "public.iostat"  Column  | Type | Modifiers 
----------+------+----------- relname  | name | heap_blk | text | idx_blk  | text | seq_tup  | text | idx_tup  | text |

View definition: SELECT blk.relname, (blk.heap_blks_read::text || ':'::text) || blk.heap_blks_hit::text AS heap_blk,
(blk.idx_blks_read::text|| ':'::text) || blk.idx_blks_hit::text AS idx_blk, (tpl.seq_scan::text || ':'::text) ||
tpl.seq_tup_read::textAS seq_tup, (tpl.idx_scan::text || ':'::text) || tpl.idx_tup_fetch::text AS idx_tup   FROM
pg_statio_user_tablesblk   JOIN pg_stat_user_tables tpl USING (relname);
 



>    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