Hi there,
I'm investigating one performance issue with tsearch2 index and trying to
interperet io statiscs from pg_statio_user_tables, pg_stat_user_tables.
But from documentation it's not clear what numbers I shoud take into
account and I'm a bit confused :)
I'm looking for blocks *actually* read from disk, since IO is the most
important factor.
I reseted stats and run my query and then obtained statistics:
=# select pg_stat_reset();
=# explain analyze select 1 from message_parts where message_parts.index_fts @@ '\'star\'';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
IndexScan using a_gist_key on message_parts (cost=0.00..1381.92 rows=469 width=0) (actual time=0.785..1236.086
rows=5142loops=1) Index Cond: (index_fts @@ '\'star\''::tsquery) Total runtime: 1240.274 ms
(3 rows)
=# select 'StatB:',heap_blks_read,heap_blks_hit,idx_blks_read, idx_blks_hit from pg_statio_user_tables where
relname='message_parts';?column? | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit
----------+----------------+---------------+---------------+-------------- StatB: | 1888 | 1700 |
1056 | 7226
(1 row)
=# select 'StatR:',seq_scan,seq_tup_read,idx_scan,idx_tup_fetch from pg_stat_user_tables where relname='message_parts';
?column?| seq_scan | seq_tup_read | idx_scan | idx_tup_fetch
----------+----------+--------------+----------+--------------- StatR: | 0 | 0 | 1 |
5939
(1 row)
From documentation:
the total number of disk blocks read from table - 1888
the number of buffer hits from table - 1700
the numbers of disk blocks read from indices - 1056
the number of buffer hits from indices - 7226
total numbers of rows returned by index scan - 5939
So, the total number of table blocks read is (1888+1700), and index blocks
is (1056+7226) ? Or from 1888 table blocks read there were 1700 blocks
already in buffer, but then I dont' understand index stats.
Since disk io is the most important performance factor,
should I look mostly on heap_blks_read and idx_blks_read ?
My query returns 5142 rows, while I see from idx_tup_fetch that
index returns 5939 rows. So, does it means that 5939 table rows was actually read
from disk and checked for lossines (index is lossy) and 797 hits was
actually false drops ?
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
