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,