Обсуждение: Optimizing Update with WHERE x IN (id1,id2,...)

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/

Michael Monnerie <michael.monnerie@is.it-management.at> writes:
> I just saw this EXPLAIN in PostgreSQL 8.3.12:
> [ just like any other bitmap index scan ]
> 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.

You evidently don't understand what that's for.  In typical cases it's
not rechecked.  The recheck condition is only applied if the index scan
becomes lossy.

            regards, tom lane