Hi!
I'm testing an update on 66k rows on Postgresql, and it seems
something is not right here.
My server is a Quad-Xeon 3.2 Ghz with 2 GB RAM and a RAID 1 running
FreeBSD 6.3 and PgSQL 8.3. My development machine is a PowerBook G4
1.67 Ghz with 2 GB RAM, OS X Leopard and PgSQL 8.3.
I detected that an update in my application was runnning to slow.
So, I'm testing an update query with no conditions, just:
UPDATE text_answer_mapping_ebt SET f1 = false;
f1 is a boolean column, so it can't get much simpler than this.
I've analysed and vaccumed several times, yet the results I get on the
Xeon are:
EXPLAIN ANALYZE UPDATE text_answer_mapping_ebt SET f1 = false;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Seq Scan on text_answer_mapping_ebt (cost=0.00..13945.72
rows=265072 width=92) (actual time=21.123..1049.054 rows=66268 loops=1)
Total runtime: 63235.363 ms
(2 rows)
On my powerbook, this runs on about 25 seconds.
Also, when I do the same operation on a very similar-structured
table with less rows, I get *much* faster times:
EXPLAIN ANALYZE UPDATE respondent_mapping_ebt SET f1 = false;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Seq Scan on respondent_mapping_ebt (cost=0.00..1779.03 rows=36003
width=68) (actual time=3.023..76.096 rows=12001 loops=1)
Total runtime: 894.888 ms
(2 rows)
Of course that, less rows, less time, but how can 12k rows take
less than one second, and 66k rows take more than one minute?
I've read some stuff about PgSQL tuning, and played with the
configuration files, but I keep getting the feeling that I'm doing
this in a blind way. I'm trying to guess the problem and avoid it. I'm
sure there's a better way, but I can't seem to find it. My question
is, how can I "ask" PgSQL what's happening? How can I avoid guessing,
and be sure of what is causing this slowdown? Is some buffer too small
for this? Is this related to checkpoints?
I would appreciate if someone could point me in the right
direction. Of course I don't need to say I'm relatively new to this
kind of problems. :)
Yours
Miguel Arroz
Miguel Arroz
http://www.terminalapp.net
http://www.ipragma.com
