>>> On Mon, Oct 22, 2007 at 5:04 PM, in message
<471CD819.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
> Oops. That is not logically equivalent. We want to delete WHERE NOT
> EXISTS; the logic of that suggestion is backwards.
>
> Disregard that last post, please.
Maybe that last post shouldn't be totally disregarded -- it wouldn't
be a bad idea to support a Merge NOT IN Join if it the effort isn't
out of line with the benefit.
Pavel suggested a clever kludge to accomplish this, which costs out
better than anything else I've tried:
step=# explain DELETE FROM "Body"
step-# WHERE "bodySeqNo" IN (SELECT "Body"."bodySeqNo"
step(# FROM "Body"
step(# LEFT JOIN "Message"
step(# ON "Body"."bodySeqNo" = "Message"."bodySeqNo"
step(# WHERE "Message"."bodySeqNo" IS NULL);
QUERYPLAN
--------------------------------------------------------------------------------------------------------------Merge IN
Join (cost=825315.30..1265285.81 rows=2010418 width=6) Merge Cond: ((public."Body"."bodySeqNo")::numeric =
(public."Body"."bodySeqNo")::numeric) -> Index Scan using "Body_pkey" on "Body" (cost=0.00..383702.32 rows=4020835
width=18) -> Materialize (cost=825315.30..846401.18 rows=2010418 width=12) -> Merge Left Join
(cost=0.00..822323.18rows=2010418 width=12) Merge Cond: ((public."Body"."bodySeqNo")::numeric =
("Message"."bodySeqNo")::numeric) Filter: ("Message"."bodySeqNo" IS NULL) -> Index Scan using
"Body_pkey"on "Body" (cost=0.00..383702.32 rows=4020835 width=12) -> Index Scan using "Message_Body" on
"Message" (cost=0.00..378901.17 rows=4021733 width=12)
(9 rows)
Just some ideas to look at while you're "in the neighborhood."
-Kevin
