From: "Qingqing Zhou" <zhouqq@cs.toronto.edu>
Subject: Re: Query seem to slow if table have more than 200 million rows
Date: Mon, 3 Oct 2005 16:04:50 -0700
Organization: Hub.Org Networking Services
Lines: 56
Message-ID: <dhs2t1$ge0$1@news.hub.org>
References: <dh9ti0$ovs$1@news.hub.org>
	<SVONERLVbNDN4CxpAOA00000061@ki-communication.com>
To: pgsql-performance@postgresql.org


""Ahmad Fajar"" <gendowo@konphalindo.or.id> wrote
> Hi Qingqing,
>
> I don't know whether the statistic got is bad or good, this is the
> statistic:

Please do it in this way:

1. Start postmaster with "stats_start_collector=true" and 
"stats_block_level=true".

2. Use psql connect it, do something like this:

test=# select pg_stat_reset();
 pg_stat_reset
---------------
 t
(1 row)

test=# select * from pg_statio_user_indexes ;
 relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | 
idx_
blks_hit
-------+------------+------------+---------+--------------+---------------+-----
---------
 16385 |      16390 | public     | test    | test_idx     |             0 |
       0
(1 row)

test=# select count(*) from test where a <= 1234;
 count
-------
  7243
(1 row)

test=# select * from pg_statio_user_indexes ;
 relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | 
idx_
blks_hit
-------+------------+------------+---------+--------------+---------------+-----
---------
 16385 |      16390 | public     | test    | test_idx     |            55 |
       0
(1 row)


This gives us that to get "select count(*) from test where a <= 1234", I 
have to read 55 index blocks (no index block hit since I just restart 
postmaster so the bufferpool is empty).


Regards,
Qingqing