From: Qingqing Zhou <zhouqq@cs.toronto.edu>
To: pgsql-performance@postgresql.org
Subject: Re: Query seem to slow if table have more than 200 million rows
Date: Mon, 3 Oct 2005 16:04:50 -0700
Message-ID: <dhs2t1$ge0$1@news.hub.org> (raw)
References: <dh9ti0$ovs$1@news.hub.org>
<SVONERLVbNDN4CxpAOA00000061@ki-communication.com>
""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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: zhouqq@cs.toronto.edu
Subject: Re: Query seem to slow if table have more than 200 million rows
In-Reply-To: <dhs2t1$ge0$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox