|
Maybe you could post the query and an
EXPLAIN ANALYZE of the query. That would give more information for trying to
decide what is wrong. So your question is basically why you get
a slower read rate on this query than on other queries? If I had to guess, maybe it could be that
you are scanning an index with a low correlation (The order of the records in
the index is very different then the order of the records on the disk.) causing
your drives to do a lot of seeking.
A possible fix for this might be to cluster the table on the index, but
I would check out the explain analyze first to see which
step is really the slow one. -----Original Message----- 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, |
