Trivial query, large query, but very sad results.

From: Alexander Haväng <eel(at)musiknet(dot)se>
To: pgsql-general(at)postgresql(dot)org
Subject: Trivial query, large query, but very sad results.
Date: 2002-04-02 14:32:24
Message-ID: 20020402163224.A8115@galway.metamatrix.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dean Hill 2002-04-02 14:39:55 Re: v7.2.1 Released: Critical Bug Fix
Previous Message David Esposito 2002-04-02 14:23:00 Re: Core dump on PG 7.1.3