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
