From: Stephen Frost <sfrost@snowman.net>
To: Jay Greenfield <jag@timberline.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres slower than MS ACCESS
Date: Tue, 14 Feb 2006 12:20:22 -0500
Message-ID: <20060214172022.GC4474@ns.snowman.net> (raw)
In-Reply-To: <200602141551.k1EFpN6F020512@timberline.ca>
References: <200602141551.k1EFpN6F020512@timberline.ca>
* Jay Greenfield (jag@timberline.ca) wrote:
> Database has one table with 1.2 million rows
> Query:
>
> UPDATE ntdn SET gha=area/10000
>
> I could post the EXPLAIN ANALYZE results but its 4,000+ lines long
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?
> I've run various tests on a number of Postgres parameters; none of which
> have come close to Access' time of 5.00 min. Postgres times range between
> 24 min and 121 min.
>
> Some of the Postgres variables and ranges I've tested.
> work_mem: 1,000 to 2,000,000
> temp_buffers: 1,000 to 10,000
> shared_buffers: 1,000 to 64,000
> sort_mem: 1,024,000
> fsync on / off
>
> Why does Access run so much faster? How can I get Postgres to run as fast
> as Access?
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. Is this table exceptionally large in same way (ie: lots
of columns)? I expect running with fsync off would be closer to 'Access
mode' though it has risks (of course). Also, it might be faster to
insert into a seperate table rather than run a huge update like that in
Postgres. Also, if there are indexes on the table in question, you
might drop them before doing the update/insert and recreate them after
the query has finished.
You really havn't provided anywhere near enough information to figure
out what the actual problem is here. Access does take shortcuts but the
times you're posting for Postgres seem quite far off based on the
hardware and commands you've described...
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 188B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sfrost@snowman.net, jag@timberline.ca
Subject: Re: Postgres slower than MS ACCESS
In-Reply-To: <20060214172022.GC4474@ns.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox