Hi,
Can someone please help me. My PostgreSQL queries
are very slow, especially update statements. What
can I do to improve the speed? I have already try
VACUUM and ANALYZE. " From the command line I have
vacuumdb -z -a -f
from inside psql:
database=# VACUUM FULL ANALYZE;"
I work on a Linux (Mandake 9.1) computer with 2 Zeon
Prosessors and 4Gig of ram. The HD on which
postgreSQL is running is a 80Gig drive and read
55Mbit/Sec. The next query is an example. The table
in which I work here contains 747 524 records and 14
fields (columns).
bons_acc=# explain update edc_ww set edc=null;
QUERY PLAN
-------------------------------------
---------------------------
Seq Scan on edc_ww
(cost=0.00..156793.91 rows=3491 width=184)
(1 row)
Time: 0.61 ms
bons_acc=# update edc_ww set edc=null;
UPDATE 747524
Time: 7628686.23 ms
This is just a Seq Scan where a numeric field must be updated to
NULL but if I run it you can see that this simple query takes
forever (7628686.23 ms this is over 2 hours for only updating
747524 records!). I dont think that the tables are to big? Could it
be my hardware/software/postgreSQL? What can I do to
optimise postgreSQL? I already increased the shared buffer in
the conf. file aswell.
Bobbie______________________________________________
____________
Bobbie van der Westhuizen
Quantitative Animal Breeding (BLUP)
ARC - Animal Improvement Institute
+27-12-672-9128 (o/h)
+27-12-665-1419 (fax)
bobbie@irene.agric.za
____________________________________________________
______