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, 26 Sep 2005 18:43:14 -0700
Message-ID: <dh9ti0$ovs$1@news.hub.org> (raw)
References: <SVONEswFCHOzyfyz5b60000023a@ki-communication.com>
""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
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: <dh9ti0$ovs$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