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
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
