Jon Nelson <jnelson+pgsql@xxxxxxxxxxx> writes: > I was hoping that somebody could help me understand the differences > between three plans. > All of the plans are updating a table using a second table, and should > be logically equivalent. > Two of the plans use joins, and one uses an exists subquery. > One of the plans uses row constructors and IS NOT DISTINCT FROM. It is > this plan which has really awful performance. > Clearly it is due to the nested loop, but why would the planner choose > that approach? IS NOT DISTINCT FROM pretty much disables all optimizations: it can't be an indexqual, merge join qual, or hash join qual. So it's not surprising that you get a sucky plan for it. Possibly somebody will work on improving that someday. As for your other questions, what PG version are you using? Because I do get pretty much the same plan (modulo a plain join versus a semijoin) for the first two queries, when using 9.0 or later. And the results of ANALYZE are only approximate, so you shouldn't be surprised at all if a rowcount estimate is off by a couple percent. Most of the time, you should be happy if it's within a factor of 2 of reality. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
