From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: plan variations: join vs. exists vs. row comparison
Date: Mon, 07 Mar 2011 15:00:10 -0500
Message-ID: <11707.1299528010@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTikAH2aWpo78cW13AJDvxwgu=NsEokiTfjWQdjp3@mail.gmail.com>
References: <AANLkTikAH2aWpo78cW13AJDvxwgu=NsEokiTfjWQdjp3@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jnelson+pgsql@jamponi.net
Subject: Re: plan variations: join vs. exists vs. row comparison
In-Reply-To: <11707.1299528010@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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