>>> On Mon, Oct 22, 2007 at  5:04 PM, in message
<[EMAIL PROTECTED]>, "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);
                                                  QUERY PLAN
 Merge IN Join  (cost=825315.30..1265285.81 rows=2010418 width=6)
   Merge Cond: ((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.18 rows=2010418 width=12)
               Merge Cond: ((public."Body"."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."

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?


Reply via email to