Message-Id: <200602152129.k1FLTpiO008671@timberline.ca>
From: "Jay Greenfield" <jag@timberline.ca>
To: <pgsql-performance@postgresql.org>
Subject: Re: Postgres slower than MS ACCESS 
Date: Wed, 15 Feb 2006 13:29:51 -0800
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcYyO2oHDK0P9LqwQsCIzVv3ZfSeLwAKmD6g
In-reply-to: <24B89C42-C597-4571-8BD1-A89073C3C035@torgo.978.org>


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