Date: Tue, 14 Feb 2006 12:20:22 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Jay Greenfield <jag@timberline.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres slower than MS ACCESS
Message-ID: <20060214172022.GC4474@ns.snowman.net>
Mail-Followup-To: Jay Greenfield <jag@timberline.ca>,
	pgsql-performance@postgresql.org
References: <200602141551.k1EFpN6F020512@timberline.ca>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="wahTu2FS/smxRA7d"
Content-Disposition: inline
In-Reply-To: <200602141551.k1EFpN6F020512@timberline.ca>
User-Agent: Mutt/1.5.11


--wahTu2FS/smxRA7d
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Jay Greenfield (jag@timberline.ca) wrote:
> Database has one table with 1.2 million rows
> Query:
>=20
> UPDATE ntdn SET gha=3Darea/10000
>=20
> 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=20
mean explain verbose, though I don't really see how that'd be so long=20
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.
>=20
> 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
>=20
> 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=20
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

--wahTu2FS/smxRA7d
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFD8hFWrzgMPqB3kigRAp9WAJ9eeECf1tV9jOQNH1qWEeq1pGa6hgCfRlZM
9rzEn6mVVcY9JrDoQn6yXpM=
=VMWg
-----END PGP SIGNATURE-----

--wahTu2FS/smxRA7d--