understanding pg_stat* numbers

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: understanding pg_stat* numbers
Date: 2005-03-26 10:41:38
Message-ID: Pine.GSO.4.62.0503261259050.17555@ra.sai.msu.su
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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
-------------------------------------------------------------------------------------------------------------------------------------
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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Euler Taveira de Oliveira 2005-03-26 11:32:25 Re: problem with CR+LF in files in psql \i command
Previous Message Devrim GUNDUZ 2005-03-26 10:14:28 Re: pg_dump issue : Cannot drop a non-existent(?) trigger