Trivial query, large query, but very sad results.

Поиск
Список
Период
Сортировка
От Alexander Haväng
Тема Trivial query, large query, but very sad results.
Дата
Msg-id 20020402163224.A8115@galway.metamatrix.se
обсуждение исходный текст
Ответы Re: Trivial query, large query, but very sad results.  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Trivial query, large query, but very sad results.  (Dan Langille <dan@langille.org>)
Список pgsql-general
Hello.

I'm using postgresql 7.1.3 (linux 2.4.17, 2*1.13 Mhz SMP), and I've created a table with 10 million rows.

When I do a query, say,
SELECT count(id) FROM bigtable WHERE somestring='something';
then what I would expect from the database is that it would completely hog the cpu
and disk i/o for a while until the query is completed, which on this machine well..
should take one or two minutes. Instead, it takes 12 minutes.

Doing the "same" thing with grep(1) takes 37 seconds.

Now, of course I realize the difference between these two scenarios, but bear with me.

During the grep(1)-run, grep uses about 20% userspace cpu, and vmstat(1) reveals a throughput
of 24 MB/s.

During the postgres query, postgres uses about 4-5% userspace cpu, and vmstat(1) reveals a throughput
of 2 MB/s.

System cpu is about 8% in both cases. All cpu figures are from top(1) output.

So my question is.. why the hell doesn't it use more cpu and more i/o resources when they are available?
Is there anything tunable in postgres that would increase performance on DB:s like this?

Regards,
Alexander

В списке pgsql-general по дате отправления:

Предыдущее
От: "David Esposito"
Дата:
Сообщение: Re: Core dump on PG 7.1.3
Следующее
От: Jan Wieck
Дата:
Сообщение: Re: %ROWTYPE as PL/pgsql argument