To: Jon Nelson <jnelson+pgsql@jamponi.net>
cc: pgsql-performance@postgresql.org
Subject: Re: plan variations: join vs. exists vs. row comparison 
In-reply-to: <AANLkTikAH2aWpo78cW13AJDvxwgu=NsEokiTfjWQdjp3@mail.gmail.com> 
References: <AANLkTikAH2aWpo78cW13AJDvxwgu=NsEokiTfjWQdjp3@mail.gmail.com>
Comments: In-reply-to Jon Nelson <jnelson+pgsql@jamponi.net>
	message dated "Mon, 07 Mar 2011 13:07:35 -0600"
Date: Mon, 07 Mar 2011 15:00:10 -0500
Message-ID: <11707.1299528010@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jon Nelson <jnelson+pgsql@jamponi.net> 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