Return-Path: Mailing-List: contact turbine-torque-user-help@jakarta.apache.org; run by ezmlm Delivered-To: mailing list turbine-torque-user@jakarta.apache.org Received: (qmail 26972 invoked by uid 98); 24 Jan 2003 13:49:34 -0000 X-Antivirus: nagoya (v4218 created Aug 14 2002) Received: (qmail 26939 invoked from network); 24 Jan 2003 13:49:32 -0000 Received: from unknown (HELO apache.org) (208.185.179.12) by nagoya.betaversion.org with SMTP; 24 Jan 2003 13:49:32 -0000 Received: (qmail 72065 invoked by uid 500); 24 Jan 2003 13:48:08 -0000 Received: (qmail 72054 invoked from network); 24 Jan 2003 13:48:08 -0000 Received: from unknown (HELO smtp3.crystaltech.com) (216.119.106.212) by 208.185.179.12.available.above.net with SMTP; 24 Jan 2003 13:48:08 -0000 Received: (qmail 31818 invoked from network); 24 Jan 2003 13:51:37 -0000 Received: from mail1.crystaltech.com (HELO webterminator19.CRYSTALTECH.COM) (216.119.106.20) by smtp3.crystaltech.com with SMTP; 24 Jan 2003 13:51:37 -0000 Received: from pool-151-201-11-254.pitt.east.verizon.net [151.201.11.254] by webterminator19.CRYSTALTECH.COM with ESMTP (SMTPD32-7.13) id A41512B00B2; Fri, 24 Jan 2003 06:48:05 -0700 Subject: UPDATEs taking a long time From: Patryk Laurent To: Turbine Torque Users List In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-u4y8Cn4qIQgxM9ByUG+V" X-Mailer: Ximian Evolution 1.0.8 (1.0.8-10) Date: 24 Jan 2003 08:51:39 -0500 Message-Id: <1043416301.16807.571.camel@tanooki> Mime-Version: 1.0 X-Spam-Rating: 208.185.179.12.available.above.net 1.6.2 0/1000/N --=-u4y8Cn4qIQgxM9ByUG+V Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Greetings, I have discovered that UPDATEs in PostgreSQL of the following structure UPDATE mytable SET mycolumn=3D# where id=3D# uses Sequential scans (try EXPLAIN, you get Seq Scan) when id is an indexed primary key (bigint) generated by a sequence. It is very slow for large tables. (To do the UPDATE in Torque, I was just taking the old object, oldRow, and doing oldRow.setmycolumn(newValue); oldRow.save(); ) To get an Index Scan, it turns out you must enclose the ID number in single quotes in your query, like this: UPDATE mytable SET mycolumn=3D# where id=3D'#' Are any of the Torque developers aware of this bug/feature? Torque is clearly using the sequential scan (it was taking me about 5 seconds to update a single row in a 3,000,000 row table, just like when I didn't use quotes.) Any feedback on this would be much appreciated. Thanks, Patryk Senior Programmer SUPERnatural tools, Inc. --=-u4y8Cn4qIQgxM9ByUG+V Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQA+MUTrNg67vCDdkt4RAm2HAJ4m2V4tyZJ87l78nZD05G+6HSL2MgCcDvP0 s7TucM3DGWfv++6f4s0zK+k= =PPbP -----END PGP SIGNATURE----- --=-u4y8Cn4qIQgxM9ByUG+V--