pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizing Update with WHERE x IN (id1,id2,...)
2+ messages / 2 participants
[nested] [flat]
* Optimizing Update with WHERE x IN (id1,id2,...)
@ 2010-12-22 09:59 Michael Monnerie <michael.monnerie@is.it-management.at>
0 siblings, 1 reply; 2+ messages in thread
From: Michael Monnerie @ 2010-12-22 09:59 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Optimizing Update with WHERE x IN (id1,id2,...)
@ 2010-12-22 15:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Monnerie <michael.monnerie@is.it-management.at>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-12-22 15:23 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@is.it-management.at>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-12-22 15:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-22 09:59 Optimizing Update with WHERE x IN (id1,id2,...) Michael Monnerie <michael.monnerie@is.it-management.at>
2010-12-22 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
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