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 -------------------------------------------------------------------------------------------------------------------------------------
Index Scan using a_gist_key on message_parts (cost=0.00..1381.92 rows=469 width=0) (actual time=0.785..1236.086 rows=5142 loops=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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
