> 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;"
Good start to taking performance measurements - the planner's statistics
are updated you will be able to get accurate analyses.
> 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).
So you have some decent sized hardware here but you may want a second
drive for backups or housing the logfiles. Also your database is not
exceptionally large - PostgreSQL can handle much larger.
> bons_acc=# explain update edc_ww set edc=null;
> 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.
For improving the performance of large updates such as this query you will
need to adjust some other parameters of postgresql.conf such as increasing
the number of checkpoint segments and setting logging to a minimum level.
Or to accomplish this particular update quickly, you can drop the edc
column, re-add it and set the default to null.
However updating every row to null with 700k rows is going to take a while
and this query is probably not a good test case to judge your database
performance. Try testing some of your other queries. Post the EXPLAIN
ANALYZE results of those queries to the psql-performance@postgresql.org
list along with a complete copy of your postgresql.conf file. I think you
are not getting a good measurement of your actual database performance by
judging it with this simple test case where every row is updated.
Regards,
Fred
