understanding pg_stat* numbers
От | Oleg Bartunov |
---|---|
Тема | understanding pg_stat* numbers |
Дата | |
Msg-id | Pine.GSO.4.62.0503261259050.17555@ra.sai.msu.su обсуждение исходный текст |
Ответы |
Re: understanding pg_stat* numbers
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: