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@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jeff Trout
Sent: Wednesday, February 15, 2006 6:23 AM
To: Jay Greenfield
Cc: 'Tom Lane'; 'Stephen Frost'; pgsql-performance@postgresql.org
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@jefftrout.com>
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