I've been vacuuming between each test run. Not vacuuming results in times all the way up to 121 minutes. For a direct comparison with Access, the vacuuming time with Postgres should really be included as this is not required with Access. By removing all of the indexes I have been able to get the Postgres time down to 4.35 minutes with default setting for all except the following: fsync: off work_mem: 1024000 shared_buffers: 10000 I did a run with checkpoint_segments @ 30 (from 3 in 4.35 min run) and posted a time of 6.78 minutes. Any idea why this would increase the time? Thanks, Jay. -----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Jeff Trout Sent: Wednesday, February 15, 2006 6:23 AM To: Jay Greenfield Cc: 'Tom Lane'; 'Stephen Frost'; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: [PERFORM] Postgres slower than MS ACCESS On Feb 14, 2006, at 3:56 PM, Jay Greenfield wrote: >> 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" > Have you been vacuuming or running autovacuum? If you keep running queries like this you're certianly going to have a ton of dead tuples, which would def explain these times too. -- Jeff Trout <jeff@xxxxxxxxxxxxx> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
