From: "Qingqing Zhou" <zhouqq@cs.toronto.edu>
Subject: Re: Query seem to slow if table have more than 200 million rows
Date: Mon, 26 Sep 2005 18:43:14 -0700
Organization: Hub.Org Networking Services
Lines: 30
Message-ID: <dh9ti0$ovs$1@news.hub.org>
References: <SVONEswFCHOzyfyz5b60000023a@ki-communication.com>
To: pgsql-performance@postgresql.org


""Ahmad Fajar"" <gendowo@konphalindo.or.id> wrote
>
> Select ids, keywords from dict where keywords='blabla' ('blabla' is a 
> single
> word);
>
> The table have 200 million rows, I have index the keywords field. On the
> first time my query seem to slow to get the result, about 15-60 sec to get
> the result. But if I repeat the query I will get fast result. My question 
> is
> why on the first time the query seem very slow.
>
> Table structure is quite simple:
>
> Ids bigint, keywords varchar(150), weight varchar(1), dpos int.
>

The first slowness is obviously caused by disk IOs. The second time is 
faster because all data pages it requires are already in buffer pool. 200 
million rows is not a problem for btree index, even if your client tool 
appends some spaces to your keywords at your insertion time, the ideal btree 
is 5 to 6 layers high at most. Can you show the iostats of index from your 
statistics view? 
http://www.postgresql.org/docs/8.0/static/monitoring-stats.html#MONITORING-STATS-VIEWS

Regards,
Qingqing