Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 0481C1337B69 for ; Wed, 22 Dec 2010 06:05:06 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 95258-09 for ; Wed, 22 Dec 2010 10:04:58 +0000 (UTC) X-Greylist: delayed 00:05:17.253531 by SQLgrey-1.7.6 Received: from mailsrv14.zmi.at (mailsrv1.zmi.at [212.69.164.54]) by mail.postgresql.org (Postfix) with ESMTP id 2CB731337999 for ; Wed, 22 Dec 2010 06:04:58 -0400 (AST) Received: from mailsrv.i.zmi.at (h081217106033.dyn.cm.kabsi.at [81.217.106.33]) (using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits)) (Client CN "mailsrv2.i.zmi.at", Issuer "power4u.zmi.at" (not verified)) by mailsrv14.zmi.at (Postfix) with ESMTPSA id 2AFF0400 for ; Wed, 22 Dec 2010 10:59:39 +0100 (CET) Received: from saturn.localnet (saturn.i.zmi.at [10.72.27.2]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client did not present a certificate) by mailsrv.i.zmi.at (Postfix) with ESMTPSA id 60477401C3E for ; Wed, 22 Dec 2010 10:59:38 +0100 (CET) To: pgsql-admin@postgresql.org Subject: Optimizing Update with WHERE x IN (id1,id2,...) From: Michael Monnerie Organization: it-management http://it-management.at Date: Wed, 22 Dec 2010 10:59:37 +0100 MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart8267947.QAEYHKO7xu"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <201012221059.38145@zmi.at> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201012/160 X-Sequence-Number: 36389 --nextPart8267947.QAEYHKO7xu Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Dear devs, I just saw this EXPLAIN in PostgreSQL 8.3.12: EXPLAIN UPDATE dbmail_messages SET recent_flag =3D 0 WHERE message_idnr IN= =20 (3506054,3506052,3506050,3506048,3506046,3506044,3506042,3506040,3506038,35= 06036,3506034,3506032,3506030)=20 AND recent_flag =3D 1; = QUERY=20 PLAN = =20 =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D--------------- Bitmap Heap Scan on dbmail_messages (cost=3D55.39..105.04 rows=3D1=20 width=3D75) Recheck Cond: (message_idnr =3D ANY=20 ('{3506054,3506052,3506050,3506048,3506046,3506044,3506042,3506040,3506038,= 3506036,3506034,3506032,3506030}'::bigint[])) Filter: (recent_flag =3D 1) -> Bitmap Index Scan on dbmail_messages_pkey (cost=3D0.00..55.39=20 rows=3D13 width=3D0) Index Cond: (message_idnr =3D ANY=20 ('{3506054,3506052,3506050,3506048,3506046,3506044,3506042,3506040,3506038,= 3506036,3506034,3506032,3506030}'::bigint[])) (5 rows) And it seems to me that could be optimized to skip the "Recheck Cond" as that is already checked by the Index Scan? Should save some CPU cycles. =2D-=20 mit freundlichen Gr=C3=BCssen, Michael Monnerie, Ing. BSc it-management Internet Services: Prot=C3=A9ger http://proteger.at [gesprochen: Prot-e-schee] Tel: +43 660 / 415 6531 // ****** Radiointerview zum Thema Spam ****** // http://www.it-podcast.at/archiv.html#podcast-100716 //=20 // Haus zu verkaufen: http://zmi.at/langegg/ --nextPart8267947.QAEYHKO7xu Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) iEYEABECAAYFAk0RzAoACgkQzhSR9xwSCbRTEgCgy7/R47xvBDHDmxwFqjk/R5RB vV8AoMQ0PoehP3EpurmaxxIfIdzrCmD3 =kLr/ -----END PGP SIGNATURE----- --nextPart8267947.QAEYHKO7xu--