> How do you get 4,000+ lines of explain analyze for one update query in a
> database with only one table? Something a bit fishy there. Perhaps you
> mean explain verbose, though I don't really see how that'd be so long
> either, but it'd be closer. Could you provide some more sane
> information?
My mistake - there was 4,000 lines in the EXPLAIN ANALYZE VERBOSE output.
Here is the output of EXPLAIN ANALYZE:
QUERY PLAN
"Seq Scan on ntdn (cost=0.00..3471884.39 rows=1221391 width=1592) (actual
time=57292.580..1531300.003 rows=1221391 loops=1)"
"Total runtime: 4472646.988 ms"
> Is this table exceptionally large in same way (ie: lots
> of columns)?
The table is 1.2 million rows X 246 columns. The only index is the primary
key. I will try to remove that index to see if that improves performance at
all.
Jay
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, February 14, 2006 12:43 PM
To: Stephen Frost
Cc: Jay Greenfield; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Postgres slower than MS ACCESS
Stephen Frost <sfrost@snowman.net> writes:
> While it's true that Access almost certainly takes some shortcuts, 24
> minutes for an update across 1.2 millon rows seems an awefully long time
> for Postgres.
I did some experiments along this line with a trivial table (2 integer
columns) of 1.28M rows. I used CVS tip with all parameters at defaults.
With no indexes, an UPDATE took about 50 seconds. With one index, it
took 628 seconds. It's not hard to believe you could get to Jay's
figures with multiple indexes.
Looking in the postmaster log, I see I was getting checkpoints every few
seconds. Increasing checkpoint_segments to 30 (a factor of 10) brought
it down to 355 seconds, and then increasing shared_buffers to 20000
brought it down to 165 sec. Separating WAL and data onto different
disks would have helped too, no doubt, but I'm too lazy to try it.
regards, tom lane