Hi,
There are some performance issues I am still confused over.
I've got a Linux box, raid1, 1GB memory
CPU-wise the server is mostly idle
PG 8.0.10,
shared_buffers = 10000
work_mem = 16348
maintenance_work_mem = 65536
Parameters are tweaked without much difference.
The following query is over a 12000 row table and apa_user is indexed.
I've recently vaccumed the db, analyze and recreated indexes on
apa_item_common.
So how on earth could this query take 10s to complete? Are my disks bad?
Are there other possible reasons?
If there were a couple of million rows I would still think it would run
way faster than this.
I cannot see odd messages in dmesg. It more or less started to act like
this over night.
Best regards,
Marcus
apa=> explain analyze
apa-> select
apa-> aic.objectid as aic_objectid
apa-> from
apa-> apa_item_common aic
apa-> where
apa-> aic.apa_user = 704 AND
apa-> aic.status = 30
apa-> ORDER BY aic.calc_rating desc
apa-> LIMIT 1000;
Limit (cost=5016.54..5019.04 rows=1000 width=8) (actual
time=9560.471..9562.201 rows=1000 loops=1)
-> Sort (cost=5016.54..5019.47 rows=1174 width=8) (actual
time=9560.469..9561.065 rows=1000 loops=1)
Sort Key: calc_rating
-> Index Scan using apa_item_common_x1 on apa_item_common aic
(cost=0.00..4956.68 rows=1174 width=8) (actual time=19.854..9557.606
rows=1226 loops=1)
Index Cond: (apa_user = 704)
Filter: (status = 30)
Total runtime: 9563.016 ms
(7 rows)
running the same query again, now it is cached then?
Limit (cost=5016.54..5019.04 rows=1000 width=8) (actual
time=7.261..9.016 rows=1000 loops=1)
-> Sort (cost=5016.54..5019.47 rows=1174 width=8) (actual
time=7.258..7.870 rows=1000 loops=1)
Sort Key: calc_rating
-> Index Scan using pond_item_common_x1 on pond_item_common
pic (cost=0.00..4956.68 rows=1174 width=8) (actual time=0.029..5.483
rows=1226 loops=1)
Index Cond: (pond_user = 704)
Filter: (status = 30)
Total runtime: 9.840 ms
(7 rows)