From: Michael Monnerie <michael.monnerie@is.it-management.at>
To: pgsql-admin@postgresql.org
Subject: Optimizing Update with WHERE x IN (id1,id2,...)
Date: Wed, 22 Dec 2010 10:59:37 +0100
Message-ID: <201012221059.38145@zmi.at> (raw)
Dear devs,
I just saw this EXPLAIN in PostgreSQL 8.3.12:
EXPLAIN UPDATE dbmail_messages SET recent_flag = 0 WHERE message_idnr IN
(3506054,3506052,3506050,3506048,3506046,3506044,3506042,3506040,3506038,3506036,3506034,3506032,3506030)
AND recent_flag = 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on dbmail_messages (cost=55.39..105.04 rows=1
width=75)
Recheck Cond: (message_idnr = ANY
('{3506054,3506052,3506050,3506048,3506046,3506044,3506042,3506040,3506038,3506036,3506034,3506032,3506030}'::bigint[]))
Filter: (recent_flag = 1)
-> Bitmap Index Scan on dbmail_messages_pkey (cost=0.00..55.39
rows=13 width=0)
Index Cond: (message_idnr = ANY
('{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.
--
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc
it-management Internet Services: Protéger
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
//
// Haus zu verkaufen: http://zmi.at/langegg/Attachments:
signature.asc (application/pgp-signature, 197B) 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-admin@postgresql.org
Cc: michael.monnerie@is.it-management.at
Subject: Re: Optimizing Update with WHERE x IN (id1,id2,...)
In-Reply-To: <201012221059.38145@zmi.at>
* 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