Little use of CPU ( < 5%) - Mailing list pgsql-performance

Hello ,

I have a problem of performance with a query. I use PostgreSQL 8.1.3.

The distribution of Linux is Red Hat Enterprise Linux ES release 4 (Nahant Update 2) and the server is a bi-processor Xeon 2.4GHz with 1 Go of Ram and the size of the database files is about 60 Go.

The problem is that this query uses only a few percentage of the cpu as seen with the top command :

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND         

3342 postgres  18   0  140m 134m 132m D  5.9 13.3  17:04.06 postmaster

The vm stat command : 

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----

 r  b   swpd   free   buff  cache          si   so    bi    bo   in      cs     us  sy  id wa

 0  1    184  16804  38104 933516    0    0  3092    55  667   145  12  4   71 14

 0  1    184  16528  38140 933480    0    0  2236     0 1206   388   2   1  50 47

 0  1    184  15008  38188 935252    0    0  2688    92 1209    396  2  0  49 48

The config of PostgresQL is : 

shared_buffers = 16384            (128Mo)

work_mem = 65536          (64 Mo)

maintenance_work_mem = 98304  (96 Mo)

effective_cache_size = 84000

I think that the problem is there are too much  %wait  that are waiting cause of the really bad rate of lecture (bi) which is only 3 Mo/s .

It is this value I do not understand because whit other queries this rate is about 120 Mo/s. I use SCSI DISK and a RAID 0 hardware system .

This is the query plan of the query :

                                                 QUERY PLAN                                                 

------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=24582205.20..24582205.22 rows=1 width=13)

   ->  Nested Loop  (cost=2.11..24582054.88 rows=60129 width=13)

         Join Filter: ("inner".l_quantity < (subplan))

         ->  Seq Scan on part  (cost=0.00..238744.00 rows=6013 width=4)

               Filter: ((p_brand = 'Brand#51'::bpchar) AND (p_container = 'MED JAR'::bpchar))

         ->  Bitmap Heap Scan on lineitem  (cost=2.11..126.18 rows=31 width=27)

               Recheck Cond: ("outer".p_partkey = lineitem.l_partkey)

               ->  Bitmap Index Scan on id_partkey_lineitem  (cost=0.00..2.11 rows=31 width=0)

                     Index Cond: ("outer".p_partkey = lineitem.l_partkey)

         SubPlan

           ->  Aggregate  (cost=126.50..126.51 rows=1 width=10)

                 ->  Index Scan using id_partkey_lineitem on lineitem  (cost=0.00..126.42 rows=31 width=10)

                       Index Cond: (l_partkey = $0)

(13 rows)

The number of tuples in Lineitem is 180 000 000.

So my question is what I have to do to increase the rate of the read which improve the execution of the query?

I add that the server is only dedicated for PostgreSQL.

Regards,